1set global innodb_table_locks=1; 2select @@innodb_table_locks; 3@@innodb_table_locks 41 5set @@innodb_table_locks=1; 6connect con1,localhost,root,,; 7create table t1 (id integer, x integer) engine=INNODB; 8insert into t1 values(0, 0); 9set autocommit=0; 10SELECT * from t1 where id = 0 FOR UPDATE; 11id x 120 0 13connect con2,localhost,root,,; 14set autocommit=0; 15lock table t1 write; 16connection con1; 17update t1 set x=1 where id = 0; 18select * from t1; 19id x 200 1 21commit; 22connection con2; 23update t1 set x=2 where id = 0; 24commit; 25unlock tables; 26connection con1; 27select * from t1; 28id x 290 2 30commit; 31drop 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# 42set @@innodb_table_locks=0; 43create table t1 (id integer primary key, x integer) engine=INNODB; 44insert into t1 values(0, 0),(1,1),(2,2); 45commit; 46SELECT * from t1 where id = 0 FOR UPDATE; 47id x 480 0 49connection con2; 50set autocommit=0; 51set @@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: 56lock table t1 write;; 57connection con1; 58# Wait until LOCK TABLE is blocked on SQL-level lock. 59# We should be able to do UPDATEs and SELECTs within transaction. 60update t1 set x=1 where id = 0; 61select * from t1; 62id x 630 1 641 1 652 2 66# Unblock LOCK TABLE. 67commit; 68connection con2; 69# Reap LOCK TABLE. 70unlock tables; 71connection con1; 72select * from t1 where id = 0 for update; 73id x 740 1 75connection 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: 80lock table t1 read;; 81connection con1; 82# Wait until LOCK TABLE is blocked on SQL-level lock. 83# We should be able to do UPDATEs and SELECTs within transaction. 84update t1 set x=2 where id = 0; 85select * from t1; 86id x 870 2 881 1 892 2 90# Unblock LOCK TABLE. 91commit; 92connection con2; 93# Reap LOCK TABLE. 94unlock tables; 95connection default; 96drop 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# 102connection default; 103CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL) ENGINE=InnoDB; 104INSERT INTO t1 VALUES(3,1); 105BEGIN; 106INSERT IGNORE INTO t1 VALUES(3,14); 107Warnings: 108Warning 1062 Duplicate entry '3' for key 'PRIMARY' 109connection con1; 110BEGIN; 111INSERT IGNORE INTO t1 VALUES(3,23); 112Warnings: 113Warning 1062 Duplicate entry '3' for key 'PRIMARY' 114SELECT * FROM t1 FOR UPDATE; 115connection con2; 116disconnect con2; 117connection default; 118COMMIT; 119connection con1; 120a b 1213 1 122COMMIT; 123connection default; 124DROP TABLE t1; 125# 126# MDEV-11080 InnoDB: Failing assertion: 127# table->n_waiting_or_granted_auto_inc_locks > 0 128# 129CREATE TABLE t1 (pk INTEGER AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB; 130INSERT INTO t1 VALUES (NULL),(NULL); 131CREATE TABLE t2 LIKE t1; 132BEGIN; 133connection con1; 134BEGIN; 135DELETE FROM t2; 136connection default; 137LOCK TABLE t2 READ;; 138connection con1; 139SET innodb_lock_wait_timeout= 1, lock_wait_timeout= 2; 140INSERT INTO t2 SELECT * FROM t1; 141COMMIT; 142connection default; 143UNLOCK TABLES; 144DROP TABLE t1, t2; 145# 146# MDEV-16709 InnoDB: Error: trx already had an AUTO-INC lock 147# 148CREATE TABLE t1 (pk INT AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB 149PARTITION BY key (pk) PARTITIONS 2; 150CREATE TABLE t2 (a INT) ENGINE=InnoDB; 151INSERT INTO t2 VALUES (1),(2),(3),(4),(5),(6); 152CREATE TABLE t3 (b INT) ENGINE=InnoDB; 153INSERT INTO t3 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9); 154connection con1; 155INSERT t1 SELECT NULL FROM t2; 156connection default; 157INSERT t1 SELECT NULL FROM t3; 158connection con1; 159disconnect con1; 160connection default; 161DROP TABLE t1, t2, t3; 162