Debian Codegraph @

mysql-test/suite/innodb/t/innodb-ac-non-locking-select.test

raw
1 # DEBUG_SYNC must be compiled in.
2 --source include/have_debug_sync.inc
3
4
5 CREATE TABLE t1 (c1 INT , c2 CHAR(10), PRIMARY KEY (c1)) ENGINE = InnoDB;
6 INSERT INTO t1 VALUES(0, "0");
7 INSERT INTO t1 VALUES(1, "1");
8 INSERT INTO t1 VALUES(2, "2");
9 INSERT INTO t1 VALUES(3, "3");
10
11 --connect (con1,localhost,root,,)
12 --connect (con2,localhost,root,,)
13 --connect (con3,localhost,root,,)
14 --connect (con4,localhost,root,,)
15 --connect (con5,localhost,root,,)
16 --connect (con6,localhost,root,,)
17
18 connection default;
19 # Disable query log to avoid non-deterministic output conflicts
20 SET AUTOCOMMIT=0;
21 BEGIN;
22 # Lock all the records
23 SELECT * FROM t1 FOR UPDATE;
24 --disable_query_log
25
26 connection con1;
27 SET AUTOCOMMIT=1;
28 # Test if locking autocommit selects end up in the trx_sys_t::trx_list.
29 # We check this via the INFORMATION_SCHEMA.INNODB_TRX.
30 # This should block and show up in the I_S.
31 SET DEBUG_SYNC='lock_wait_suspend_thread_enter SIGNAL waiting1';
32 --send
33 SELECT COUNT(*) FROM t1 LOCK IN SHARE MODE;
34
35 connection con2;
36 SET AUTOCOMMIT=1;
37 # Test if non-locking autocommit selects end up in the trx_sys_t::trx_list.
38 # We check this via the INFORMATION_SCHEMA.INNODB_TRX.
39 # This should not block and should not show up in the I_S.
40 --send
41 SELECT COUNT(*) FROM t1;
42
43 connection con3;
44 SET AUTOCOMMIT=1;
45 # Note: autocommit non-locking selects are not converted to locking selects
46 # Therefore this should not block;
47 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
48 --send
49 SELECT COUNT(*) FROM t1;
50
51 connection con4;
52 SET AUTOCOMMIT=0;
53 # Note: Non-locking selects are converted to locking selects
54 # therefore this should block;
55 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
56 SET DEBUG_SYNC='now WAIT_FOR waiting1';
57 SET DEBUG_SYNC='lock_wait_suspend_thread_enter SIGNAL waiting4';
58 --send
59 SELECT COUNT(*) FROM t1 WHERE c1 >= 0;
60
61 connection con5;
62 SET AUTOCOMMIT=1;
63 # This should not block
64 BEGIN;
65 --send
66 SELECT COUNT(*) FROM t1;
67
68 connection con6;
69 SET AUTOCOMMIT=1;
70 # This will ignore the auto-commit setting but wont block because it is
71 # a non-locking select.
72 XA START '1';
73 --enable_query_log
74 SELECT * FROM t1 WHERE c1 <= 3;
75
76 connection default;
77 # Wait for SELECTs to get into the lock wait queue
78 SET DEBUG_SYNC='now WAIT_FOR waiting4';
79
80 # Check the number of non-locking transactions
81 let $wait_condition =
82 SELECT COUNT(*) = 5
83 FROM INFORMATION_SCHEMA.INNODB_TRX
84 WHERE trx_autocommit_non_locking = 0;
85 --source include/wait_condition.inc
86
87 # Check the waiting transactions
88 SELECT trx_state, trx_query, trx_autocommit_non_locking
89 FROM INFORMATION_SCHEMA.INNODB_TRX
90 WHERE trx_state = 'LOCK WAIT'
91 ORDER BY trx_query;
92
93 INSERT INTO t1 VALUES(4, '4');
94 COMMIT;
95
96 connection con6;
97 SELECT * FROM t1 WHERE c1 <= 4;
98 XA END '1';
99 XA PREPARE '1';
100 XA ROLLBACK '1';
101
102 connection default;
103 disconnect con2;
104 disconnect con3;
105 disconnect con5;
106 disconnect con6;
107
108 connection con1;
109 reap;
110 disconnect con1;
111
112 connection con4;
113 reap;
114 disconnect con4;
115
116 connection default;
117 DROP TABLE t1;
118
119 # Clean up resources used in this test case.
120 --disable_warnings
121 SET DEBUG_SYNC= 'RESET';
122 --enable_warnings