Debian Codegraph @

mysql-test/suite/innodb/r/innodb-lock.result

raw
1 set global innodb_table_locks=1;
2 select @@innodb_table_locks;
3 @@innodb_table_locks
4 1
5 set @@innodb_table_locks=1;
6 connect con1,localhost,root,,;
7 create table t1 (id integer, x integer) engine=INNODB;
8 insert into t1 values(0, 0);
9 set autocommit=0;
10 SELECT * from t1 where id = 0 FOR UPDATE;
11 id x
12 0 0
13 connect con2,localhost,root,,;
14 set autocommit=0;
15 lock table t1 write;
16 connection con1;
17 update t1 set x=1 where id = 0;
18 select * from t1;
19 id x
20 0 1
21 commit;
22 connection con2;
23 update t1 set x=2 where id = 0;
24 commit;
25 unlock tables;
26 connection con1;
27 select * from t1;
28 id x
29 0 2
30 commit;
31 drop table t1;
32 #
33 # Old lock method (where LOCK TABLE was ignored by InnoDB) no longer
34 # works when LOCK TABLE ... WRITE is used due to fix for bugs #46272
35 # "MySQL 5.4.4, new MDL: unnecessary and bug #37346 "innodb does not
36 # detect deadlock between update and alter table".
37 # After WL#6671 "Improve scalability by not using thr_lock.c locks
38 # for InnoDB tables" was implemented it no longer works for LOCK TABLES
39 # ,,, READ as well.
40 # LOCK TABLES locks are now completely handled by MDL subsystem.
41 #
42 set @@innodb_table_locks=0;
43 create table t1 (id integer primary key, x integer) engine=INNODB;
44 insert into t1 values(0, 0),(1,1),(2,2);
45 commit;
46 SELECT * from t1 where id = 0 FOR UPDATE;
47 id x
48 0 0
49 connection con2;
50 set autocommit=0;
51 set @@innodb_table_locks=0;
52 # The following statement should block because SQL-level lock
53 # is taken on t1 which will wait until concurrent transaction
54 # is commited.
55 # Sending:
56 lock table t1 write;;
57 connection con1;
58 # Wait until LOCK TABLE is blocked on SQL-level lock.
59 # We should be able to do UPDATEs and SELECTs within transaction.
60 update t1 set x=1 where id = 0;
61 select * from t1;
62 id x
63 0 1
64 1 1
65 2 2
66 # Unblock LOCK TABLE.
67 commit;
68 connection con2;
69 # Reap LOCK TABLE.
70 unlock tables;
71 connection con1;
72 select * from t1 where id = 0 for update;
73 id x
74 0 1
75 connection con2;
76 # The following statement should block because SQL-level lock
77 # is taken on t1 which will wait until concurrent transaction
78 # is commited.
79 # Sending:
80 lock table t1 read;;
81 connection con1;
82 # Wait until LOCK TABLE is blocked on SQL-level lock.
83 # We should be able to do UPDATEs and SELECTs within transaction.
84 update t1 set x=2 where id = 0;
85 select * from t1;
86 id x
87 0 2
88 1 1
89 2 2
90 # Unblock LOCK TABLE.
91 commit;
92 connection con2;
93 # Reap LOCK TABLE.
94 unlock tables;
95 connection default;
96 drop table t1;
97 #
98 #Bug#12842206 INNODB LOCKING REGRESSION FOR INSERT IGNORE
99 #fixed by re-fixing Bug#7975
100 #aka Bug#11759688 52020: InnoDB can still deadlock on just INSERT...
101 #
102 connection default;
103 CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL) ENGINE=InnoDB;
104 INSERT INTO t1 VALUES(3,1);
105 BEGIN;
106 INSERT IGNORE INTO t1 VALUES(3,14);
107 Warnings:
108 Warning 1062 Duplicate entry '3' for key 'PRIMARY'
109 connection con1;
110 BEGIN;
111 INSERT IGNORE INTO t1 VALUES(3,23);
112 Warnings:
113 Warning 1062 Duplicate entry '3' for key 'PRIMARY'
114 SELECT * FROM t1 FOR UPDATE;
115 connection con2;
116 disconnect con2;
117 connection default;
118 COMMIT;
119 connection con1;
120 a b
121 3 1
122 COMMIT;
123 connection default;
124 DROP TABLE t1;
125 #
126 # MDEV-11080 InnoDB: Failing assertion:
127 # table->n_waiting_or_granted_auto_inc_locks > 0
128 #
129 CREATE TABLE t1 (pk INTEGER AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;
130 INSERT INTO t1 VALUES (NULL),(NULL);
131 CREATE TABLE t2 LIKE t1;
132 BEGIN;
133 connection con1;
134 BEGIN;
135 DELETE FROM t2;
136 connection default;
137 LOCK TABLE t2 READ;;
138 connection con1;
139 SET innodb_lock_wait_timeout= 1, lock_wait_timeout= 2;
140 INSERT INTO t2 SELECT * FROM t1;
141 COMMIT;
142 connection default;
143 UNLOCK TABLES;
144 DROP TABLE t1, t2;
145 #
146 # MDEV-16709 InnoDB: Error: trx already had an AUTO-INC lock
147 #
148 CREATE TABLE t1 (pk INT AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB
149 PARTITION BY key (pk) PARTITIONS 2;
150 INSERT INTO t1 VALUES(100);
151 INSERT INTO t1 VALUES(101);
152 CREATE TABLE t2 (a INT) ENGINE=InnoDB;
153 INSERT INTO t2 VALUES (1),(2),(3),(4),(5),(6);
154 CREATE TABLE t3 (b INT) ENGINE=InnoDB;
155 INSERT INTO t3 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9);
156 connection con1;
157 INSERT t1 SELECT NULL FROM t2;
158 connection default;
159 INSERT t1 SELECT NULL FROM t3;
160 connection con1;
161 disconnect con1;
162 connection default;
163 DROP TABLE t1, t2, t3;