Debian Codegraph @

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

raw
1 --source include/have_debug.inc
2
3 # This test demonstrates what locks are taken by the current implementation
4 # during various combinations of:
5 # {INSERT,REPLACE} x
6 # {no conflict, conflict, "conflict" with delete-marked}^
7 # {PRIMARY, UNIQUE KEY}
8 # This test does not represent any requirement, i.e. failing this test
9 # does not necessarily mean that the implementation is wrong.
10 # This test simply documents what is the current state of affairs, so
11 # that we can easier spot if some change in the code affects this part
12 # of the system, and so that we can discuss/think about improvements.
13
14 --let i=0
15
16 while ( $i <= 17 )
17 {
18 --disable_query_log
19 --disable_result_log
20 CREATE TABLE `t1` (
21 `a` tinyint(4) NOT NULL,
22 `b` tinyint(4) NOT NULL,
23 PRIMARY KEY (`b`),
24 UNIQUE KEY `a` (`a`)
25 ) ENGINE=InnoDB;
26 INSERT INTO t1 VALUES
27 ( 0, 0 ),
28 ( 1, 1 ),
29 ( 2, 2 ),
30 ( 3, 3 ),
31 ( 4, 4 );
32
33 --sorted_result
34 SELECT * FROM t1;
35 SET GLOBAL innodb_purge_stop_now = ON;
36
37 DELETE FROM t1 WHERE a=2 AND b=2;
38
39 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
40 BEGIN;
41 --enable_query_log
42 --enable_result_log
43 if($i == 0){
44 --echo # does not conflict
45 INSERT INTO t1 VALUES (5,5);
46 }
47 if($i == 1){
48 --echo # conflicts on `a`, but not on `b`
49 --error ER_DUP_ENTRY
50 INSERT INTO t1 VALUES (0,5);
51 }
52 if($i == 2){
53 --echo # conflicts on `b`, but not on `a`
54 --error ER_DUP_ENTRY
55 INSERT INTO t1 VALUES (5,0);
56 }
57 if($i == 3){
58 --echo # conflicts on both `a` and `b`
59 --error ER_DUP_ENTRY
60 INSERT INTO t1 VALUES (0,0);
61 }
62 if($i == 4){
63 --echo # conflicts on `a` with deleted, but not on `b`
64 INSERT INTO t1 VALUES (2,5);
65 }
66 if($i == 5){
67 --echo # conflicts on `b` with deleted, but not on `a`
68 INSERT INTO t1 VALUES (5,2);
69 }
70 if($i == 6){
71 --echo # conflicts on both `a` and `b` with deleted row
72 INSERT INTO t1 VALUES (2,2);
73 }
74 if($i == 7){
75 --echo # conflicts on `a` with deleted, and with `b` on not-deleted
76 --error ER_DUP_ENTRY
77 INSERT INTO t1 VALUES (2,0);
78 }
79 if($i == 8){
80 --echo # conflicts on `b` with deleted, and with `a` on not-deleted
81 --error ER_DUP_ENTRY
82 INSERT INTO t1 VALUES (0,2);
83 }
84
85 if($i == 9){
86 --echo # does not conflict
87 REPLACE INTO t1 VALUES (5,5);
88 }
89 if($i == 10){
90 --echo # conflicts on `a`, but not on `b`
91 REPLACE INTO t1 VALUES (0,5);
92 }
93 if($i == 11){
94 --echo # conflicts on `b`, but not on `a`
95 REPLACE INTO t1 VALUES (5,0);
96 }
97 if($i == 12){
98 --echo # conflicts on both `a` and `b`
99 REPLACE INTO t1 VALUES (0,0);
100 }
101 if($i == 13){
102 --echo # conflicts on `a` with deleted, but not on `b`
103 REPLACE INTO t1 VALUES (2,5);
104 }
105 if($i == 14){
106 --echo # conflicts on `b` with deleted, but not on `a`
107 REPLACE INTO t1 VALUES (5,2);
108 }
109 if($i == 15){
110 --echo # conflicts on both `a` and `b` with deleted row
111 REPLACE INTO t1 VALUES (2,2);
112 }
113 if($i == 16){
114 --echo # conflicts on `a` with deleted, and with `b` on not-deleted
115 REPLACE INTO t1 VALUES (2,0);
116 }
117 if($i == 17){
118 --echo # conflicts on `b` with deleted, and with `a` on not-deleted
119 REPLACE INTO t1 VALUES (0,2);
120 }
121
122 --sorted_result
123 SELECT index_name,lock_type,lock_mode,lock_status,lock_data
124 FROM performance_schema.data_locks
125 WHERE object_schema='test' AND object_name = 't1';
126
127 COMMIT;
128 --sorted_result
129 SELECT * FROM t1;
130 --disable_query_log
131 --disable_result_log
132 SET GLOBAL innodb_purge_run_now = ON;
133 DROP TABLE t1;
134
135 --inc $i
136 }