1
SET @orig_lock_wait_timeout= @@innodb_lock_wait_timeout;
2
SELECT @orig_lock_wait_timeout;
3
@orig_lock_wait_timeout
5
SET GLOBAL innodb_lock_wait_timeout=2;
6
DROP TABLE IF EXISTS t1;
9
, padding VARCHAR(200) NOT NULL
12
INSERT INTO t1 VALUES (1, "I love testing.");
13
INSERT INTO t1 VALUES (3, "I hate testing.");
14
INSERT INTO t1 VALUES (5, "I still hate testing.");
16
SELECT id FROM t1 FOR UPDATE;
21
UPDATE t1 SET id=1000 WHERE padding='I love testing.';
22
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
23
UPDATE t1 SET id=id + 1;
26
SET GLOBAL innodb_lock_wait_timeout=@orig_lock_wait_timeout ;
28
CREATE TABLE `test`.`t1` ( `id` INT NOT NULL, `padding` VARCHAR(200) COLLATE utf8_general_ci NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB COLLATE = utf8_general_ci;
31
INSERT INTO `test`.`t1` (`id`,`padding`) VALUES (1,'I love testing.');
34
INSERT INTO `test`.`t1` (`id`,`padding`) VALUES (3,'I hate testing.');
37
INSERT INTO `test`.`t1` (`id`,`padding`) VALUES (5,'I still hate testing.');
40
UPDATE `test`.`t1` SET `id`=2 WHERE `id`=1;
41
UPDATE `test`.`t1` SET `id`=4 WHERE `id`=3;
42
UPDATE `test`.`t1` SET `id`=6 WHERE `id`=5;
45
DROP TABLE `test`.`t1`;
48
Generating statements for innodb replication log
50
CREATE TABLE `test`.`t1` ( `id` INT NOT NULL, `padding` VARCHAR(200) COLLATE utf8_general_ci NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB COLLATE = utf8_general_ci;
53
INSERT INTO `test`.`t1` (`id`,`padding`) VALUES (1,'I love testing.');
56
INSERT INTO `test`.`t1` (`id`,`padding`) VALUES (3,'I hate testing.');
59
INSERT INTO `test`.`t1` (`id`,`padding`) VALUES (5,'I still hate testing.');
62
UPDATE `test`.`t1` SET `id`=2 WHERE `id`=1;
63
UPDATE `test`.`t1` SET `id`=4 WHERE `id`=3;
64
UPDATE `test`.`t1` SET `id`=6 WHERE `id`=5;
67
DROP TABLE `test`.`t1`;
69
SET GLOBAL transaction_log_truncate_debug= true;