~drizzle-trunk/drizzle/development

DROP DATABASE  IF EXISTS mysqltest;
DROP TABLE     IF EXISTS t1, t2, t3, t4, v1;
DROP VIEW      IF EXISTS t1, t2, t3, t4, v1;
DROP PROCEDURE IF EXISTS lock_t1_excl;
DROP PROCEDURE IF EXISTS count_t2;
DROP PROCEDURE IF EXISTS update_t2;
DROP TRIGGER   IF EXISTS t1_ai;
#
# WL3561 - transactional LOCK TABLE - Syntax tests
# ================================================
CREATE TABLE t1 (c1 INT ) ENGINE=InnoDB;
CREATE TABLE t2 (c2 INT ) ENGINE=InnoDB;
CREATE TABLE t3 (c3 INT ) ENGINE=InnoDB;
#
# Valid syntax for non-transactional locks.
LOCK TABLE t1 READ, t2 WRITE;
UNLOCK TABLES;
LOCK TABLE t1 READ LOCAL, t2 LOW_PRIORITY WRITE;
UNLOCK TABLES;
#
# Valid syntax for transactional locks.
LOCK TABLE t1 IN SHARE MODE, t2 IN EXCLUSIVE MODE;
UNLOCK TABLES;
#
# Valid syntax for transactional locks with NOWAIT option.
## In the preliminary reference implementation we expect these errors:
## NOWAIT+SHARE="timed out", NOWAIT+EXCLUSIVE="not supported".
## Statements abort on first error.
LOCK TABLE t1 IN SHARE MODE NOWAIT, t2 IN EXCLUSIVE MODE NOWAIT;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
LOCK TABLE t1 IN EXCLUSIVE MODE NOWAIT, t2 IN SHARE MODE NOWAIT;
ERROR 42000: Table 't1' uses an extension that doesn't exist in this MySQL version
LOCK TABLE t1 IN EXCLUSIVE MODE, t2 IN SHARE MODE NOWAIT;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
#
# Valid syntax for aliases with and without 'AS'.
LOCK TABLE t1 AS a1 READ, t2 a2 WRITE;
UNLOCK TABLES;
LOCK TABLE t1 AS a1 IN SHARE MODE, t2 a2 IN EXCLUSIVE MODE;
UNLOCK TABLES;
#
# Transactional locks taken on a view.
CREATE VIEW v1 AS SELECT * FROM t1, t2 WHERE t1.c1 = t2.c2;
LOCK TABLE v1 IN SHARE MODE;
LOCK TABLE v1 IN EXCLUSIVE MODE;
DROP VIEW v1;
#
# Locking INFORMATION_SCHEMA fails on missing privileges.
LOCK TABLE information_schema.tables IN SHARE MODE;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
LOCK TABLE information_schema.tables IN EXCLUSIVE MODE;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
LOCK TABLE information_schema.tables READ;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
LOCK TABLE information_schema.tables WRITE;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
#
# The new keywords EXCLUSIVE and NOWAIT are not reserved words.
CREATE TABLE t4 (exclusive INT, nowait INT) ENGINE=InnoDB;
LOCK TABLE t4 WRITE;
DROP TABLE t4;
#
# Syntax errors for misspelled modes or left out symbols.
##-------------------------------------------------------
LOCK TABLE t1 IN SHARED MODE;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SHARED MODE' at line 1
LOCK TABLE t1 SHARE MODE;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MODE' at line 1
LOCK TABLE t1 IN SHARE;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
LOCK TABLE t1 IN MODE;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MODE' at line 1
LOCK TABLE t1 READ NOWAIT, t2 WRITE NOWAIT;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOWAIT, t2 WRITE NOWAIT' at line 1
LOCK TABLE t1 READ NOWAIT, t2 IN EXCLUSIVE MODE NOWAIT;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOWAIT, t2 IN EXCLUSIVE MODE NOWAIT' at line 1
LOCK TABLE t1 IN SHARE MODE NOWAIT, t2 WRITE NOWAIT;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOWAIT' at line 1
LOCK TABLE t1 IN SHARED MODE NOWAIT;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SHARED MODE NOWAIT' at line 1
LOCK TABLE t1 SHARE MODE NOWAIT;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MODE NOWAIT' at line 1
LOCK TABLE t1 IN SHARE NOWAIT;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOWAIT' at line 1
LOCK TABLE t1 IN MODE NOWAIT;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MODE NOWAIT' at line 1
##----------------------
## End of syntax errors.
#
#
# WL3561 - transactional LOCK TABLE - Lock method conversion
# ==========================================================
#
# Implicit lock method conversion due to mix in statement.
LOCK TABLE t1 READ, t2 IN EXCLUSIVE MODE;
Warnings:
Warning	1613	Converted to non-transactional lock on 't2'
UNLOCK TABLES;
# Lock t1 share (converted to read), t2 write.
LOCK TABLE t1 IN SHARE MODE, t2 WRITE;
Warnings:
Warning	1613	Converted to non-transactional lock on 't1'
# Show t1 is read locked, t2 write locked.
INSERT INTO t1 SELECT * FROM t2;
ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
INSERT INTO t2 SELECT * FROM t1;
#
# Implicit lock method conversion due to existing non-transact. locks.
# Implicitly unlock existing non-transactional locks and take new ones.
# Lock t1 exclusive (converted to write), t2 share (converted to read).
LOCK TABLE t1 IN EXCLUSIVE MODE, t2 IN SHARE MODE;
Warnings:
Warning	1613	Converted to non-transactional lock on 't1'
Warning	1613	Converted to non-transactional lock on 't2'
# Show t1 is write locked, t2 read locked.
INSERT INTO t1 SELECT * FROM t2;
INSERT INTO t2 SELECT * FROM t1;
ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
UNLOCK TABLES;
#
# Reject lock method conversion in strict mode.
# Set strict mode.
SET @wl3561_save_sql_mode= @@SQL_MODE;
SET @@SQL_MODE= 'STRICT_ALL_TABLES';
# Try mixed mode locks.
LOCK TABLE t1 READ, t2 IN EXCLUSIVE MODE;
ERROR HY000: Cannot convert to non-transactional lock in strict mode on 't2'
LOCK TABLE t1 IN SHARE MODE, t2 WRITE;
ERROR HY000: Cannot convert to non-transactional lock in strict mode on 't1'
# Lock non-transactional.
LOCK TABLE t1 READ, t2 WRITE;
# Try transactional locks on top of the existing non-transactional locks.
LOCK TABLE t1 IN SHARE MODE, t2 IN EXCLUSIVE MODE;
ERROR HY000: Cannot convert to non-transactional lock in strict mode on 't1'
## Error is reported on first table only. Show both errors:
SHOW WARNINGS;
Level	Code	Message
Error	1614	Cannot convert to non-transactional lock in strict mode on 't1'
Error	1614	Cannot convert to non-transactional lock in strict mode on 't2'
UNLOCK TABLES;
SET @@SQL_MODE= @wl3561_save_sql_mode;
#
# Reject lock method conversion in an active transaction.
# Start transaction.
START TRANSACTION;
# Try mixed mode locks.
LOCK TABLE t1 READ, t2 IN EXCLUSIVE MODE;
ERROR HY000: Cannot convert to non-transactional lock in an active transaction on 't2'
LOCK TABLE t1 IN SHARE MODE, t2 WRITE;
ERROR HY000: Cannot convert to non-transactional lock in an active transaction on 't1'
COMMIT;
#
# Implicit lock method conversion for non-transactional storage engine.
# Create a non-transactional table.
CREATE TABLE t4 (c4 INT) ENGINE= MyISAM;
# Request a transactional lock, which is converted to non-transactional.
LOCK TABLE t4 IN SHARE MODE;
Warnings:
Warning	1613	Converted to non-transactional lock on 't4'
# Try a conflict with the existing non-transactional lock.
INSERT INTO t4 VALUES(444);
ERROR HY000: Table 't4' was locked with a READ lock and can't be updated
UNLOCK TABLES;
# Set strict mode.
SET @@SQL_MODE= 'STRICT_ALL_TABLES';
# Try a transactional lock, which would need a conversion.
LOCK TABLE t4 IN SHARE MODE;
ERROR HY000: Cannot convert to non-transactional lock in strict mode on 't4'
SET @@SQL_MODE= @wl3561_save_sql_mode;
#
# View with transactional and non-transactional storage engine.
CREATE VIEW v1 AS SELECT * FROM t3, t4 WHERE t3.c3 = t4.c4;
# Request a share lock on the view, which is converted to read locks.
LOCK TABLE v1 IN SHARE MODE;
Warnings:
Warning	1613	Converted to non-transactional lock on 't3'
Warning	1613	Converted to non-transactional lock on 't4'
# Show that read locks on the base tables prohibit writing ...
INSERT INTO t3 SELECT * FROM t4;
ERROR HY000: Table 't3' was locked with a READ lock and can't be updated
INSERT INTO t4 SELECT * FROM t3;
ERROR HY000: Table 't4' was locked with a READ lock and can't be updated
# ... but allow reading.
SELECT COUNT(*) FROM t3, t4 WHERE t3.c3 = t4.c4;
COUNT(*)
0
SELECT COUNT(*) FROM v1;
COUNT(*)
0
## Report conversion on view due to existing non-transactional locks.
LOCK TABLE v1 IN EXCLUSIVE MODE;
Warnings:
Warning	1613	Converted to non-transactional lock on 'v1'
INSERT INTO t3 VALUES(333);
INSERT INTO t4 VALUES(444);
INSERT INTO t1 VALUES(111);
ERROR HY000: Table 't1' was not locked with LOCK TABLES
UNLOCK TABLES;
## Now report conversion on base table again.
LOCK TABLE v1 IN EXCLUSIVE MODE;
Warnings:
Warning	1613	Converted to non-transactional lock on 't3'
Warning	1613	Converted to non-transactional lock on 't4'
INSERT INTO t3 VALUES(333);
INSERT INTO t4 VALUES(444);
INSERT INTO t1 VALUES(111);
ERROR HY000: Table 't1' was not locked with LOCK TABLES
UNLOCK TABLES;
DROP VIEW v1;
TRUNCATE t4;
#
# Insufficient privileges do not unlock tables nor end transactions.
# Prepare database, tables and an user with insufficient privileges.
# Make a new connection with this user.
CREATE DATABASE mysqltest;
CREATE TABLE mysqltest.t5 (c5 INT) ENGINE=InnoDB;
CREATE TABLE mysqltest.t6 (c6 INT) ENGINE=InnoDB;
CREATE USER mysqltest_1@localhost;
GRANT SELECT, INSERT ON mysqltest.* TO mysqltest_1@localhost;
# connection conn1.
# Show sufficient privileges to lock tables in the test database.
LOCK TABLE t1 READ, t2 WRITE;
# Show insufficient privileges in the mysqltest database.
LOCK TABLE mysqltest.t5 READ, mysqltest.t6 WRITE;
ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'mysqltest'
# Show that the locks in 'test' still exist.
INSERT INTO t1 SELECT * FROM t2;
ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
INSERT INTO t2 SELECT * FROM t1;
# Unlock tables.
UNLOCK TABLES;
# Start transaction.
START TRANSACTION;
# Insert a value.
INSERT INTO t1 VALUES(111);
# Try a lock that fails on privileges.
LOCK TABLE mysqltest.t5 READ;
ERROR 42000: Access denied for user 'mysqltest_1'@'localhost' to database 'mysqltest'
# Rollback transaction.
ROLLBACK;
# Show that the inserted value has gone.
SELECT * FROM t1;
c1
# Drop the connection with the unprivileged user.
# connection default.
#
# Sufficient privileges do unlock tables and end transactions.
# Grant sufficient privileges to the user.
# Make a new connection with this user.
GRANT SELECT, INSERT, LOCK TABLES ON mysqltest.* TO mysqltest_1@localhost;
# connection conn1.
# Lock tables in the test database.
LOCK TABLE t1 READ, t2 WRITE;
# Lock tables in the mysqltest database.
LOCK TABLE mysqltest.t5 READ, mysqltest.t6 WRITE;
# Show that the locks in 'test' have been replaced ...
INSERT INTO t1 SELECT * FROM t2;
ERROR HY000: Table 't1' was not locked with LOCK TABLES
INSERT INTO t2 SELECT * FROM t1;
ERROR HY000: Table 't2' was not locked with LOCK TABLES
# ... by the locks in 'mysqltest'.
INSERT INTO mysqltest.t5 SELECT * FROM mysqltest.t6;
ERROR HY000: Table 't5' was locked with a READ lock and can't be updated
INSERT INTO mysqltest.t6 SELECT * FROM mysqltest.t5;
# Unlock tables.
UNLOCK TABLES;
# Start transaction.
START TRANSACTION;
# Insert a value.
INSERT INTO t1 VALUES(111);
# Take a new lock.
LOCK TABLE mysqltest.t5 READ;
# Rollback transaction.
ROLLBACK;
UNLOCK TABLES;
# Show that the inserted value had been committed.
SELECT * FROM t1;
c1
111
TRUNCATE t1;
# connection default.
UNLOCK TABLES;
DROP USER mysqltest_1@localhost;
DROP DATABASE mysqltest;
DROP TABLE t1, t2, t3, t4;
#
# WL3594 - transactional LOCK TABLE Testing - Functional tests
# ============================================================
# Prepare tables and connections.
# Set AUTOCOMMIT= 0 in each connection.
SET AUTOCOMMIT= 0;
CREATE TABLE t1 (c1 INT) ENGINE=InnoDB;
CREATE TABLE t2 (c2 INT) ENGINE=InnoDB;
# connection conn1.
SET AUTOCOMMIT= 0;
# connection conn2.
SET AUTOCOMMIT= 0;
# connection default.
#
# Transactional lock behaviour:
# LOCK TABLE does _not_ commit a transaction.
# Insert a value.
INSERT INTO t1 VALUES (111);
# Lock transactional.
LOCK TABLE t1 IN EXCLUSIVE MODE;
# Rollback.
ROLLBACK;
# Show that the inserted value has gone.
SELECT * FROM t1;
c1
#
# After LOCK TABLE one can access tables not mentioned in LOCK TABLE.
# Lock t1 transactional.
LOCK TABLE t1 IN EXCLUSIVE MODE;
# Insert a value into t2.
INSERT INTO t2 VALUES (222);
# Show that the inserted value is indeed in the table.
SELECT * FROM t2;
c2
222
#
# One can issue LOCK TABLE many times, adding more tables.
# Lock t2 transactional.
LOCK TABLE t2 IN EXCLUSIVE MODE;
#
# LOCK TABLE does not rollback a transaction.
# Show that the inserted value is still in the table.
SELECT * FROM t2;
c2
222
# Rollback transaction.
ROLLBACK;
# Show that the inserted value has gone.
SELECT * FROM t2;
c2
#
# Tables are unlocked at the end of transaction (commit).
# Take an exclusive lock.
LOCK TABLE t1 IN EXCLUSIVE MODE;
# Commit.
COMMIT;
# connection conn1.
# Take an exclusive lock.
# This would fail after timeout if t1 is still locked.
LOCK TABLE t1 IN EXCLUSIVE MODE;
# Commit.
COMMIT;
# connection default.
#
# Tables are unlocked at the end of transaction (rollback).
# Take an exclusive lock.
LOCK TABLE t1 IN EXCLUSIVE MODE;
# Rollback.
ROLLBACK;
# connection conn1.
# Take an exclusive lock.
# This would fail after timeout if t1 is still locked.
LOCK TABLE t1 IN EXCLUSIVE MODE;
# Rollback.
ROLLBACK;
# connection default.
#
# UNLOCK TABLES does not touch a transaction when
# no non-transactional table locks exist.
# Take a transactional lock.
LOCK TABLE t1 IN EXCLUSIVE MODE;
# Insert a value.
INSERT INTO t1 VALUES(111);
# Unlock (non-transactional) table locks.
UNLOCK TABLES;
# Show that the inserted value is still in the table.
SELECT * FROM t1;
c1
111
# Rollback.
ROLLBACK;
# Show that the inserted value has gone.
SELECT * FROM t1;
c1
#
# UNLOCK TABLES commits a transaction when
# non-transactional table locks exist.
# Take a non-transactional lock.
LOCK TABLE t1 WRITE;
# Insert a value.
INSERT INTO t1 VALUES(111);
# Unlock (non-transactional) table locks.
UNLOCK TABLES;
# Show that the inserted value is still in the table.
SELECT * FROM t1;
c1
111
# Rollback.
ROLLBACK;
# Show that the inserted value is still in the table.
SELECT * FROM t1;
c1
111
TRUNCATE t1;
#
# START TRANSACTION removes a previous lock.
# Take an exclusive lock.
LOCK TABLE t1 IN EXCLUSIVE MODE;
# Start transaction.
START TRANSACTION;
# connection conn1.
# Take an exclusive lock.
# This would fail after timeout if t1 is still locked.
LOCK TABLE t1 IN EXCLUSIVE MODE;
COMMIT;
# connection default.
COMMIT;
#
# With Auto commit on, transactional locks will be ignored
# Set AUTOCOMMIT= 1.
SET AUTOCOMMIT= 1;
# Take an exclusive lock.
LOCK TABLE t1 IN EXCLUSIVE MODE;
# connection conn1.
# Set AUTOCOMMIT= 1.
SET AUTOCOMMIT= 1;
# Take an exclusive lock.
# This would fail after timeout if t1 is still locked.
LOCK TABLE t1 IN SHARE MODE;
SET AUTOCOMMIT= 0;
COMMIT;
# connection default.
UNLOCK TABLES;
SET AUTOCOMMIT= 0;
COMMIT;
#
# With Auto commit on, transactional locks can time out.
# Default connection runs in transactional mode.
# Set AUTOCOMMIT= 0.
SET AUTOCOMMIT= 0;
# Take an exclusive lock, which persists.
LOCK TABLE t1 IN EXCLUSIVE MODE;
# connection conn1.
# conn1 runs in autocommit mode.
# Set AUTOCOMMIT= 1.
SET AUTOCOMMIT= 1;
# Try an exclusive lock,
# which times out though running in autocommit mode.
LOCK TABLE t1 IN SHARE MODE;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET AUTOCOMMIT= 0;
COMMIT;
# connection default.
UNLOCK TABLES;
SET AUTOCOMMIT= 0;
COMMIT;
#
# Normal WRITE locks go before readers (autocommit).
# Set AUTOCOMMIT= 1.
SET AUTOCOMMIT= 1;
# Insert a value.
INSERT INTO t1 VALUES(111);
# Take a non-transactional lock.
LOCK TABLE t1 READ;
# connection conn1.
# Set AUTOCOMMIT= 1.
SET AUTOCOMMIT= 1;
# Take a non-transactional WRITE lock,
# which waits in background until first read lock is released.
LOCK TABLE t1 WRITE;
# connection default.
# Wait for the helper thread to sit on its lock.
# connection conn2.
# Set AUTOCOMMIT= 1.
SET AUTOCOMMIT= 1;
# Take a non-transactional READ lock,
# which waits in background until the WRITE lock is released.
LOCK TABLE t1 READ;
# connection default.
# Wait for the helper threads to sit on their locks.
# Unlock this connections non-transactional lock.
UNLOCK TABLES;
# connection conn1.
# Now the WRITE lock is taken.
# Insert a value.
INSERT INTO t1 VALUES(1111);
# Unlock table.
UNLOCK TABLES;
# connection conn2.
# Now the READ lock is taken.
# Select from the table.
SELECT * FROM t1;
c1
111
1111
# Unlock table.
UNLOCK TABLES;
# connection default.
TRUNCATE t1;
#
# LOW_PRIORITY WRITE locks wait for readers (autocommit).
# Insert a value.
INSERT INTO t1 VALUES(111);
# Take a non-transactional lock.
LOCK TABLE t1 READ;
# connection conn1.
# Take a non-transactional LOW_PRIORITY WRITE lock,
# which waits in background until all read locks are released.
LOCK TABLE t1 LOW_PRIORITY WRITE;
# connection default.
# Wait for the helper thread to sit on its lock.
# connection conn2.
# Take a non-transactional READ lock,
# which goes before the LOW_PRIORITY WRITE lock.
LOCK TABLE t1 READ;
# The READ lock could be taken immediately.
# Select from the table.
SELECT * FROM t1;
c1
111
# Unlock table.
UNLOCK TABLES;
SET AUTOCOMMIT= 0;
# connection default.
# Unlock this connections non-transactional lock.
UNLOCK TABLES;
# connection conn1.
# Now the LOW_PRIORITY WRITE lock is taken.
# Insert a value.
INSERT INTO t1 VALUES(1111);
# Unlock table.
UNLOCK TABLES;
SET AUTOCOMMIT= 0;
# connection default.
TRUNCATE t1;
SET AUTOCOMMIT= 0;
COMMIT;
#
# Normal WRITE locks go before readers (transaction).
# Insert a value.
INSERT INTO t1 VALUES(111);
COMMIT;
# Take a non-transactional lock.
LOCK TABLE t1 READ;
# connection conn1.
# Take a non-transactional WRITE lock,
# which waits in background until first read lock is released.
LOCK TABLE t1 WRITE;
# connection default.
# Wait for the helper thread to sit on its lock.
# connection conn2.
# Take a non-transactional READ lock,
# which waits in background until the WRITE lock is released.
LOCK TABLE t1 READ;
# connection default.
# Wait for the helper threads to sit on their locks.
# Unlock this connections non-transactional lock.
UNLOCK TABLES;
# connection conn1.
# Now the WRITE lock is taken.
# Insert a value.
INSERT INTO t1 VALUES(1111);
# Unlock table.
UNLOCK TABLES;
# connection conn2.
# Now the READ lock is taken.
# Select from the table.
SELECT * FROM t1;
c1
111
1111
# Unlock table.
UNLOCK TABLES;
# connection default.
TRUNCATE t1;
COMMIT;
#
# LOW_PRIORITY WRITE behaves like WRITE in transaction mode.
# Insert a value.
INSERT INTO t1 VALUES(111);
COMMIT;
# Take a non-transactional lock.
LOCK TABLE t1 READ;
# connection conn1.
# Take a non-transactional LOW_PRIORITY WRITE lock,
# which waits in background until first read lock is released.
LOCK TABLE t1 LOW_PRIORITY WRITE;
# connection default.
# Wait for the helper thread to sit on its lock.
# connection conn2.
# Take a non-transactional READ lock,
# which waits in background for the LOW_PRIORITY WRITE lock.
LOCK TABLE t1 READ;
# connection default.
# Wait for the helper threads to sit on their locks.
# Unlock this connections non-transactional lock.
UNLOCK TABLES;
# connection conn1.
# Now the LOW_PRIORITY WRITE lock is taken.
# Insert a value.
INSERT INTO t1 VALUES(1111);
# Unlock table.
UNLOCK TABLES;
# connection conn2.
# Now the READ lock is taken.
# Select from the table.
SELECT * FROM t1;
c1
111
1111
# Unlock table.
UNLOCK TABLES;
# connection default.
TRUNCATE t1;
COMMIT;
#
# NOWAIT.
# Take an exclusive lock.
LOCK TABLE t1 IN EXCLUSIVE MODE;
# connection conn1.
# Try an exclusive lock,
# which conflicts and cannot immediately be taken.
LOCK TABLE t1 IN SHARE MODE NOWAIT;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
# connection default.
# Commit.
COMMIT;
#
# Transactional table locks do not interfere with the global read lock.
# Take an exclusive lock on t1.
LOCK TABLE t1 IN EXCLUSIVE MODE;
# connection conn1.
# Try an exclusive lock, which conflicts.
LOCK TABLE t1 IN EXCLUSIVE MODE;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
# Can take the global read lock when an exclusive lock exist.
FLUSH TABLES WITH READ LOCK;
# Show that the global read lock exists.
LOCK TABLE t2 WRITE;
ERROR HY000: Can't execute the query because you have a conflicting read lock
# connection default.
# Can take an exclusive lock when the global read lock exists.
# Take an exclusive lock on t2.
LOCK TABLE t2 IN EXCLUSIVE MODE;
# connection conn1.
# Show that an exclusive lock on t1 exists.
LOCK TABLE t1 IN EXCLUSIVE MODE;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
# Show that an exclusive lock on t2 exists.
LOCK TABLE t2 IN EXCLUSIVE MODE;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
# Show that the global read lock exists.
LOCK TABLE t2 WRITE;
ERROR HY000: Can't execute the query because you have a conflicting read lock
# Release global read lock.
UNLOCK TABLES;
# Commit.
COMMIT;
# connection default.
UNLOCK TABLES;
# Commit.
COMMIT;
#
# Access conflict on INSERT.
# Take an share lock on t1.
LOCK TABLE t1 IN SHARE MODE;
# connection conn1.
SELECT GET_LOCK("mysqltest1", 10);
GET_LOCK("mysqltest1", 10)
1
# Try to insert a value,
# which must wait in background for the lock to go away.
INSERT INTO t1 VALUES (111);
## connection default.
## Wait in background until the insert times out and releases lock.
SELECT GET_LOCK("mysqltest1", 10);
# connection conn1.
# Wait for INSERT to timeout.
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SELECT RELEASE_LOCK("mysqltest1");
RELEASE_LOCK("mysqltest1")
1
COMMIT;
# connection default.
GET_LOCK("mysqltest1", 10)
1
# Commit.
COMMIT;
# Show that the insert in conn1 failed.
SELECT * FROM t1;
c1
#
# Access conflict on UPDATE with exclusive lock.
# Insert a value.
INSERT INTO t1 VALUES (111);
COMMIT;
# connection conn1.
# Take an exclusive lock.
LOCK TABLE t1 IN EXCLUSIVE MODE;
# connection default.
# Try a second exclusive lock, which fails due to the other lock.
LOCK TABLE t1 IN EXCLUSIVE MODE;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
# Try an update, which fails due to the exclusive lock.
UPDATE t1 SET c1= 111222;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
# connection conn1.
# The exclusive table locker can still update.
UPDATE t1 SET c1= 111333;
# connection default.
# Select is allowed despite the table lock, but sees old data.
SELECT * FROM t1;
c1
111
# connection conn1.
# Commit.
COMMIT;
# connection default.
# It seems like the failed update began a transaction, so still old data.
SELECT * FROM t1;
c1
111
# Commit.
COMMIT;
# Now select sees current data.
SELECT * FROM t1;
c1
111333
TRUNCATE t1;
COMMIT;
#
# Access conflict on UPDATE with share lock.
# Insert a value.
INSERT INTO t1 VALUES (111);
COMMIT;
# connection conn1.
# Take a share lock.
LOCK TABLE t1 IN SHARE MODE;
# Update with a single share lock is possible.
UPDATE t1 SET c1= 111222;
# Commit to get rid of the row lock.
COMMIT;
# Take a share lock.
LOCK TABLE t1 IN SHARE MODE;
# connection default.
# An exclusive lock is not possible on a share lock.
LOCK TABLE t1 IN EXCLUSIVE MODE;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
# More share locks are possible.
LOCK TABLE t1 IN SHARE MODE;
# Noone can update when multiple share locks exist.
UPDATE t1 SET c1= 111333;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
# connection conn1.
# Noone can update when multiple share locks exist.
UPDATE t1 SET c1= 111444;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
COMMIT;
# connection default.
SELECT * FROM t1;
c1
111222
TRUNCATE t1;
COMMIT;
#
# LOCK TABLE is prohibited in stored procedure.
CREATE PROCEDURE lock_t1_excl()
LOCK TABLE t1 IN EXCLUSIVE MODE;
ERROR 0A000: LOCK is not allowed in stored procedures
#
# LOCK TABLE is prohibited in trigger.
CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
LOCK TABLE t2 IN EXCLUSIVE MODE;
ERROR 0A000: LOCK is not allowed in stored procedures
#
# LOCK TABLE on a pre-locked table through a trigger.
# Create a trigger on t1 that updates t2.
CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
BEGIN
UPDATE t2 SET c2= c2 + 111;
END//
# Take an exclusive lock on t1.
# This pre-locks t2 through the trigger.
LOCK TABLE t1 IN EXCLUSIVE MODE;
# connection conn1.
# Try to take an exclusive lock on t2,
# which is pre-locked through the trigger on t1.
LOCK TABLE t2 IN EXCLUSIVE MODE;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
COMMIT;
# connection default.
# Commit to release the lock.
COMMIT;
DROP TRIGGER t1_ai;
#
# LOCK TABLE on a pre-locked table through a view.
# Create a function that selects from t2.
CREATE FUNCTION count_t2() RETURNS INT
BEGIN
RETURN (SELECT COUNT(*) FROM t2);
END//
# Create a view with t1 and the function.
CREATE VIEW v1 AS SELECT COUNT(*), count_t2() FROM t1 GROUP BY 2;
# Take an exclusive lock on v1.
# This pre-locks t2 through the view.
LOCK TABLE v1 IN EXCLUSIVE MODE;
# connection conn1.
# Try to take an exclusive lock on t2,
# which is pre-locked through the function in v1.
LOCK TABLE t2 IN EXCLUSIVE MODE;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
COMMIT;
# connection default.
# Commit to release the lock.
COMMIT;
DROP VIEW v1;
DROP FUNCTION count_t2;
#
# Transactional LOCK TABLE by-passes pre-lock.
#
# Insert a value to t1 and t2 each.
INSERT INTO t1 VALUES (111);
INSERT INTO t2 VALUES (222);
COMMIT;
# Create a trigger on t1 that updates t2.
CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
BEGIN
INSERT INTO t2 SELECT GET_LOCK("mysqltest1", 10);
UPDATE t2 SET c2= c2 + 111;
INSERT INTO t2 SELECT RELEASE_LOCK("mysqltest1");
END//
# Take an SQL lock which blocks the trigger.
SELECT GET_LOCK("mysqltest1", 10);
GET_LOCK("mysqltest1", 10)
1
# connection conn1.
# Insert into t1 to fire trigger. This waits on GET_LOCK.
INSERT INTO t1 VALUES(111222);
# connection default.
# Wait for the helper thread to sit on its lock.
# Take an exclusive lock.
LOCK TABLE t2 IN EXCLUSIVE MODE;
# Use the lock for insert.
INSERT INTO t2 VALUES (111333);
# Commit to release the lock again.
COMMIT;
# Release the SQL lock to let the trigger finish.
SELECT RELEASE_LOCK("mysqltest1");
RELEASE_LOCK("mysqltest1")
1
# connection conn1.
# Trigger succeeded.
# Commit.
COMMIT;
# connection default.
# Commit.
COMMIT;
# Show the results.
SELECT * FROM t1;
c1
111
111222
SELECT * FROM t2;
c2
333
111444
112
1
TRUNCATE t1;
TRUNCATE t2;
COMMIT;
DROP TRIGGER t1_ai;
#
# Non-transactional LOCK TABLE cannot by-passes pre-lock.
#
# Insert a value to t1 and t2 each.
INSERT INTO t1 VALUES (111);
INSERT INTO t2 VALUES (222);
COMMIT;
# Create a trigger on t1 that updates t2.
CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
BEGIN
# This will time out. So use a small value.
INSERT INTO t2 SELECT GET_LOCK("mysqltest1", 1);
UPDATE t2 SET c2= c2 + 111;
INSERT INTO t2 SELECT RELEASE_LOCK("mysqltest1");
END//
# Take an SQL lock which blocks the trigger.
SELECT GET_LOCK("mysqltest1", 10);
GET_LOCK("mysqltest1", 10)
1
# connection conn1.
# Insert into t1 to fire trigger. This waits on GET_LOCK.
INSERT INTO t1 VALUES(111222);
# connection default.
# Wait for the helper thread to sit on its lock.
# Take a write lock. This waits until the trigger times out.
LOCK TABLE t2 WRITE;
# Use the lock for insert.
INSERT INTO t2 VALUES (111333);
# Release the lock again.
UNLOCK TABLES;
# Release the SQL lock.
SELECT RELEASE_LOCK("mysqltest1");
RELEASE_LOCK("mysqltest1")
1
# connection conn1.
# Trigger timed out.
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
# Commit.
COMMIT;
# connection default.
# Commit.
COMMIT;
# Show the results.
SELECT * FROM t1;
c1
111
SELECT * FROM t2;
c2
222
111333
TRUNCATE t1;
TRUNCATE t2;
COMMIT;
DROP TRIGGER t1_ai;
## Cleanup.
SET AUTOCOMMIT= 1;
UNLOCK TABLES;
DROP TABLE t1, t2;