| 1 |
# BUG#32586458 INNODB CLUSTER SLOWNESS SEEING SESSIONS WITH STATUS "WAITING |
| 2 |
# FOR HANDLER TO COMMIT" |
| 3 |
# |
| 4 |
--source include/have_debug_sync.inc |
| 5 |
|
| 6 |
# This scenario demonstrates that when there are N reasons for a lock wait, |
| 7 |
# (connections rk_1,..,rk_N hold S lock on a row needed by rt_wait) |
| 8 |
# which go away one by one (as rk_$k get rolled back), then InnoDB correctly |
| 9 |
# reports one of the remaining reasons for wait. |
| 10 |
# InnoDB either grants a requested lock or picks one of conflicting transactions |
| 11 |
# as the current reason it has to wait. If there is more than one conflicting |
| 12 |
# transaction, the choice is non-deterministic. Thus, this test uses a trick: |
| 13 |
# it relies on the InnoDB deadlock checker's logic, which rolls back a |
| 14 |
# transaction which is part of a deadlock cycle consisting of edges that are |
| 15 |
# "current reason for wait". |
| 16 |
# Therefore this MTR does not have to know in which order it should rollback the |
| 17 |
# transactions rt_1,...,rt_N to follow the order of picking "current reason for |
| 18 |
# wait". Instead this MTR simply arranges it so that there are N small deadlock |
| 19 |
# cycles, k-th of them involving rt_k and rt_wait only: |
| 20 |
# |
| 21 |
# rt_wait <--x-locked-by----- 100 <---has-requested-s-lock---\ |
| 22 |
# | | |
| 23 |
# | / | \ |
| 24 |
# | rt_1 ... rt_N |
| 25 |
# | ^ ^ ^ |
| 26 |
# | \ | / |
| 27 |
# | | |
| 28 |
# \--has-requested-x-lock--> 20 --s-locked-----------------/ |
| 29 |
# |
| 30 |
# The wait-for graph between transactions inspected by InnoDB's deadlock checker |
| 31 |
# is a sparse subgraph of the above: only one of the paths from rt_wait to one |
| 32 |
# rt_k is picked. It is the same edge which is reported to Server layer. |
| 33 |
# As it is a part of a deadlock cycle, the victim will be chosen, cycle removed, |
| 34 |
# and another edge will be picked and so on. |
| 35 |
# We make sure rt_wait is never chosen as a victim, by making its TRX_WEIGHT |
| 36 |
# high, by INSERTing several records. |
| 37 |
# What remains is to count that the number of reported wait-for edges is N*2. |
| 38 |
|
| 39 |
# Create a table |
| 40 |
CREATE TABLE t ( |
| 41 |
id INT PRIMARY KEY |
| 42 |
) ENGINE=InnoDB; |
| 43 |
INSERT INTO t VALUES (10), (20), (30), (40), (50); |
| 44 |
|
| 45 |
# We want to be informed about any lock waits reported to Server layer |
| 46 |
--let $debug_point = syncpoint_report_lock_collision |
| 47 |
--source include/add_debug_point.inc |
| 48 |
|
| 49 |
--let $n_blockers = 13 |
| 50 |
|
| 51 |
# Create N connections to the server. |
| 52 |
# Using connections 1..N, start a transaction, acquire shared lock over a given |
| 53 |
# record using `SELECT ... FOR SHARE` |
| 54 |
--let $k = $n_blockers |
| 55 |
while ($k != 0) |
| 56 |
{ |
| 57 |
--connect (rt_$k,127.0.0.1,root,,test,,) |
| 58 |
BEGIN; |
| 59 |
SELECT id FROM t FORCE INDEX (PRIMARY) WHERE id = 20 FOR SHARE; |
| 60 |
--dec $k |
| 61 |
} |
| 62 |
|
| 63 |
|
| 64 |
# Using connection N + 1, acquire exclusive lock over the same record |
| 65 |
# using `SELECT ... FOR UPDATE`. This will cause the rt_wait to wait on a lock. |
| 66 |
--connect (rt_wait,127.0.0.1,root,,test,,) |
| 67 |
BEGIN; |
| 68 |
# make it heavy by inserting a lot of records |
| 69 |
INSERT INTO t VALUES (60), (70), (80), (90), (100), (110), (120); |
| 70 |
--send SELECT * FROM t FORCE INDEX (PRIMARY) WHERE id = 20 FOR UPDATE |
| 71 |
|
| 72 |
|
| 73 |
# Verify that the first reason for wait was reported |
| 74 |
--connection default |
| 75 |
SET DEBUG_SYNC = 'now WAIT_FOR reached_report_lock_collision'; |
| 76 |
SET DEBUG_SYNC = 'now SIGNAL continue_report_lock_collision'; |
| 77 |
|
| 78 |
# create N small deadlocks cycles between rt_$k and and rt_wait |
| 79 |
while ($k != $n_blockers) |
| 80 |
{ |
| 81 |
--inc $k |
| 82 |
--connection rt_$k |
| 83 |
--send SELECT * FROM t FORCE INDEX (PRIMARY) WHERE id=100 FOR SHARE |
| 84 |
} |
| 85 |
|
| 86 |
--connection default |
| 87 |
|
| 88 |
--let expected_reports = `SELECT $n_blockers * 2 - 1` |
| 89 |
while ($expected_reports != 0) |
| 90 |
{ |
| 91 |
--echo # expect $expected_reports more edges to be reported |
| 92 |
SET DEBUG_SYNC = 'now WAIT_FOR reached_report_lock_collision'; |
| 93 |
SET DEBUG_SYNC = 'now SIGNAL continue_report_lock_collision'; |
| 94 |
--dec $expected_reports |
| 95 |
} |
| 96 |
while ($k != 0) |
| 97 |
{ |
| 98 |
--echo # excpecting that connection $k was rolled back as a deadlock victim |
| 99 |
--connection rt_$k |
| 100 |
--error ER_LOCK_DEADLOCK |
| 101 |
--reap |
| 102 |
--disconnect rt_$k |
| 103 |
--dec $k |
| 104 |
} |
| 105 |
|
| 106 |
# Clean up |
| 107 |
--connection rt_wait |
| 108 |
--reap |
| 109 |
ROLLBACK; |
| 110 |
--connection default |
| 111 |
--disconnect rt_wait |
| 112 |
--source include/remove_debug_point.inc |
| 113 |
DROP TABLE t; |