~drizzle-trunk/drizzle/development

# include/locktrans.inc

#

# Transactional LOCK TABLE tests

# eval SET SESSION STORAGE_ENGINE = $engine_type;

--disable_warnings

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;

--enable_warnings

--echo #

--echo # WL3561 - transactional LOCK TABLE - Syntax tests

--echo # ================================================

#

eval CREATE TABLE t1 (c1 INT ) ENGINE=$engine_type;

eval CREATE TABLE t2 (c2 INT ) ENGINE=$engine_type;

eval CREATE TABLE t3 (c3 INT ) ENGINE=$engine_type;

#

--echo #

--echo # 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;

#

--echo #

--echo # Valid syntax for transactional locks.

LOCK TABLE t1 IN SHARE MODE, t2 IN EXCLUSIVE MODE;

UNLOCK TABLES;

#

if ($nowait_support)

{

--echo #

--echo # Valid syntax for transactional locks with NOWAIT option.

--echo ## In the preliminary reference implementation we expect these errors:

--echo ## NOWAIT+SHARE="timed out", NOWAIT+EXCLUSIVE="not supported".

--echo ## Statements abort on first error.

--error ER_LOCK_WAIT_TIMEOUT

LOCK TABLE t1 IN SHARE MODE NOWAIT, t2 IN EXCLUSIVE MODE NOWAIT;

--error ER_UNSUPPORTED_EXTENSION

LOCK TABLE t1 IN EXCLUSIVE MODE NOWAIT, t2 IN SHARE MODE NOWAIT;

--error ER_LOCK_WAIT_TIMEOUT

LOCK TABLE t1 IN EXCLUSIVE MODE, t2 IN SHARE MODE NOWAIT;

}

#

--echo #

--echo # 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;

#

--echo #

--echo # 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;

#

--echo #

--echo # Locking INFORMATION_SCHEMA fails on missing privileges.

--error ER_DBACCESS_DENIED_ERROR

LOCK TABLE information_schema.tables IN SHARE MODE;

--error ER_DBACCESS_DENIED_ERROR

LOCK TABLE information_schema.tables IN EXCLUSIVE MODE;

--error ER_DBACCESS_DENIED_ERROR

LOCK TABLE information_schema.tables READ;

--error ER_DBACCESS_DENIED_ERROR

LOCK TABLE information_schema.tables WRITE;

#

--echo #

--echo # The new keywords EXCLUSIVE and NOWAIT are not reserved words.

eval CREATE TABLE t4 (exclusive INT, nowait INT) ENGINE=$engine_type;

LOCK TABLE t4 WRITE;

DROP TABLE t4;

#

--echo #

--echo # Syntax errors for misspelled modes or left out symbols.

--echo ##-------------------------------------------------------

--error ER_PARSE_ERROR

LOCK TABLE t1 IN SHARED MODE;

--error ER_PARSE_ERROR

LOCK TABLE t1 SHARE MODE;

--error ER_PARSE_ERROR

LOCK TABLE t1 IN SHARE;

--error ER_PARSE_ERROR

LOCK TABLE t1 IN MODE;

--error ER_PARSE_ERROR

LOCK TABLE t1 READ NOWAIT, t2 WRITE NOWAIT;

--error ER_PARSE_ERROR

LOCK TABLE t1 READ NOWAIT, t2 IN EXCLUSIVE MODE NOWAIT;

--error ER_PARSE_ERROR

LOCK TABLE t1 IN SHARE MODE NOWAIT, t2 WRITE NOWAIT;

--error ER_PARSE_ERROR

LOCK TABLE t1 IN SHARED MODE NOWAIT;

--error ER_PARSE_ERROR

LOCK TABLE t1 SHARE MODE NOWAIT;

--error ER_PARSE_ERROR

LOCK TABLE t1 IN SHARE NOWAIT;

--error ER_PARSE_ERROR

LOCK TABLE t1 IN MODE NOWAIT;

--echo ##----------------------

--echo ## End of syntax errors.

#

--echo #

--echo #

--echo # WL3561 - transactional LOCK TABLE - Lock method conversion

--echo # ==========================================================

--echo #

--echo # Implicit lock method conversion due to mix in statement.

