Debian Codegraph @

mysql-test/suite/innodb/t/mysql_tables_deadlocks.test

raw
1 --source include/have_debug_sync.inc
2
3 --echo # Bug #28523042
4 --echo # INNODB: ASSERTION FAILURE: LOCK0LOCK.CC:7034 IN DEADLOCKCHECKER::SEARCH*
5
6 # Save the original settings, to be restored at the end of test
7 SET @innodb_lock_wait_timeout_saved = @@global.innodb_lock_wait_timeout;
8
9 # Make sure that transactions will not finish prematurely
10 SET @@global.innodb_lock_wait_timeout = 100000;
11
12 # The original scenario from bug report, simplified a little, to still produce
13 # assertion failure on old code, but not involve XA and nondeterministic
14 # ouput.
15 CREATE TABLE t1(f1 INT KEY,f2 INT);
16 INSERT INTO t1 VALUES(2,2);
17 # We need SERIALIZABLE level, as in REPEATABLE READ, SELECT .. FOR SHARE
18 # does not take any locks on DD tables.
19 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
20 BEGIN;
21 # This SELECT places an S,REC_NOT_GAP on a single row of innodb_table_stats
22 SELECT COUNT(1) FROM mysql.innodb_table_stats
23 WHERE database_name='test' AND table_name='t1' FOR SHARE;
24 # This INSERT causes a background update of stats for test.t1,
25 # which has to wait because we hold S,REC_NOT_GAP on this particular record
26 INSERT INTO t1 VALUES(1,2);
27 # This SELECT will attempt to get X,REC_NOT_GAP lock on the same record,
28 # but has to wait for the background thread due to FCFS nature of queue,
29 # which causes a deadlock cycle
30 SELECT count(1) FROM mysql.innodb_table_stats
31 WHERE database_name='test' AND table_name='t1' FOR UPDATE;
32 # The background thread will be chosen as a victim, which will cause an ERROR
33 # to be reported to the error log:
34 call mtr.add_suppression(
35 "Cannot save table statistics for table `test`.`t1`: Deadlock"
36 );
37 COMMIT;
38 DROP TABLE t1;
39
40 # The test below intends to check how our code reacts to a deadlock in an
41 # InnoDB table from mysql.* database schema. In particular, we don't want
42 # any assertion failures.
43 # Make a list of names of tables to test.
44 # These are the InnoDB tables visible to the end user in mysql.* database.
45 # We exclude some table names from the list:
46 # - ndb_binlog_index is only available on builds with NDB Cluster
47 # - We exclude ACL tables as Server skips row locking, which is not
48 # desirable for this test case.
49 CREATE TABLE t1 (
50 id INT PRIMARY KEY AUTO_INCREMENT,
51 name TEXT
52 ) ENGINE=InnoDB
53 SELECT table_name AS name
54 FROM information_schema.tables
55 WHERE table_schema="mysql" AND engine="InnoDB"
56 AND table_name NOT IN (
57 "ndb_binlog_index", "user", "db", "tables_priv", "columns_priv",
58 "procs_priv", "proxies_priv", "role_edges", "default_roles",
59 "global_grants", "password_history",
60 "replication_asynchronous_connection_failover_managed"
61 )
62 ORDER BY table_name;
63
64 --let $i = 1
65 --let $n = `SELECT COUNT(*) FROM t1`
66
67 while($i <= $n)
68 {
69 --let $name = `SELECT name FROM t1 WHERE id = $i`
70 --echo Preparing deadlock scenario for mysql.$name
71 --let $cnt = `SELECT COUNT(*) FROM mysql.$name`
72 --disable_query_log
73 --disable_result_log
74
75 # We plan to create a deadlock cycle in a quite simple way:
76 # 1. c1: locks at least one row in $name FOR UPDATE (let's call the row "A")
77 # 2. c2: locks the row with id=10 in t1 FOR SHARE (let's call the row "B")
78 # 3. c2: waits for a lock on the row "A" FOR UPDATE
79 # 4. c1: now tries to lock the row "B" FOR UPDATE
80 # However, if the table $name is initially empty, we need to first insert row "A",
81 # to have something to work with.
82 # This requires us to guess plauisible default values for all columns of "A".
83 # We exploit that in sql-mode="" we can simply pass 0 for each column.
84 # We simply count number of columns and prepare a string with that many
85 # zeros.
86 # Also, to force the c2 to be chosen as a victim, we ensure that c1 has
87 # greater weight, by allowing it to lock more rows in t1 in step 4.
88 # I am aware that these tests are fragile, so if it is costly to maintain them
89 # please feel free to remove them - they were useful during development, but
90 # probably not worth spending resources to keep them up-to-date.
91 --connect (c1, localhost, root,,)
92 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
93 BEGIN;
94 if(!$cnt)
95 {
96 SET SESSION sql_mode = "";
97 let $zeros= `
98 SELECT REPEAT("0,", COUNT(*) - 1)
99 FROM information_schema.columns
100 WHERE table_schema = 'mysql'
101 AND table_name = '$name'
102 `;
103
104 --eval INSERT INTO mysql.$name VALUES ($zeros 0)
105 }
106 # 1. c1: locks at least one row in $name FOR UPDATE (let's call the row "A")
107 --eval SELECT COUNT(*) FROM mysql.$name FOR UPDATE
108
109 --connect (c2, localhost, root,,)
110 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
111 BEGIN;
112 # 2. c2: locks the row with id=10 in t1 FOR SHARE (let's call the row "B")
113 SELECT id FROM t1 WHERE id=10 FOR SHARE;
114 SET DEBUG_SYNC = 'lock_wait_will_wait SIGNAL c2_will_wait';
115 # 3. c2: waits for a lock on the row "A" FOR UPDATE
116 --send_eval SELECT COUNT(*) FROM mysql.$name FOR UPDATE
117
118 --connection c1
119 SET DEBUG_SYNC = 'now WAIT_FOR c2_will_wait';
120 # 4. c1: now tries to lock the row "B" FOR UPDATE
121 SELECT COUNT(*) FROM t1 FOR UPDATE;
122 --enable_query_log
123 --enable_result_log
124 ROLLBACK;
125
126 --connection c2
127 --error ER_LOCK_DEADLOCK
128 --reap
129 ROLLBACK;
130
131 --connection default
132 --disconnect c1
133 --disconnect c2
134
135 --inc $i
136 }
137
138 DROP TABLE t1;
139
140 SET @@global.innodb_lock_wait_timeout = @innodb_lock_wait_timeout_saved;