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