LOCK TABLE t1 READ, t2 IN EXCLUSIVE MODE;

UNLOCK TABLES;

--echo # Lock t1 share (converted to read), t2 write.

LOCK TABLE t1 IN SHARE MODE, t2 WRITE;

--echo # Show t1 is read locked, t2 write locked.

--error ER_TABLE_NOT_LOCKED_FOR_WRITE

INSERT INTO t1 SELECT * FROM t2;

INSERT INTO t2 SELECT * FROM t1;

#

--echo #

--echo # Implicit lock method conversion due to existing non-transact. locks.

--echo # Implicitly unlock existing non-transactional locks and take new ones.

--echo # Lock t1 exclusive (converted to write), t2 share (converted to read).

LOCK TABLE t1 IN EXCLUSIVE MODE, t2 IN SHARE MODE;

--echo # Show t1 is write locked, t2 read locked.

INSERT INTO t1 SELECT * FROM t2;

--error ER_TABLE_NOT_LOCKED_FOR_WRITE

INSERT INTO t2 SELECT * FROM t1;

UNLOCK TABLES;

#

--echo #

--echo # Reject lock method conversion in strict mode.

--echo # Set strict mode.

SET @wl3561_save_sql_mode= @@SQL_MODE;

SET @@SQL_MODE= 'STRICT_ALL_TABLES';

--echo # Try mixed mode locks.

--error ER_NO_AUTO_CONVERT_LOCK_STRICT

LOCK TABLE t1 READ, t2 IN EXCLUSIVE MODE;

--error ER_NO_AUTO_CONVERT_LOCK_STRICT

LOCK TABLE t1 IN SHARE MODE, t2 WRITE;

--echo # Lock non-transactional.

LOCK TABLE t1 READ, t2 WRITE;

--echo # Try transactional locks on top of the existing non-transactional locks.

--error ER_NO_AUTO_CONVERT_LOCK_STRICT

LOCK TABLE t1 IN SHARE MODE, t2 IN EXCLUSIVE MODE;

--echo ## Error is reported on first table only. Show both errors:

SHOW WARNINGS;

UNLOCK TABLES;

SET @@SQL_MODE= @wl3561_save_sql_mode;

#

--echo #

--echo # Reject lock method conversion in an active transaction.

--echo # Start transaction.

START TRANSACTION;

--echo # Try mixed mode locks.

--error ER_NO_AUTO_CONVERT_LOCK_TRANSACTION

LOCK TABLE t1 READ, t2 IN EXCLUSIVE MODE;

--error ER_NO_AUTO_CONVERT_LOCK_TRANSACTION

LOCK TABLE t1 IN SHARE MODE, t2 WRITE;

COMMIT;

#

--echo #

--echo # Implicit lock method conversion for non-transactional storage engine.

--echo # Create a non-transactional table.

eval CREATE TABLE t4 (c4 INT) ENGINE= $other_non_trans_engine_type;

--echo # Request a transactional lock, which is converted to non-transactional.

LOCK TABLE t4 IN SHARE MODE;

--echo # Try a conflict with the existing non-transactional lock.

--error ER_TABLE_NOT_LOCKED_FOR_WRITE

INSERT INTO t4 VALUES(444);

UNLOCK TABLES;

--echo # Set strict mode.

SET @@SQL_MODE= 'STRICT_ALL_TABLES';

--echo # Try a transactional lock, which would need a conversion.

--error ER_NO_AUTO_CONVERT_LOCK_STRICT

LOCK TABLE t4 IN SHARE MODE;

SET @@SQL_MODE= @wl3561_save_sql_mode;

#

--echo #

--echo # View with transactional and non-transactional storage engine.

CREATE VIEW v1 AS SELECT * FROM t3, t4 WHERE t3.c3 = t4.c4;

--echo # Request a share lock on the view, which is converted to read locks.

LOCK TABLE v1 IN SHARE MODE;

--echo # Show that read locks on the base tables prohibit writing ...

--error ER_TABLE_NOT_LOCKED_FOR_WRITE

INSERT INTO t3 SELECT * FROM t4;

--error ER_TABLE_NOT_LOCKED_FOR_WRITE

INSERT INTO t4 SELECT * FROM t3;

--echo # ... but allow reading.

