1drop table if exists t1; 2connect a,localhost,root,,; 3connect b,localhost,root,,; 4connection a; 5set binlog_format=mixed; 6set session transaction isolation level repeatable read; 7create table t1(a int not null) 8engine=innodb 9DEFAULT CHARSET=latin1 10PARTITION BY RANGE(a) 11(PARTITION p0 VALUES LESS THAN (20), 12PARTITION p1 VALUES LESS THAN MAXVALUE); 13insert into t1 values (1),(2),(3),(4),(5),(6),(7); 14set autocommit=0; 15select * from t1 where a=3 lock in share mode; 16a 173 18connection b; 19set binlog_format=mixed; 20set session transaction isolation level repeatable read; 21set autocommit=0; 22update t1 set a=10 where a=5; 23ERROR HY000: Lock wait timeout exceeded; try restarting transaction 24commit; 25connection a; 26commit; 27connection b; 28set session transaction isolation level read committed; 29update t1 set a=10 where a=5; 30connection a; 31select * from t1 where a=2 for update; 32ERROR HY000: Lock wait timeout exceeded; try restarting transaction 33select * from t1 where a=2 limit 1 for update; 34a 352 36connection b; 37update t1 set a=11 where a=6; 38update t1 set a=12 where a=2; 39ERROR HY000: Lock wait timeout exceeded; try restarting transaction 40update t1 set a=13 where a=1; 41ERROR HY000: Lock wait timeout exceeded; try restarting transaction 42connection a; 43commit; 44connection b; 45update t1 set a=14 where a=1; 46commit; 47connection a; 48select * from t1; 49a 5010 5111 5214 532 543 554 567 57drop table t1; 58connection default; 59disconnect a; 60disconnect b; 61connect con1,localhost,root,,; 62connect con2,localhost,root,,; 63SET SESSION AUTOCOMMIT = 0; 64SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 65set binlog_format=mixed; 66connection con1; 67CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(256)) 68ENGINE = InnoDB 69PARTITION BY RANGE (a) 70(PARTITION p0 VALUES LESS THAN (300), 71PARTITION p1 VALUES LESS THAN MAXVALUE); 72INSERT INTO t1 VALUES (1,2); 73# 1. test for locking: 74BEGIN; 75UPDATE t1 SET b = 12 WHERE a = 1; 76affected rows: 1 77info: Rows matched: 1 Changed: 1 Warnings: 0 78SELECT * FROM t1; 79a b 801 12 81connection con2; 82UPDATE t1 SET b = 21 WHERE a = 1; 83ERROR HY000: Lock wait timeout exceeded; try restarting transaction 84ROLLBACK; 85connection con1; 86SELECT * FROM t1; 87a b 881 12 89ROLLBACK; 90# 2. test for serialized update: 91CREATE TABLE t2 (a INT); 92TRUNCATE t1; 93INSERT INTO t1 VALUES (1,'init'); 94CREATE PROCEDURE p1() 95BEGIN 96# retry the UPDATE in case it times out the lock before con1 has time 97# to COMMIT. 98DECLARE do_retry INT DEFAULT 0; 99DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET do_retry = 1; 100retry_loop:LOOP 101UPDATE t1 SET b = CONCAT(b, '+con2') WHERE a = 1; 102IF do_retry = 0 THEN 103LEAVE retry_loop; 104END IF; 105SET do_retry = 0; 106END LOOP; 107INSERT INTO t2 VALUES (); 108END| 109BEGIN; 110UPDATE t1 SET b = CONCAT(b, '+con1') WHERE a = 1; 111affected rows: 1 112info: Rows matched: 1 Changed: 1 Warnings: 0 113SELECT * FROM t1; 114a b 1151 init+con1 116connection con2; 117CALL p1;; 118connection con1; 119SELECT * FROM t1; 120a b 1211 init+con1 122COMMIT; 123SELECT * FROM t1; 124a b 1251 init+con1 126connection con2; 127SELECT * FROM t1; 128a b 1291 init+con1+con2 130COMMIT; 131connection con1; 132# 3. test for updated key column: 133TRUNCATE t1; 134DELETE FROM t2; 135INSERT INTO t1 VALUES (1,'init'); 136BEGIN; 137UPDATE t1 SET a = 2, b = CONCAT(b, '+con1') WHERE a = 1; 138affected rows: 1 139info: Rows matched: 1 Changed: 1 Warnings: 0 140SELECT * FROM t1; 141a b 1422 init+con1 143connection con2; 144CALL p1;; 145connection con1; 146SELECT * FROM t1; 147a b 1482 init+con1 149COMMIT; 150SELECT * FROM t1; 151a b 1522 init+con1 153connection con2; 154SELECT * FROM t1; 155a b 1562 init+con1 157connection default; 158disconnect con1; 159disconnect con2; 160DROP PROCEDURE p1; 161DROP TABLE t1, t2; 162