338 lines
9.3 KiB
Plaintext
338 lines
9.3 KiB
Plaintext
################################################################
|
|
# #
|
|
# Bug #89829: lock_rec_has_to_wait_vats ignores locks held by #
|
|
# transactions being rolled back #
|
|
# #
|
|
################################################################
|
|
CREATE TABLE t1 (
|
|
id INT PRIMARY KEY,
|
|
val INT NOT NULL
|
|
) ENGINE=InnoDB;
|
|
INSERT INTO t1 (id, val) VALUES (1,1), (3,3), (5,5);
|
|
SET @innodb_lock_wait_timeout_saved = @@global.innodb_lock_wait_timeout;
|
|
SET @@global.innodb_lock_wait_timeout = 100000;
|
|
# establishing connection cats0
|
|
# establishing connection cats1
|
|
# establishing connection cats2
|
|
# establishing connection cats3
|
|
# establishing connection cats4
|
|
# establishing connection cats5
|
|
# establishing connection cats6
|
|
# establishing connection cats7
|
|
# establishing connection cats8
|
|
# establishing connection cats9
|
|
# establishing connection cats10
|
|
# establishing connection cats11
|
|
# establishing connection cats12
|
|
# establishing connection cats13
|
|
# establishing connection cats14
|
|
# establishing connection cats15
|
|
# establishing connection cats16
|
|
# establishing connection cats17
|
|
# establishing connection cats18
|
|
# establishing connection cats19
|
|
# establishing connection cats20
|
|
# establishing connection cats21
|
|
# establishing connection cats22
|
|
# establishing connection cats23
|
|
# establishing connection cats24
|
|
# establishing connection cats25
|
|
# establishing connection cats26
|
|
# establishing connection cats27
|
|
# establishing connection cats28
|
|
# establishing connection cats29
|
|
# establishing connection cats30
|
|
# establishing connection cats31
|
|
# establishing connection cats32
|
|
# wating for cats1
|
|
# wating for cats2
|
|
# wating for cats3
|
|
# wating for cats4
|
|
# wating for cats5
|
|
# wating for cats6
|
|
# wating for cats7
|
|
# wating for cats8
|
|
# wating for cats9
|
|
# wating for cats10
|
|
# wating for cats11
|
|
# wating for cats12
|
|
# wating for cats13
|
|
# wating for cats14
|
|
# wating for cats15
|
|
# wating for cats16
|
|
# wating for cats17
|
|
# wating for cats18
|
|
# wating for cats19
|
|
# wating for cats20
|
|
# wating for cats21
|
|
# wating for cats22
|
|
# wating for cats23
|
|
# wating for cats24
|
|
# wating for cats25
|
|
# wating for cats26
|
|
# wating for cats27
|
|
# wating for cats28
|
|
# wating for cats29
|
|
# wating for cats30
|
|
# wating for cats31
|
|
# wating for cats32
|
|
# too_big_to_fail:
|
|
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
|
|
BEGIN;
|
|
INSERT INTO t1 (id, val)
|
|
VALUES (100, 100), (200, 200), (300, 300), (400, 400), (500, 500);
|
|
SELECT * FROM t1 WHERE id = 2 FOR UPDATE;
|
|
id val
|
|
# victim:
|
|
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
|
|
BEGIN;
|
|
SELECT * FROM t1 WHERE id = 4 FOR UPDATE;
|
|
id val
|
|
UPDATE t1 SET val = 200 WHERE id = 3;
|
|
SET DEBUG_SYNC =
|
|
'lock_wait_has_finished_waiting WAIT_FOR victim_can_rollback';
|
|
INSERT INTO t1 (id, val) VALUES (2,2);
|
|
# seer:
|
|
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
|
|
BEGIN;
|
|
SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL seer_will_wait';
|
|
UPDATE t1 SET val = val + 10 WHERE id = 3;
|
|
# too_big_to_fail:
|
|
SET DEBUG_SYNC = 'now WAIT_FOR seer_will_wait';
|
|
SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL too_big_to_fail_will_wait';
|
|
INSERT INTO t1 (id, val) VALUES (4, 4);
|
|
# default:
|
|
SET DEBUG_SYNC = 'now WAIT_FOR too_big_to_fail_will_wait';
|
|
# Number of X locks granted: 1
|
|
SET DEBUG_SYNC = 'now SIGNAL victim_can_rollback';
|
|
# seer:
|
|
SELECT * FROM t1 WHERE id = 3 FOR UPDATE;
|
|
id val
|
|
3 13
|
|
SET DEBUG_SYNC = 'now SIGNAL victim_can_rollback';
|
|
COMMIT;
|
|
# victim:
|
|
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
|
|
ROLLBACK;
|
|
# too_big_to_fail:
|
|
ROLLBACK;
|
|
# default:
|
|
SELECT * FROM t1;
|
|
id val
|
|
1 1
|
|
3 13
|
|
5 5
|
|
# cleaning up connection cats0
|
|
# cleaning up connection cats1
|
|
# cleaning up connection cats2
|
|
# cleaning up connection cats3
|
|
# cleaning up connection cats4
|
|
# cleaning up connection cats5
|
|
# cleaning up connection cats6
|
|
# cleaning up connection cats7
|
|
# cleaning up connection cats8
|
|
# cleaning up connection cats9
|
|
# cleaning up connection cats10
|
|
# cleaning up connection cats11
|
|
# cleaning up connection cats12
|
|
# cleaning up connection cats13
|
|
# cleaning up connection cats14
|
|
# cleaning up connection cats15
|
|
# cleaning up connection cats16
|
|
# cleaning up connection cats17
|
|
# cleaning up connection cats18
|
|
# cleaning up connection cats19
|
|
# cleaning up connection cats20
|
|
# cleaning up connection cats21
|
|
# cleaning up connection cats22
|
|
# cleaning up connection cats23
|
|
# cleaning up connection cats24
|
|
# cleaning up connection cats25
|
|
# cleaning up connection cats26
|
|
# cleaning up connection cats27
|
|
# cleaning up connection cats28
|
|
# cleaning up connection cats29
|
|
# cleaning up connection cats30
|
|
# cleaning up connection cats31
|
|
# cleaning up connection cats32
|
|
DROP TABLE t1;
|
|
SET DEBUG_SYNC = 'RESET';
|
|
SET @@global.innodb_lock_wait_timeout = @innodb_lock_wait_timeout_saved;
|
|
#####################
|
|
# #
|
|
# End of Bug #89829 #
|
|
# #
|
|
#####################
|
|
#####################################################################
|
|
# #
|
|
# Bug #89737: Using VATS with spatial index can lead to transaction #
|
|
# never being woken up #
|
|
# #
|
|
#####################################################################
|
|
CREATE TABLE t1 (
|
|
id INT PRIMARY KEY,
|
|
p GEOMETRY NOT NULL SRID 0,
|
|
SPATIAL KEY p_idx(p)
|
|
) ENGINE=InnoDB;
|
|
INSERT INTO t1 (id, p) VALUES
|
|
(1,POINT(1,1)),
|
|
(2,POINT(2,2)),
|
|
(3,POINT(3,3)),
|
|
(4,POINT(4,4));
|
|
SET @innodb_lock_wait_timeout_saved = @@global.innodb_lock_wait_timeout;
|
|
SET @@global.innodb_lock_wait_timeout = 10;
|
|
# Using FCFS
|
|
# in selecting_thread
|
|
USE test;
|
|
BEGIN;
|
|
SELECT id
|
|
FROM t1
|
|
WHERE MBRContains(
|
|
ST_GeomFromText('Polygon((2 2, 2 4, 4 4, 4 2, 2 2))'),
|
|
p
|
|
)
|
|
FOR UPDATE;
|
|
id
|
|
3
|
|
# in inserting_thread
|
|
USE test;
|
|
BEGIN;
|
|
SET DEBUG_SYNC =
|
|
'lock_wait_will_wait SIGNAL inserting_thread_waits';
|
|
INSERT INTO t1 (id, p) VALUES (0, POINT (3, 3));;
|
|
# in selecting_thread
|
|
SET DEBUG_SYNC = 'now WAIT_FOR inserting_thread_waits';
|
|
COMMIT;
|
|
# in inserting_thread
|
|
ROLLBACK;
|
|
# Using CATS
|
|
# establishing connection cats0
|
|
# establishing connection cats1
|
|
# establishing connection cats2
|
|
# establishing connection cats3
|
|
# establishing connection cats4
|
|
# establishing connection cats5
|
|
# establishing connection cats6
|
|
# establishing connection cats7
|
|
# establishing connection cats8
|
|
# establishing connection cats9
|
|
# establishing connection cats10
|
|
# establishing connection cats11
|
|
# establishing connection cats12
|
|
# establishing connection cats13
|
|
# establishing connection cats14
|
|
# establishing connection cats15
|
|
# establishing connection cats16
|
|
# establishing connection cats17
|
|
# establishing connection cats18
|
|
# establishing connection cats19
|
|
# establishing connection cats20
|
|
# establishing connection cats21
|
|
# establishing connection cats22
|
|
# establishing connection cats23
|
|
# establishing connection cats24
|
|
# establishing connection cats25
|
|
# establishing connection cats26
|
|
# establishing connection cats27
|
|
# establishing connection cats28
|
|
# establishing connection cats29
|
|
# establishing connection cats30
|
|
# establishing connection cats31
|
|
# establishing connection cats32
|
|
# wating for cats1
|
|
# wating for cats2
|
|
# wating for cats3
|
|
# wating for cats4
|
|
# wating for cats5
|
|
# wating for cats6
|
|
# wating for cats7
|
|
# wating for cats8
|
|
# wating for cats9
|
|
# wating for cats10
|
|
# wating for cats11
|
|
# wating for cats12
|
|
# wating for cats13
|
|
# wating for cats14
|
|
# wating for cats15
|
|
# wating for cats16
|
|
# wating for cats17
|
|
# wating for cats18
|
|
# wating for cats19
|
|
# wating for cats20
|
|
# wating for cats21
|
|
# wating for cats22
|
|
# wating for cats23
|
|
# wating for cats24
|
|
# wating for cats25
|
|
# wating for cats26
|
|
# wating for cats27
|
|
# wating for cats28
|
|
# wating for cats29
|
|
# wating for cats30
|
|
# wating for cats31
|
|
# wating for cats32
|
|
# in selecting_thread
|
|
USE test;
|
|
BEGIN;
|
|
SELECT id
|
|
FROM t1
|
|
WHERE MBRContains(
|
|
ST_GeomFromText('Polygon((2 2, 2 4, 4 4, 4 2, 2 2))'),
|
|
p
|
|
)
|
|
FOR UPDATE;
|
|
id
|
|
3
|
|
# in inserting_thread
|
|
USE test;
|
|
BEGIN;
|
|
SET DEBUG_SYNC =
|
|
'lock_wait_will_wait SIGNAL inserting_thread_waits';
|
|
INSERT INTO t1 (id, p) VALUES (0, POINT (3, 3));;
|
|
# in selecting_thread
|
|
SET DEBUG_SYNC = 'now WAIT_FOR inserting_thread_waits';
|
|
COMMIT;
|
|
# in inserting_thread
|
|
ROLLBACK;
|
|
# cleaning up connection cats0
|
|
# cleaning up connection cats1
|
|
# cleaning up connection cats2
|
|
# cleaning up connection cats3
|
|
# cleaning up connection cats4
|
|
# cleaning up connection cats5
|
|
# cleaning up connection cats6
|
|
# cleaning up connection cats7
|
|
# cleaning up connection cats8
|
|
# cleaning up connection cats9
|
|
# cleaning up connection cats10
|
|
# cleaning up connection cats11
|
|
# cleaning up connection cats12
|
|
# cleaning up connection cats13
|
|
# cleaning up connection cats14
|
|
# cleaning up connection cats15
|
|
# cleaning up connection cats16
|
|
# cleaning up connection cats17
|
|
# cleaning up connection cats18
|
|
# cleaning up connection cats19
|
|
# cleaning up connection cats20
|
|
# cleaning up connection cats21
|
|
# cleaning up connection cats22
|
|
# cleaning up connection cats23
|
|
# cleaning up connection cats24
|
|
# cleaning up connection cats25
|
|
# cleaning up connection cats26
|
|
# cleaning up connection cats27
|
|
# cleaning up connection cats28
|
|
# cleaning up connection cats29
|
|
# cleaning up connection cats30
|
|
# cleaning up connection cats31
|
|
# cleaning up connection cats32
|
|
DROP TABLE t1;
|
|
SET DEBUG_SYNC = 'RESET';
|
|
SET @@global.innodb_lock_wait_timeout = @innodb_lock_wait_timeout_saved;
|
|
#####################
|
|
# #
|
|
# End of Bug #89737 #
|
|
# #
|
|
#####################
|