SELECT COUNT(*) FROM t3, t4 WHERE t3.c3 = t4.c4;

SELECT COUNT(*) FROM v1;

--echo ## Report conversion on view due to existing non-transactional locks.

LOCK TABLE v1 IN EXCLUSIVE MODE;

INSERT INTO t3 VALUES(333);

INSERT INTO t4 VALUES(444);

--error ER_TABLE_NOT_LOCKED

INSERT INTO t1 VALUES(111);

UNLOCK TABLES;

--echo ## Now report conversion on base table again.

LOCK TABLE v1 IN EXCLUSIVE MODE;

INSERT INTO t3 VALUES(333);

INSERT INTO t4 VALUES(444);

--error ER_TABLE_NOT_LOCKED

INSERT INTO t1 VALUES(111);

UNLOCK TABLES;

DROP VIEW v1;

TRUNCATE t4;

#

--echo #

--echo # Insufficient privileges do not unlock tables nor end transactions.

--echo # Prepare database, tables and an user with insufficient privileges.

--echo # Make a new connection with this user.

CREATE DATABASE mysqltest;

eval CREATE TABLE mysqltest.t5 (c5 INT) ENGINE=$engine_type;

eval CREATE TABLE mysqltest.t6 (c6 INT) ENGINE=$engine_type;

CREATE USER mysqltest_1@localhost;

GRANT SELECT, INSERT ON mysqltest.* TO mysqltest_1@localhost;

connect (conn1,localhost,mysqltest_1,,);

    --echo # connection conn1.

    connection conn1;

    --echo # Show sufficient privileges to lock tables in the test database.

    LOCK TABLE t1 READ, t2 WRITE;

    --echo # Show insufficient privileges in the mysqltest database.

    --error ER_DBACCESS_DENIED_ERROR

    LOCK TABLE mysqltest.t5 READ, mysqltest.t6 WRITE;

    --echo # Show that the locks in 'test' still exist.

    --error ER_TABLE_NOT_LOCKED_FOR_WRITE

    INSERT INTO t1 SELECT * FROM t2;

    INSERT INTO t2 SELECT * FROM t1;

    --echo # Unlock tables.

    UNLOCK TABLES;

    --echo # Start transaction.

    START TRANSACTION;

    --echo # Insert a value.

    INSERT INTO t1 VALUES(111);

    --echo # Try a lock that fails on privileges.

    --error ER_DBACCESS_DENIED_ERROR

    LOCK TABLE mysqltest.t5 READ;

    --echo # Rollback transaction.

    ROLLBACK;

    --echo # Show that the inserted value has gone.

    SELECT * FROM t1;

    --echo # Drop the connection with the unprivileged user.

    disconnect conn1;

--echo # connection default.

connection default;

--echo #

--echo # Sufficient privileges do unlock tables and end transactions.

--echo # Grant sufficient privileges to the user.

--echo # Make a new connection with this user.

GRANT SELECT, INSERT, LOCK TABLES ON mysqltest.* TO mysqltest_1@localhost;

connect (conn1,localhost,mysqltest_1,,);

    --echo # connection conn1.

    connection conn1;

    --echo # Lock tables in the test database.

    LOCK TABLE t1 READ, t2 WRITE;

    --echo # Lock tables in the mysqltest database.

    LOCK TABLE mysqltest.t5 READ, mysqltest.t6 WRITE;

    --echo # Show that the locks in 'test' have been replaced ...

    --error ER_TABLE_NOT_LOCKED

    INSERT INTO t1 SELECT * FROM t2;

    --error ER_TABLE_NOT_LOCKED

    INSERT INTO t2 SELECT * FROM t1;

    --echo # ... by the locks in 'mysqltest'.

    --error ER_TABLE_NOT_LOCKED_FOR_WRITE

    INSERT INTO mysqltest.t5 SELECT * FROM mysqltest.t6;

    INSERT INTO mysqltest.t6 SELECT * FROM mysqltest.t5;

    --echo # Unlock tables.

    UNLOCK TABLES;

    --echo # Start transaction.

    START TRANSACTION;

    --echo # Insert a value.

    INSERT INTO t1 VALUES(111);

    --echo # Take a new lock.

    LOCK TABLE mysqltest.t5 READ;

    --echo # Rollback transaction.

    ROLLBACK;

    UNLOCK TABLES;

    --echo # Show that the inserted value had been committed.

    SELECT * FROM t1;

    TRUNCATE t1;

    disconnect conn1;

