diff options
Diffstat (limited to 'mysql-test/suite/innodb/r/innodb-lock.result')
| -rw-r--r-- | mysql-test/suite/innodb/r/innodb-lock.result | 163 |
1 files changed, 163 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/r/innodb-lock.result b/mysql-test/suite/innodb/r/innodb-lock.result new file mode 100644 index 00000000..83bed139 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb-lock.result @@ -0,0 +1,163 @@ +set global innodb_table_locks=1; +select @@innodb_table_locks; +@@innodb_table_locks +1 +set @@innodb_table_locks=1; +connect con1,localhost,root,,; +create table t1 (id integer, x integer) engine=INNODB; +insert into t1 values(0, 0); +set autocommit=0; +SELECT * from t1 where id = 0 FOR UPDATE; +id x +0 0 +connect con2,localhost,root,,; +set autocommit=0; +lock table t1 write; +connection con1; +update t1 set x=1 where id = 0; +select * from t1; +id x +0 1 +commit; +connection con2; +update t1 set x=2 where id = 0; +commit; +unlock tables; +connection con1; +select * from t1; +id x +0 2 +commit; +drop table t1; +# +# Old lock method (where LOCK TABLE was ignored by InnoDB) no longer +# works when LOCK TABLE ... WRITE is used due to fix for bugs #46272 +# "MySQL 5.4.4, new MDL: unnecessary and bug #37346 "innodb does not +# detect deadlock between update and alter table". +# After WL#6671 "Improve scalability by not using thr_lock.c locks +# for InnoDB tables" was implemented it no longer works for LOCK TABLES +# ,,, READ as well. +# LOCK TABLES locks are now completely handled by MDL subsystem. +# +set @@innodb_table_locks=0; +create table t1 (id integer primary key, x integer) engine=INNODB; +insert into t1 values(0, 0),(1,1),(2,2); +commit; +SELECT * from t1 where id = 0 FOR UPDATE; +id x +0 0 +connection con2; +set autocommit=0; +set @@innodb_table_locks=0; +# The following statement should block because SQL-level lock +# is taken on t1 which will wait until concurrent transaction +# is commited. +# Sending: +lock table t1 write;; +connection con1; +# Wait until LOCK TABLE is blocked on SQL-level lock. +# We should be able to do UPDATEs and SELECTs within transaction. +update t1 set x=1 where id = 0; +select * from t1; +id x +0 1 +1 1 +2 2 +# Unblock LOCK TABLE. +commit; +connection con2; +# Reap LOCK TABLE. +unlock tables; +connection con1; +select * from t1 where id = 0 for update; +id x +0 1 +connection con2; +# The following statement should block because SQL-level lock +# is taken on t1 which will wait until concurrent transaction +# is commited. +# Sending: +lock table t1 read;; +connection con1; +# Wait until LOCK TABLE is blocked on SQL-level lock. +# We should be able to do UPDATEs and SELECTs within transaction. +update t1 set x=2 where id = 0; +select * from t1; +id x +0 2 +1 1 +2 2 +# Unblock LOCK TABLE. +commit; +connection con2; +# Reap LOCK TABLE. +unlock tables; +connection default; +drop table t1; +# +#Bug#12842206 INNODB LOCKING REGRESSION FOR INSERT IGNORE +#fixed by re-fixing Bug#7975 +#aka Bug#11759688 52020: InnoDB can still deadlock on just INSERT... +# +connection default; +CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL) ENGINE=InnoDB; +INSERT INTO t1 VALUES(3,1); +BEGIN; +INSERT IGNORE INTO t1 VALUES(3,14); +Warnings: +Warning 1062 Duplicate entry '3' for key 'PRIMARY' +connection con1; +BEGIN; +INSERT IGNORE INTO t1 VALUES(3,23); +Warnings: +Warning 1062 Duplicate entry '3' for key 'PRIMARY' +SELECT * FROM t1 FOR UPDATE; +connection con2; +disconnect con2; +connection default; +COMMIT; +connection con1; +a b +3 1 +COMMIT; +connection default; +DROP TABLE t1; +# +# MDEV-11080 InnoDB: Failing assertion: +# table->n_waiting_or_granted_auto_inc_locks > 0 +# +CREATE TABLE t1 (pk INTEGER AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES (NULL),(NULL); +CREATE TABLE t2 LIKE t1; +BEGIN; +connection con1; +BEGIN; +DELETE FROM t2; +connection default; +LOCK TABLE t2 READ;; +connection con1; +SET innodb_lock_wait_timeout= 1, lock_wait_timeout= 2; +INSERT INTO t2 SELECT * FROM t1; +COMMIT; +connection default; +UNLOCK TABLES; +DROP TABLE t1, t2; +# +# MDEV-16709 InnoDB: Error: trx already had an AUTO-INC lock +# +CREATE TABLE t1 (pk INT AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB +PARTITION BY key (pk) PARTITIONS 2; +INSERT INTO t1 VALUES(100); +INSERT INTO t1 VALUES(101); +CREATE TABLE t2 (a INT) ENGINE=InnoDB; +INSERT INTO t2 VALUES (1),(2),(3),(4),(5),(6); +CREATE TABLE t3 (b INT) ENGINE=InnoDB; +INSERT INTO t3 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9); +connection con1; +INSERT t1 SELECT NULL FROM t2; +connection default; +INSERT t1 SELECT NULL FROM t3; +connection con1; +disconnect con1; +connection default; +DROP TABLE t1, t2, t3; |