From 1f8b3d0f22070d450226ef4f6e0e31c8ef8cd401 Mon Sep 17 00:00:00 2001
From: "monty@mysql.com" <>
Date: Wed, 20 Oct 2004 11:24:08 +0300
Subject: Code cleanups (done during review of new code) Rename
 innodb_table_locks_old_behavior -> innodb_table_locks Set innodb_table_locks
 to off by default to get same behaviour as in MySQL 4.0.20 (This means that
 Innodb ignore table locks by default, which makes it easier to combine MyISAM
 and InnoDB to simulate a transaction)

---
 mysql-test/t/innodb-lock.test | 46 ++++++++++++++++++++++++++++++++++++++++++-
 1 file changed, 45 insertions(+), 1 deletion(-)

(limited to 'mysql-test/t/innodb-lock.test')

diff --git a/mysql-test/t/innodb-lock.test b/mysql-test/t/innodb-lock.test
index 43a175508b4..11395b301c4 100644
--- a/mysql-test/t/innodb-lock.test
+++ b/mysql-test/t/innodb-lock.test
@@ -5,10 +5,54 @@ connect (con2,localhost,root,,);
 drop table if exists t1;
 
 #
-# Testing of explicit table locks
+# Check and select innodb lock type
 #
 
+select @@innodb_table_locks;
+
+#
+# Testing of explicit table locks with enforced table locks
+#
+
+set @@innodb_table_locks=1;
+
+connection con1;
+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;
+
+connection con2;
+set autocommit=0;
+
+# The following statement should hang because con1 is locking the page
+--send
+lock table t1 write;
+--sleep 2;
+
 connection con1;
+update t1 set x=1 where id = 0;
+select * from t1;
+commit;
+
+connection con2;
+reap;
+update t1 set x=2 where id = 0;
+commit;
+unlock tables;
+
+connection con1;
+select * from t1;
+commit;
+
+drop table t1;
+
+#
+# Try with old lock method (where LOCK TABLE is ignored)
+#
+
+set @@innodb_table_locks=0;
+
 create table t1 (id integer, x integer) engine=INNODB;
 insert into t1 values(0, 0);
 set autocommit=0;
-- 
cgit v1.2.1


From b32ffec8fdc9d05377e69c8b1abb92d562670324 Mon Sep 17 00:00:00 2001
From: "monty@mysql.com" <>
Date: Wed, 20 Oct 2004 16:04:28 +0300
Subject: Fix test case for innodb-lock

---
 mysql-test/t/innodb-lock.test | 47 ++++++++++++++++++++++++++-----------------
 1 file changed, 29 insertions(+), 18 deletions(-)

(limited to 'mysql-test/t/innodb-lock.test')

diff --git a/mysql-test/t/innodb-lock.test b/mysql-test/t/innodb-lock.test
index 11395b301c4..430369f4fda 100644
--- a/mysql-test/t/innodb-lock.test
+++ b/mysql-test/t/innodb-lock.test
@@ -1,9 +1,5 @@
 -- source include/have_innodb.inc
 
-connect (con1,localhost,root,,);
-connect (con2,localhost,root,,);
-drop table if exists t1;
-
 #
 # Check and select innodb lock type
 #
@@ -14,6 +10,14 @@ select @@innodb_table_locks;
 # Testing of explicit table locks with enforced table locks
 #
 
+connect (con1,localhost,root,,);
+connect (con2,localhost,root,,);
+drop table if exists t1;
+
+#
+# Testing of explicit table locks with enforced table locks
+#
+
 set @@innodb_table_locks=1;
 
 connection con1;
@@ -48,37 +52,44 @@ commit;
 drop table t1;
 
 #
-# Try with old lock method (where LOCK TABLE is ignored)
+# Try with old lock method (where LOCK TABLE is ignored by InnoDB)
 #
 
 set @@innodb_table_locks=0;
 
-create table t1 (id integer, x integer) engine=INNODB;
-insert into t1 values(0, 0);
-set autocommit=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;
 
 connection con2;
 set autocommit=0;
+set @@innodb_table_locks=0;
 
-# The following statement should hang because con1 is locking the page
---send
+# The following statement should work becase innodb doesn't check table locks
 lock table t1 write;
---sleep 2;
 
 connection con1;
-update t1 set x=1 where id = 0;
-select * from t1;
-commit;
+
+# This will be locked by MySQL
+--send
+update t1 set x=10 where id = 2;
+--sleep 2
 
 connection con2;
-reap;
-update t1 set x=2 where id = 0;
+
+# Note that we will get a deadlock if we try to select any rows marked
+# for update by con1 !
+
+SELECT * from t1 where id = 2;
+UPDATE t1 set x=3 where id = 2;
+commit;
+SELECT * from t1;
 commit;
 unlock tables;
 
 connection con1;
-select * from t1;
+reap;
 commit;
-
+select * from t1;
 drop table t1;
-- 
cgit v1.2.1