--echo # connection default.

connection default;

#

UNLOCK TABLES;

DROP USER mysqltest_1@localhost;

DROP DATABASE mysqltest;

DROP TABLE t1, t2, t3, t4;

#

# Overcome timing problems DROP t1 -> CREATE t1 (*locktrans_myisam).

--sleep 0.1

#

--echo #

--echo # WL3594 - transactional LOCK TABLE Testing - Functional tests

--echo # ============================================================

#

--echo # Prepare tables and connections.

--echo # Set AUTOCOMMIT= 0 in each connection.

SET AUTOCOMMIT= 0;

eval CREATE TABLE t1 (c1 INT) ENGINE=$engine_type;

eval CREATE TABLE t2 (c2 INT) ENGINE=$engine_type;

connect (conn1,localhost,root,,);

    --echo # connection conn1.

    connection conn1;

    SET AUTOCOMMIT= 0;

connect (conn2,localhost,root,,);

        --echo # connection conn2.

        connection conn2;

        SET AUTOCOMMIT= 0;

--echo # connection default.

connection default;

#

if ($transactional)

{

--echo #

--echo # Transactional lock behaviour:

--echo # LOCK TABLE does _not_ commit a transaction.

--echo # Insert a value.

INSERT INTO t1 VALUES (111);

--echo # Lock transactional.

LOCK TABLE t1 IN EXCLUSIVE MODE;

--echo # Rollback.

ROLLBACK;

--echo # Show that the inserted value has gone.

SELECT * FROM t1;

--echo #

--echo # After LOCK TABLE one can access tables not mentioned in LOCK TABLE.

--echo # Lock t1 transactional.

LOCK TABLE t1 IN EXCLUSIVE MODE;

--echo # Insert a value into t2.

INSERT INTO t2 VALUES (222);

--echo # Show that the inserted value is indeed in the table.

SELECT * FROM t2;

--echo #

--echo # One can issue LOCK TABLE many times, adding more tables.

--echo # Lock t2 transactional.

LOCK TABLE t2 IN EXCLUSIVE MODE;

--echo #

--echo # LOCK TABLE does not rollback a transaction.

--echo # Show that the inserted value is still in the table.

SELECT * FROM t2;

--echo # Rollback transaction.

ROLLBACK;

--echo # Show that the inserted value has gone.

SELECT * FROM t2;

--echo #

--echo # Tables are unlocked at the end of transaction (commit).

--echo # Take an exclusive lock.

LOCK TABLE t1 IN EXCLUSIVE MODE;

--echo # Commit.

COMMIT;

    --echo # connection conn1.

    connection conn1;

    --echo # Take an exclusive lock.

    --echo # This would fail after timeout if t1 is still locked.

    LOCK TABLE t1 IN EXCLUSIVE MODE;

    --echo # Commit.

    COMMIT;

--echo # connection default.

connection default;

--echo #

--echo # Tables are unlocked at the end of transaction (rollback).

--echo # Take an exclusive lock.

LOCK TABLE t1 IN EXCLUSIVE MODE;

--echo # Rollback.

ROLLBACK;

    --echo # connection conn1.

    connection conn1;

    --echo # Take an exclusive lock.

    --echo # This would fail after timeout if t1 is still locked.

    LOCK TABLE t1 IN EXCLUSIVE MODE;

    --echo # Rollback.

    ROLLBACK;

--echo # connection default.

connection default;

--echo #

--echo # UNLOCK TABLES does not touch a transaction when

--echo # no non-transactional table locks exist.

--echo # Take a transactional lock.

LOCK TABLE t1 IN EXCLUSIVE MODE;

--echo # Insert a value.

INSERT INTO t1 VALUES(111);

--echo # Unlock (non-transactional) table locks.

UNLOCK TABLES;

--echo # Show that the inserted value is still in the table.

SELECT * FROM t1;

--echo # Rollback.

ROLLBACK;

--echo # Show that the inserted value has gone.

SELECT * FROM t1;

--echo #

--echo # UNLOCK TABLES commits a transaction when

--echo # non-transactional table locks exist.

--echo # Take a non-transactional lock.

LOCK TABLE t1 WRITE;

--echo # Insert a value.

INSERT INTO t1 VALUES(111);

--echo # Unlock (non-transactional) table locks.

UNLOCK TABLES;

--echo # Show that the inserted value is still in the table.

SELECT * FROM t1;

--echo # Rollback.

ROLLBACK;

--echo # Show that the inserted value is still in the table.

SELECT * FROM t1;

TRUNCATE t1;

--echo #

--echo # START TRANSACTION removes a previous lock.

--echo # Take an exclusive lock.

LOCK TABLE t1 IN EXCLUSIVE MODE;

--echo # Start transaction.

START TRANSACTION;

    --echo # connection conn1.

    connection conn1;

    --echo # Take an exclusive lock.

    --echo # This would fail after timeout if t1 is still locked.

    LOCK TABLE t1 IN EXCLUSIVE MODE;

    COMMIT;

--echo # connection default.

connection default;

COMMIT;

--echo #

--echo # With Auto commit on, transactional locks will be ignored

--echo # Set AUTOCOMMIT= 1.

SET AUTOCOMMIT= 1;

--echo # Take an exclusive lock.

LOCK TABLE t1 IN EXCLUSIVE MODE;

    --echo # connection conn1.

    connection conn1;

    --echo # Set AUTOCOMMIT= 1.

    SET AUTOCOMMIT= 1;

    --echo # Take an exclusive lock.

    --echo # This would fail after timeout if t1 is still locked.

    LOCK TABLE t1 IN SHARE MODE;

    SET AUTOCOMMIT= 0;

    COMMIT;

--echo # connection default.

connection default;

UNLOCK TABLES;

SET AUTOCOMMIT= 0;

COMMIT;

--echo #

--echo # With Auto commit on, transactional locks can time out.

--echo # Default connection runs in transactional mode.

--echo # Set AUTOCOMMIT= 0.

SET AUTOCOMMIT= 0;

--echo # Take an exclusive lock, which persists.

LOCK TABLE t1 IN EXCLUSIVE MODE;

    --echo # connection conn1.

    connection conn1;

    --echo # conn1 runs in autocommit mode.

    --echo # Set AUTOCOMMIT= 1.

    SET AUTOCOMMIT= 1;

    --echo # Try an exclusive lock,

    --echo # which times out though running in autocommit mode.

    --error ER_LOCK_WAIT_TIMEOUT

    LOCK TABLE t1 IN SHARE MODE;

    SET AUTOCOMMIT= 0;

    COMMIT;

--echo # connection default.

connection default;

UNLOCK TABLES;

SET AUTOCOMMIT= 0;

COMMIT;

#

} 

