diff options
| -rw-r--r-- | mysql-test/include/concurrent.inc | 909 | ||||
| -rw-r--r-- | mysql-test/r/concurrent_innodb_safelog.result | 803 | ||||
| -rw-r--r-- | mysql-test/r/concurrent_innodb_unsafelog.result (renamed from mysql-test/r/concurrent_innodb.result) | 348 | ||||
| -rw-r--r-- | mysql-test/t/concurrent_innodb.test | 20 | ||||
| -rw-r--r-- | mysql-test/t/concurrent_innodb_safelog-master.opt | 1 | ||||
| -rw-r--r-- | mysql-test/t/concurrent_innodb_safelog.test | 23 | ||||
| -rw-r--r-- | mysql-test/t/concurrent_innodb_unsafelog-master.opt (renamed from mysql-test/t/concurrent_innodb-master.opt) | 1 | ||||
| -rw-r--r-- | mysql-test/t/concurrent_innodb_unsafelog.test | 23 |
8 files changed, 1719 insertions, 409 deletions
diff --git a/mysql-test/include/concurrent.inc b/mysql-test/include/concurrent.inc index 7eee50c52e6..febab1eceb4 100644 --- a/mysql-test/include/concurrent.inc +++ b/mysql-test/include/concurrent.inc @@ -4,354 +4,641 @@ # Bug#3300 # Designed and tested by Sinisa Milivojevic, sinisa@mysql.com # -# two non-interfering UPDATE's not changing result set -# -# The variable -# $engine_type -- storage engine to be tested -# has to be set before sourcing this script. +# These variables have to be set before sourcing this script: +# TRANSACTION ISOLATION LEVEL REPEATABLE READ +# innodb_locks_unsafe_for_binlog 0 (default) or 1 (by +# --innodb_locks_unsafe_for_binlog) +# $engine_type storage engine to be tested # # Last update: # 2006-08-02 ML test refactored # old name was t/innodb_concurrent.test # main code went into include/concurrent.inc +# 2008-06-03 KP test refactored; removed name locks, added comments. +# renamed wrapper t/concurrent_innodb.test -> +# t/concurrent_innodb_unsafelog.test +# new wrapper t/concurrent_innodb_safelog.test # connection default; -eval SET SESSION STORAGE_ENGINE = $engine_type; + +# +# Show prerequisites for this test. +# +SELECT @@global.tx_isolation; +SELECT @@global.innodb_locks_unsafe_for_binlog; +# +# When innodb_locks_unsafe_for_binlog is not set (zero), which is the +# default, InnoDB takes "next-key locks"/"gap locks". This means it +# locks the gap before the keys that it accessed to find the rows to +# use for a statement. In this case we have to expect some more lock +# wait timeouts in the tests below as if innodb_locks_unsafe_for_binlog +# is set (non-zero). In the latter case no "next-key locks"/"gap locks" +# are taken and locks on keys that do not match the WHERE conditon are +# released. Hence less lock collisions occur. +# We use the variable $keep_locks to set the expectations for +# lock wait timeouts accordingly. +# +let $keep_locks= `SELECT NOT @@global.innodb_locks_unsafe_for_binlog`; +--echo # keep_locks == $keep_locks + +# +# Set up privileges and remove user level locks, if exist. +# +GRANT USAGE ON test.* TO mysqltest@localhost; + +# +# Preparatory cleanup. +# +DO release_lock("hello"); +DO release_lock("hello2"); --disable_warnings drop table if exists t1; --enable_warnings -create table t1(eta int(11) not null, tipo int(11), c varchar(255)); -connect (thread1, localhost, mysqltest,,); -connection thread1; -eval SET SESSION STORAGE_ENGINE = $engine_type; -insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); -insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); -insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); -insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); -insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); -insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); -insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); -insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); -insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); -insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); -insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); -select get_lock("hello",1); -connect (thread2, localhost, mysqltest,,); -connection thread2; -begin; -send update t1 set eta=1+get_lock("hello",1)*0 where tipo=11; -sleep 1; -connection thread1; -begin; -update t1 set eta=2 where tipo=22; -select release_lock("hello"); -select * from t1; -connection thread2; -reap; -select * from t1; -send commit; -connection thread1; -select * from t1; -commit; -select * from t1; -connection thread2; -reap; -select * from t1; -connection thread1; -select * from t1; + + +--echo +--echo ** +--echo ** two UPDATE's running and both changing distinct result sets +--echo ** + --echo ** connection thread1 + connect (thread1, localhost, mysqltest,,); + connection thread1; + --echo ** Set up table + eval SET SESSION STORAGE_ENGINE = $engine_type; + create table t1(eta int(11) not null, tipo int(11), c varchar(255)); + insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); + insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); + insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); + insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); + insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); + insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); + insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); + insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); + insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); + insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); + insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); + --echo ** Get user level lock (ULL) for thread 1 + select get_lock("hello",10); + + --echo ** connection thread2 + connect (thread2, localhost, mysqltest,,); + connection thread2; + --echo ** Start transaction for thread 2 + begin; + --echo ** Update will cause a table scan and a new ULL will + --echo ** be created and blocked on the first row where tipo=11. + send update t1 set eta=1+get_lock("hello",10)*0 where tipo=11; + sleep 1; + + --echo ** connection thread1 + connection thread1; + --echo ** Start new transaction for thread 1 + begin; + --echo ** Update on t1 will cause a table scan which will be blocked because + --echo ** the previously initiated table scan applied exclusive key locks on + --echo ** all primary keys. + --echo ** Not so if innodb_locks_unsafe_for_binlog is set. The locks that + --echo ** do not match the WHERE condition are released. + if ($keep_locks) + { + --error ER_LOCK_WAIT_TIMEOUT + update t1 set eta=2 where tipo=22; + } + if (!$keep_locks) + { + update t1 set eta=2 where tipo=22; + } + --echo ** Release user level name lock from thread 1. This will cause the ULL + --echo ** on thread 2 to end its wait. + select release_lock("hello"); + --echo ** Table is now updated with a new eta on tipo=22 for thread 1. + select * from t1; + + --echo ** connection thread2 + connection thread2; + --echo ** Release the lock and collect result from update on thread 2 + reap; + select release_lock("hello"); + --echo ** Table should have eta updates where tipo=11 but updates made by + --echo ** thread 1 shouldn't be visible yet. + select * from t1; + --echo ** Sending commit on thread 2. + commit; + + --echo ** connection thread1 + connection thread1; + --echo ** Make sure table reads didn't change yet on thread 1. + select * from t1; + --echo ** And send final commit on thread 1. + commit; + --echo ** Table should now be updated by both updates in the order of + --echo ** thread 1,2. + select * from t1; + + --echo ** connection thread2 + connection thread2; + --echo ** Make sure the output is similar for t1. + select * from t1; + + --echo ** connection thread1 + connection thread1; + select * from t1; + +--echo ** connection default connection default; drop table t1; -# -# two UPDATE's running and one changing result set -# -#connect (thread1, localhost, mysqltest,,); -connection thread1; -create table t1(eta int(11) not null, tipo int(11), c varchar(255)); -insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); -insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); -insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); -insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); -insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); -insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); -insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); -insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); -insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); -insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); -insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); -select get_lock("hello",10); -#connect (thread2, localhost, mysqltest,,); -connection thread2; -begin; -send update t1 set eta=1+get_lock("hello",10)*0 where tipo=1; -sleep 1; -connection thread1; -begin; -update t1 set tipo=1 where tipo=2; -select release_lock("hello"); -select * from t1; -connection thread2; -reap; -select * from t1; -send commit; -connection thread1; -select * from t1; -commit; -select * from t1; -connection thread2; -reap; -select * from t1; -connection thread1; -select * from t1; + +--echo +--echo ** +--echo ** two UPDATE's running and one changing result set +--echo ** + --echo ** connection thread1 + #connect (thread1, localhost, mysqltest,,); + connection thread1; + --echo ** Set up table + eval SET SESSION STORAGE_ENGINE = $engine_type; + create table t1(eta int(11) not null, tipo int(11), c varchar(255)); + insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); + insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); + insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); + insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); + insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); + insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); + insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); + insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); + insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); + insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); + insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); + --echo ** Get ULL "hello" on thread 1 + select get_lock("hello",10); + + --echo ** connection thread2 + #connect (thread2, localhost, mysqltest,,); + connection thread2; + --echo ** Start transaction on thread 2 + begin; + --echo ** Update will cause a table scan. + --echo ** This will cause a hang on the first row where tipo=1 until the + --echo ** blocking ULL is released. + send update t1 set eta=1+get_lock("hello",10)*0 where tipo=1; + sleep 1; + + --echo ** connection thread1 + connection thread1; + --echo ** Start transaction on thread 1 + begin; + --echo ** Update on t1 will cause a table scan which will be blocked because + --echo ** the previously initiated table scan applied exclusive key locks on + --echo ** all primary keys. + --echo ** Not so if innodb_locks_unsafe_for_binlog is set. The locks that + --echo ** do not match the WHERE condition are released. + if ($keep_locks) + { + --error ER_LOCK_WAIT_TIMEOUT + update t1 set tipo=1 where tipo=2; + } + if (!$keep_locks) + { + update t1 set tipo=1 where tipo=2; + } + --echo ** Release ULL. This will release the next waiting ULL on thread 2. + select release_lock("hello"); + --echo ** The table should still be updated with updates for thread 1 only: + select * from t1; + + --echo ** connection thread2 + connection thread2; + --echo ** Release the lock and collect result from thread 2: + reap; + select release_lock("hello"); + --echo ** Seen from thread 2 the table should have been updated on four + --echo ** places. + select * from t1; + commit; + + --echo ** connection thread1 + connection thread1; + --echo ** Thread 2 has committed but the result should remain the same for + --echo ** thread 1 (updated on three places): + select * from t1; + commit; + --echo ** After a commit the table should be merged with the previous + --echo ** commit. + --echo ** This select should show both updates: + select * from t1; + + --echo ** connection thread2 + connection thread2; + select * from t1; + + --echo ** connection thread1 + connection thread1; + select * from t1; + +--echo ** connection default connection default; drop table t1; -# -# One UPDATE and one INSERT .... Monty's test -# +--echo +--echo ** +--echo ** One UPDATE and one INSERT .... Monty's test +--echo ** + --echo ** connection thread1 + #connect (thread1, localhost, mysqltest,,); + connection thread1; + --echo ** Set up table + eval SET SESSION STORAGE_ENGINE = $engine_type; + create table t1 (a int not null, b int not null); + insert into t1 values (1,1),(2,1),(3,1),(4,1); + --echo ** Create ULL 'hello2' + select get_lock("hello2",10); + + --echo ** connection thread2 + #connect (thread2, localhost, mysqltest,,); + connection thread2; + --echo ** Begin a new transaction on thread 2 + begin; + --echo ** Update will create a table scan which creates a ULL where a=2; + --echo ** this will hang waiting on thread 1. + send update t1 set b=10+get_lock(concat("hello",a),10)*0 where a=2; + sleep 1; + + --echo ** connection thread1 + connection thread1; + --echo ** Insert new values to t1 from thread 1; this created an implicit + --echo ** commit since there are no on-going transactions. + insert into t1 values (1,1); + --echo ** Release the ULL (thread 2 updates will finish). + select release_lock("hello2"); + --echo ** ..but thread 1 will still see t1 as if nothing has happend: + select * from t1; -#connect (thread1, localhost, mysqltest,,); -connection thread1; -create table t1 (a int not null, b int not null); -insert into t1 values (1,1),(2,1),(3,1),(4,1); -select get_lock("hello2",1000); -#connect (thread2, localhost, mysqltest,,); -connection thread2; -begin; -send update t1 set b=10+get_lock(concat("hello",a),1000)*0 where -a=2; -sleep 1; -connection thread1; -insert into t1 values (1,1); -select release_lock("hello2"); -select * from t1; -connection thread2; -reap; -select * from t1; -send commit; -connection thread1; -sleep 1; -connection thread2; -reap; + --echo ** connection thread2 + connection thread2; + --echo ** Collect results from thread 2 and release the lock. + reap; + select release_lock("hello2"); + --echo ** The table should look like the original+updates for thread 2, + --echo ** and consist of new rows: + select * from t1; + --echo ** Commit changes from thread 2 + commit; + +--echo ** connection default connection default; drop table t1; -# -# one UPDATE changing result set and SELECT ... FOR UPDATE -# -#connect (thread1, localhost, mysqltest,,); -connection thread1; -create table t1(eta int(11) not null, tipo int(11), c varchar(255)); -insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); -insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); -insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); -insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); -insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); -insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); -insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); -insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); -insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); -insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); -insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); -select get_lock("hello",10); -#connect (thread2, localhost, mysqltest,,); -connection thread2; -begin; -send select * from t1 where tipo=2 FOR UPDATE; -sleep 1; -connection thread1; -begin; -select release_lock("hello"); ---error 1205 -update t1 set tipo=1+get_lock("hello",10)*0 where tipo=2; -select * from t1; -connection thread2; -reap; -select * from t1; -send commit; -connection thread1; -commit; -connection thread2; -reap; -select * from t1; -connection thread1; -select * from t1; + +--echo +--echo ** +--echo ** one UPDATE changing result set and SELECT ... FOR UPDATE +--echo ** + --echo ** connection thread1 + #connect (thread1, localhost, mysqltest,,); + connection thread1; + --echo ** Set up table + eval SET SESSION STORAGE_ENGINE = $engine_type; + create table t1(eta int(11) not null, tipo int(11), c varchar(255)); + insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); + insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); + insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); + insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); + insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); + insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); + insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); + insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); + insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); + insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); + insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); + + --echo ** connection thread2 + #connect (thread2, localhost, mysqltest,,); + connection thread2; + --echo ** Begin a new transaction on thread 2 + begin; + --echo ** Select a range for update. + select * from t1 where tipo=2 FOR UPDATE; + + --echo ** connection thread1 + connection thread1; + --echo ** Begin a new transaction on thread 1 + begin; + --echo ** Update the same range which is marked for update on thread 2; this + --echo ** will hang because of row locks. + --error ER_LOCK_WAIT_TIMEOUT + update t1 set tipo=1 where tipo=2; + --echo ** After the update the table will be unmodified because the previous + --echo ** transaction failed and was rolled back. + select * from t1; + + --echo ** connection thread2 + connection thread2; + --echo ** The table should look unmodified from thread 2. + select * from t1; + --echo ** Sending a commit should release the row locks and enable + --echo ** thread 1 to complete the transaction. + commit; + + --echo ** connection thread1 + connection thread1; + --echo ** Commit on thread 1. + commit; + + --echo ** connection thread2 + connection thread2; + --echo ** The table should not have been changed. + select * from t1; + + --echo ** connection thread1 + connection thread1; + --echo ** Even on thread 1: + select * from t1; + +--echo ** connection default connection default; drop table t1; -# -# one UPDATE not changing result set and SELECT ... FOR UPDATE -# -#connect (thread1, localhost, mysqltest,,); -connection thread1; -create table t1(eta int(11) not null, tipo int(11), c varchar(255)); -insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); -insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); -insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); -insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); -insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); -insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); -insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); -insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); -insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); -insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); -insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); -select get_lock("hello",10); -#connect (thread2, localhost, mysqltest,,); -connection thread2; -begin; -send select * from t1 where tipo=2 FOR UPDATE; -sleep 1; -connection thread1; -begin; -select release_lock("hello"); ---error 1205 -update t1 set tipo=11+get_lock("hello",10)*0 where tipo=22; -select * from t1; -connection thread2; -reap; -select * from t1; -send commit; -connection thread1; -commit; -connection thread2; -reap; -select * from t1; -connection thread1; -select * from t1; + +--echo +--echo ** +--echo ** one UPDATE not changing result set and SELECT ... FOR UPDATE +--echo ** + --echo ** connection thread1 + #connect (thread1, localhost, mysqltest,,); + connection thread1; + --echo ** Set up table + eval SET SESSION STORAGE_ENGINE = $engine_type; + create table t1(eta int(11) not null, tipo int(11), c varchar(255)); + insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); + insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); + insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); + insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); + insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); + insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); + insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); + insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); + insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); + insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); + insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); + + --echo ** connection thread2 + #connect (thread2, localhost, mysqltest,,); + connection thread2; + --echo ** Starting new transaction on thread 2. + begin; + --echo ** Starting SELECT .. FOR UPDATE + select * from t1 where tipo=2 FOR UPDATE; + + --echo ** connection thread1 + connection thread1; + --echo + --echo ** Starting new transaction on thread 1 + begin; + --echo ** Updating single row using a table scan. This will time out + --echo ** because of ongoing transaction on thread 1 holding lock on + --echo ** all primary keys in the scan. + --echo ** Not so if innodb_locks_unsafe_for_binlog is set. The locks that + --echo ** do not match the WHERE condition are released. + if ($keep_locks) + { + --error ER_LOCK_WAIT_TIMEOUT + update t1 set tipo=11 where tipo=22; + } + if (!$keep_locks) + { + update t1 set tipo=11 where tipo=22; + } + --echo ** After the time out the transaction is aborted; no rows should + --echo ** have changed. + select * from t1; + + --echo ** connection thread2 + connection thread2; + --echo ** The same thing should hold true for the transaction on + --echo ** thread 2 + select * from t1; + send commit; + + --echo ** connection thread1 + connection thread1; + commit; + + --echo ** connection thread2 + connection thread2; + --echo ** Even after committing: + reap; + select * from t1; + + --echo ** connection thread1 + connection thread1; + select * from t1; + +--echo ** connection default connection default; drop table t1; -# -# two SELECT ... FOR UPDATE -# -#connect (thread1, localhost, mysqltest,,); -connection thread1; -create table t1(eta int(11) not null, tipo int(11), c varchar(255)); -insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); -insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); -insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); -insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); -insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); -insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); -insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); -insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); -insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); -insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); -insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); -select get_lock("hello",10); -#connect (thread2, localhost, mysqltest,,); -connection thread2; -begin; -send select * from t1 where tipo=2 FOR UPDATE; -sleep 1; -connection thread1; -begin; -select release_lock("hello"); ---error 1205 -select * from t1 where tipo=1 FOR UPDATE; -connection thread2; -reap; -select * from t1; -send commit; -connection thread1; -commit; -connection thread2; -reap; -select * from t1; -connection thread1; -select * from t1; + +--echo +--echo ** +--echo ** two SELECT ... FOR UPDATE +--echo ** + --echo ** connection thread1 + #connect (thread1, localhost, mysqltest,,); + connection thread1; + --echo ** Set up table + eval SET SESSION STORAGE_ENGINE = $engine_type; + create table t1(eta int(11) not null, tipo int(11), c varchar(255)); + insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); + insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); + insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); + insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); + insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); + insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); + insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); + insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); + insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); + insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); + insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); + + --echo ** connection thread2 + #connect (thread2, localhost, mysqltest,,); + connection thread2; + --echo ** Begin a new transaction on thread 2 + begin; + select * from t1 where tipo=2 FOR UPDATE; + + --echo ** connection thread1 + connection thread1; + --echo ** Begin a new transaction on thread 1 + begin; + --echo ** Selecting a range for update by table scan will be blocked + --echo ** because of on-going transaction on thread 2. + --error ER_LOCK_WAIT_TIMEOUT + select * from t1 where tipo=1 FOR UPDATE; + + --echo ** connection thread2 + connection thread2; + --echo ** Table will be unchanged and the select command will not be + --echo ** blocked: + select * from t1; + --echo ** Commit transacton on thread 2. + commit; + + --echo ** connection thread1 + connection thread1; + --echo ** Commit transaction on thread 1. + commit; + + --echo ** connection thread2 + connection thread2; + --echo ** Make sure table isn't blocked on thread 2: + select * from t1; + + --echo ** connection thread1 + connection thread1; + --echo ** Make sure table isn't blocked on thread 1: + select * from t1; + +--echo ** connection default connection default; drop table t1; -# -# one UPDATE changing result set and DELETE -# -#connect (thread1, localhost, mysqltest,,); -connection thread1; -create table t1(eta int(11) not null, tipo int(11), c varchar(255)); -insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); -insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); -insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); -insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); -insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); -insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); -insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); -insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); -insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); -insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); -insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); -select get_lock("hello",10); -#connect (thread2, localhost, mysqltest,,); -connection thread2; -begin; -send delete from t1 where tipo=2; -sleep 1; -connection thread1; -begin; -select release_lock("hello"); ---error 1205 -update t1 set tipo=1+get_lock("hello",10)*0 where tipo=2; -select * from t1; -connection thread2; -reap; -select * from t1; -send commit; -connection thread1; -commit; -connection thread2; -reap; -select * from t1; -connection thread1; -select * from t1; +--echo +--echo ** +--echo ** one UPDATE changing result set and DELETE +--echo ** + --echo ** connection thread1 + #connect (thread1, localhost, mysqltest,,); + connection thread1; + --echo ** Set up table + eval SET SESSION STORAGE_ENGINE = $engine_type; + create table t1(eta int(11) not null, tipo int(11), c varchar(255)); + insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); + insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); + insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); + insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); + insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); + insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); + insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); + insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); + insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); + insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); + insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); + + --echo ** connection thread2 + #connect (thread2, localhost, mysqltest,,); + connection thread2; + begin; + send delete from t1 where tipo=2; + sleep 1; + + --echo ** connection thread1 + connection thread1; + begin; + --error ER_LOCK_WAIT_TIMEOUT + update t1 set tipo=1 where tipo=2; + select * from t1; + + --echo ** connection thread2 + connection thread2; + reap; + select * from t1; + send commit; + + --echo ** connection thread1 + connection thread1; + commit; + + --echo ** connection thread2 + connection thread2; + reap; + select * from t1; + + --echo ** connection thread1 + connection thread1; + select * from t1; + +--echo ** connection default connection default; drop table t1; -# -# one UPDATE not changing result set and DELETE -# -#connect (thread1, localhost, mysqltest,,); -connection thread1; -create table t1(eta int(11) not null, tipo int(11), c varchar(255)); -insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); -insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); -insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); -insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); -insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); -insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); -insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); -insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); -insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); -insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); -insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); -select get_lock("hello",10); -#connect (thread2, localhost, mysqltest,,); -connection thread2; -begin; -send delete from t1 where tipo=2; -sleep 1; -connection thread1; -begin; -select release_lock("hello"); -update t1 set tipo=1+get_lock("hello",10)*0 where tipo=22; -select * from t1; -connection thread2; -reap; -select * from t1; -send commit; -connection thread1; -commit; -connection thread2; -reap; -select * from t1; -connection thread1; -select * from t1; +--echo +--echo ** +--echo ** one UPDATE not changing result set and DELETE +--echo ** + --echo ** connection thread1 + #connect (thread1, localhost, mysqltest,,); + connection thread1; + --echo ** Set up table + eval SET SESSION STORAGE_ENGINE = $engine_type; + create table t1(eta int(11) not null, tipo int(11), c varchar(255)); + insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); + insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); + insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); + insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); + insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); + insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); + insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); + insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); + insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); + insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); + insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); + + --echo ** connection thread2 + #connect (thread2, localhost, mysqltest,,); + connection thread2; + begin; + send delete from t1 where tipo=2; + sleep 1; + + --echo ** connection thread1 + connection thread1; + begin; + --echo ** Update on t1 will cause a table scan which will be blocked because + --echo ** the previously initiated table scan applied exclusive key locks on + --echo ** all primary keys. + --echo ** Not so if innodb_locks_unsafe_for_binlog is set. The locks that + --echo ** do not match the WHERE condition are released. + if ($keep_locks) + { + --error ER_LOCK_WAIT_TIMEOUT + update t1 set tipo=1 where tipo=22; + } + if (!$keep_locks) + { + update t1 set tipo=1 where tipo=22; + } + select * from t1; + + --echo ** connection thread2 + connection thread2; + reap; + select * from t1; + send commit; + + --echo ** connection thread1 + connection thread1; + commit; + + --echo ** connection thread2 + connection thread2; + reap; + select * from t1; + + --echo ** connection thread1 + connection thread1; + select * from t1; + +--echo ** connection default connection default; -sleep 1; drop table t1; + + disconnect thread1; disconnect thread2; + diff --git a/mysql-test/r/concurrent_innodb_safelog.result b/mysql-test/r/concurrent_innodb_safelog.result new file mode 100644 index 00000000000..98e1205901a --- /dev/null +++ b/mysql-test/r/concurrent_innodb_safelog.result @@ -0,0 +1,803 @@ +SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ; +SELECT @@global.tx_isolation; +@@global.tx_isolation +REPEATABLE-READ +SELECT @@global.innodb_locks_unsafe_for_binlog; +@@global.innodb_locks_unsafe_for_binlog +0 +# keep_locks == 1 +GRANT USAGE ON test.* TO mysqltest@localhost; +DO release_lock("hello"); +DO release_lock("hello2"); +drop table if exists t1; + +** +** two UPDATE's running and both changing distinct result sets +** +** connection thread1 +** Set up table +SET SESSION STORAGE_ENGINE = InnoDB; +create table t1(eta int(11) not null, tipo int(11), c varchar(255)); +insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); +insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); +insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); +insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); +insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); +insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); +insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); +insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); +insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); +insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); +insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); +** Get user level lock (ULL) for thread 1 +select get_lock("hello",10); +get_lock("hello",10) +1 +** connection thread2 +** Start transaction for thread 2 +begin; +** Update will cause a table scan and a new ULL will +** be created and blocked on the first row where tipo=11. +update t1 set eta=1+get_lock("hello",10)*0 where tipo=11; +** connection thread1 +** Start new transaction for thread 1 +begin; +** Update on t1 will cause a table scan which will be blocked because +** the previously initiated table scan applied exclusive key locks on +** all primary keys. +** Not so if innodb_locks_unsafe_for_binlog is set. The locks that +** do not match the WHERE condition are released. +update t1 set eta=2 where tipo=22; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +** Release user level name lock from thread 1. This will cause the ULL +** on thread 2 to end its wait. +select release_lock("hello"); +release_lock("hello") +1 +** Table is now updated with a new eta on tipo=22 for thread 1. +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +10 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +50 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection thread2 +** Release the lock and collect result from update on thread 2 +select release_lock("hello"); +release_lock("hello") +1 +** Table should have eta updates where tipo=11 but updates made by +** thread 1 shouldn't be visible yet. +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +10 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +50 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +1 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** Sending commit on thread 2. +commit; +** connection thread1 +** Make sure table reads didn't change yet on thread 1. +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +10 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +50 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** And send final commit on thread 1. +commit; +** Table should now be updated by both updates in the order of +** thread 1,2. +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +10 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +50 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +1 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection thread2 +** Make sure the output is similar for t1. +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +10 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +50 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +1 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection thread1 +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +10 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +50 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +1 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection default +drop table t1; + +** +** two UPDATE's running and one changing result set +** +** connection thread1 +** Set up table +SET SESSION STORAGE_ENGINE = InnoDB; +create table t1(eta int(11) not null, tipo int(11), c varchar(255)); +insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); +insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); +insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); +insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); +insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); +insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); +insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); +insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); +insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); +insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); +insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); +** Get ULL "hello" on thread 1 +select get_lock("hello",10); +get_lock("hello",10) +1 +** connection thread2 +** Start transaction on thread 2 +begin; +** Update will cause a table scan. +** This will cause a hang on the first row where tipo=1 until the +** blocking ULL is released. +update t1 set eta=1+get_lock("hello",10)*0 where tipo=1; +** connection thread1 +** Start transaction on thread 1 +begin; +** Update on t1 will cause a table scan which will be blocked because +** the previously initiated table scan applied exclusive key locks on +** all primary keys. +** Not so if innodb_locks_unsafe_for_binlog is set. The locks that +** do not match the WHERE condition are released. +update t1 set tipo=1 where tipo=2; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +** Release ULL. This will release the next waiting ULL on thread 2. +select release_lock("hello"); +release_lock("hello") +1 +** The table should still be updated with updates for thread 1 only: +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +10 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +50 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection thread2 +** Release the lock and collect result from thread 2: +select release_lock("hello"); +release_lock("hello") +1 +** Seen from thread 2 the table should have been updated on four +** places. +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +1 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +1 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +1 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +1 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +commit; +** connection thread1 +** Thread 2 has committed but the result should remain the same for +** thread 1 (updated on three places): +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +10 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +50 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +commit; +** After a commit the table should be merged with the previous +** commit. +** This select should show both updates: +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +1 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +1 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +1 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +1 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection thread2 +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +1 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +1 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +1 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +1 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection thread1 +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +1 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +1 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +1 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +1 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection default +drop table t1; + +** +** One UPDATE and one INSERT .... Monty's test +** +** connection thread1 +** Set up table +SET SESSION STORAGE_ENGINE = InnoDB; +create table t1 (a int not null, b int not null); +insert into t1 values (1,1),(2,1),(3,1),(4,1); +** Create ULL 'hello2' +select get_lock("hello2",10); +get_lock("hello2",10) +1 +** connection thread2 +** Begin a new transaction on thread 2 +begin; +** Update will create a table scan which creates a ULL where a=2; +** this will hang waiting on thread 1. +update t1 set b=10+get_lock(concat("hello",a),10)*0 where a=2; +** connection thread1 +** Insert new values to t1 from thread 1; this created an implicit +** commit since there are no on-going transactions. +insert into t1 values (1,1); +** Release the ULL (thread 2 updates will finish). +select release_lock("hello2"); +release_lock("hello2") +1 +** ..but thread 1 will still see t1 as if nothing has happend: +select * from t1; +a b +1 1 +2 1 +3 1 +4 1 +1 1 +** connection thread2 +** Collect results from thread 2 and release the lock. +select release_lock("hello2"); +release_lock("hello2") +1 +** The table should look like the original+updates for thread 2, +** and consist of new rows: +select * from t1; +a b +1 1 +2 10 +3 1 +4 1 +1 1 +** Commit changes from thread 2 +commit; +** connection default +drop table t1; + +** +** one UPDATE changing result set and SELECT ... FOR UPDATE +** +** connection thread1 +** Set up table +SET SESSION STORAGE_ENGINE = InnoDB; +create table t1(eta int(11) not null, tipo int(11), c varchar(255)); +insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); +insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); +insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); +insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); +insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); +insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); +insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); +insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); +insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); +insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); +insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); +** connection thread2 +** Begin a new transaction on thread 2 +begin; +** Select a range for update. +select * from t1 where tipo=2 FOR UPDATE; +eta tipo c +20 2 ddddddddddddddddddddddddddddddddddddddddddd +40 2 fffffffffffffffffffffffffffffffffffffffffff +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +** connection thread1 +** Begin a new transaction on thread 1 +begin; +** Update the same range which is marked for update on thread 2; this +** will hang because of row locks. +update t1 set tipo=1 where tipo=2; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +** After the update the table will be unmodified because the previous +** transaction failed and was rolled back. +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +10 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +50 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection thread2 +** The table should look unmodified from thread 2. +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +10 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +50 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** Sending a commit should release the row locks and enable +** thread 1 to complete the transaction. +commit; +** connection thread1 +** Commit on thread 1. +commit; +** connection thread2 +** The table should not have been changed. +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +10 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +50 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection thread1 +** Even on thread 1: +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +10 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +50 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection default +drop table t1; + +** +** one UPDATE not changing result set and SELECT ... FOR UPDATE +** +** connection thread1 +** Set up table +SET SESSION STORAGE_ENGINE = InnoDB; +create table t1(eta int(11) not null, tipo int(11), c varchar(255)); +insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); +insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); +insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); +insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); +insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); +insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); +insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); +insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); +insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); +insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); +insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); +** connection thread2 +** Starting new transaction on thread 2. +begin; +** Starting SELECT .. FOR UPDATE +select * from t1 where tipo=2 FOR UPDATE; +eta tipo c +20 2 ddddddddddddddddddddddddddddddddddddddddddd +40 2 fffffffffffffffffffffffffffffffffffffffffff +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +** connection thread1 + +** Starting new transaction on thread 1 +begin; +** Updating single row using a table scan. This will time out +** because of ongoing transaction on thread 1 holding lock on +** all primary keys in the scan. +** Not so if innodb_locks_unsafe_for_binlog is set. The locks that +** do not match the WHERE condition are released. +update t1 set tipo=11 where tipo=22; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +** After the time out the transaction is aborted; no rows should +** have changed. +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +10 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +50 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection thread2 +** The same thing should hold true for the transaction on +** thread 2 +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +10 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +50 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +commit; +** connection thread1 +commit; +** connection thread2 +** Even after committing: +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +10 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +50 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection thread1 +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +10 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +50 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection default +drop table t1; + +** +** two SELECT ... FOR UPDATE +** +** connection thread1 +** Set up table +SET SESSION STORAGE_ENGINE = InnoDB; +create table t1(eta int(11) not null, tipo int(11), c varchar(255)); +insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); +insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); +insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); +insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); +insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); +insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); +insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); +insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); +insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); +insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); +insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); +** connection thread2 +** Begin a new transaction on thread 2 +begin; +select * from t1 where tipo=2 FOR UPDATE; +eta tipo c +20 2 ddddddddddddddddddddddddddddddddddddddddddd +40 2 fffffffffffffffffffffffffffffffffffffffffff +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +** connection thread1 +** Begin a new transaction on thread 1 +begin; +** Selecting a range for update by table scan will be blocked +** because of on-going transaction on thread 2. +select * from t1 where tipo=1 FOR UPDATE; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +** connection thread2 +** Table will be unchanged and the select command will not be +** blocked: +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +10 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +50 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** Commit transacton on thread 2. +commit; +** connection thread1 +** Commit transaction on thread 1. +commit; +** connection thread2 +** Make sure table isn't blocked on thread 2: +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +10 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +50 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection thread1 +** Make sure table isn't blocked on thread 1: +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +10 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +50 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection default +drop table t1; + +** +** one UPDATE changing result set and DELETE +** +** connection thread1 +** Set up table +SET SESSION STORAGE_ENGINE = InnoDB; +create table t1(eta int(11) not null, tipo int(11), c varchar(255)); +insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); +insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); +insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); +insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); +insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); +insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); +insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); +insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); +insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); +insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); +insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); +** connection thread2 +begin; +delete from t1 where tipo=2; +** connection thread1 +begin; +update t1 set tipo=1 where tipo=2; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +10 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +50 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection thread2 +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +10 1 ccccccccccccccccccccccccccccccccccccccccccc +30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +50 1 ggggggggggggggggggggggggggggggggggggggggggg +70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +commit; +** connection thread1 +commit; +** connection thread2 +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +10 1 ccccccccccccccccccccccccccccccccccccccccccc +30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +50 1 ggggggggggggggggggggggggggggggggggggggggggg +70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection thread1 +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +10 1 ccccccccccccccccccccccccccccccccccccccccccc +30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +50 1 ggggggggggggggggggggggggggggggggggggggggggg +70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection default +drop table t1; + +** +** one UPDATE not changing result set and DELETE +** +** connection thread1 +** Set up table +SET SESSION STORAGE_ENGINE = InnoDB; +create table t1(eta int(11) not null, tipo int(11), c varchar(255)); +insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); +insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); +insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); +insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd"); +insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee"); +insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff"); +insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg"); +insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); +insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); +insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); +insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); +** connection thread2 +begin; +delete from t1 where tipo=2; +** connection thread1 +begin; +** Update on t1 will cause a table scan which will be blocked because +** the previously initiated table scan applied exclusive key locks on +** all primary keys. +** Not so if innodb_locks_unsafe_for_binlog is set. The locks that +** do not match the WHERE condition are released. +update t1 set tipo=1 where tipo=22; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +10 1 ccccccccccccccccccccccccccccccccccccccccccc +20 2 ddddddddddddddddddddddddddddddddddddddddddd +30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +40 2 fffffffffffffffffffffffffffffffffffffffffff +50 1 ggggggggggggggggggggggggggggggggggggggggggg +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection thread2 +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +10 1 ccccccccccccccccccccccccccccccccccccccccccc +30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +50 1 ggggggggggggggggggggggggggggggggggggggggggg +70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +commit; +** connection thread1 +commit; +** connection thread2 +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +10 1 ccccccccccccccccccccccccccccccccccccccccccc +30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +50 1 ggggggggggggggggggggggggggggggggggggggggggg +70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection thread1 +select * from t1; +eta tipo c +7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa +8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb +10 1 ccccccccccccccccccccccccccccccccccccccccccc +30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +50 1 ggggggggggggggggggggggggggggggggggggggggggg +70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection default +drop table t1; diff --git a/mysql-test/r/concurrent_innodb.result b/mysql-test/r/concurrent_innodb_unsafelog.result index 27e2cde077c..14973fe07d9 100644 --- a/mysql-test/r/concurrent_innodb.result +++ b/mysql-test/r/concurrent_innodb_unsafelog.result @@ -1,7 +1,23 @@ -SET SESSION STORAGE_ENGINE = InnoDB; +SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ; +SELECT @@global.tx_isolation; +@@global.tx_isolation +REPEATABLE-READ +SELECT @@global.innodb_locks_unsafe_for_binlog; +@@global.innodb_locks_unsafe_for_binlog +1 +# keep_locks == 0 +GRANT USAGE ON test.* TO mysqltest@localhost; +DO release_lock("hello"); +DO release_lock("hello2"); drop table if exists t1; -create table t1(eta int(11) not null, tipo int(11), c varchar(255)); + +** +** two UPDATE's running and both changing distinct result sets +** +** connection thread1 +** Set up table SET SESSION STORAGE_ENGINE = InnoDB; +create table t1(eta int(11) not null, tipo int(11), c varchar(255)); insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc"); @@ -13,16 +29,31 @@ insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); -select get_lock("hello",1); -get_lock("hello",1) +** Get user level lock (ULL) for thread 1 +select get_lock("hello",10); +get_lock("hello",10) 1 +** connection thread2 +** Start transaction for thread 2 begin; - update t1 set eta=1+get_lock("hello",1)*0 where tipo=11; +** Update will cause a table scan and a new ULL will +** be created and blocked on the first row where tipo=11. +update t1 set eta=1+get_lock("hello",10)*0 where tipo=11; +** connection thread1 +** Start new transaction for thread 1 begin; +** Update on t1 will cause a table scan which will be blocked because +** the previously initiated table scan applied exclusive key locks on +** all primary keys. +** Not so if innodb_locks_unsafe_for_binlog is set. The locks that +** do not match the WHERE condition are released. update t1 set eta=2 where tipo=22; +** Release user level name lock from thread 1. This will cause the ULL +** on thread 2 to end its wait. select release_lock("hello"); release_lock("hello") 1 +** Table is now updated with a new eta on tipo=22 for thread 1. select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -36,6 +67,13 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 2 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection thread2 +** Release the lock and collect result from update on thread 2 +select release_lock("hello"); +release_lock("hello") +1 +** Table should have eta updates where tipo=11 but updates made by +** thread 1 shouldn't be visible yet. select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -49,7 +87,10 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 1 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk - commit; +** Sending commit on thread 2. +commit; +** connection thread1 +** Make sure table reads didn't change yet on thread 1. select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -63,7 +104,10 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 2 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** And send final commit on thread 1. commit; +** Table should now be updated by both updates in the order of +** thread 1,2. select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -77,6 +121,8 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 2 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 1 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection thread2 +** Make sure the output is similar for t1. select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -90,6 +136,7 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 2 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 1 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection thread1 select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -103,7 +150,15 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 2 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 1 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection default drop table t1; + +** +** two UPDATE's running and one changing result set +** +** connection thread1 +** Set up table +SET SESSION STORAGE_ENGINE = InnoDB; create table t1(eta int(11) not null, tipo int(11), c varchar(255)); insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); @@ -116,16 +171,31 @@ insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); +** Get ULL "hello" on thread 1 select get_lock("hello",10); get_lock("hello",10) -0 +1 +** connection thread2 +** Start transaction on thread 2 begin; - update t1 set eta=1+get_lock("hello",10)*0 where tipo=1; +** Update will cause a table scan. +** This will cause a hang on the first row where tipo=1 until the +** blocking ULL is released. +update t1 set eta=1+get_lock("hello",10)*0 where tipo=1; +** connection thread1 +** Start transaction on thread 1 begin; +** Update on t1 will cause a table scan which will be blocked because +** the previously initiated table scan applied exclusive key locks on +** all primary keys. +** Not so if innodb_locks_unsafe_for_binlog is set. The locks that +** do not match the WHERE condition are released. update t1 set tipo=1 where tipo=2; +** Release ULL. This will release the next waiting ULL on thread 2. select release_lock("hello"); release_lock("hello") -0 +1 +** The table should still be updated with updates for thread 1 only: select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -139,6 +209,13 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection thread2 +** Release the lock and collect result from thread 2: +select release_lock("hello"); +release_lock("hello") +1 +** Seen from thread 2 the table should have been updated on four +** places. select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -152,7 +229,10 @@ eta tipo c 1 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk - commit; +commit; +** connection thread1 +** Thread 2 has committed but the result should remain the same for +** thread 1 (updated on three places): select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -167,19 +247,23 @@ eta tipo c 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk commit; +** After a commit the table should be merged with the previous +** commit. +** This select should show both updates: select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 8 8 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb -10 1 ccccccccccccccccccccccccccccccccccccccccccc +1 1 ccccccccccccccccccccccccccccccccccccccccccc 20 1 ddddddddddddddddddddddddddddddddddddddddddd -30 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee +1 1 eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee 40 1 fffffffffffffffffffffffffffffffffffffffffff -50 1 ggggggggggggggggggggggggggggggggggggggggggg +1 1 ggggggggggggggggggggggggggggggggggggggggggg 60 1 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh -70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii +1 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection thread2 select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -193,6 +277,7 @@ eta tipo c 1 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection thread1 select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -206,19 +291,36 @@ eta tipo c 1 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection default drop table t1; + +** +** One UPDATE and one INSERT .... Monty's test +** +** connection thread1 +** Set up table +SET SESSION STORAGE_ENGINE = InnoDB; create table t1 (a int not null, b int not null); insert into t1 values (1,1),(2,1),(3,1),(4,1); -select get_lock("hello2",1000); -get_lock("hello2",1000) +** Create ULL 'hello2' +select get_lock("hello2",10); +get_lock("hello2",10) 1 +** connection thread2 +** Begin a new transaction on thread 2 begin; - update t1 set b=10+get_lock(concat("hello",a),1000)*0 where -a=2; +** Update will create a table scan which creates a ULL where a=2; +** this will hang waiting on thread 1. +update t1 set b=10+get_lock(concat("hello",a),10)*0 where a=2; +** connection thread1 +** Insert new values to t1 from thread 1; this created an implicit +** commit since there are no on-going transactions. insert into t1 values (1,1); +** Release the ULL (thread 2 updates will finish). select release_lock("hello2"); release_lock("hello2") 1 +** ..but thread 1 will still see t1 as if nothing has happend: select * from t1; a b 1 1 @@ -226,6 +328,13 @@ a b 3 1 4 1 1 1 +** connection thread2 +** Collect results from thread 2 and release the lock. +select release_lock("hello2"); +release_lock("hello2") +1 +** The table should look like the original+updates for thread 2, +** and consist of new rows: select * from t1; a b 1 1 @@ -233,8 +342,17 @@ a b 3 1 4 1 1 1 - commit; +** Commit changes from thread 2 +commit; +** connection default drop table t1; + +** +** one UPDATE changing result set and SELECT ... FOR UPDATE +** +** connection thread1 +** Set up table +SET SESSION STORAGE_ENGINE = InnoDB; create table t1(eta int(11) not null, tipo int(11), c varchar(255)); insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); @@ -247,17 +365,24 @@ insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); -select get_lock("hello",10); -get_lock("hello",10) -1 +** connection thread2 +** Begin a new transaction on thread 2 begin; - select * from t1 where tipo=2 FOR UPDATE; +** Select a range for update. +select * from t1 where tipo=2 FOR UPDATE; +eta tipo c +20 2 ddddddddddddddddddddddddddddddddddddddddddd +40 2 fffffffffffffffffffffffffffffffffffffffffff +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +** connection thread1 +** Begin a new transaction on thread 1 begin; -select release_lock("hello"); -release_lock("hello") -1 -update t1 set tipo=1+get_lock("hello",10)*0 where tipo=2; +** Update the same range which is marked for update on thread 2; this +** will hang because of row locks. +update t1 set tipo=1 where tipo=2; ERROR HY000: Lock wait timeout exceeded; try restarting transaction +** After the update the table will be unmodified because the previous +** transaction failed and was rolled back. select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -271,10 +396,8 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -eta tipo c -20 2 ddddddddddddddddddddddddddddddddddddddddddd -40 2 fffffffffffffffffffffffffffffffffffffffffff -60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +** connection thread2 +** The table should look unmodified from thread 2. select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -288,8 +411,14 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk - commit; +** Sending a commit should release the row locks and enable +** thread 1 to complete the transaction. commit; +** connection thread1 +** Commit on thread 1. +commit; +** connection thread2 +** The table should not have been changed. select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -303,6 +432,8 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection thread1 +** Even on thread 1: select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -316,7 +447,15 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection default drop table t1; + +** +** one UPDATE not changing result set and SELECT ... FOR UPDATE +** +** connection thread1 +** Set up table +SET SESSION STORAGE_ENGINE = InnoDB; create table t1(eta int(11) not null, tipo int(11), c varchar(255)); insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); @@ -329,17 +468,27 @@ insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); -select get_lock("hello",10); -get_lock("hello",10) -1 +** connection thread2 +** Starting new transaction on thread 2. begin; - select * from t1 where tipo=2 FOR UPDATE; +** Starting SELECT .. FOR UPDATE +select * from t1 where tipo=2 FOR UPDATE; +eta tipo c +20 2 ddddddddddddddddddddddddddddddddddddddddddd +40 2 fffffffffffffffffffffffffffffffffffffffffff +60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +** connection thread1 + +** Starting new transaction on thread 1 begin; -select release_lock("hello"); -release_lock("hello") -1 -update t1 set tipo=11+get_lock("hello",10)*0 where tipo=22; -ERROR HY000: Lock wait timeout exceeded; try restarting transaction +** Updating single row using a table scan. This will time out +** because of ongoing transaction on thread 1 holding lock on +** all primary keys in the scan. +** Not so if innodb_locks_unsafe_for_binlog is set. The locks that +** do not match the WHERE condition are released. +update t1 set tipo=11 where tipo=22; +** After the time out the transaction is aborted; no rows should +** have changed. select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -351,12 +500,11 @@ eta tipo c 50 1 ggggggggggggggggggggggggggggggggggggggggggg 60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii -80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +80 11 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk -eta tipo c -20 2 ddddddddddddddddddddddddddddddddddddddddddd -40 2 fffffffffffffffffffffffffffffffffffffffffff -60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +** connection thread2 +** The same thing should hold true for the transaction on +** thread 2 select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -370,8 +518,11 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk - commit; commit; +** connection thread1 +commit; +** connection thread2 +** Even after committing: select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -383,8 +534,9 @@ eta tipo c 50 1 ggggggggggggggggggggggggggggggggggggggggggg 60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii -80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +80 11 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection thread1 select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -396,9 +548,17 @@ eta tipo c 50 1 ggggggggggggggggggggggggggggggggggggggggggg 60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii -80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj +80 11 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection default drop table t1; + +** +** two SELECT ... FOR UPDATE +** +** connection thread1 +** Set up table +SET SESSION STORAGE_ENGINE = InnoDB; create table t1(eta int(11) not null, tipo int(11), c varchar(255)); insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); @@ -411,21 +571,24 @@ insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); -select get_lock("hello",10); -get_lock("hello",10) -1 +** connection thread2 +** Begin a new transaction on thread 2 begin; - select * from t1 where tipo=2 FOR UPDATE; -begin; -select release_lock("hello"); -release_lock("hello") -1 -select * from t1 where tipo=1 FOR UPDATE; -ERROR HY000: Lock wait timeout exceeded; try restarting transaction +select * from t1 where tipo=2 FOR UPDATE; eta tipo c 20 2 ddddddddddddddddddddddddddddddddddddddddddd 40 2 fffffffffffffffffffffffffffffffffffffffffff 60 2 hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh +** connection thread1 +** Begin a new transaction on thread 1 +begin; +** Selecting a range for update by table scan will be blocked +** because of on-going transaction on thread 2. +select * from t1 where tipo=1 FOR UPDATE; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +** connection thread2 +** Table will be unchanged and the select command will not be +** blocked: select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -439,8 +602,13 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk - commit; +** Commit transacton on thread 2. commit; +** connection thread1 +** Commit transaction on thread 1. +commit; +** connection thread2 +** Make sure table isn't blocked on thread 2: select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -454,6 +622,8 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection thread1 +** Make sure table isn't blocked on thread 1: select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -467,7 +637,15 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection default drop table t1; + +** +** one UPDATE changing result set and DELETE +** +** connection thread1 +** Set up table +SET SESSION STORAGE_ENGINE = InnoDB; create table t1(eta int(11) not null, tipo int(11), c varchar(255)); insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); @@ -480,16 +658,12 @@ insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); -select get_lock("hello",10); -get_lock("hello",10) -1 +** connection thread2 begin; - delete from t1 where tipo=2; +delete from t1 where tipo=2; +** connection thread1 begin; -select release_lock("hello"); -release_lock("hello") -1 -update t1 set tipo=1+get_lock("hello",10)*0 where tipo=2; +update t1 set tipo=1 where tipo=2; ERROR HY000: Lock wait timeout exceeded; try restarting transaction select * from t1; eta tipo c @@ -504,6 +678,7 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection thread2 select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -514,8 +689,10 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk - commit; commit; +** connection thread1 +commit; +** connection thread2 select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -526,6 +703,7 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection thread1 select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -536,7 +714,15 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection default drop table t1; + +** +** one UPDATE not changing result set and DELETE +** +** connection thread1 +** Set up table +SET SESSION STORAGE_ENGINE = InnoDB; create table t1(eta int(11) not null, tipo int(11), c varchar(255)); insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"); insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"); @@ -549,16 +735,17 @@ insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh"); insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii"); insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj"); insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk"); -select get_lock("hello",10); -get_lock("hello",10) -1 +** connection thread2 begin; - delete from t1 where tipo=2; +delete from t1 where tipo=2; +** connection thread1 begin; -select release_lock("hello"); -release_lock("hello") -1 -update t1 set tipo=1+get_lock("hello",10)*0 where tipo=22; +** Update on t1 will cause a table scan which will be blocked because +** the previously initiated table scan applied exclusive key locks on +** all primary keys. +** Not so if innodb_locks_unsafe_for_binlog is set. The locks that +** do not match the WHERE condition are released. +update t1 set tipo=1 where tipo=22; select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -572,6 +759,7 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 1 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection thread2 select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -582,8 +770,10 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 22 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk - commit; commit; +** connection thread1 +commit; +** connection thread2 select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -594,6 +784,7 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 1 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection thread1 select * from t1; eta tipo c 7 7 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa @@ -604,4 +795,5 @@ eta tipo c 70 1 iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii 80 1 jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj 90 11 kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk +** connection default drop table t1; diff --git a/mysql-test/t/concurrent_innodb.test b/mysql-test/t/concurrent_innodb.test deleted file mode 100644 index 5e9258af8f1..00000000000 --- a/mysql-test/t/concurrent_innodb.test +++ /dev/null @@ -1,20 +0,0 @@ -# t/concurrent_innodb.test -# -# Concurrent InnoDB tests, mainly in UPDATE's -# Bug#3300 -# Designed and tested by Sinisa Milivojevic, sinisa@mysql.com -# -# two non-interfering UPDATE's not changing result set -# -# Last update: -# 2006-07-26 ML test refactored (MySQL 5.1) -# main code t/innodb_concurrent.test -> include/concurrent.inc -# new wrapper t/concurrent_innodb.test - -# test takes circa 5 minutes to run, so it's big ---source include/big_test.inc - ---source include/have_innodb.inc -let $engine_type= InnoDB; - ---source include/concurrent.inc diff --git a/mysql-test/t/concurrent_innodb_safelog-master.opt b/mysql-test/t/concurrent_innodb_safelog-master.opt new file mode 100644 index 00000000000..462f8fbe828 --- /dev/null +++ b/mysql-test/t/concurrent_innodb_safelog-master.opt @@ -0,0 +1 @@ +--innodb_lock_wait_timeout=1 diff --git a/mysql-test/t/concurrent_innodb_safelog.test b/mysql-test/t/concurrent_innodb_safelog.test new file mode 100644 index 00000000000..828df9ef717 --- /dev/null +++ b/mysql-test/t/concurrent_innodb_safelog.test @@ -0,0 +1,23 @@ +# t/concurrent_innodb_safelog.test +# +# Concurrent InnoDB tests +# +# Last update: +# 2006-07-26 ML test refactored (MySQL 5.1) +# main code t/innodb_concurrent.test -> include/concurrent.inc +# new wrapper t/concurrent_innodb.test +# 2008-06-03 KP test refactored; removed name locks, added comments. +# renamed wrapper t/concurrent_innodb.test -> +# t/concurrent_innodb_unsafelog.test +# new wrapper t/concurrent_innodb_safelog.test +# + +--source include/have_innodb.inc + +let $engine_type= InnoDB; + +SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ; +# innodb_locks_unsafe_for_binlog not set for this test + +--source include/concurrent.inc + diff --git a/mysql-test/t/concurrent_innodb-master.opt b/mysql-test/t/concurrent_innodb_unsafelog-master.opt index f76bada5208..210212a40bc 100644 --- a/mysql-test/t/concurrent_innodb-master.opt +++ b/mysql-test/t/concurrent_innodb_unsafelog-master.opt @@ -1 +1,2 @@ --innodb_locks_unsafe_for_binlog +--innodb_lock_wait_timeout=1 diff --git a/mysql-test/t/concurrent_innodb_unsafelog.test b/mysql-test/t/concurrent_innodb_unsafelog.test new file mode 100644 index 00000000000..e2c03655e43 --- /dev/null +++ b/mysql-test/t/concurrent_innodb_unsafelog.test @@ -0,0 +1,23 @@ +# t/concurrent_innodb_unsafelog.test +# +# Concurrent InnoDB tests +# +# Last update: +# 2006-07-26 ML test refactored (MySQL 5.1) +# main code t/innodb_concurrent.test -> include/concurrent.inc +# new wrapper t/concurrent_innodb.test +# 2008-06-03 KP test refactored; removed name locks, added comments. +# renamed wrapper t/concurrent_innodb.test -> +# t/concurrent_innodb_unsafelog.test +# new wrapper t/concurrent_innodb_safelog.test +# + +--source include/have_innodb.inc + +let $engine_type= InnoDB; + +SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ; +# innodb_locks_unsafe_for_binlog is set fro this test. + +--source include/concurrent.inc + |