~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;