--echo #

--echo # Normal WRITE locks go before readers (autocommit).

--echo # Set AUTOCOMMIT= 1.

SET AUTOCOMMIT= 1;

--echo # Insert a value.

INSERT INTO t1 VALUES(111);

--echo # Take a non-transactional lock.

LOCK TABLE t1 READ;

    --echo # connection conn1.

    connection conn1;

    --echo # Set AUTOCOMMIT= 1.

    SET AUTOCOMMIT= 1;

    --echo # Take a non-transactional WRITE lock,

    --echo # which waits in background until first read lock is released.

    send LOCK TABLE t1 WRITE;

--echo # connection default.

connection default;

--echo # Wait for the helper thread to sit on its lock.

while (`SELECT COUNT(*) < 1 FROM INFORMATION_SCHEMA.PROCESSLIST

        WHERE STATE LIKE '%lock%'`)

{

  --sleep 0.1

}

        --echo # connection conn2.

        connection conn2;

        --echo # Set AUTOCOMMIT= 1.

        SET AUTOCOMMIT= 1;

        --echo # Take a non-transactional READ lock,

        --echo # which waits in background until the WRITE lock is released.

        send LOCK TABLE t1 READ;

--echo # connection default.

connection default;

--echo # Wait for the helper threads to sit on their locks.

