diff options
| -rw-r--r-- | mysql-test/include/check_concurrent_insert.inc | 90 | ||||
| -rw-r--r-- | mysql-test/include/check_no_concurrent_insert.inc | 75 | ||||
| -rw-r--r-- | mysql-test/include/check_no_row_lock.inc | 71 | ||||
| -rw-r--r-- | mysql-test/include/check_shared_row_lock.inc | 61 | ||||
| -rw-r--r-- | mysql-test/r/bug39022.result | 6 | ||||
| -rw-r--r-- | mysql-test/r/innodb_mysql_lock2.result | 564 | ||||
| -rw-r--r-- | mysql-test/r/lock_sync.result | 592 | ||||
| -rw-r--r-- | mysql-test/t/bug39022.test | 6 | ||||
| -rw-r--r-- | mysql-test/t/innodb_mysql_lock2.test | 765 | ||||
| -rw-r--r-- | mysql-test/t/lock_sync.test | 820 | ||||
| -rw-r--r-- | sql/log_event.cc | 23 | ||||
| -rw-r--r-- | sql/log_event.h | 16 | ||||
| -rw-r--r-- | sql/sp_head.cc | 3 | ||||
| -rw-r--r-- | sql/sql_acl.cc | 6 | ||||
| -rw-r--r-- | sql/sql_base.cc | 32 | ||||
| -rw-r--r-- | sql/sql_base.h | 4 | ||||
| -rw-r--r-- | sql/sql_lex.cc | 7 | ||||
| -rw-r--r-- | sql/sql_lex.h | 54 | ||||
| -rw-r--r-- | sql/sql_load.cc | 10 | ||||
| -rw-r--r-- | sql/sql_parse.cc | 1 | ||||
| -rw-r--r-- | sql/sql_priv.h | 6 | ||||
| -rw-r--r-- | sql/sql_select.cc | 3 | ||||
| -rw-r--r-- | sql/sql_show.cc | 10 | ||||
| -rw-r--r-- | sql/sql_table.cc | 6 | ||||
| -rw-r--r-- | sql/sql_trigger.cc | 7 | ||||
| -rw-r--r-- | sql/sql_update.cc | 2 | ||||
| -rw-r--r-- | sql/sql_yacc.yy | 52 |
27 files changed, 3205 insertions, 87 deletions
diff --git a/mysql-test/include/check_concurrent_insert.inc b/mysql-test/include/check_concurrent_insert.inc new file mode 100644 index 00000000000..7a7ef7de786 --- /dev/null +++ b/mysql-test/include/check_concurrent_insert.inc @@ -0,0 +1,90 @@ +# +# SUMMARY +# Check if statement reading table '$table' allows concurrent +# inserts in it. +# +# PARAMETERS +# $table Table in which concurrent inserts should be allowed. +# $con_aux1 Name of the first auxiliary connection to be used by this +# script. +# $con_aux2 Name of the second auxiliary connection to be used by this +# script. +# $statement Statement to be checked. +# $restore_table Table which might be modified affected by statement to be +# checked and thus needs backing up before its execution +# and restoring after it (can be empty). +# +# EXAMPLE +# lock_sync.test +# +--disable_result_log +--disable_query_log + +if (`SELECT '$restore_table' <> ''`) +{ +--eval create table t_backup select * from $restore_table; +} + +connection $con_aux1; +set debug_sync='after_lock_tables_takes_lock SIGNAL parked WAIT_FOR go'; +--send_eval $statement; + +connection $con_aux2; +set debug_sync='now WAIT_FOR parked'; +--send_eval insert into $table values (0); + +--enable_result_log +--enable_query_log +connection default; +# Wait until concurrent insert is successfully executed while +# statement being checked has its tables locked. +# We use wait_condition.inc instead of simply executing +# concurrent insert here in order to avoid deadlocks if test +# fails and timing out instead. +let $wait_condition= + select count(*) = 0 from information_schema.processlist + where info = "insert into $table values (0)"; +--source include/wait_condition.inc + +--disable_result_log +--disable_query_log + +if ($success) +{ +# Apparently concurrent insert was successfully executed. +# To be safe against wait_condition.inc succeeding due to +# races let us first reap concurrent insert to ensure that +# it has really been successfully executed. +connection $con_aux2; +--reap +connection default; +set debug_sync= 'now SIGNAL go'; +connection $con_aux1; +--reap +connection default; +--echo Success: '$statement' allows concurrent inserts into '$table'. +} +if (!$success) +{ +# Waiting has timed out. Apparently concurrent insert was blocked. +# So to be able to continue we need to end our statement first. +set debug_sync= 'now SIGNAL go'; +connection $con_aux1; +--reap +connection $con_aux2; +--reap +connection default; +--echo Error: '$statement' doesn't allow concurrent inserts into '$table'! +} + +--eval delete from $table where i = 0; + +if (`SELECT '$restore_table' <> ''`) +{ +--eval truncate table $restore_table; +--eval insert into $restore_table select * from t_backup; +drop table t_backup; +} + +--enable_result_log +--enable_query_log diff --git a/mysql-test/include/check_no_concurrent_insert.inc b/mysql-test/include/check_no_concurrent_insert.inc new file mode 100644 index 00000000000..856e1eca4ac --- /dev/null +++ b/mysql-test/include/check_no_concurrent_insert.inc @@ -0,0 +1,75 @@ +# +# SUMMARY +# Check that statement reading table '$table' doesn't allow concurrent +# inserts in it. +# +# PARAMETERS +# $table Table in which concurrent inserts should be disallowed. +# $con_aux1 Name of the first auxiliary connection to be used by this +# script. +# $con_aux2 Name of the second auxiliary connection to be used by this +# script. +# $statement Statement to be checked. +# $restore_table Table which might be modified affected by statement to be +# checked and thus needs backing up before its execution +# and restoring after it (can be empty). +# +# EXAMPLE +# lock_sync.test +# +--disable_result_log +--disable_query_log + +if (`SELECT '$restore_table' <> ''`) +{ +--eval create table t_backup select * from $restore_table; +} + +connection $con_aux1; +set debug_sync='after_lock_tables_takes_lock SIGNAL parked WAIT_FOR go'; +--send_eval $statement; + +connection $con_aux2; +set debug_sync='now WAIT_FOR parked'; +--send_eval insert into $table values (0); + +--enable_result_log +--enable_query_log +connection default; +# Wait until concurrent insert is successfully blocked because +# of our statement. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state = "Table lock" and info = "insert into $table values (0)"; +--source include/wait_condition.inc + +--disable_result_log +--disable_query_log + +set debug_sync= 'now SIGNAL go'; +connection $con_aux1; +--reap +connection $con_aux2; +--reap +connection default; + +if ($success) +{ +--echo Success: '$statement' doesn't allow concurrent inserts into '$table'. +} +if (!$success) +{ +--echo Error: '$statement' allows concurrent inserts into '$table'! +} + +--eval delete from $table where i = 0; + +if (`SELECT '$restore_table' <> ''`) +{ +--eval truncate table $restore_table; +--eval insert into $restore_table select * from t_backup; +drop table t_backup; +} + +--enable_result_log +--enable_query_log diff --git a/mysql-test/include/check_no_row_lock.inc b/mysql-test/include/check_no_row_lock.inc new file mode 100644 index 00000000000..958161b9b7f --- /dev/null +++ b/mysql-test/include/check_no_row_lock.inc @@ -0,0 +1,71 @@ +# +# SUMMARY +# Check if statement affecting or reading table '$table' doesn't +# take any kind of locks on its rows. +# +# PARAMETERS +# $table Table for which presence of row locks should be checked. +# $con_aux Name of auxiliary connection to be used by this script. +# $statement Statement to be checked. +# +# EXAMPLE +# innodb_mysql_lock2.test +# +--disable_result_log +--disable_query_log + +connection default; +begin; +--eval select * from $table for update; + +connection $con_aux; +begin; +--send_eval $statement; + +--enable_result_log +--enable_query_log + +connection default; +# Wait until statement is successfully executed while +# all rows in table are X-locked. This means that it +# does not acquire any row locks. +# We use wait_condition.inc instead of simply executing +# statement here in order to avoid deadlocks if test +# fails and timing out instead. +let $wait_condition= + select count(*) = 0 from information_schema.processlist + where info = "$statement"; +--source include/wait_condition.inc + +--disable_result_log +--disable_query_log + +if ($success) +{ +# Apparently statement was successfully executed and thus it +# has not required any row locks. +# To be safe against wait_condition.inc succeeding due to +# races let us first reap the statement being checked to +# ensure that it has been successfully executed. +connection $con_aux; +--reap +rollback; +connection default; +rollback; +--echo Success: '$statement' doesn't take row locks on '$table'. +} +if (!$success) +{ +# Waiting has timed out. Apparently statement was blocked on +# some row lock. So to be able to continue we need to unlock +# rows first. +rollback; +connection $con_aux; +--reap +rollback; +connection default; +--echo Error: '$statement' takes some row locks on '$table'! +} + +--enable_result_log +--enable_query_log diff --git a/mysql-test/include/check_shared_row_lock.inc b/mysql-test/include/check_shared_row_lock.inc new file mode 100644 index 00000000000..efc7e13b3aa --- /dev/null +++ b/mysql-test/include/check_shared_row_lock.inc @@ -0,0 +1,61 @@ +# +# SUMMARY +# Check if statement reading table '$table' takes shared locks +# on some of its rows. +# +# PARAMETERS +# $table Table for which presence of row locks should be checked. +# $con_aux Name of auxiliary connection to be used by this script. +# $statement Statement to be checked. +# $wait_statement Sub-statement which is supposed to acquire locks (should +# be the same as $statement for ordinary statements). +# +# EXAMPLE +# innodb_mysql_lock2.test +# +--disable_result_log +--disable_query_log + +connection default; +begin; +--eval select * from $table for update; + +connection $con_aux; +begin; +--send_eval $statement; + +--enable_result_log +--enable_query_log + +connection default; +# Wait until statement is successfully blocked because +# all rows in table are X-locked. This means that at +# least it acquires S-locks on some of rows. +let $wait_condition= + select count(*) = 1 from information_schema.processlist + where state in ("Sending data","statistics", "preparing") and + info = "$wait_statement"; +--source include/wait_condition.inc + +--disable_result_log +--disable_query_log + +rollback; + +connection $con_aux; +--reap +rollback; + +connection default; +--enable_result_log +--enable_query_log + +if ($success) +{ +--echo Success: '$statement' takes shared row locks on '$table'. +} + +if (!$success) +{ +--echo Error: '$statement' hasn't taken shared row locks on '$table'! +} diff --git a/mysql-test/r/bug39022.result b/mysql-test/r/bug39022.result index 5963709aa2a..75899ed686b 100644 --- a/mysql-test/r/bug39022.result +++ b/mysql-test/r/bug39022.result @@ -12,7 +12,7 @@ INSERT INTO t2 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10), START TRANSACTION; # in thread2 REPLACE INTO t2 VALUES (-17); -SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d); +SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d) LOCK IN SHARE MODE; d # in thread1 REPLACE INTO t1(a,b) VALUES (67,20); @@ -21,10 +21,10 @@ COMMIT; START TRANSACTION; REPLACE INTO t1(a,b) VALUES (65,-50); REPLACE INTO t2 VALUES (-91); -SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d); +SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d) LOCK IN SHARE MODE; # in thread1 # should not crash -SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d); +SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d) LOCK IN SHARE MODE; ERROR 40001: Deadlock found when trying to get lock; try restarting transaction # in thread2 d diff --git a/mysql-test/r/innodb_mysql_lock2.result b/mysql-test/r/innodb_mysql_lock2.result new file mode 100644 index 00000000000..aed704e6b3e --- /dev/null +++ b/mysql-test/r/innodb_mysql_lock2.result @@ -0,0 +1,564 @@ +# +# Test how do we handle locking in various cases when +# we read data from InnoDB tables. +# +# In fact by performing this test we check two things: +# 1) That SQL-layer correctly determine type of thr_lock.c +# lock to be acquired/passed to InnoDB engine. +# 2) That InnoDB engine correctly interprets this lock +# type and takes necessary row locks or does not +# take them if they are not necessary. +# +# This test makes sense only in REPEATABLE-READ mode as +# in SERIALIZABLE mode all statements that read data take +# shared lock on them to enforce its semantics. +select @@session.tx_isolation; +@@session.tx_isolation +REPEATABLE-READ +# Prepare playground by creating tables, views, +# routines and triggers used in tests. +drop table if exists t0, t1, t2, t3, t4, t5; +drop view if exists v1, v2; +drop procedure if exists p1; +drop procedure if exists p2; +drop function if exists f1; +drop function if exists f2; +drop function if exists f3; +drop function if exists f4; +drop function if exists f5; +drop function if exists f6; +drop function if exists f7; +drop function if exists f8; +drop function if exists f9; +drop function if exists f10; +drop function if exists f11; +drop function if exists f12; +drop function if exists f13; +drop function if exists f14; +drop function if exists f15; +create table t1 (i int primary key) engine=innodb; +insert into t1 values (1), (2), (3), (4), (5); +create table t2 (j int primary key) engine=innodb; +insert into t2 values (1), (2), (3), (4), (5); +create table t3 (k int primary key) engine=innodb; +insert into t3 values (1), (2), (3); +create table t4 (l int primary key) engine=innodb; +insert into t4 values (1); +create table t5 (l int primary key) engine=innodb; +insert into t5 values (1); +create view v1 as select i from t1; +create view v2 as select j from t2 where j in (select i from t1); +create procedure p1(k int) insert into t2 values (k); +create function f1() returns int +begin +declare j int; +select i from t1 where i = 1 into j; +return j; +end| +create function f2() returns int +begin +declare k int; +select i from t1 where i = 1 into k; +insert into t2 values (k + 5); +return 0; +end| +create function f3() returns int +begin +return (select i from t1 where i = 3); +end| +create function f4() returns int +begin +if (select i from t1 where i = 3) then +return 1; +else +return 0; +end if; +end| +create function f5() returns int +begin +insert into t2 values ((select i from t1 where i = 1) + 5); +return 0; +end| +create function f6() returns int +begin +declare k int; +select i from v1 where i = 1 into k; +return k; +end| +create function f7() returns int +begin +declare k int; +select j from v2 where j = 1 into k; +return k; +end| +create function f8() returns int +begin +declare k int; +select i from v1 where i = 1 into k; +insert into t2 values (k+5); +return k; +end| +create function f9() returns int +begin +update v2 set j=j+10 where j=1; +return 1; +end| +create function f10() returns int +begin +return f1(); +end| +create function f11() returns int +begin +declare k int; +set k= f1(); +insert into t2 values (k+5); +return k; +end| +create function f12(p int) returns int +begin +insert into t2 values (p); +return p; +end| +create function f13(p int) returns int +begin +return p; +end| +create procedure p2(inout p int) +begin +select i from t1 where i = 1 into p; +end| +create function f14() returns int +begin +declare k int; +call p2(k); +insert into t2 values (k+5); +return k; +end| +create function f15() returns int +begin +declare k int; +call p2(k); +return k; +end| +create trigger t4_bi before insert on t4 for each row +begin +declare k int; +select i from t1 where i=1 into k; +set new.l= k+1; +end| +create trigger t4_bu before update on t4 for each row +begin +if (select i from t1 where i=1) then +set new.l= 2; +end if; +end| +create trigger t4_bd before delete on t4 for each row +begin +if !(select i from v1 where i=1) then +signal sqlstate '45000'; +end if; +end| +create trigger t5_bi before insert on t5 for each row +begin +set new.l= f1()+1; +end| +create trigger t5_bu before update on t5 for each row +begin +declare j int; +call p2(j); +set new.l= j + 1; +end| +# +# Set common variables to be used by scripts called below. +# +# +# 1. Statements that read tables and do not use subqueries. +# +# +# 1.1 Simple SELECT statement. +# +# No locks are necessary as this statement won't be written +# to the binary log and thanks to how MyISAM works SELECT +# will see version of the table prior to concurrent insert. +Success: 'select * from t1' doesn't take row locks on 't1'. +# +# 1.2 Multi-UPDATE statement. +# +# Has to take shared locks on rows in the table being read as this +# statement will be written to the binary log and therefore should +# be serialized with concurrent statements. +Success: 'update t2, t1 set j= j - 1 where i = j' takes shared row locks on 't1'. +# +# 1.3 Multi-DELETE statement. +# +# The above is true for this statement as well. +Success: 'delete t2 from t1, t2 where i = j' takes shared row locks on 't1'. +# +# 1.4 DESCRIBE statement. +# +# This statement does not really read data from the +# target table and thus does not take any lock on it. +# We check this for completeness of coverage. +Success: 'describe t1' doesn't take row locks on 't1'. +# +# 1.5 SHOW statements. +# +# The above is true for SHOW statements as well. +Success: 'show create table t1' doesn't take row locks on 't1'. +Success: 'show keys from t1' doesn't take row locks on 't1'. +# +# 2. Statements which read tables through subqueries. +# +# +# 2.1 CALL with a subquery. +# +# A strong lock is not necessary as this statement is not +# written to the binary log as a whole (it is written +# statement-by-statement) and thanks to MVCC we can always get +# versions of rows prior to the update that has locked them. +# But in practice InnoDB does locking reads for all statements +# other than SELECT (unless it is a READ-COMITTED mode or +# innodb_locks_unsafe_for_binlog is ON). +Success: 'call p1((select i + 5 from t1 where i = 1))' takes shared row locks on 't1'. +# +# 2.2 CREATE TABLE with a subquery. +# +# Has to take shared locks on rows in the table being read as +# this statement is written to the binary log and therefore +# should be serialized with concurrent statements. +Success: 'create table t0 engine=innodb select * from t1' takes shared row locks on 't1'. +drop table t0; +Success: 'create table t0 engine=innodb select j from t2 where j in (select i from t1)' takes shared row locks on 't1'. +drop table t0; +# +# 2.3 DELETE with a subquery. +# +# The above is true for this statement as well. +Success: 'delete from t2 where j in (select i from t1)' takes shared row locks on 't1'. +# +# 2.4 MULTI-DELETE with a subquery. +# +# Same is true for this statement as well. +Success: 'delete t2 from t3, t2 where k = j and j in (select i from t1)' takes shared row locks on 't1'. +# +# 2.5 DO with a subquery. +# +# In theory should not take row locks as it is not logged. +# In practice InnoDB takes shared row locks. +Success: 'do (select i from t1 where i = 1)' takes shared row locks on 't1'. +# +# 2.6 INSERT with a subquery. +# +# Has to take shared locks on rows in the table being read as +# this statement is written to the binary log and therefore +# should be serialized with concurrent statements. +Success: 'insert into t2 select i+5 from t1' takes shared row locks on 't1'. +Success: 'insert into t2 values ((select i+5 from t1 where i = 4))' takes shared row locks on 't1'. +# +# 2.7 LOAD DATA with a subquery. +# +# The above is true for this statement as well. +Success: 'load data infile '../../std_data/rpl_loaddata.dat' into table t2 (@a, @b) set j= @b + (select i from t1 where i = 1)' takes shared row locks on 't1'. +# +# 2.8 REPLACE with a subquery. +# +# Same is true for this statement as well. +Success: 'replace into t2 select i+5 from t1' takes shared row locks on 't1'. +Success: 'replace into t2 values ((select i+5 from t1 where i = 4))' takes shared row locks on 't1'. +# +# 2.9 SELECT with a subquery. +# +# Locks are not necessary as this statement is not written +# to the binary log and thanks to MVCC we can always get +# versions of rows prior to the update that has locked them. +# +# Also serves as a test case for bug #46947 "Embedded SELECT +# without FOR UPDATE is causing a lock". +Success: 'select * from t2 where j in (select i from t1)' doesn't take row locks on 't1'. +# +# 2.10 SET with a subquery. +# +# In theory should not require locking as it is not written +# to the binary log. In practice InnoDB acquires shared row +# locks. +Success: 'set @a:= (select i from t1 where i = 1)' takes shared row locks on 't1'. +# +# 2.11 SHOW with a subquery. +# +# Similarly to the previous case, in theory should not require locking +# as it is not written to the binary log. In practice InnoDB +# acquires shared row locks. +Success: 'show tables from test where Tables_in_test = 't2' and (select i from t1 where i = 1)' takes shared row locks on 't1'. +Success: 'show columns from t2 where (select i from t1 where i = 1)' takes shared row locks on 't1'. +# +# 2.12 UPDATE with a subquery. +# +# Has to take shared locks on rows in the table being read as +# this statement is written to the binary log and therefore +# should be serialized with concurrent statements. +Success: 'update t2 set j= j-10 where j in (select i from t1)' takes shared row locks on 't1'. +# +# 2.13 MULTI-UPDATE with a subquery. +# +# Same is true for this statement as well. +Success: 'update t2, t3 set j= j -10 where j=k and j in (select i from t1)' takes shared row locks on 't1'. +# +# 3. Statements which read tables through a view. +# +# +# 3.1 SELECT statement which uses some table through a view. +# +# Since this statement is not written to the binary log +# and old version of rows are accessible thanks to MVCC, +# no locking is necessary. +Success: 'select * from v1' doesn't take row locks on 't1'. +Success: 'select * from v2' doesn't take row locks on 't1'. +Success: 'select * from t2 where j in (select i from v1)' doesn't take row locks on 't1'. +Success: 'select * from t3 where k in (select j from v2)' doesn't take row locks on 't1'. +# +# 3.2 Statements which modify a table and use views. +# +# Since such statements are going to be written to the binary +# log they need to be serialized against concurrent statements +# and therefore should take shared row locks on data read. +Success: 'update t2 set j= j-10 where j in (select i from v1)' takes shared row locks on 't1'. +Success: 'update t3 set k= k-10 where k in (select j from v2)' takes shared row locks on 't1'. +Success: 'update t2, v1 set j= j-10 where j = i' takes shared row locks on 't1'. +Success: 'update v2 set j= j-10 where j = 3' takes shared row locks on 't1'. +# +# 4. Statements which read tables through stored functions. +# +# +# 4.1 SELECT/SET with a stored function which does not +# modify data and uses SELECT in its turn. +# +# In theory there is no need to take row locks on the table +# being selected from in SF as the call to such function +# won't get into the binary log. In practice, however, we +# discover that fact too late in the process to be able to +# affect the decision what locks should be taken. +# Hence, strong locks are taken in this case. +Success: 'select f1()' takes shared row locks on 't1'. +Success: 'set @a:= f1()' takes shared row locks on 't1'. +# +# 4.2 INSERT (or other statement which modifies data) with +# a stored function which does not modify data and uses +# SELECT. +# +# Since such statement is written to the binary log it should +# be serialized with concurrent statements affecting the data +# it uses. Therefore it should take row locks on the data +# it reads. +Success: 'insert into t2 values (f1() + 5)' takes shared row locks on 't1'. +# +# 4.3 SELECT/SET with a stored function which +# reads and modifies data. +# +# Since a call to such function is written to the binary log, +# it should be serialized with concurrent statements affecting +# the data it uses. Hence, row locks on the data read +# should be taken. +Success: 'select f2()' takes shared row locks on 't1'. +Success: 'set @a:= f2()' takes shared row locks on 't1'. +# +# 4.4. SELECT/SET with a stored function which does not +# modify data and reads a table through subselect +# in a control construct. +# +# Again, in theory a call to this function won't get to the +# binary log and thus no locking is needed. But in practice +# we don't detect this fact early enough (get_lock_type_for_table()) +# to avoid taking row locks. +Success: 'select f3()' takes shared row locks on 't1'. +Success: 'set @a:= f3()' takes shared row locks on 't1'. +Success: 'select f4()' takes shared row locks on 't1'. +Success: 'set @a:= f4()' takes shared row locks on 't1'. +# +# 4.5. INSERT (or other statement which modifies data) with +# a stored function which does not modify data and reads +# the table through a subselect in one of its control +# constructs. +# +# Since such statement is written to the binary log it should +# be serialized with concurrent statements affecting data it +# uses. Therefore it should take row locks on the data +# it reads. +Success: 'insert into t2 values (f3() + 5)' takes shared row locks on 't1'. +Success: 'insert into t2 values (f4() + 6)' takes shared row locks on 't1'. +# +# 4.6 SELECT/SET which uses a stored function with +# DML which reads a table via a subquery. +# +# Since call to such function is written to the binary log +# it should be serialized with concurrent statements. +# Hence reads should take row locks. +Success: 'select f5()' takes shared row locks on 't1'. +Success: 'set @a:= f5()' takes shared row locks on 't1'. +# +# 4.7 SELECT/SET which uses a stored function which +# doesn't modify data and reads tables through +# a view. +# +# Once again, in theory, calls to such functions won't +# get into the binary log and thus don't need row +# locks. But in practice this fact is discovered +# too late to have any effect. +Success: 'select f6()' takes shared row locks on 't1'. +Success: 'set @a:= f6()' takes shared row locks on 't1'. +Success: 'select f7()' takes shared row locks on 't1'. +Success: 'set @a:= f7()' takes shared row locks on 't1'. +# +# 4.8 INSERT which uses stored function which +# doesn't modify data and reads a table +# through a view. +# +# Since such statement is written to the binary log and +# should be serialized with concurrent statements affecting +# the data it uses. Therefore it should take row locks on +# the rows it reads. +Success: 'insert into t3 values (f6() + 5)' takes shared row locks on 't1'. +Success: 'insert into t3 values (f7() + 5)' takes shared row locks on 't1'. +# +# 4.9 SELECT which uses a stored function which +# modifies data and reads tables through a view. +# +# Since a call to such function is written to the binary log +# it should be serialized with concurrent statements. +# Hence, reads should take row locks. +Success: 'select f8()' takes shared row locks on 't1'. +Success: 'select f9()' takes shared row locks on 't1'. +# +# 4.10 SELECT which uses stored function which doesn't modify +# data and reads a table indirectly, by calling another +# function. +# +# In theory, calls to such functions won't get into the binary +# log and thus don't need to acquire row locks. But in practice +# this fact is discovered too late to have any effect. +Success: 'select f10()' takes shared row locks on 't1'. +# +# 4.11 INSERT which uses a stored function which doesn't modify +# data and reads a table indirectly, by calling another +# function. +# +# Since such statement is written to the binary log, it should +# be serialized with concurrent statements affecting the data it +# uses. Therefore it should take row locks on data it reads. +Success: 'insert into t2 values (f10() + 5)' takes shared row locks on 't1'. +# +# 4.12 SELECT which uses a stored function which modifies +# data and reads a table indirectly, by calling another +# function. +# +# Since a call to such function is written to the binary log +# it should be serialized from concurrent statements. +# Hence, reads should take row locks. +Success: 'select f11()' takes shared row locks on 't1'. +# +# 4.13 SELECT that reads a table through a subquery passed +# as a parameter to a stored function which modifies +# data. +# +# Even though a call to this function is written to the +# binary log, values of its parameters are written as literals. +# So there is no need to acquire row locks on rows used in +# the subquery. +Success: 'select f12((select i+10 from t1 where i=1))' doesn't take row locks on 't1'. +# +# 4.14 INSERT that reads a table via a subquery passed +# as a parameter to a stored function which doesn't +# modify data. +# +# Since this statement is written to the binary log it should +# be serialized with concurrent statements affecting the data it +# uses. Therefore it should take row locks on the data it reads. +Success: 'insert into t2 values (f13((select i+10 from t1 where i=1)))' takes shared row locks on 't1'. +# +# 5. Statements that read tables through stored procedures. +# +# +# 5.1 CALL statement which reads a table via SELECT. +# +# Since neither this statement nor its components are +# written to the binary log, there is no need to take +# row locks on the data it reads. +Success: 'call p2(@a)' doesn't take row locks on 't1'. +# +# 5.2 Function that modifes data and uses CALL, +# which reads a table through SELECT. +# +# Since a call to such function is written to the binary +# log, it should be serialized with concurrent statements. +# Hence, in this case reads should take row locks on data. +Success: 'select f14()' takes shared row locks on 't1'. +# +# 5.3 SELECT that calls a function that doesn't modify data and +# uses a CALL statement that reads a table via SELECT. +# +# In theory, calls to such functions won't get into the binary +# log and thus don't need to acquire row locks. But in practice +# this fact is discovered too late to have any effect. +Success: 'select f15()' takes shared row locks on 't1'. +# +# 5.4 INSERT which calls function which doesn't modify data and +# uses CALL statement which reads table through SELECT. +# +# Since such statement is written to the binary log it should +# be serialized with concurrent statements affecting data it +# uses. Therefore it should take row locks on data it reads. +Success: 'insert into t2 values (f15()+5)' takes shared row locks on 't1'. +# +# 6. Statements that use triggers. +# +# +# 6.1 Statement invoking a trigger that reads table via SELECT. +# +# Since this statement is written to the binary log it should +# be serialized with concurrent statements affecting the data +# it uses. Therefore, it should take row locks on the data +# it reads. +Success: 'insert into t4 values (2)' takes shared row locks on 't1'. +# +# 6.2 Statement invoking a trigger that reads table through +# a subquery in a control construct. +# +# The above is true for this statement as well. +Success: 'update t4 set l= 2 where l = 1' takes shared row locks on 't1'. +# +# 6.3 Statement invoking a trigger that reads a table through +# a view. +# +# And for this statement. +Success: 'delete from t4 where l = 1' takes shared row locks on 't1'. +# +# 6.4 Statement invoking a trigger that reads a table through +# a stored function. +# +# And for this statement. +Success: 'insert into t5 values (2)' takes shared row locks on 't1'. +# +# 6.5 Statement invoking a trigger that reads a table through +# stored procedure. +# +# And for this statement. +Success: 'update t5 set l= 2 where l = 1' takes shared row locks on 't1'. +# Clean-up. +drop function f1; +drop function f2; +drop function f3; +drop function f4; +drop function f5; +drop function f6; +drop function f7; +drop function f8; +drop function f9; +drop function f10; +drop function f11; +drop function f12; +drop function f13; +drop function f14; +drop function f15; +drop view v1, v2; +drop procedure p1; +drop procedure p2; +drop table t1, t2, t3, t4, t5; diff --git a/mysql-test/r/lock_sync.result b/mysql-test/r/lock_sync.result index 18f3f6bc1a7..299b5546716 100644 --- a/mysql-test/r/lock_sync.result +++ b/mysql-test/r/lock_sync.result @@ -1,4 +1,596 @@ # +# Test how we handle locking in various cases when +# we read data from MyISAM tables. +# +# In this test we mostly check that the SQL-layer correctly +# determines the type of thr_lock.c lock for a table being +# read. +# I.e. that it disallows concurrent inserts when the statement +# is going to be written to the binary log and therefore +# should be serialized, and allows concurrent inserts when +# such serialization is not necessary (e.g. when +# the statement is not written to binary log). +# +# Force concurrent inserts to be performed even if the table +# has gaps. This allows to simplify clean up in scripts +# used below (instead of backing up table being inserted +# into and then restoring it from backup at the end of the +# script we can simply delete rows which were inserted). +set @old_concurrent_insert= @@global.concurrent_insert; +set @@global.concurrent_insert= 2; +select @@global.concurrent_insert; +@@global.concurrent_insert +ALWAYS +# Prepare playground by creating tables, views, +# routines and triggers used in tests. +drop table if exists t0, t1, t2, t3, t4, t5; +drop view if exists v1, v2; +drop procedure if exists p1; +drop procedure if exists p2; +drop function if exists f1; +drop function if exists f2; +drop function if exists f3; +drop function if exists f4; +drop function if exists f5; +drop function if exists f6; +drop function if exists f7; +drop function if exists f8; +drop function if exists f9; +drop function if exists f10; +drop function if exists f11; +drop function if exists f12; +drop function if exists f13; +drop function if exists f14; +drop function if exists f15; +create table t1 (i int primary key); +insert into t1 values (1), (2), (3), (4), (5); +create table t2 (j int primary key); +insert into t2 values (1), (2), (3), (4), (5); +create table t3 (k int primary key); +insert into t3 values (1), (2), (3); +create table t4 (l int primary key); +insert into t4 values (1); +create table t5 (l int primary key); +insert into t5 values (1); +create view v1 as select i from t1; +create view v2 as select j from t2 where j in (select i from t1); +create procedure p1(k int) insert into t2 values (k); +create function f1() returns int +begin +declare j int; +select i from t1 where i = 1 into j; +return j; +end| +create function f2() returns int +begin +declare k int; +select i from t1 where i = 1 into k; +insert into t2 values (k + 5); +return 0; +end| +create function f3() returns int +begin +return (select i from t1 where i = 3); +end| +create function f4() returns int +begin +if (select i from t1 where i = 3) then +return 1; +else +return 0; +end if; +end| +create function f5() returns int +begin +insert into t2 values ((select i from t1 where i = 1) + 5); +return 0; +end| +create function f6() returns int +begin +declare k int; +select i from v1 where i = 1 into k; +return k; +end| +create function f7() returns int +begin +declare k int; +select j from v2 where j = 1 into k; +return k; +end| +create function f8() returns int +begin +declare k int; +select i from v1 where i = 1 into k; +insert into t2 values (k+5); +return k; +end| +create function f9() returns int +begin +update v2 set j=j+10 where j=1; +return 1; +end| +create function f10() returns int +begin +return f1(); +end| +create function f11() returns int +begin +declare k int; +set k= f1(); +insert into t2 values (k+5); +return k; +end| +create function f12(p int) returns int +begin +insert into t2 values (p); +return p; +end| +create function f13(p int) returns int +begin +return p; +end| +create procedure p2(inout p int) +begin +select i from t1 where i = 1 into p; +end| +create function f14() returns int +begin +declare k int; +call p2(k); +insert into t2 values (k+5); +return k; +end| +create function f15() returns int +begin +declare k int; +call p2(k); +return k; +end| +create trigger t4_bi before insert on t4 for each row +begin +declare k int; +select i from t1 where i=1 into k; +set new.l= k+1; +end| +create trigger t4_bu before update on t4 for each row +begin +if (select i from t1 where i=1) then +set new.l= 2; +end if; +end| +create trigger t4_bd before delete on t4 for each row +begin +if !(select i from v1 where i=1) then +signal sqlstate '45000'; +end if; +end| +create trigger t5_bi before insert on t5 for each row +begin +set new.l= f1()+1; +end| +create trigger t5_bu before update on t5 for each row +begin +declare j int; +call p2(j); +set new.l= j + 1; +end| +# +# Set common variables to be used by the scripts +# called below. +# +# Switch to connection 'con1'. +# Cache all functions used in the tests below so statements +# calling them won't need to open and lock mysql.proc table +# and we can assume that each statement locks its tables +# once during its execution. +show create procedure p1; +show create procedure p2; +show create function f1; +show create function f2; +show create function f3; +show create function f4; +show create function f5; +show create function f6; +show create function f7; +show create function f8; +show create function f9; +show create function f10; +show create function f11; +show create function f12; +show create function f13; +show create function f14; +show create function f15; +# Switch back to connection 'default'. +# +# 1. Statements that read tables and do not use subqueries. +# +# +# 1.1 Simple SELECT statement. +# +# No locks are necessary as this statement won't be written +# to the binary log and thanks to how MyISAM works SELECT +# will see version of the table prior to concurrent insert. +Success: 'select * from t1' allows concurrent inserts into 't1'. +# +# 1.2 Multi-UPDATE statement. +# +# Has to take shared locks on rows in the table being read as this +# statement will be written to the binary log and therefore should +# be serialized with concurrent statements. +Success: 'update t2, t1 set j= j - 1 where i = j' doesn't allow concurrent inserts into 't1'. +# +# 1.3 Multi-DELETE statement. +# +# The above is true for this statement as well. +Success: 'delete t2 from t1, t2 where i = j' doesn't allow concurrent inserts into 't1'. +# +# 1.4 DESCRIBE statement. +# +# This statement does not really read data from the +# target table and thus does not take any lock on it. +# We check this for completeness of coverage. +lock table t1 write; +# Switching to connection 'con1'. +# This statement should not be blocked. +describe t1; +# Switching to connection 'default'. +unlock tables; +# +# 1.5 SHOW statements. +# +# The above is true for SHOW statements as well. +lock table t1 write; +# Switching to connection 'con1'. +# These statements should not be blocked. +show keys from t1; +# Switching to connection 'default'. +unlock tables; +# +# 2. Statements which read tables through subqueries. +# +# +# 2.1 CALL with a subquery. +# +# A strong lock is not necessary as this statement is not +# written to the binary log as a whole (it is written +# statement-by-statement). +Success: 'call p1((select i + 5 from t1 where i = 1))' allows concurrent inserts into 't1'. +# +# 2.2 CREATE TABLE with a subquery. +# +# Has to take a strong lock on the table being read as +# this statement is written to the binary log and therefore +# should be serialized with concurrent statements. +Success: 'create table t0 select * from t1' doesn't allow concurrent inserts into 't1'. +drop table t0; +Success: 'create table t0 select j from t2 where j in (select i from t1)' doesn't allow concurrent inserts into 't1'. +drop table t0; +# +# 2.3 DELETE with a subquery. +# +# The above is true for this statement as well. +Success: 'delete from t2 where j in (select i from t1)' doesn't allow concurrent inserts into 't1'. +# +# 2.4 MULTI-DELETE with a subquery. +# +# Same is true for this statement as well. +Success: 'delete t2 from t3, t2 where k = j and j in (select i from t1)' doesn't allow concurrent inserts into 't1'. +# +# 2.5 DO with a subquery. +# +# A strong lock is not necessary as it is not logged. +Success: 'do (select i from t1 where i = 1)' allows concurrent inserts into 't1'. +# +# 2.6 INSERT with a subquery. +# +# Has to take a strong lock on the table being read as +# this statement is written to the binary log and therefore +# should be serialized with concurrent inserts. +Success: 'insert into t2 select i+5 from t1' doesn't allow concurrent inserts into 't1'. +Success: 'insert into t2 values ((select i+5 from t1 where i = 4))' doesn't allow concurrent inserts into 't1'. +# +# 2.7 LOAD DATA with a subquery. +# +# The above is true for this statement as well. +Success: 'load data infile '../../std_data/rpl_loaddata.dat' into table t2 (@a, @b) set j= @b + (select i from t1 where i = 1)' doesn't allow concurrent inserts into 't1'. +# +# 2.8 REPLACE with a subquery. +# +# Same is true for this statement as well. +Success: 'replace into t2 select i+5 from t1' doesn't allow concurrent inserts into 't1'. +Success: 'replace into t2 values ((select i+5 from t1 where i = 4))' doesn't allow concurrent inserts into 't1'. +# +# 2.9 SELECT with a subquery. +# +# Strong locks are not necessary as this statement is not written +# to the binary log and thanks to how MyISAM works this statement +# sees a version of the table prior to the concurrent insert. +Success: 'select * from t2 where j in (select i from t1)' allows concurrent inserts into 't1'. +# +# 2.10 SET with a subquery. +# +# The same is true for this statement as well. +Success: 'set @a:= (select i from t1 where i = 1)' allows concurrent inserts into 't1'. +# +# 2.11 SHOW with a subquery. +# +# And for this statement too. +Success: 'show tables from test where Tables_in_test = 't2' and (select i from t1 where i = 1)' allows concurrent inserts into 't1'. +Success: 'show columns from t2 where (select i from t1 where i = 1)' allows concurrent inserts into 't1'. +# +# 2.12 UPDATE with a subquery. +# +# Has to take a strong lock on the table being read as +# this statement is written to the binary log and therefore +# should be serialized with concurrent inserts. +Success: 'update t2 set j= j-10 where j in (select i from t1)' doesn't allow concurrent inserts into 't1'. +# +# 2.13 MULTI-UPDATE with a subquery. +# +# Same is true for this statement as well. +Success: 'update t2, t3 set j= j -10 where j=k and j in (select i from t1)' doesn't allow concurrent inserts into 't1'. +# +# 3. Statements which read tables through a view. +# +# +# 3.1 SELECT statement which uses some table through a view. +# +# Since this statement is not written to the binary log and +# an old version of the table is accessible thanks to how MyISAM +# handles concurrent insert, no locking is necessary. +Success: 'select * from v1' allows concurrent inserts into 't1'. +Success: 'select * from v2' allows concurrent inserts into 't1'. +Success: 'select * from t2 where j in (select i from v1)' allows concurrent inserts into 't1'. +Success: 'select * from t3 where k in (select j from v2)' allows concurrent inserts into 't1'. +# +# 3.2 Statements which modify a table and use views. +# +# Since such statements are going to be written to the binary +# log they need to be serialized against concurrent statements +# and therefore should take strong locks on the data read. +Success: 'update t2 set j= j-10 where j in (select i from v1)' doesn't allow concurrent inserts into 't1'. +Success: 'update t3 set k= k-10 where k in (select j from v2)' doesn't allow concurrent inserts into 't1'. +Success: 'update t2, v1 set j= j-10 where j = i' doesn't allow concurrent inserts into 't1'. +Success: 'update v2 set j= j-10 where j = 3' doesn't allow concurrent inserts into 't1'. +# +# 4. Statements which read tables through stored functions. +# +# +# 4.1 SELECT/SET with a stored function which does not +# modify data and uses SELECT in its turn. +# +# In theory there is no need to take strong locks on the table +# being selected from in SF as the call to such function +# won't get into the binary log. In practice, however, we +# discover that fact too late in the process to be able to +# affect the decision what locks should be taken. +# Hence, strong locks are taken in this case. +Success: 'select f1()' doesn't allow concurrent inserts into 't1'. +Success: 'set @a:= f1()' doesn't allow concurrent inserts into 't1'. +# +# 4.2 INSERT (or other statement which modifies data) with +# a stored function which does not modify data and uses +# SELECT. +# +# Since such statement is written to the binary log it should +# be serialized with concurrent statements affecting the data +# it uses. Therefore it should take strong lock on the data +# it reads. +Success: 'insert into t2 values (f1() + 5)' doesn't allow concurrent inserts into 't1'. +# +# 4.3 SELECT/SET with a stored function which +# reads and modifies data. +# +# Since a call to such function is written to the binary log, +# it should be serialized with concurrent statements affecting +# the data it uses. Hence, a strong lock on the data read +# should be taken. +Success: 'select f2()' doesn't allow concurrent inserts into 't1'. +Success: 'set @a:= f2()' doesn't allow concurrent inserts into 't1'. +# +# 4.4. SELECT/SET with a stored function which does not +# modify data and reads a table through subselect +# in a control construct. +# +# Again, in theory a call to this function won't get to the +# binary log and thus no strong lock is needed. But in practice +# we don't detect this fact early enough (get_lock_type_for_table()) +# to avoid taking a strong lock. +Success: 'select f3()' doesn't allow concurrent inserts into 't1'. +Success: 'set @a:= f3()' doesn't allow concurrent inserts into 't1'. +Success: 'select f4()' doesn't allow concurrent inserts into 't1'. +Success: 'set @a:= f4()' doesn't allow concurrent inserts into 't1'. +# +# 4.5. INSERT (or other statement which modifies data) with +# a stored function which does not modify data and reads +# the table through a subselect in one of its control +# constructs. +# +# Since such statement is written to the binary log it should +# be serialized with concurrent statements affecting data it +# uses. Therefore it should take a strong lock on the data +# it reads. +Success: 'insert into t2 values (f3() + 5)' doesn't allow concurrent inserts into 't1'. +Success: 'insert into t2 values (f4() + 6)' doesn't allow concurrent inserts into 't1'. +# +# 4.6 SELECT/SET which uses a stored function with +# DML which reads a table via a subquery. +# +# Since call to such function is written to the binary log +# it should be serialized with concurrent statements. +# Hence reads should take a strong lock. +Success: 'select f5()' doesn't allow concurrent inserts into 't1'. +Success: 'set @a:= f5()' doesn't allow concurrent inserts into 't1'. +# +# 4.7 SELECT/SET which uses a stored function which +# doesn't modify data and reads tables through +# a view. +# +# Once again, in theory, calls to such functions won't +# get into the binary log and thus don't need strong +# locks. But in practice this fact is discovered +# too late to have any effect. +Success: 'select f6()' doesn't allow concurrent inserts into 't1'. +Success: 'set @a:= f6()' doesn't allow concurrent inserts into 't1'. +Success: 'select f7()' doesn't allow concurrent inserts into 't1'. +Success: 'set @a:= f7()' doesn't allow concurrent inserts into 't1'. +# +# 4.8 INSERT which uses stored function which +# doesn't modify data and reads a table +# through a view. +# +# Since such statement is written to the binary log and +# should be serialized with concurrent statements affecting +# the data it uses. Therefore it should take a strong lock on +# the table it reads. +Success: 'insert into t3 values (f6() + 5)' doesn't allow concurrent inserts into 't1'. +Success: 'insert into t3 values (f7() + 5)' doesn't allow concurrent inserts into 't1'. +# +# 4.9 SELECT which uses a stored function which +# modifies data and reads tables through a view. +# +# Since a call to such function is written to the binary log +# it should be serialized with concurrent statements. +# Hence, reads should take strong locks. +Success: 'select f8()' doesn't allow concurrent inserts into 't1'. +Success: 'select f9()' doesn't allow concurrent inserts into 't1'. +# +# 4.10 SELECT which uses a stored function which doesn't modify +# data and reads a table indirectly, by calling another +# function. +# +# In theory, calls to such functions won't get into the binary +# log and thus don't need to acquire strong locks. But in practice +# this fact is discovered too late to have any effect. +Success: 'select f10()' doesn't allow concurrent inserts into 't1'. +# +# 4.11 INSERT which uses a stored function which doesn't modify +# data and reads a table indirectly, by calling another +# function. +# +# Since such statement is written to the binary log, it should +# be serialized with concurrent statements affecting the data it +# uses. Therefore it should take strong locks on data it reads. +Success: 'insert into t2 values (f10() + 5)' doesn't allow concurrent inserts into 't1'. +# +# 4.12 SELECT which uses a stored function which modifies +# data and reads a table indirectly, by calling another +# function. +# +# Since a call to such function is written to the binary log +# it should be serialized from concurrent statements. +# Hence, read should take a strong lock. +Success: 'select f11()' doesn't allow concurrent inserts into 't1'. +# +# 4.13 SELECT that reads a table through a subquery passed +# as a parameter to a stored function which modifies +# data. +# +# Even though a call to this function is written to the +# binary log, values of its parameters are written as literals. +# So there is no need to acquire strong locks for tables used in +# the subquery. +Success: 'select f12((select i+10 from t1 where i=1))' allows concurrent inserts into 't1'. +# +# 4.14 INSERT that reads a table via a subquery passed +# as a parameter to a stored function which doesn't +# modify data. +# +# Since this statement is written to the binary log it should +# be serialized with concurrent statements affecting the data it +# uses. Therefore it should take strong locks on the data it reads. +Success: 'insert into t2 values (f13((select i+10 from t1 where i=1)))' doesn't allow concurrent inserts into 't1'. +# +# 5. Statements that read tables through stored procedures. +# +# +# 5.1 CALL statement which reads a table via SELECT. +# +# Since neither this statement nor its components are +# written to the binary log, there is no need to take +# strong locks on the data read it reads. +Success: 'call p2(@a)' allows concurrent inserts into 't1'. +# +# 5.2 Function that modifes data and uses CALL, +# which reads a table through SELECT. +# +# Since a call to such function is written to the binary +# log, it should be serialized with concurrent statements. +# Hence, in this case reads should take strong locks on data. +Success: 'select f14()' doesn't allow concurrent inserts into 't1'. +# +# 5.3 SELECT that calls a function that doesn't modify data and +# uses a CALL statement that reads a table via SELECT. +# +# In theory, calls to such functions won't get into the binary +# log and thus don't need to acquire strong locks. But in practice +# this fact is discovered too late to have any effect. +Success: 'select f15()' doesn't allow concurrent inserts into 't1'. +# +# 5.4 INSERT which calls function which doesn't modify data and +# uses CALL statement which reads table through SELECT. +# +# Since such statement is written to the binary log it should +# be serialized with concurrent statements affecting data it +# uses. Therefore it should take strong locks on data it reads. +Success: 'insert into t2 values (f15()+5)' doesn't allow concurrent inserts into 't1'. +# +# 6. Statements that use triggers. +# +# +# 6.1 Statement invoking a trigger that reads table via SELECT. +# +# Since this statement is written to the binary log it should +# be serialized with concurrent statements affecting the data +# it uses. Therefore, it should take strong locks on the data +# it reads. +Success: 'insert into t4 values (2)' doesn't allow concurrent inserts into 't1'. +# +# 6.2 Statement invoking a trigger that reads table through +# a subquery in a control construct. +# +# The above is true for this statement as well. +Success: 'update t4 set l= 2 where l = 1' doesn't allow concurrent inserts into 't1'. +# +# 6.3 Statement invoking a trigger that reads a table through +# a view. +# +# And for this statement. +Success: 'delete from t4 where l = 1' doesn't allow concurrent inserts into 't1'. +# +# 6.4 Statement invoking a trigger that reads a table through +# a stored function. +# +# And for this statement. +Success: 'insert into t5 values (2)' doesn't allow concurrent inserts into 't1'. +# +# 6.5 Statement invoking a trigger that reads a table through +# stored procedure. +# +# And for this statement. +Success: 'update t5 set l= 2 where l = 1' doesn't allow concurrent inserts into 't1'. +# Clean-up. +drop function f1; +drop function f2; +drop function f3; +drop function f4; +drop function f5; +drop function f6; +drop function f7; +drop function f8; +drop function f9; +drop function f10; +drop function f11; +drop function f12; +drop function f13; +drop function f14; +drop function f15; +drop view v1, v2; +drop procedure p1; +drop procedure p2; +drop table t1, t2, t3, t4, t5; +set @@global.concurrent_insert= @old_concurrent_insert; +# # Test for bug #45143 "All connections hang on concurrent ALTER TABLE". # # Concurrent execution of statements which required weak write lock diff --git a/mysql-test/t/bug39022.test b/mysql-test/t/bug39022.test index 268b207e0e5..6056dbf0e7b 100644 --- a/mysql-test/t/bug39022.test +++ b/mysql-test/t/bug39022.test @@ -24,7 +24,7 @@ START TRANSACTION; connection thread2; --echo # in thread2 REPLACE INTO t2 VALUES (-17); -SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d); +SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d) LOCK IN SHARE MODE; connection thread1; --echo # in thread1 @@ -37,14 +37,14 @@ START TRANSACTION; REPLACE INTO t1(a,b) VALUES (65,-50); REPLACE INTO t2 VALUES (-91); send; -SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d); #waits +SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d) LOCK IN SHARE MODE; #waits connection thread1; --echo # in thread1 --echo # should not crash --error ER_LOCK_DEADLOCK -SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d); #crashes +SELECT d FROM t2,t1 WHERE d=(SELECT MAX(a) FROM t1 WHERE t1.a > t2.d) LOCK IN SHARE MODE; #crashes connection thread2; --echo # in thread2 diff --git a/mysql-test/t/innodb_mysql_lock2.test b/mysql-test/t/innodb_mysql_lock2.test new file mode 100644 index 00000000000..5111d56225a --- /dev/null +++ b/mysql-test/t/innodb_mysql_lock2.test @@ -0,0 +1,765 @@ +# This test covers behavior for InnoDB tables. +--source include/have_innodb.inc +# This test requires statement/mixed mode binary logging. +# Row-based mode puts weaker serializability requirements +# so weaker locks are acquired for it. +--source include/have_binlog_format_mixed_or_statement.inc +# Save the initial number of concurrent sessions. +--source include/count_sessions.inc + +--echo # +--echo # Test how do we handle locking in various cases when +--echo # we read data from InnoDB tables. +--echo # +--echo # In fact by performing this test we check two things: +--echo # 1) That SQL-layer correctly determine type of thr_lock.c +--echo # lock to be acquired/passed to InnoDB engine. +--echo # 2) That InnoDB engine correctly interprets this lock +--echo # type and takes necessary row locks or does not +--echo # take them if they are not necessary. +--echo # + +--echo # This test makes sense only in REPEATABLE-READ mode as +--echo # in SERIALIZABLE mode all statements that read data take +--echo # shared lock on them to enforce its semantics. +select @@session.tx_isolation; + +--echo # Prepare playground by creating tables, views, +--echo # routines and triggers used in tests. +connect (con1, localhost, root,,); +connection default; +--disable_warnings +drop table if exists t0, t1, t2, t3, t4, t5; +drop view if exists v1, v2; +drop procedure if exists p1; +drop procedure if exists p2; +drop function if exists f1; +drop function if exists f2; +drop function if exists f3; +drop function if exists f4; +drop function if exists f5; +drop function if exists f6; +drop function if exists f7; +drop function if exists f8; +drop function if exists f9; +drop function if exists f10; +drop function if exists f11; +drop function if exists f12; +drop function if exists f13; +drop function if exists f14; +drop function if exists f15; +--enable_warnings +create table t1 (i int primary key) engine=innodb; +insert into t1 values (1), (2), (3), (4), (5); +create table t2 (j int primary key) engine=innodb; +insert into t2 values (1), (2), (3), (4), (5); +create table t3 (k int primary key) engine=innodb; +insert into t3 values (1), (2), (3); +create table t4 (l int primary key) engine=innodb; +insert into t4 values (1); +create table t5 (l int primary key) engine=innodb; +insert into t5 values (1); +create view v1 as select i from t1; +create view v2 as select j from t2 where j in (select i from t1); +create procedure p1(k int) insert into t2 values (k); +delimiter |; +create function f1() returns int +begin + declare j int; + select i from t1 where i = 1 into j; + return j; +end| +create function f2() returns int +begin + declare k int; + select i from t1 where i = 1 into k; + insert into t2 values (k + 5); + return 0; +end| +create function f3() returns int +begin + return (select i from t1 where i = 3); +end| +create function f4() returns int +begin + if (select i from t1 where i = 3) then + return 1; + else + return 0; + end if; +end| +create function f5() returns int +begin + insert into t2 values ((select i from t1 where i = 1) + 5); + return 0; +end| +create function f6() returns int +begin + declare k int; + select i from v1 where i = 1 into k; + return k; +end| +create function f7() returns int +begin + declare k int; + select j from v2 where j = 1 into k; + return k; +end| +create function f8() returns int +begin + declare k int; + select i from v1 where i = 1 into k; + insert into t2 values (k+5); + return k; +end| +create function f9() returns int +begin + update v2 set j=j+10 where j=1; + return 1; +end| +create function f10() returns int +begin + return f1(); +end| +create function f11() returns int +begin + declare k int; + set k= f1(); + insert into t2 values (k+5); + return k; +end| +create function f12(p int) returns int +begin + insert into t2 values (p); + return p; +end| +create function f13(p int) returns int +begin + return p; +end| +create procedure p2(inout p int) +begin + select i from t1 where i = 1 into p; +end| +create function f14() returns int +begin + declare k int; + call p2(k); + insert into t2 values (k+5); + return k; +end| +create function f15() returns int +begin + declare k int; + call p2(k); + return k; +end| +create trigger t4_bi before insert on t4 for each row +begin + declare k int; + select i from t1 where i=1 into k; + set new.l= k+1; +end| +create trigger t4_bu before update on t4 for each row +begin + if (select i from t1 where i=1) then + set new.l= 2; + end if; +end| +create trigger t4_bd before delete on t4 for each row +begin + if !(select i from v1 where i=1) then + signal sqlstate '45000'; + end if; +end| +create trigger t5_bi before insert on t5 for each row +begin + set new.l= f1()+1; +end| +create trigger t5_bu before update on t5 for each row +begin + declare j int; + call p2(j); + set new.l= j + 1; +end| +delimiter ;| + +--echo # +--echo # Set common variables to be used by scripts called below. +--echo # +let $con_aux= con1; +let $table= t1; + + +--echo # +--echo # 1. Statements that read tables and do not use subqueries. +--echo # + +--echo # +--echo # 1.1 Simple SELECT statement. +--echo # +--echo # No locks are necessary as this statement won't be written +--echo # to the binary log and thanks to how MyISAM works SELECT +--echo # will see version of the table prior to concurrent insert. +let $statement= select * from t1; +--source include/check_no_row_lock.inc + +--echo # +--echo # 1.2 Multi-UPDATE statement. +--echo # +--echo # Has to take shared locks on rows in the table being read as this +--echo # statement will be written to the binary log and therefore should +--echo # be serialized with concurrent statements. +let $statement= update t2, t1 set j= j - 1 where i = j; +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 1.3 Multi-DELETE statement. +--echo # +--echo # The above is true for this statement as well. +let $statement= delete t2 from t1, t2 where i = j; +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 1.4 DESCRIBE statement. +--echo # +--echo # This statement does not really read data from the +--echo # target table and thus does not take any lock on it. +--echo # We check this for completeness of coverage. +let $statement= describe t1; +--source include/check_no_row_lock.inc + +--echo # +--echo # 1.5 SHOW statements. +--echo # +--echo # The above is true for SHOW statements as well. +let $statement= show create table t1; +--source include/check_no_row_lock.inc +let $statement= show keys from t1; +--source include/check_no_row_lock.inc + + +--echo # +--echo # 2. Statements which read tables through subqueries. +--echo # + +--echo # +--echo # 2.1 CALL with a subquery. +--echo # +--echo # A strong lock is not necessary as this statement is not +--echo # written to the binary log as a whole (it is written +--echo # statement-by-statement) and thanks to MVCC we can always get +--echo # versions of rows prior to the update that has locked them. +--echo # But in practice InnoDB does locking reads for all statements +--echo # other than SELECT (unless it is a READ-COMITTED mode or +--echo # innodb_locks_unsafe_for_binlog is ON). +let $statement= call p1((select i + 5 from t1 where i = 1)); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 2.2 CREATE TABLE with a subquery. +--echo # +--echo # Has to take shared locks on rows in the table being read as +--echo # this statement is written to the binary log and therefore +--echo # should be serialized with concurrent statements. +let $statement= create table t0 engine=innodb select * from t1; +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc +drop table t0; +let $statement= create table t0 engine=innodb select j from t2 where j in (select i from t1); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc +drop table t0; + +--echo # +--echo # 2.3 DELETE with a subquery. +--echo # +--echo # The above is true for this statement as well. +let $statement= delete from t2 where j in (select i from t1); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 2.4 MULTI-DELETE with a subquery. +--echo # +--echo # Same is true for this statement as well. +let $statement= delete t2 from t3, t2 where k = j and j in (select i from t1); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 2.5 DO with a subquery. +--echo # +--echo # In theory should not take row locks as it is not logged. +--echo # In practice InnoDB takes shared row locks. +let $statement= do (select i from t1 where i = 1); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 2.6 INSERT with a subquery. +--echo # +--echo # Has to take shared locks on rows in the table being read as +--echo # this statement is written to the binary log and therefore +--echo # should be serialized with concurrent statements. +let $statement= insert into t2 select i+5 from t1; +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc +let $statement= insert into t2 values ((select i+5 from t1 where i = 4)); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 2.7 LOAD DATA with a subquery. +--echo # +--echo # The above is true for this statement as well. +let $statement= load data infile '../../std_data/rpl_loaddata.dat' into table t2 (@a, @b) set j= @b + (select i from t1 where i = 1); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 2.8 REPLACE with a subquery. +--echo # +--echo # Same is true for this statement as well. +let $statement= replace into t2 select i+5 from t1; +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc +let $statement= replace into t2 values ((select i+5 from t1 where i = 4)); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 2.9 SELECT with a subquery. +--echo # +--echo # Locks are not necessary as this statement is not written +--echo # to the binary log and thanks to MVCC we can always get +--echo # versions of rows prior to the update that has locked them. +--echo # +--echo # Also serves as a test case for bug #46947 "Embedded SELECT +--echo # without FOR UPDATE is causing a lock". +let $statement= select * from t2 where j in (select i from t1); +--source include/check_no_row_lock.inc + +--echo # +--echo # 2.10 SET with a subquery. +--echo # +--echo # In theory should not require locking as it is not written +--echo # to the binary log. In practice InnoDB acquires shared row +--echo # locks. +let $statement= set @a:= (select i from t1 where i = 1); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 2.11 SHOW with a subquery. +--echo # +--echo # Similarly to the previous case, in theory should not require locking +--echo # as it is not written to the binary log. In practice InnoDB +--echo # acquires shared row locks. +let $statement= show tables from test where Tables_in_test = 't2' and (select i from t1 where i = 1); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc +let $statement= show columns from t2 where (select i from t1 where i = 1); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 2.12 UPDATE with a subquery. +--echo # +--echo # Has to take shared locks on rows in the table being read as +--echo # this statement is written to the binary log and therefore +--echo # should be serialized with concurrent statements. +let $statement= update t2 set j= j-10 where j in (select i from t1); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 2.13 MULTI-UPDATE with a subquery. +--echo # +--echo # Same is true for this statement as well. +let $statement= update t2, t3 set j= j -10 where j=k and j in (select i from t1); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc + + +--echo # +--echo # 3. Statements which read tables through a view. +--echo # + +--echo # +--echo # 3.1 SELECT statement which uses some table through a view. +--echo # +--echo # Since this statement is not written to the binary log +--echo # and old version of rows are accessible thanks to MVCC, +--echo # no locking is necessary. +let $statement= select * from v1; +--source include/check_no_row_lock.inc +let $statement= select * from v2; +--source include/check_no_row_lock.inc +let $statement= select * from t2 where j in (select i from v1); +--source include/check_no_row_lock.inc +let $statement= select * from t3 where k in (select j from v2); +--source include/check_no_row_lock.inc + +--echo # +--echo # 3.2 Statements which modify a table and use views. +--echo # +--echo # Since such statements are going to be written to the binary +--echo # log they need to be serialized against concurrent statements +--echo # and therefore should take shared row locks on data read. +let $statement= update t2 set j= j-10 where j in (select i from v1); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc +let $statement= update t3 set k= k-10 where k in (select j from v2); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc +let $statement= update t2, v1 set j= j-10 where j = i; +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc +let $statement= update v2 set j= j-10 where j = 3; +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc + + +--echo # +--echo # 4. Statements which read tables through stored functions. +--echo # + +--echo # +--echo # 4.1 SELECT/SET with a stored function which does not +--echo # modify data and uses SELECT in its turn. +--echo # +--echo # In theory there is no need to take row locks on the table +--echo # being selected from in SF as the call to such function +--echo # won't get into the binary log. In practice, however, we +--echo # discover that fact too late in the process to be able to +--echo # affect the decision what locks should be taken. +--echo # Hence, strong locks are taken in this case. +let $statement= select f1(); +let $wait_statement= select i from t1 where i = 1 into j; +--source include/check_shared_row_lock.inc +let $statement= set @a:= f1(); +let $wait_statement= select i from t1 where i = 1 into j; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 4.2 INSERT (or other statement which modifies data) with +--echo # a stored function which does not modify data and uses +--echo # SELECT. +--echo # +--echo # Since such statement is written to the binary log it should +--echo # be serialized with concurrent statements affecting the data +--echo # it uses. Therefore it should take row locks on the data +--echo # it reads. +let $statement= insert into t2 values (f1() + 5); +let $wait_statement= select i from t1 where i = 1 into j; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 4.3 SELECT/SET with a stored function which +--echo # reads and modifies data. +--echo # +--echo # Since a call to such function is written to the binary log, +--echo # it should be serialized with concurrent statements affecting +--echo # the data it uses. Hence, row locks on the data read +--echo # should be taken. +let $statement= select f2(); +let $wait_statement= select i from t1 where i = 1 into k; +--source include/check_shared_row_lock.inc +let $statement= set @a:= f2(); +let $wait_statement= select i from t1 where i = 1 into k; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 4.4. SELECT/SET with a stored function which does not +--echo # modify data and reads a table through subselect +--echo # in a control construct. +--echo # +--echo # Again, in theory a call to this function won't get to the +--echo # binary log and thus no locking is needed. But in practice +--echo # we don't detect this fact early enough (get_lock_type_for_table()) +--echo # to avoid taking row locks. +let $statement= select f3(); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc +let $statement= set @a:= f3(); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc +let $statement= select f4(); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc +let $statement= set @a:= f4(); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 4.5. INSERT (or other statement which modifies data) with +--echo # a stored function which does not modify data and reads +--echo # the table through a subselect in one of its control +--echo # constructs. +--echo # +--echo # Since such statement is written to the binary log it should +--echo # be serialized with concurrent statements affecting data it +--echo # uses. Therefore it should take row locks on the data +--echo # it reads. +let $statement= insert into t2 values (f3() + 5); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc +let $statement= insert into t2 values (f4() + 6); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 4.6 SELECT/SET which uses a stored function with +--echo # DML which reads a table via a subquery. +--echo # +--echo # Since call to such function is written to the binary log +--echo # it should be serialized with concurrent statements. +--echo # Hence reads should take row locks. +let $statement= select f5(); +let $wait_statement= insert into t2 values ((select i from t1 where i = 1) + 5); +--source include/check_shared_row_lock.inc +let $statement= set @a:= f5(); +let $wait_statement= insert into t2 values ((select i from t1 where i = 1) + 5); +--source include/check_shared_row_lock.inc + +--echo # +--echo # 4.7 SELECT/SET which uses a stored function which +--echo # doesn't modify data and reads tables through +--echo # a view. +--echo # +--echo # Once again, in theory, calls to such functions won't +--echo # get into the binary log and thus don't need row +--echo # locks. But in practice this fact is discovered +--echo # too late to have any effect. +let $statement= select f6(); +let $wait_statement= select i from v1 where i = 1 into k; +--source include/check_shared_row_lock.inc +let $statement= set @a:= f6(); +let $wait_statement= select i from v1 where i = 1 into k; +--source include/check_shared_row_lock.inc +let $statement= select f7(); +let $wait_statement= select j from v2 where j = 1 into k; +--source include/check_shared_row_lock.inc +let $statement= set @a:= f7(); +let $wait_statement= select j from v2 where j = 1 into k; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 4.8 INSERT which uses stored function which +--echo # doesn't modify data and reads a table +--echo # through a view. +--echo # +--echo # Since such statement is written to the binary log and +--echo # should be serialized with concurrent statements affecting +--echo # the data it uses. Therefore it should take row locks on +--echo # the rows it reads. +let $statement= insert into t3 values (f6() + 5); +let $wait_statement= select i from v1 where i = 1 into k; +--source include/check_shared_row_lock.inc +let $statement= insert into t3 values (f7() + 5); +let $wait_statement= select j from v2 where j = 1 into k; +--source include/check_shared_row_lock.inc + + +--echo # +--echo # 4.9 SELECT which uses a stored function which +--echo # modifies data and reads tables through a view. +--echo # +--echo # Since a call to such function is written to the binary log +--echo # it should be serialized with concurrent statements. +--echo # Hence, reads should take row locks. +let $statement= select f8(); +let $wait_statement= select i from v1 where i = 1 into k; +--source include/check_shared_row_lock.inc +let $statement= select f9(); +let $wait_statement= update v2 set j=j+10 where j=1; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 4.10 SELECT which uses stored function which doesn't modify +--echo # data and reads a table indirectly, by calling another +--echo # function. +--echo # +--echo # In theory, calls to such functions won't get into the binary +--echo # log and thus don't need to acquire row locks. But in practice +--echo # this fact is discovered too late to have any effect. +let $statement= select f10(); +let $wait_statement= select i from t1 where i = 1 into j; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 4.11 INSERT which uses a stored function which doesn't modify +--echo # data and reads a table indirectly, by calling another +--echo # function. +--echo # +--echo # Since such statement is written to the binary log, it should +--echo # be serialized with concurrent statements affecting the data it +--echo # uses. Therefore it should take row locks on data it reads. +let $statement= insert into t2 values (f10() + 5); +let $wait_statement= select i from t1 where i = 1 into j; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 4.12 SELECT which uses a stored function which modifies +--echo # data and reads a table indirectly, by calling another +--echo # function. +--echo # +--echo # Since a call to such function is written to the binary log +--echo # it should be serialized from concurrent statements. +--echo # Hence, reads should take row locks. +let $statement= select f11(); +let $wait_statement= select i from t1 where i = 1 into j; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 4.13 SELECT that reads a table through a subquery passed +--echo # as a parameter to a stored function which modifies +--echo # data. +--echo # +--echo # Even though a call to this function is written to the +--echo # binary log, values of its parameters are written as literals. +--echo # So there is no need to acquire row locks on rows used in +--echo # the subquery. +let $statement= select f12((select i+10 from t1 where i=1)); +--source include/check_no_row_lock.inc + +--echo # +--echo # 4.14 INSERT that reads a table via a subquery passed +--echo # as a parameter to a stored function which doesn't +--echo # modify data. +--echo # +--echo # Since this statement is written to the binary log it should +--echo # be serialized with concurrent statements affecting the data it +--echo # uses. Therefore it should take row locks on the data it reads. +let $statement= insert into t2 values (f13((select i+10 from t1 where i=1))); +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc + + +--echo # +--echo # 5. Statements that read tables through stored procedures. +--echo # + +--echo # +--echo # 5.1 CALL statement which reads a table via SELECT. +--echo # +--echo # Since neither this statement nor its components are +--echo # written to the binary log, there is no need to take +--echo # row locks on the data it reads. +let $statement= call p2(@a); +--source include/check_no_row_lock.inc + +--echo # +--echo # 5.2 Function that modifes data and uses CALL, +--echo # which reads a table through SELECT. +--echo # +--echo # Since a call to such function is written to the binary +--echo # log, it should be serialized with concurrent statements. +--echo # Hence, in this case reads should take row locks on data. +let $statement= select f14(); +let $wait_statement= select i from t1 where i = 1 into p; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 5.3 SELECT that calls a function that doesn't modify data and +--echo # uses a CALL statement that reads a table via SELECT. +--echo # +--echo # In theory, calls to such functions won't get into the binary +--echo # log and thus don't need to acquire row locks. But in practice +--echo # this fact is discovered too late to have any effect. +let $statement= select f15(); +let $wait_statement= select i from t1 where i = 1 into p; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 5.4 INSERT which calls function which doesn't modify data and +--echo # uses CALL statement which reads table through SELECT. +--echo # +--echo # Since such statement is written to the binary log it should +--echo # be serialized with concurrent statements affecting data it +--echo # uses. Therefore it should take row locks on data it reads. +let $statement= insert into t2 values (f15()+5); +let $wait_statement= select i from t1 where i = 1 into p; +--source include/check_shared_row_lock.inc + + +--echo # +--echo # 6. Statements that use triggers. +--echo # + +--echo # +--echo # 6.1 Statement invoking a trigger that reads table via SELECT. +--echo # +--echo # Since this statement is written to the binary log it should +--echo # be serialized with concurrent statements affecting the data +--echo # it uses. Therefore, it should take row locks on the data +--echo # it reads. +let $statement= insert into t4 values (2); +let $wait_statement= select i from t1 where i=1 into k; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 6.2 Statement invoking a trigger that reads table through +--echo # a subquery in a control construct. +--echo # +--echo # The above is true for this statement as well. +let $statement= update t4 set l= 2 where l = 1; +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 6.3 Statement invoking a trigger that reads a table through +--echo # a view. +--echo # +--echo # And for this statement. +let $statement= delete from t4 where l = 1; +let $wait_statement= $statement; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 6.4 Statement invoking a trigger that reads a table through +--echo # a stored function. +--echo # +--echo # And for this statement. +let $statement= insert into t5 values (2); +let $wait_statement= select i from t1 where i = 1 into j; +--source include/check_shared_row_lock.inc + +--echo # +--echo # 6.5 Statement invoking a trigger that reads a table through +--echo # stored procedure. +--echo # +--echo # And for this statement. +let $statement= update t5 set l= 2 where l = 1; +let $wait_statement= select i from t1 where i = 1 into p; +--source include/check_shared_row_lock.inc + +--echo # Clean-up. +drop function f1; +drop function f2; +drop function f3; +drop function f4; +drop function f5; +drop function f6; +drop function f7; +drop function f8; +drop function f9; +drop function f10; +drop function f11; +drop function f12; +drop function f13; +drop function f14; +drop function f15; +drop view v1, v2; +drop procedure p1; +drop procedure p2; +drop table t1, t2, t3, t4, t5; +disconnect con1; + +# Check that all connections opened by test cases in this file are really +# gone so execution of other tests won't be affected by their presence. +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/t/lock_sync.test b/mysql-test/t/lock_sync.test index 31884c1b79c..d93caed8b7e 100644 --- a/mysql-test/t/lock_sync.test +++ b/mysql-test/t/lock_sync.test @@ -4,10 +4,10 @@ --source include/have_debug_sync.inc # We need InnoDB to be able use TL_WRITE_ALLOW_WRITE type of locks in our tests. --source include/have_innodb.inc -# The test for Bug#50821 requires binary logging turned on. -# With binary logging on, sub-queries in DML statements acquire -# TL_READ_NO_INSERT which was needed to reproduce this deadlock bug. ---source include/have_log_bin.inc +# This test requires statement/mixed mode binary logging. +# Row-based mode puts weaker serializability requirements +# so weaker locks are acquired for it. +--source include/have_binlog_format_mixed_or_statement.inc # Until bug#41971 'Thread state on embedded server is always "Writing to net"' # is fixed this test can't be run on embedded version of server. --source include/not_embedded.inc @@ -17,6 +17,818 @@ --echo # +--echo # Test how we handle locking in various cases when +--echo # we read data from MyISAM tables. +--echo # +--echo # In this test we mostly check that the SQL-layer correctly +--echo # determines the type of thr_lock.c lock for a table being +--echo # read. +--echo # I.e. that it disallows concurrent inserts when the statement +--echo # is going to be written to the binary log and therefore +--echo # should be serialized, and allows concurrent inserts when +--echo # such serialization is not necessary (e.g. when +--echo # the statement is not written to binary log). +--echo # + +--echo # Force concurrent inserts to be performed even if the table +--echo # has gaps. This allows to simplify clean up in scripts +--echo # used below (instead of backing up table being inserted +--echo # into and then restoring it from backup at the end of the +--echo # script we can simply delete rows which were inserted). +set @old_concurrent_insert= @@global.concurrent_insert; +set @@global.concurrent_insert= 2; +select @@global.concurrent_insert; + +--echo # Prepare playground by creating tables, views, +--echo # routines and triggers used in tests. +connect (con1, localhost, root,,); +connect (con2, localhost, root,,); +connection default; +--disable_warnings +drop table if exists t0, t1, t2, t3, t4, t5; +drop view if exists v1, v2; +drop procedure if exists p1; +drop procedure if exists p2; +drop function if exists f1; +drop function if exists f2; +drop function if exists f3; +drop function if exists f4; +drop function if exists f5; +drop function if exists f6; +drop function if exists f7; +drop function if exists f8; +drop function if exists f9; +drop function if exists f10; +drop function if exists f11; +drop function if exists f12; +drop function if exists f13; +drop function if exists f14; +drop function if exists f15; +--enable_warnings +create table t1 (i int primary key); +insert into t1 values (1), (2), (3), (4), (5); +create table t2 (j int primary key); +insert into t2 values (1), (2), (3), (4), (5); +create table t3 (k int primary key); +insert into t3 values (1), (2), (3); +create table t4 (l int primary key); +insert into t4 values (1); +create table t5 (l int primary key); +insert into t5 values (1); +create view v1 as select i from t1; +create view v2 as select j from t2 where j in (select i from t1); +create procedure p1(k int) insert into t2 values (k); +delimiter |; +create function f1() returns int +begin + declare j int; + select i from t1 where i = 1 into j; + return j; +end| +create function f2() returns int +begin + declare k int; + select i from t1 where i = 1 into k; + insert into t2 values (k + 5); + return 0; +end| +create function f3() returns int +begin + return (select i from t1 where i = 3); +end| +create function f4() returns int +begin + if (select i from t1 where i = 3) then + return 1; + else + return 0; + end if; +end| +create function f5() returns int +begin + insert into t2 values ((select i from t1 where i = 1) + 5); + return 0; +end| +create function f6() returns int +begin + declare k int; + select i from v1 where i = 1 into k; + return k; +end| +create function f7() returns int +begin + declare k int; + select j from v2 where j = 1 into k; + return k; +end| +create function f8() returns int +begin + declare k int; + select i from v1 where i = 1 into k; + insert into t2 values (k+5); + return k; +end| +create function f9() returns int +begin + update v2 set j=j+10 where j=1; + return 1; +end| +create function f10() returns int +begin + return f1(); +end| +create function f11() returns int +begin + declare k int; + set k= f1(); + insert into t2 values (k+5); + return k; +end| +create function f12(p int) returns int +begin + insert into t2 values (p); + return p; +end| +create function f13(p int) returns int +begin + return p; +end| +create procedure p2(inout p int) +begin + select i from t1 where i = 1 into p; +end| +create function f14() returns int +begin + declare k int; + call p2(k); + insert into t2 values (k+5); + return k; +end| +create function f15() returns int +begin + declare k int; + call p2(k); + return k; +end| +create trigger t4_bi before insert on t4 for each row +begin + declare k int; + select i from t1 where i=1 into k; + set new.l= k+1; +end| +create trigger t4_bu before update on t4 for each row +begin + if (select i from t1 where i=1) then + set new.l= 2; + end if; +end| +create trigger t4_bd before delete on t4 for each row +begin + if !(select i from v1 where i=1) then + signal sqlstate '45000'; + end if; +end| +create trigger t5_bi before insert on t5 for each row +begin + set new.l= f1()+1; +end| +create trigger t5_bu before update on t5 for each row +begin + declare j int; + call p2(j); + set new.l= j + 1; +end| +delimiter ;| + +--echo # +--echo # Set common variables to be used by the scripts +--echo # called below. +--echo # +let $con_aux1= con1; +let $con_aux2= con2; +let $table= t1; + +--echo # Switch to connection 'con1'. +connection con1; +--echo # Cache all functions used in the tests below so statements +--echo # calling them won't need to open and lock mysql.proc table +--echo # and we can assume that each statement locks its tables +--echo # once during its execution. +--disable_result_log +show create procedure p1; +show create procedure p2; +show create function f1; +show create function f2; +show create function f3; +show create function f4; +show create function f5; +show create function f6; +show create function f7; +show create function f8; +show create function f9; +show create function f10; +show create function f11; +show create function f12; +show create function f13; +show create function f14; +show create function f15; +--enable_result_log +--echo # Switch back to connection 'default'. +connection default; + +--echo # +--echo # 1. Statements that read tables and do not use subqueries. +--echo # + +--echo # +--echo # 1.1 Simple SELECT statement. +--echo # +--echo # No locks are necessary as this statement won't be written +--echo # to the binary log and thanks to how MyISAM works SELECT +--echo # will see version of the table prior to concurrent insert. +let $statement= select * from t1; +let $restore_table= ; +--source include/check_concurrent_insert.inc + +--echo # +--echo # 1.2 Multi-UPDATE statement. +--echo # +--echo # Has to take shared locks on rows in the table being read as this +--echo # statement will be written to the binary log and therefore should +--echo # be serialized with concurrent statements. +let $statement= update t2, t1 set j= j - 1 where i = j; +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 1.3 Multi-DELETE statement. +--echo # +--echo # The above is true for this statement as well. +let $statement= delete t2 from t1, t2 where i = j; +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 1.4 DESCRIBE statement. +--echo # +--echo # This statement does not really read data from the +--echo # target table and thus does not take any lock on it. +--echo # We check this for completeness of coverage. +lock table t1 write; +--echo # Switching to connection 'con1'. +connection con1; +--echo # This statement should not be blocked. +--disable_result_log +describe t1; +--enable_result_log +--echo # Switching to connection 'default'. +connection default; +unlock tables; + +--echo # +--echo # 1.5 SHOW statements. +--echo # +--echo # The above is true for SHOW statements as well. +lock table t1 write; +--echo # Switching to connection 'con1'. +connection con1; +--echo # These statements should not be blocked. +# The below test for SHOW CREATE TABLE is disabled until bug 52593 +# "SHOW CREATE TABLE is blocked if table is locked for write by another +# connection" is fixed. +--disable_parsing +show create table t1; +--enable_parsing +--disable_result_log +show keys from t1; +--enable_result_log +--echo # Switching to connection 'default'. +connection default; +unlock tables; + + +--echo # +--echo # 2. Statements which read tables through subqueries. +--echo # + +--echo # +--echo # 2.1 CALL with a subquery. +--echo # +--echo # A strong lock is not necessary as this statement is not +--echo # written to the binary log as a whole (it is written +--echo # statement-by-statement). +let $statement= call p1((select i + 5 from t1 where i = 1)); +let $restore_table= t2; +--source include/check_concurrent_insert.inc + +--echo # +--echo # 2.2 CREATE TABLE with a subquery. +--echo # +--echo # Has to take a strong lock on the table being read as +--echo # this statement is written to the binary log and therefore +--echo # should be serialized with concurrent statements. +let $statement= create table t0 select * from t1; +let $restore_table= ; +--source include/check_no_concurrent_insert.inc +drop table t0; +let $statement= create table t0 select j from t2 where j in (select i from t1); +let $restore_table= ; +--source include/check_no_concurrent_insert.inc +drop table t0; + +--echo # +--echo # 2.3 DELETE with a subquery. +--echo # +--echo # The above is true for this statement as well. +let $statement= delete from t2 where j in (select i from t1); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 2.4 MULTI-DELETE with a subquery. +--echo # +--echo # Same is true for this statement as well. +let $statement= delete t2 from t3, t2 where k = j and j in (select i from t1); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc + + +--echo # +--echo # 2.5 DO with a subquery. +--echo # +--echo # A strong lock is not necessary as it is not logged. +let $statement= do (select i from t1 where i = 1); +let $restore_table= ; +--source include/check_concurrent_insert.inc + +--echo # +--echo # 2.6 INSERT with a subquery. +--echo # +--echo # Has to take a strong lock on the table being read as +--echo # this statement is written to the binary log and therefore +--echo # should be serialized with concurrent inserts. +let $statement= insert into t2 select i+5 from t1; +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc +let $statement= insert into t2 values ((select i+5 from t1 where i = 4)); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 2.7 LOAD DATA with a subquery. +--echo # +--echo # The above is true for this statement as well. +let $statement= load data infile '../../std_data/rpl_loaddata.dat' into table t2 (@a, @b) set j= @b + (select i from t1 where i = 1); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 2.8 REPLACE with a subquery. +--echo # +--echo # Same is true for this statement as well. +let $statement= replace into t2 select i+5 from t1; +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc +let $statement= replace into t2 values ((select i+5 from t1 where i = 4)); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 2.9 SELECT with a subquery. +--echo # +--echo # Strong locks are not necessary as this statement is not written +--echo # to the binary log and thanks to how MyISAM works this statement +--echo # sees a version of the table prior to the concurrent insert. +let $statement= select * from t2 where j in (select i from t1); +let $restore_table= ; +--source include/check_concurrent_insert.inc + +--echo # +--echo # 2.10 SET with a subquery. +--echo # +--echo # The same is true for this statement as well. +let $statement= set @a:= (select i from t1 where i = 1); +let $restore_table= ; +--source include/check_concurrent_insert.inc + +--echo # +--echo # 2.11 SHOW with a subquery. +--echo # +--echo # And for this statement too. +let $statement= show tables from test where Tables_in_test = 't2' and (select i from t1 where i = 1); +let $restore_table= ; +--source include/check_concurrent_insert.inc +let $statement= show columns from t2 where (select i from t1 where i = 1); +let $restore_table= ; +--source include/check_concurrent_insert.inc + +--echo # +--echo # 2.12 UPDATE with a subquery. +--echo # +--echo # Has to take a strong lock on the table being read as +--echo # this statement is written to the binary log and therefore +--echo # should be serialized with concurrent inserts. +let $statement= update t2 set j= j-10 where j in (select i from t1); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 2.13 MULTI-UPDATE with a subquery. +--echo # +--echo # Same is true for this statement as well. +let $statement= update t2, t3 set j= j -10 where j=k and j in (select i from t1); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc + + +--echo # +--echo # 3. Statements which read tables through a view. +--echo # + +--echo # +--echo # 3.1 SELECT statement which uses some table through a view. +--echo # +--echo # Since this statement is not written to the binary log and +--echo # an old version of the table is accessible thanks to how MyISAM +--echo # handles concurrent insert, no locking is necessary. +let $statement= select * from v1; +let $restore_table= ; +--source include/check_concurrent_insert.inc +let $statement= select * from v2; +let $restore_table= ; +--source include/check_concurrent_insert.inc +let $statement= select * from t2 where j in (select i from v1); +let $restore_table= ; +--source include/check_concurrent_insert.inc +let $statement= select * from t3 where k in (select j from v2); +let $restore_table= ; +--source include/check_concurrent_insert.inc + +--echo # +--echo # 3.2 Statements which modify a table and use views. +--echo # +--echo # Since such statements are going to be written to the binary +--echo # log they need to be serialized against concurrent statements +--echo # and therefore should take strong locks on the data read. +let $statement= update t2 set j= j-10 where j in (select i from v1); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc +let $statement= update t3 set k= k-10 where k in (select j from v2); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc +let $statement= update t2, v1 set j= j-10 where j = i; +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc +let $statement= update v2 set j= j-10 where j = 3; +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc + + +--echo # +--echo # 4. Statements which read tables through stored functions. +--echo # + +--echo # +--echo # 4.1 SELECT/SET with a stored function which does not +--echo # modify data and uses SELECT in its turn. +--echo # +--echo # In theory there is no need to take strong locks on the table +--echo # being selected from in SF as the call to such function +--echo # won't get into the binary log. In practice, however, we +--echo # discover that fact too late in the process to be able to +--echo # affect the decision what locks should be taken. +--echo # Hence, strong locks are taken in this case. +let $statement= select f1(); +let $restore_table= ; +--source include/check_no_concurrent_insert.inc +let $statement= set @a:= f1(); +let $restore_table= ; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 4.2 INSERT (or other statement which modifies data) with +--echo # a stored function which does not modify data and uses +--echo # SELECT. +--echo # +--echo # Since such statement is written to the binary log it should +--echo # be serialized with concurrent statements affecting the data +--echo # it uses. Therefore it should take strong lock on the data +--echo # it reads. +let $statement= insert into t2 values (f1() + 5); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 4.3 SELECT/SET with a stored function which +--echo # reads and modifies data. +--echo # +--echo # Since a call to such function is written to the binary log, +--echo # it should be serialized with concurrent statements affecting +--echo # the data it uses. Hence, a strong lock on the data read +--echo # should be taken. +let $statement= select f2(); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc +let $statement= set @a:= f2(); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 4.4. SELECT/SET with a stored function which does not +--echo # modify data and reads a table through subselect +--echo # in a control construct. +--echo # +--echo # Again, in theory a call to this function won't get to the +--echo # binary log and thus no strong lock is needed. But in practice +--echo # we don't detect this fact early enough (get_lock_type_for_table()) +--echo # to avoid taking a strong lock. +let $statement= select f3(); +let $restore_table= ; +--source include/check_no_concurrent_insert.inc +let $statement= set @a:= f3(); +let $restore_table= ; +--source include/check_no_concurrent_insert.inc +let $statement= select f4(); +let $restore_table= ; +--source include/check_no_concurrent_insert.inc +let $statement= set @a:= f4(); +let $restore_table= ; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 4.5. INSERT (or other statement which modifies data) with +--echo # a stored function which does not modify data and reads +--echo # the table through a subselect in one of its control +--echo # constructs. +--echo # +--echo # Since such statement is written to the binary log it should +--echo # be serialized with concurrent statements affecting data it +--echo # uses. Therefore it should take a strong lock on the data +--echo # it reads. +let $statement= insert into t2 values (f3() + 5); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc +let $statement= insert into t2 values (f4() + 6); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 4.6 SELECT/SET which uses a stored function with +--echo # DML which reads a table via a subquery. +--echo # +--echo # Since call to such function is written to the binary log +--echo # it should be serialized with concurrent statements. +--echo # Hence reads should take a strong lock. +let $statement= select f5(); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc +let $statement= set @a:= f5(); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 4.7 SELECT/SET which uses a stored function which +--echo # doesn't modify data and reads tables through +--echo # a view. +--echo # +--echo # Once again, in theory, calls to such functions won't +--echo # get into the binary log and thus don't need strong +--echo # locks. But in practice this fact is discovered +--echo # too late to have any effect. +let $statement= select f6(); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc +let $statement= set @a:= f6(); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc +let $statement= select f7(); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc +let $statement= set @a:= f7(); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 4.8 INSERT which uses stored function which +--echo # doesn't modify data and reads a table +--echo # through a view. +--echo # +--echo # Since such statement is written to the binary log and +--echo # should be serialized with concurrent statements affecting +--echo # the data it uses. Therefore it should take a strong lock on +--echo # the table it reads. +let $statement= insert into t3 values (f6() + 5); +let $restore_table= t3; +--source include/check_no_concurrent_insert.inc +let $statement= insert into t3 values (f7() + 5); +let $restore_table= t3; +--source include/check_no_concurrent_insert.inc + + +--echo # +--echo # 4.9 SELECT which uses a stored function which +--echo # modifies data and reads tables through a view. +--echo # +--echo # Since a call to such function is written to the binary log +--echo # it should be serialized with concurrent statements. +--echo # Hence, reads should take strong locks. +let $statement= select f8(); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc +let $statement= select f9(); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 4.10 SELECT which uses a stored function which doesn't modify +--echo # data and reads a table indirectly, by calling another +--echo # function. +--echo # +--echo # In theory, calls to such functions won't get into the binary +--echo # log and thus don't need to acquire strong locks. But in practice +--echo # this fact is discovered too late to have any effect. +let $statement= select f10(); +let $restore_table= ; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 4.11 INSERT which uses a stored function which doesn't modify +--echo # data and reads a table indirectly, by calling another +--echo # function. +--echo # +--echo # Since such statement is written to the binary log, it should +--echo # be serialized with concurrent statements affecting the data it +--echo # uses. Therefore it should take strong locks on data it reads. +let $statement= insert into t2 values (f10() + 5); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 4.12 SELECT which uses a stored function which modifies +--echo # data and reads a table indirectly, by calling another +--echo # function. +--echo # +--echo # Since a call to such function is written to the binary log +--echo # it should be serialized from concurrent statements. +--echo # Hence, read should take a strong lock. +let $statement= select f11(); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 4.13 SELECT that reads a table through a subquery passed +--echo # as a parameter to a stored function which modifies +--echo # data. +--echo # +--echo # Even though a call to this function is written to the +--echo # binary log, values of its parameters are written as literals. +--echo # So there is no need to acquire strong locks for tables used in +--echo # the subquery. +let $statement= select f12((select i+10 from t1 where i=1)); +let $restore_table= t2; +--source include/check_concurrent_insert.inc + +--echo # +--echo # 4.14 INSERT that reads a table via a subquery passed +--echo # as a parameter to a stored function which doesn't +--echo # modify data. +--echo # +--echo # Since this statement is written to the binary log it should +--echo # be serialized with concurrent statements affecting the data it +--echo # uses. Therefore it should take strong locks on the data it reads. +let $statement= insert into t2 values (f13((select i+10 from t1 where i=1))); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc + + +--echo # +--echo # 5. Statements that read tables through stored procedures. +--echo # + +--echo # +--echo # 5.1 CALL statement which reads a table via SELECT. +--echo # +--echo # Since neither this statement nor its components are +--echo # written to the binary log, there is no need to take +--echo # strong locks on the data it reads. +let $statement= call p2(@a); +let $restore_table= ; +--source include/check_concurrent_insert.inc + +--echo # +--echo # 5.2 Function that modifes data and uses CALL, +--echo # which reads a table through SELECT. +--echo # +--echo # Since a call to such function is written to the binary +--echo # log, it should be serialized with concurrent statements. +--echo # Hence, in this case reads should take strong locks on data. +let $statement= select f14(); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 5.3 SELECT that calls a function that doesn't modify data and +--echo # uses a CALL statement that reads a table via SELECT. +--echo # +--echo # In theory, calls to such functions won't get into the binary +--echo # log and thus don't need to acquire strong locks. But in practice +--echo # this fact is discovered too late to have any effect. +let $statement= select f15(); +let $restore_table= ; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 5.4 INSERT which calls function which doesn't modify data and +--echo # uses CALL statement which reads table through SELECT. +--echo # +--echo # Since such statement is written to the binary log it should +--echo # be serialized with concurrent statements affecting data it +--echo # uses. Therefore it should take strong locks on data it reads. +let $statement= insert into t2 values (f15()+5); +let $restore_table= t2; +--source include/check_no_concurrent_insert.inc + + +--echo # +--echo # 6. Statements that use triggers. +--echo # + +--echo # +--echo # 6.1 Statement invoking a trigger that reads table via SELECT. +--echo # +--echo # Since this statement is written to the binary log it should +--echo # be serialized with concurrent statements affecting the data +--echo # it uses. Therefore, it should take strong locks on the data +--echo # it reads. +let $statement= insert into t4 values (2); +let $restore_table= t4; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 6.2 Statement invoking a trigger that reads table through +--echo # a subquery in a control construct. +--echo # +--echo # The above is true for this statement as well. +let $statement= update t4 set l= 2 where l = 1; +let $restore_table= t4; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 6.3 Statement invoking a trigger that reads a table through +--echo # a view. +--echo # +--echo # And for this statement. +let $statement= delete from t4 where l = 1; +let $restore_table= t4; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 6.4 Statement invoking a trigger that reads a table through +--echo # a stored function. +--echo # +--echo # And for this statement. +let $statement= insert into t5 values (2); +let $restore_table= t5; +--source include/check_no_concurrent_insert.inc + +--echo # +--echo # 6.5 Statement invoking a trigger that reads a table through +--echo # stored procedure. +--echo # +--echo # And for this statement. +let $statement= update t5 set l= 2 where l = 1; +let $restore_table= t5; +--source include/check_no_concurrent_insert.inc + + +--echo # Clean-up. +drop function f1; +drop function f2; +drop function f3; +drop function f4; +drop function f5; +drop function f6; +drop function f7; +drop function f8; +drop function f9; +drop function f10; +drop function f11; +drop function f12; +drop function f13; +drop function f14; +drop function f15; +drop view v1, v2; +drop procedure p1; +drop procedure p2; +drop table t1, t2, t3, t4, t5; + +disconnect con1; +disconnect con2; + +set @@global.concurrent_insert= @old_concurrent_insert; + + +--echo # --echo # Test for bug #45143 "All connections hang on concurrent ALTER TABLE". --echo # --echo # Concurrent execution of statements which required weak write lock diff --git a/sql/log_event.cc b/sql/log_event.cc index 3a52b72909a..3a3d6c74e8e 100644 --- a/sql/log_event.cc +++ b/sql/log_event.cc @@ -4226,7 +4226,7 @@ void Load_log_event::print_query(bool need_db, const char *cs, char *buf, pos= strmov(pos, "LOAD DATA "); - if (thd->lex->lock_option == TL_WRITE_CONCURRENT_INSERT) + if (is_concurrent) pos= strmov(pos, "CONCURRENT "); if (fn_start) @@ -4368,6 +4368,7 @@ bool Load_log_event::write_data_body(IO_CACHE* file) Load_log_event::Load_log_event(THD *thd_arg, sql_exchange *ex, const char *db_arg, const char *table_name_arg, List<Item> &fields_arg, + bool is_concurrent_arg, enum enum_duplicates handle_dup, bool ignore, bool using_trans) :Log_event(thd_arg, @@ -4378,7 +4379,8 @@ Load_log_event::Load_log_event(THD *thd_arg, sql_exchange *ex, num_fields(0),fields(0), field_lens(0),field_block_len(0), table_name(table_name_arg ? table_name_arg : ""), - db(db_arg), fname(ex->file_name), local_fname(FALSE) + db(db_arg), fname(ex->file_name), local_fname(FALSE), + is_concurrent(is_concurrent_arg) { time_t end_time; time(&end_time); @@ -4459,7 +4461,13 @@ Load_log_event::Load_log_event(const char *buf, uint event_len, const Format_description_log_event *description_event) :Log_event(buf, description_event), num_fields(0), fields(0), field_lens(0),field_block_len(0), - table_name(0), db(0), fname(0), local_fname(FALSE) + table_name(0), db(0), fname(0), local_fname(FALSE), + /* + Load_log_event which comes from the binary log does not contain + information about the type of insert which was used on the master. + Assume that it was an ordinary, non-concurrent LOAD DATA. + */ + is_concurrent(FALSE) { DBUG_ENTER("Load_log_event"); /* @@ -6149,11 +6157,14 @@ int Stop_log_event::do_update_pos(Relay_log_info *rli) Create_file_log_event:: Create_file_log_event(THD* thd_arg, sql_exchange* ex, const char* db_arg, const char* table_name_arg, - List<Item>& fields_arg, enum enum_duplicates handle_dup, + List<Item>& fields_arg, + bool is_concurrent_arg, + enum enum_duplicates handle_dup, bool ignore, uchar* block_arg, uint block_len_arg, bool using_trans) - :Load_log_event(thd_arg,ex,db_arg,table_name_arg,fields_arg,handle_dup, ignore, - using_trans), + :Load_log_event(thd_arg, ex, db_arg, table_name_arg, fields_arg, + is_concurrent_arg, + handle_dup, ignore, using_trans), fake_base(0), block(block_arg), event_buf(0), block_len(block_len_arg), file_id(thd_arg->file_id = mysql_bin_log.next_file_id()) { diff --git a/sql/log_event.h b/sql/log_event.h index 36397c427e5..e281fd6e206 100644 --- a/sql/log_event.h +++ b/sql/log_event.h @@ -2069,6 +2069,17 @@ public: uint32 skip_lines; sql_ex_info sql_ex; bool local_fname; + /** + Indicates that this event corresponds to LOAD DATA CONCURRENT, + + @note Since Load_log_event event coming from the binary log + lacks information whether LOAD DATA on master was concurrent + or not, this flag is only set to TRUE for an auxiliary + Load_log_event object which is used in mysql_load() to + re-construct LOAD DATA statement from function parameters, + for logging. + */ + bool is_concurrent; /* fname doesn't point to memory inside Log_event::temp_buf */ void set_fname_outside_temp_buf(const char *afname, uint alen) @@ -2089,7 +2100,9 @@ public: Load_log_event(THD* thd, sql_exchange* ex, const char* db_arg, const char* table_name_arg, - List<Item>& fields_arg, enum enum_duplicates handle_dup, bool ignore, + List<Item>& fields_arg, + bool is_concurrent_arg, + enum enum_duplicates handle_dup, bool ignore, bool using_trans); void set_fields(const char* db, List<Item> &fields_arg, Name_resolution_context *context); @@ -2708,6 +2721,7 @@ public: Create_file_log_event(THD* thd, sql_exchange* ex, const char* db_arg, const char* table_name_arg, List<Item>& fields_arg, + bool is_concurrent_arg, enum enum_duplicates handle_dup, bool ignore, uchar* block_arg, uint block_len_arg, bool using_trans); diff --git a/sql/sp_head.cc b/sql/sp_head.cc index c91ba2a68b4..2e66aec91e5 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -2127,6 +2127,9 @@ sp_head::reset_lex(THD *thd) sublex->interval_list.empty(); sublex->type= 0; + /* Reset part of parser state which needs this. */ + thd->m_parser_state->m_yacc.reset_before_substatement(); + DBUG_RETURN(FALSE); } diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc index f8be3ff6d4a..55d83f49245 100644 --- a/sql/sql_acl.cc +++ b/sql/sql_acl.cc @@ -3159,6 +3159,12 @@ int mysql_table_grant(THD *thd, TABLE_LIST *table_list, */ Query_tables_list backup; thd->lex->reset_n_backup_query_tables_list(&backup); + /* + Restore Query_tables_list::sql_command value, which was reset + above, as the code writing query to the binary log assumes that + this value corresponds to the statement being executed. + */ + thd->lex->sql_command= backup.sql_command; if (open_and_lock_tables(thd, tables, FALSE, MYSQL_LOCK_IGNORE_TIMEOUT)) { // Should never happen close_thread_tables(thd); /* purecov: deadcode */ diff --git a/sql/sql_base.cc b/sql/sql_base.cc index baf13431d77..1d7ad87eae0 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -3963,7 +3963,8 @@ recover_from_failed_open(THD *thd, MDL_request *mdl_request, Return a appropriate read lock type given a table object. @param thd Thread context - @param table TABLE object for table to be locked + @param prelocking_ctx Prelocking context. + @param table_list Table list element for table to be locked. @remark Due to a statement-based replication limitation, statements such as INSERT INTO .. SELECT FROM .. and CREATE TABLE .. SELECT FROM need @@ -3972,20 +3973,31 @@ recover_from_failed_open(THD *thd, MDL_request *mdl_request, source table. If such a statement gets applied on the slave before the INSERT .. SELECT statement finishes, data on the master could differ from data on the slave and end-up with a discrepancy between - the binary log and table state. Furthermore, this does not apply to - I_S and log tables as it's always unsafe to replicate such tables - under statement-based replication as the table on the slave might - contain other data (ie: general_log is enabled on the slave). The - statement will be marked as unsafe for SBR in decide_logging_format(). + the binary log and table state. + This also applies to SELECT/SET/DO statements which use stored + functions. Calls to such functions are going to be logged as a + whole and thus should be serialized against concurrent changes + to tables used by those functions. This can be avoided if functions + only read data but doing so requires more complex analysis than it + is done now. + Furthermore, this does not apply to I_S and log tables as it's + always unsafe to replicate such tables under statement-based + replication as the table on the slave might contain other data + (ie: general_log is enabled on the slave). The statement will + be marked as unsafe for SBR in decide_logging_format(). */ -thr_lock_type read_lock_type_for_table(THD *thd, TABLE *table) +thr_lock_type read_lock_type_for_table(THD *thd, + Query_tables_list *prelocking_ctx, + TABLE_LIST *table_list) { bool log_on= mysql_bin_log.is_open() && (thd->variables.option_bits & OPTION_BIN_LOG); ulong binlog_format= thd->variables.binlog_format; if ((log_on == FALSE) || (binlog_format == BINLOG_FORMAT_ROW) || - (table->s->table_category == TABLE_CATEGORY_LOG) || - (table->s->table_category == TABLE_CATEGORY_PERFORMANCE)) + (table_list->table->s->table_category == TABLE_CATEGORY_LOG) || + (table_list->table->s->table_category == TABLE_CATEGORY_PERFORMANCE) || + !(is_update_query(prelocking_ctx->sql_command) || + table_list->prelocking_placeholder)) return TL_READ; else return TL_READ_NO_INSERT; @@ -4336,7 +4348,7 @@ open_and_process_table(THD *thd, LEX *lex, TABLE_LIST *tables, tables->table->reginfo.lock_type= thd->update_lock_default; else if (tables->lock_type == TL_READ_DEFAULT) tables->table->reginfo.lock_type= - read_lock_type_for_table(thd, tables->table); + read_lock_type_for_table(thd, lex, tables); else tables->table->reginfo.lock_type= tables->lock_type; } diff --git a/sql/sql_base.h b/sql/sql_base.h index a57666afe49..77fbc7458ca 100644 --- a/sql/sql_base.h +++ b/sql/sql_base.h @@ -123,7 +123,9 @@ TABLE *open_temporary_table(THD *thd, const char *path, const char *db, TABLE *find_locked_table(TABLE *list, const char *db, const char *table_name); TABLE *find_write_locked_table(TABLE *list, const char *db, const char *table_name); -thr_lock_type read_lock_type_for_table(THD *thd, TABLE *table); +thr_lock_type read_lock_type_for_table(THD *thd, + Query_tables_list *prelocking_ctx, + TABLE_LIST *table_list); my_bool mysql_rm_tmp_tables(void); bool rm_temporary_table(handlerton *base, char *path); diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 13f85b24299..6bd6a374883 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -350,7 +350,6 @@ void lex_start(THD *thd) lex->subqueries= FALSE; lex->view_prepare_mode= FALSE; lex->derived_tables= 0; - lex->lock_option= TL_READ; lex->safe_to_cache_query= 1; lex->leaf_tables_insert= 0; lex->parsing_options.reset(); @@ -363,7 +362,6 @@ void lex_start(THD *thd) lex->select_lex.ftfunc_list= &lex->select_lex.ftfunc_list_alloc; lex->select_lex.group_list.empty(); lex->select_lex.order_list.empty(); - lex->sql_command= SQLCOM_END; lex->duplicates= DUP_ERROR; lex->ignore= 0; lex->spname= NULL; @@ -1708,7 +1706,6 @@ void st_select_lex::init_query() exclude_from_table_unique_test= no_wrap_view_item= FALSE; nest_level= 0; link_next= 0; - lock_option= TL_READ_DEFAULT; } void st_select_lex::init_select() @@ -2219,6 +2216,7 @@ void LEX::cleanup_lex_after_parse_error(THD *thd) void Query_tables_list::reset_query_tables_list(bool init) { + sql_command= SQLCOM_END; if (!init && query_tables) { TABLE_LIST *table= query_tables; @@ -2281,8 +2279,7 @@ void Query_tables_list::destroy_query_tables_list() */ LEX::LEX() - :result(0), - sql_command(SQLCOM_END), option_type(OPT_DEFAULT), is_lex_started(0) + :result(0), option_type(OPT_DEFAULT), is_lex_started(0) { my_init_dynamic_array2(&plugins, sizeof(plugin_ref), diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 85ae2697f21..6f7acc4a609 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -739,14 +739,6 @@ public: List<udf_func> udf_list; /* udf function calls stack */ - /** - Per sub-query locking strategy. - Note: This variable might interfer with the corresponding statement-level - variable Lex::lock_option because on how different parser rules depend - on eachother. - */ - thr_lock_type lock_option; - /* This is a copy of the original JOIN USING list that comes from the parser. The parser : @@ -1005,8 +997,11 @@ extern const LEX_STRING empty_lex_str; /* - Class representing list of all tables used by statement. - It also contains information about stored functions used by statement + Class representing list of all tables used by statement and other + information which is necessary for opening and locking its tables, + like SQL command for this statement. + + Also contains information about stored functions used by statement since during its execution we may have to add all tables used by its stored functions/triggers to this list in order to pre-open and lock them. @@ -1018,6 +1013,13 @@ extern const LEX_STRING empty_lex_str; class Query_tables_list { public: + /** + SQL command for this statement. Part of this class since the + process of opening and locking tables for the statement needs + this information to determine correct type of lock for some of + the tables. + */ + enum_sql_command sql_command; /* Global list of all tables used by this statement */ TABLE_LIST *query_tables; /* Pointer to next_global member of last element in the previous list. */ @@ -1918,7 +1920,6 @@ struct LEX: public Query_tables_list the variable can contain 0 or 1 for each nest level. */ nesting_map allow_sum_func; - enum_sql_command sql_command; Sql_statement *m_stmt; @@ -1930,7 +1931,6 @@ struct LEX: public Query_tables_list */ bool expr_allows_subselect; - thr_lock_type lock_option; enum SSL_type ssl_type; /* defined in violite.h */ enum enum_duplicates duplicates; enum enum_tx_isolation tx_isolation; @@ -2237,12 +2237,21 @@ class Yacc_state { public: Yacc_state() - : yacc_yyss(NULL), yacc_yyvs(NULL) + : yacc_yyss(NULL), yacc_yyvs(NULL), m_lock_type(TL_READ_DEFAULT) {} ~Yacc_state(); /** + Reset part of the state which needs resetting before parsing + substatement. + */ + void reset_before_substatement() + { + m_lock_type= TL_READ_DEFAULT; + } + + /** Bison internal state stack, yyss, when dynamically allocated using my_yyoverflow(). */ @@ -2260,6 +2269,25 @@ public: */ Set_signal_information m_set_signal_info; + /** + Type of lock to be used for tables being added to the statement's + table list in table_factor, table_alias_ref, single_multi and + table_wild_one rules. + Statements which use these rules but require lock type different + from one specified by this member have to override it by using + st_select_lex::set_lock_for_tables() method. + + The default value of this member is TL_READ_DEFAULT. The only two + cases in which we change it are: + - When parsing SELECT HIGH_PRIORITY. + - Rule for DELETE. In which we use this member to pass information + about type of lock from delete to single_multi part of rule. + + We should try to avoid introducing new use cases as we would like + to get rid of this member eventually. + */ + thr_lock_type m_lock_type; + /* TODO: move more attributes from the LEX structure here. */ diff --git a/sql/sql_load.cc b/sql/sql_load.cc index 87a347b9f98..be047a8711f 100644 --- a/sql/sql_load.cc +++ b/sql/sql_load.cc @@ -132,6 +132,7 @@ static int read_xml_field(THD *thd, COPY_INFO &info, TABLE_LIST *table_list, static bool write_execute_load_query_log_event(THD *thd, sql_exchange* ex, const char* db_arg, /* table's database */ const char* table_name_arg, + bool is_concurrent, enum enum_duplicates duplicates, bool ignore, bool transactional_table, @@ -184,6 +185,7 @@ int mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list, char *tdb= thd->db ? thd->db : db; // Result is never null ulong skip_lines= ex->skip_lines; bool transactional_table; + bool is_concurrent; THD::killed_state killed_status= THD::NOT_KILLED; DBUG_ENTER("mysql_load"); @@ -245,6 +247,7 @@ int mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list, table= table_list->table; transactional_table= table->file->has_transactions(); + is_concurrent= (table_list->lock_type == TL_WRITE_CONCURRENT_INSERT); if (!fields_vars.elements) { @@ -562,6 +565,7 @@ int mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list, (void) write_execute_load_query_log_event(thd, ex, table_list->db, table_list->table_name, + is_concurrent, handle_duplicates, ignore, transactional_table, errcode); @@ -610,6 +614,7 @@ int mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list, int errcode= query_error_code(thd, killed_status == THD::NOT_KILLED); error= write_execute_load_query_log_event(thd, ex, table_list->db, table_list->table_name, + is_concurrent, handle_duplicates, ignore, transactional_table, errcode); @@ -638,6 +643,7 @@ err: static bool write_execute_load_query_log_event(THD *thd, sql_exchange* ex, const char* db_arg, /* table's database */ const char* table_name_arg, + bool is_concurrent, enum enum_duplicates duplicates, bool ignore, bool transactional_table, @@ -673,8 +679,8 @@ static bool write_execute_load_query_log_event(THD *thd, sql_exchange* ex, tbl= string_buf.c_ptr_safe(); } - Load_log_event lle(thd, ex, tdb, tbl, fv, duplicates, - ignore, transactional_table); + Load_log_event lle(thd, ex, tdb, tbl, fv, is_concurrent, + duplicates, ignore, transactional_table); /* force in a LOCAL if there was one in the original. diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 85e6866f3db..42777dbc837 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -5710,7 +5710,6 @@ void mysql_init_multi_delete(LEX *lex) lex->select_lex.select_limit= 0; lex->unit.select_limit_cnt= HA_POS_ERROR; lex->select_lex.table_list.save_and_clear(&lex->auxiliary_table_list); - lex->lock_option= TL_READ_DEFAULT; lex->query_tables= 0; lex->query_tables_last= &lex->query_tables; } diff --git a/sql/sql_priv.h b/sql/sql_priv.h index eeefd3cac04..20893e0caa8 100644 --- a/sql/sql_priv.h +++ b/sql/sql_priv.h @@ -129,6 +129,12 @@ extern char err_shared_dir[]; */ #define TMP_TABLE_FORCE_MYISAM (1ULL << 32) #define OPTION_PROFILING (1ULL << 33) +/** + Indicates that this is a HIGH_PRIORITY SELECT. + Currently used only for printing of such selects. + Type of locks to be acquired is specified directly. +*/ +#define SELECT_HIGH_PRIORITY (1ULL << 34) // SELECT, user /* The rest of the file is included in the server only */ diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 62a51a32ca2..d126d0e4ec6 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -17179,8 +17179,7 @@ void st_select_lex::print(THD *thd, String *str, enum_query_type query_type) /* First add options */ if (options & SELECT_STRAIGHT_JOIN) str->append(STRING_WITH_LEN("straight_join ")); - if ((thd->lex->lock_option == TL_READ_HIGH_PRIORITY) && - (this == &thd->lex->select_lex)) + if (options & SELECT_HIGH_PRIORITY) str->append(STRING_WITH_LEN("high_priority ")); if (options & SELECT_DISTINCT) str->append(STRING_WITH_LEN("distinct ")); diff --git a/sql/sql_show.cc b/sql/sql_show.cc index 41117650e4a..f1db513d0e2 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -3341,7 +3341,6 @@ int get_all_tables(THD *thd, TABLE_LIST *tables, COND *cond) LEX *lex= thd->lex; TABLE *table= tables->table; SELECT_LEX *old_all_select_lex= lex->all_selects_list; - enum_sql_command save_sql_command= lex->sql_command; SELECT_LEX *lsel= tables->schema_select_lex; ST_SCHEMA_TABLE *schema_table= tables->schema_table; SELECT_LEX sel; @@ -3377,6 +3376,12 @@ int get_all_tables(THD *thd, TABLE_LIST *tables, COND *cond) lex->view_prepare_mode= TRUE; lex->reset_n_backup_query_tables_list(&query_tables_list_backup); + /* + Restore Query_tables_list::sql_command value, which was reset + above, as ST_SCHEMA_TABLE::process_table() functions often rely + that this value reflects which SHOW statement is executed. + */ + lex->sql_command= query_tables_list_backup.sql_command; /* We should not introduce deadlocks even if we already have some @@ -3539,7 +3544,7 @@ int get_all_tables(THD *thd, TABLE_LIST *tables, COND *cond) (MYSQL_OPEN_IGNORE_FLUSH | MYSQL_OPEN_FORCE_SHARED_HIGH_PRIO_MDL | (can_deadlock ? MYSQL_OPEN_FAIL_ON_MDL_CONFLICT : 0))); - lex->sql_command= save_sql_command; + lex->sql_command= query_tables_list_backup.sql_command; /* XXX: show_table_list has a flag i_is_requested, and when it's set, open_normal_and_derived_tables() @@ -3598,7 +3603,6 @@ err: lex->derived_tables= derived_tables; lex->all_selects_list= old_all_select_lex; lex->view_prepare_mode= save_view_prepare_mode; - lex->sql_command= save_sql_command; DBUG_RETURN(error); } diff --git a/sql/sql_table.cc b/sql/sql_table.cc index c752905d14c..b688d706762 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -4808,6 +4808,7 @@ static bool mysql_admin_table(THD* thd, TABLE_LIST* tables, /* purecov: begin inspected */ char buff[FN_REFLEN + MYSQL_ERRMSG_SIZE]; size_t length; + enum_sql_command save_sql_command= lex->sql_command; DBUG_PRINT("admin", ("sending error message")); protocol->prepare_for_resend(); protocol->store(table_name, system_charset_info); @@ -4821,6 +4822,11 @@ static bool mysql_admin_table(THD* thd, TABLE_LIST* tables, close_thread_tables(thd); thd->mdl_context.release_transactional_locks(); lex->reset_query_tables_list(FALSE); + /* + Restore Query_tables_list::sql_command value to make statement + safe for re-execution. + */ + lex->sql_command= save_sql_command; table->table=0; // For query cache if (protocol->write()) goto err; diff --git a/sql/sql_trigger.cc b/sql/sql_trigger.cc index 701a2ec93c2..9ce62d9f2a4 100644 --- a/sql/sql_trigger.cc +++ b/sql/sql_trigger.cc @@ -411,6 +411,13 @@ bool mysql_create_or_drop_trigger(THD *thd, TABLE_LIST *tables, bool create) destructive changes necessary to open the trigger's table. */ thd->lex->reset_n_backup_query_tables_list(&backup); + /* + Restore Query_tables_list::sql_command, which was + reset above, as the code that writes the query to the + binary log assumes that this value corresponds to the + statement that is being executed. + */ + thd->lex->sql_command= backup.sql_command; if (add_table_for_trigger(thd, thd->lex->spname, if_exists, & tables)) goto end; diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 41737b33fb6..412a053014f 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -1054,7 +1054,7 @@ int mysql_multi_update_prepare(THD *thd) be write-locked (for example, trigger to be invoked might try to update this table). */ - tl->lock_type= read_lock_type_for_table(thd, table); + tl->lock_type= read_lock_type_for_table(thd, lex, tl); tl->updating= 0; /* Update TABLE::lock_type accordingly. */ if (!tl->placeholder() && !using_lock_tables) diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index a0d64e6a378..aa336f3c072 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -29,6 +29,7 @@ #define YYLEX_PARAM yythd #define YYTHD ((THD *)yythd) #define YYLIP (& YYTHD->m_parser_state->m_lip) +#define YYPS (& YYTHD->m_parser_state->m_yacc) #define MYSQL_YACC #define YYINITDEPTH 100 @@ -4937,7 +4938,6 @@ create_select: SELECT_SYM { LEX *lex=Lex; - lex->lock_option= TL_READ_DEFAULT; if (lex->sql_command == SQLCOM_INSERT) lex->sql_command= SQLCOM_INSERT_SELECT; else if (lex->sql_command == SQLCOM_REPLACE) @@ -7302,7 +7302,6 @@ select_lock_type: { LEX *lex=Lex; lex->current_select->set_lock_for_tables(TL_WRITE); - lex->current_select->lock_option= TL_WRITE; lex->safe_to_cache_query=0; lex->protect_against_global_read_lock= TRUE; } @@ -7311,7 +7310,6 @@ select_lock_type: LEX *lex=Lex; lex->current_select-> set_lock_for_tables(TL_READ_WITH_SHARED_LOCKS); - lex->current_select->lock_option= TL_READ_WITH_SHARED_LOCKS; lex->safe_to_cache_query=0; } ; @@ -9221,7 +9219,7 @@ table_factor: { if (!($$= Select->add_table_to_list(YYTHD, $2, $3, Select->get_table_join_options(), - Lex->lock_option, + YYPS->m_lock_type, Select->pop_index_hints()))) MYSQL_YYABORT; Select->add_joined_table($$); @@ -10278,7 +10276,7 @@ table_alias_ref: { if (!Select->add_table_to_list(YYTHD, $1, NULL, TL_OPTION_UPDATING | TL_OPTION_ALIAS, - Lex->lock_option )) + YYPS->m_lock_type)) MYSQL_YYABORT; } ; @@ -10303,8 +10301,6 @@ insert: lex->sql_command= SQLCOM_INSERT; lex->duplicates= DUP_ERROR; mysql_init_select(lex); - /* for subselects */ - lex->lock_option= TL_READ_DEFAULT; } insert_lock_option opt_ignore insert2 @@ -10495,7 +10491,6 @@ update: LEX *lex= Lex; mysql_init_select(lex); lex->sql_command= SQLCOM_UPDATE; - lex->lock_option= TL_UNLOCK; /* Will be set later */ lex->duplicates= DUP_ERROR; } opt_low_priority opt_ignore join_table_list @@ -10562,7 +10557,7 @@ delete: LEX *lex= Lex; lex->sql_command= SQLCOM_DELETE; mysql_init_select(lex); - lex->lock_option= TL_WRITE_DEFAULT; + YYPS->m_lock_type= TL_WRITE_DEFAULT; lex->ignore= 0; lex->select_lex.init_order(); } @@ -10573,20 +10568,27 @@ single_multi: FROM table_ident { if (!Select->add_table_to_list(YYTHD, $2, NULL, TL_OPTION_UPDATING, - Lex->lock_option)) + YYPS->m_lock_type)) MYSQL_YYABORT; + YYPS->m_lock_type= TL_READ_DEFAULT; } where_clause opt_order_clause delete_limit_clause {} | table_wild_list - { mysql_init_multi_delete(Lex); } + { + mysql_init_multi_delete(Lex); + YYPS->m_lock_type= TL_READ_DEFAULT; + } FROM join_table_list where_clause { if (multi_delete_set_locks_and_link_aux_tables(Lex)) MYSQL_YYABORT; } | FROM table_alias_ref_list - { mysql_init_multi_delete(Lex); } + { + mysql_init_multi_delete(Lex); + YYPS->m_lock_type= TL_READ_DEFAULT; + } USING join_table_list where_clause { if (multi_delete_set_locks_and_link_aux_tables(Lex)) @@ -10609,7 +10611,7 @@ table_wild_one: ti, NULL, TL_OPTION_UPDATING | TL_OPTION_ALIAS, - Lex->lock_option)) + YYPS->m_lock_type)) MYSQL_YYABORT; } | ident '.' ident opt_wild @@ -10621,7 +10623,7 @@ table_wild_one: ti, NULL, TL_OPTION_UPDATING | TL_OPTION_ALIAS, - Lex->lock_option)) + YYPS->m_lock_type)) MYSQL_YYABORT; } ; @@ -10638,7 +10640,7 @@ opt_delete_options: opt_delete_option: QUICK { Select->options|= OPTION_QUICK; } - | LOW_PRIORITY { Lex->lock_option= TL_WRITE_LOW_PRIORITY; } + | LOW_PRIORITY { YYPS->m_lock_type= TL_WRITE_LOW_PRIORITY; } | IGNORE_SYM { Lex->ignore= 1; } ; @@ -10724,7 +10726,6 @@ show: { LEX *lex=Lex; lex->wild=0; - lex->lock_option= TL_READ; mysql_init_select(lex); lex->current_select->parsing_place= SELECT_LIST; bzero((char*) &lex->create_info,sizeof(lex->create_info)); @@ -11077,7 +11078,6 @@ describe: describe_command table_ident { LEX *lex= Lex; - lex->lock_option= TL_READ; mysql_init_select(lex); lex->current_select->parsing_place= SELECT_LIST; lex->sql_command= SQLCOM_SHOW_FIELDS; @@ -11291,7 +11291,6 @@ load: { LEX *lex=Lex; lex->sql_command= SQLCOM_LOAD; - lex->lock_option= $4; lex->local_file= $5; lex->duplicates= DUP_ERROR; lex->ignore= 0; @@ -11302,7 +11301,7 @@ load: { LEX *lex=Lex; if (!Select->add_table_to_list(YYTHD, $12, NULL, TL_OPTION_UPDATING, - lex->lock_option)) + $4)) MYSQL_YYABORT; lex->field_list.empty(); lex->update_list.empty(); @@ -13734,17 +13733,6 @@ subselect_start: subselect_end: { LEX *lex=Lex; - /* - Set the required lock level for the tables associated with the - current sub-select. This will overwrite previous lock options set - using st_select_lex::add_table_to_list in any of the following - rules: single_multi, table_wild_one, load_data, table_alias_ref, - table_factor. - The default lock level is TL_READ_DEFAULT but it can be modified - with query options specific for a certain (sub-)SELECT. - */ - lex->current_select-> - set_lock_for_tables(lex->current_select->lock_option); lex->pop_context(); SELECT_LEX *child= lex->current_select; @@ -13776,8 +13764,8 @@ query_expression_option: { if (check_simple_select()) MYSQL_YYABORT; - Lex->lock_option= TL_READ_HIGH_PRIORITY; - Lex->current_select->lock_option= TL_READ_HIGH_PRIORITY; + YYPS->m_lock_type= TL_READ_HIGH_PRIORITY; + Select->options|= SELECT_HIGH_PRIORITY; } | DISTINCT { Select->options|= SELECT_DISTINCT; } | SQL_SMALL_RESULT { Select->options|= SELECT_SMALL_RESULT; } |