while (`SELECT COUNT(*) < 2 FROM INFORMATION_SCHEMA.PROCESSLIST

        WHERE STATE LIKE '%lock%'`)

{

  --sleep 0.1

}

--echo # Unlock this connections non-transactional lock.

UNLOCK TABLES;

    --echo # connection conn1.

    connection conn1;

    --echo # Now the WRITE lock is taken.

    reap;

    --echo # Insert a value.

    INSERT INTO t1 VALUES(1111);

    --echo # Unlock table.

    UNLOCK TABLES;

        --echo # connection conn2.

        connection conn2;

        --echo # Now the READ lock is taken.

        reap;

        --echo # Select from the table.

        SELECT * FROM t1;

        --echo # Unlock table.

        UNLOCK TABLES;

--echo # connection default.

connection default;

TRUNCATE t1;

--echo #

--echo # LOW_PRIORITY WRITE locks wait for readers (autocommit).

--echo # Insert a value.

INSERT INTO t1 VALUES(111);

--echo # Take a non-transactional lock.

LOCK TABLE t1 READ;

    --echo # connection conn1.

    connection conn1;

    --echo # Take a non-transactional LOW_PRIORITY WRITE lock,

    --echo # which waits in background until all read locks are released.

    send LOCK TABLE t1 LOW_PRIORITY WRITE;

--echo # connection default.

connection default;

--echo # Wait for the helper thread to sit on its lock.

while (`SELECT COUNT(*) < 1 FROM INFORMATION_SCHEMA.PROCESSLIST

        WHERE STATE LIKE '%lock%'`)

{

  --sleep 0.1

}

        --echo # connection conn2.

        connection conn2;

        --echo # Take a non-transactional READ lock,

        --echo # which goes before the LOW_PRIORITY WRITE lock.

        LOCK TABLE t1 READ;

        --echo # The READ lock could be taken immediately.

        --echo # Select from the table.

        SELECT * FROM t1;

        --echo # Unlock table.

        UNLOCK TABLES;

        SET AUTOCOMMIT= 0;

--echo # connection default.

connection default;

--echo # Unlock this connections non-transactional lock.

UNLOCK TABLES;

    --echo # connection conn1.

    connection conn1;

    --echo # Now the LOW_PRIORITY WRITE lock is taken.

    reap;

    --echo # Insert a value.

    INSERT INTO t1 VALUES(1111);

    --echo # Unlock table.

    UNLOCK TABLES;

    SET AUTOCOMMIT= 0;

--echo # connection default.

connection default;

TRUNCATE t1;

SET AUTOCOMMIT= 0;

COMMIT;

if ($transactional) 

{

--echo #

--echo # Normal WRITE locks go before readers (transaction).

--echo # Insert a value.

INSERT INTO t1 VALUES(111);

COMMIT;

--echo # Take a non-transactional lock.

LOCK TABLE t1 READ;

    --echo # connection conn1.

    connection conn1;

    --echo # Take a non-transactional WRITE lock,

    --echo # which waits in background until first read lock is released.

    send LOCK TABLE t1 WRITE;

--echo # connection default.

connection default;

--echo # Wait for the helper thread to sit on its lock.

while (`SELECT COUNT(*) < 1 FROM INFORMATION_SCHEMA.PROCESSLIST

        WHERE STATE LIKE '%lock%'`)

{

  --sleep 0.1

}

        --echo # connection conn2.

        connection conn2;

        --echo # Take a non-transactional READ lock,

        --echo # which waits in background until the WRITE lock is released.

        send LOCK TABLE t1 READ;

--echo # connection default.

connection default;

--echo # Wait for the helper threads to sit on their locks.

while (`SELECT COUNT(*) < 2 FROM INFORMATION_SCHEMA.PROCESSLIST

        WHERE STATE LIKE '%lock%'`)

{

  --sleep 0.1

}

--echo # Unlock this connections non-transactional lock.

UNLOCK TABLES;

    --echo # connection conn1.

    connection conn1;

    --echo # Now the WRITE lock is taken.

    reap;

    --echo # Insert a value.

    INSERT INTO t1 VALUES(1111);

    --echo # Unlock table.

    UNLOCK TABLES;

        --echo # connection conn2.

        connection conn2;

        --echo # Now the READ lock is taken.