mysql-test/r/user_lock.result

#
# Coverage for GET_LOCK, RELEASE_LOCK, RELEASE_ALL_LOCKS,
# IS_USED_LOCK and IS_FREE_LOCK functions.
#
# Tests for WL#1159 "Allow multiple locks in GET_LOCK()".
#
# FT-1: The current connection has no user-level lock aquired at all.
#       There is no parallel connection holding the lock 'test'.
# FT-1.1: IS_USED_LOCK returns NULL if the lock is unused.
SELECT IS_USED_LOCK('test') IS NULL AS expect_1;
expect_1
1
# FT-1.2: IS_FREE_LOCK returns 1 if the lock is unused.
SELECT IS_FREE_LOCK('test') = 1 AS expect_1;
expect_1
1
# FT-1.3: RELEASE_LOCK returns NULL when none of the existing connections
#         holds the lock 'test'.
SELECT RELEASE_LOCK('test') IS NULL AS expect_1;
expect_1
1
# FT-1.4: RELEASE_ALL_LOCKS returns the number of own locks freed.
#         This is 0 because the connection had no locks acquired.
SELECT RELEASE_ALL_LOCKS() = 0 AS expect_1;
expect_1
1
# FT-2: The current connection has success in aquiring a user level lock.
# FT-2.1: GET_LOCK returns 1 if it manages to acquire a lock.
SELECT GET_LOCK('test', 0) = 1 AS expect_1;
expect_1
1
# FT-2.2: IS_USED_LOCK returns our connection id because its our lock.
SELECT IS_USED_LOCK('test') = CONNECTION_ID() AS expect_1;
expect_1
1
# FT-2.3: IS_FREE_LOCK returns 0 because the lock is held by our connection.
SELECT IS_FREE_LOCK('test') = 0 AS expect_1;
expect_1
1
connect  con1,localhost,root,,;
connection con1;
# FT-3: Another connection holds a user-level lock.
# FT-3.1: IS_USED_LOCK returns the id of the other connection default
#         which holds that lock. The result fits to FT-2.2.
SET @aux = <default_id>;
SELECT IS_USED_LOCK('test') = @aux AS expect_1;
expect_1
1
# FT-3.2: IS_FREE_LOCK returns 0 because the lock is held by the other
#         connection default.
SELECT IS_FREE_LOCK('test') = 0 AS expect_1;
expect_1
1
# FT-3.3: GET_LOCK returns 0 if it can't acquire a lock (wait timeout).
SELECT GET_LOCK('test', 0) = 0 expect_1;
expect_1
1
# FT-3.4: RELEASE_LOCK returns 0 if the lock belongs to another connection.
SELECT RELEASE_LOCK('test') = 0 AS expect_1;
expect_1
1
# FT-3.5: RELEASE_ALL_LOCKS returns the number of own locks freed.
#         This is 0 because the connection had no locks acquired and
#         we also cannot free locks held by others.
SELECT RELEASE_ALL_LOCKS() = 0 AS expect_1;
expect_1
1
connection default;
# FT-4.1: RELEASE_LOCK returns 1 if it successfully releases a lock.
SELECT RELEASE_LOCK('test') = 1 AS expect_1;
expect_1
1
# FT-4.2: RELEASE_LOCK returns NULL if it doesn't release a lock and
#         and there is no such lock. It also does not matter that we
#         held that lock somewhere before.
SELECT RELEASE_LOCK('test') IS NULL;
RELEASE_LOCK('test') IS NULL
1
# FT-5: A connection can hold multiple user-level locks.
# FT-5.1: Several statements aquiring one lock per statement.
SELECT GET_LOCK('test1',0);
GET_LOCK('test1',0)
1
SELECT GET_LOCK('test2',0);
GET_LOCK('test2',0)
1
#         The connection holds two locks.
SELECT IS_USED_LOCK('test1') = CONNECTION_ID()
AND IS_USED_LOCK('test2') = CONNECTION_ID() AS expect_1;
expect_1
1
# FT-5.2: RELEASE_LOCK() frees the assigned user level lock only.
SELECT RELEASE_LOCK('test1') = 1 AS expect_1;
expect_1
1
SELECT IS_FREE_LOCK('test1') = 1 AS expect_1;
expect_1
1
SELECT IS_FREE_LOCK('test2') = 0 AS expect_1;
expect_1
1
SELECT RELEASE_LOCK('test2') = 1 AS expect_1;
expect_1
1
# FT-5.3: RELEASE_ALL_LOCKS frees all locks all at once
#         and returns the number of locks freed.
SELECT GET_LOCK('test1',0);
GET_LOCK('test1',0)
1
SELECT GET_LOCK('test2',0);
GET_LOCK('test2',0)
1
SELECT RELEASE_ALL_LOCKS() = 2 AS expect_1;
expect_1
1
SELECT IS_FREE_LOCK('test1') AND IS_FREE_LOCK('test2') AS expect_1;
expect_1
1
# FT-5.4: One statement aquiring more than one lock works the same way.
# FT-5.4.1: More than one result expression with GET_LOCK, one row.
SELECT GET_LOCK('test1',0), GET_LOCK('test2',0);
GET_LOCK('test1',0)	GET_LOCK('test2',0)
1	1
#         The connections holds two locks.
SELECT IS_USED_LOCK('test1') = CONNECTION_ID()
AND IS_USED_LOCK('test2') = CONNECTION_ID() AS expect_1;
expect_1
1
SELECT RELEASE_ALL_LOCKS() = 2 AS expect_1;
expect_1
1
SELECT IS_USED_LOCK('test1') IS NULL AND IS_USED_LOCK('test2') IS NULL AS expect_1;
expect_1
1
# FT-5.4.2: More than one time GET_LOCK somewhere, one row.
SELECT GET_LOCK('test1',0) FROM (SELECT 1 AS col1) AS my_tab
WHERE GET_LOCK('test2',0) = 1;
GET_LOCK('test1',0)
1
SELECT IS_USED_LOCK('test1') = CONNECTION_ID()
AND IS_USED_LOCK('test2') = CONNECTION_ID() AS expect_1;
expect_1
1
SELECT RELEASE_ALL_LOCKS() = 2 AS expect_1;
expect_1
1
SELECT IS_USED_LOCK('test1') IS NULL AND IS_USED_LOCK('test2') IS NULL AS expect_1;
expect_1
1
# FT-5.4.3: One result expression with GET_LOCK, more than one result row.
SELECT GET_LOCK(col1,0) FROM (SELECT 'test1' AS col1 UNION SELECT 'test2') AS my_tab;
GET_LOCK(col1,0)
1
1
SELECT IS_USED_LOCK('test1') = CONNECTION_ID()
AND IS_USED_LOCK('test2') = CONNECTION_ID() AS expect_1;
expect_1
1
SELECT RELEASE_ALL_LOCKS() = 2 AS expect_1;
expect_1
1
SELECT IS_USED_LOCK('test1') IS NULL AND IS_USED_LOCK('test2') IS NULL AS expect_1;
expect_1
1
# FT-6: A connection can hold multiple user-level locks with same name.
# FT-6.1: GET_LOCK() and RELEASE_LOCK() work recursively.
SELECT GET_LOCK('test', 0);
GET_LOCK('test', 0)
1
SELECT GET_LOCK('test', 0);
GET_LOCK('test', 0)
1
SELECT GET_LOCK('test', 0);
GET_LOCK('test', 0)
1
SELECT RELEASE_LOCK('test');
RELEASE_LOCK('test')
1
SELECT RELEASE_LOCK('test');
RELEASE_LOCK('test')
1
SELECT RELEASE_LOCK('test');
RELEASE_LOCK('test')
1
#         Once the last instance of the lock is released,
#         the next call returns NULL.
SELECT RELEASE_LOCK('test') IS NULL AS expect_1;
expect_1
1
# FT-6.2: Counting in RELEASE_ALL_LOCKS() for recursive locks is correct.
SELECT GET_LOCK('test', 0), GET_LOCK('test', 0);
GET_LOCK('test', 0)	GET_LOCK('test', 0)
1	1
SELECT RELEASE_ALL_LOCKS() = 2 AS expect_1;
expect_1
1
# FT-7: Check a statement with GET_LOCK() getting killed.
# FT-7.1: KILL arrives when waiting for the user lock
#         The statement must return ER_QUERY_INTERRUPTED.
SELECT GET_LOCK('test', 0);
GET_LOCK('test', 0)
1
connection con1;
# Send statement and reap result later.
SELECT GET_LOCK('test', 7200);
connection default;
SET @aux = <con1_id>;
KILL QUERY @aux;
connection con1;
# Reap result of "SELECT GET_LOCK('test', 7200)".
ERROR 70100: Query execution was interrupted
# Check that Connection con1 sees the right state.
SELECT IS_FREE_LOCK('test') = 0 AS expect_1;
expect_1
1
SELECT IS_USED_LOCK('test') <> CONNECTION_ID() AS expect_1;
expect_1
1
connection default;
SELECT RELEASE_LOCK('test') = 1 AS expect_1;
expect_1
1
# FT-7.2: The lock is not held by some other connection.
#         KILL arrives during the SLEEP phase after the lock is taken.
connection con1;
# Send statement and reap result later.
SELECT GET_LOCK('test', 7200), SLEEP(10);
connection default;
SET @aux = <con1_id>;
KILL QUERY @aux;
# Connection con1 has got the lock.
SELECT IS_FREE_LOCK('test') = 0 AS expect_1;
expect_1
1
connection con1;
# Reap result of "SELECT GET_LOCK('test', 7200), SLEEP(10)".
# We intentionally do not print the result.
# If a statement killed in the 'user sleep' phase harvests finally
# success or ER_QUERY_INTERRUPTED is NOT in the scope of current check.
# The essential is that the connection con1 has got that lock.
SELECT IS_FREE_LOCK('test') = 0 AS expect_1;
expect_1
1
SELECT IS_USED_LOCK('test') = CONNECTION_ID() AS expect_1;
expect_1
1
connection default;
SELECT IS_FREE_LOCK('test') = 0 AS expect_1;
expect_1
1
connection con1;
SELECT RELEASE_LOCK('test') = 1 AS expect_1;
expect_1
1
# FT-8: Check that user locks disappear if the session is killed.
connection con1;
SELECT GET_LOCK('test1', 0);
GET_LOCK('test1', 0)
1
SELECT GET_LOCK('test1', 0);
GET_LOCK('test1', 0)
1
connection default;
SELECT IS_FREE_LOCK('test1') = 0 AS expect_1;
expect_1
1
SET @aux = <con1_id>;
KILL @aux;
SELECT IS_FREE_LOCK('test1') = 1 AS expect_1;
expect_1
1
connection con1;
disconnect con1;
connect  con1,localhost,root,,;
# FT-9: Check that Deadlocks are detected e.g. in case of a mutual wait.
connection default;
SELECT GET_LOCK('test1', 0);
GET_LOCK('test1', 0)
1
connection con1;
SELECT GET_LOCK('test2', 0);
GET_LOCK('test2', 0)
1
# Send statement and reap result later.
SELECT GET_LOCK('test1', 7200);
connection default;
SELECT GET_LOCK('test2', 7200);
ERROR HY000: Deadlock found when trying to get user-level lock; try rolling back transaction/releasing locks and restarting lock acquisition.
SELECT RELEASE_LOCK('test1');
RELEASE_LOCK('test1')
1
connection con1;
# Reap result of "SELECT GET_LOCK('test1', 7200)".
GET_LOCK('test1', 7200)
1
#       Two RELEASE_LOCK in one statement must work too.
SELECT RELEASE_LOCK('test2') + RELEASE_LOCK('test1') = 2 AS expect_1;
expect_1
1
# FT-10: Non user lock related locking/unlocking does not free
#        user locks.
# FT-10.1: LOCK/UNLOCK TABLES
connection default;
CREATE TABLE t1 (id INT);
SELECT GET_LOCK('test1', 0);
GET_LOCK('test1', 0)
1
LOCK TABLE t1 WRITE;
SELECT GET_LOCK('test2', 0);
GET_LOCK('test2', 0)
1
UNLOCK TABLES;
SELECT RELEASE_ALL_LOCKS() = 2 AS expect_1;
expect_1
1
# FT-10.2: GLOBAL READ LOCK
SELECT GET_LOCK('test1', 0);
GET_LOCK('test1', 0)
1
FLUSH TABLES WITH READ LOCK;
SELECT GET_LOCK('test2', 0);
GET_LOCK('test2', 0)
1
UNLOCK TABLES;
SELECT (RELEASE_LOCK('test1') = 1) AND (RELEASE_LOCK('test3') IS NULL)
AND (RELEASE_LOCK('test2') = 1) AS expect_1;
expect_1
1
# FT-10.3: BEGIN/COMMIT/ROLLBACK don't unlock user locks.
DELETE FROM t1;
BEGIN;
INSERT INTO t1 SET id = 1;
SELECT GET_LOCK('test1', 0);
GET_LOCK('test1', 0)
1
COMMIT;
BEGIN;
INSERT INTO t1 SET id = 2;
SELECT GET_LOCK('test2', 0);
GET_LOCK('test2', 0)
1
ROLLBACK;
SELECT RELEASE_ALL_LOCKS() = 2 AS expect_1;
expect_1
1
SELECT id FROM t1 ORDER BY id;
id
1
DELETE FROM t1;
# FT-11: Deadlocks between user locks and other metadata locks
#        are correctly detected.
#
# FT-11.1: Waits for user-level locks are preferred as victim over DDL.
#          Without any user locks : "default" waits till "con1" COMMITs.
SELECT GET_LOCK('test', 0);
GET_LOCK('test', 0)
1
connection con1;
BEGIN;
INSERT INTO t1 VALUES (1);
# Send statement and reap result later.
SELECT GET_LOCK('test', 7200);
connection default;
# Send statement and reap result later.
RENAME TABLE t1 TO t2;
connection con1;
# Reap result of "SELECT GET_LOCK('test', 7200)".
ERROR HY000: Deadlock found when trying to get user-level lock; try rolling back transaction/releasing locks and restarting lock acquisition.
#          When user-level lock wait is aborted due to deadlock the
#          transaction is not rolled back.
COMMIT;
connection default;
# Reap result of "RENAME TABLE t1 TO t2".
RENAME TABLE t2 TO t1;
SELECT RELEASE_LOCK('test');
RELEASE_LOCK('test')
1
connection con1;
#          The row inserted in the committed transaction must exist.
SELECT COUNT(*) = 1 AS expect_1 FROM t1 WHERE id = 1;
expect_1
1
connection default;
# FT-11.2: Waits for DML locks are preferred as victim over waits for
#          user-level locks.
#          Without any user locks : "default" waits till MDL lock timout kicks in.
SELECT GET_LOCK('test', 0);
GET_LOCK('test', 0)
1
connection con1;
LOCK TABLE t1 WRITE;
# Send statement and reap result later.
SELECT GET_LOCK('test', 7200);
connection default;
SELECT COUNT(*) FROM t1;
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
#          User-level lock is not released even though transaction/statement
#          is rolled back in this case.
SELECT RELEASE_LOCK('test') = 1 AS expect_1;
expect_1
1
connection con1;
# Reap result of "SELECT GET_LOCK('test', 7200)".
GET_LOCK('test', 7200)
1
SELECT RELEASE_LOCK('test');
RELEASE_LOCK('test')
1
UNLOCK TABLES;
# FT-12: GET_LOCK in some scenarios of interest.
#        The user-level lock related functions work well.
#        The "main" property of interest seen is:
#        Some failing statement might have taken user-level locks
#        and these locks will not get freed because the statement
#        failed.
# FT-12.1: GET_LOCK within some TRIGGER, User locks stay recursive
connection default;
DELETE FROM t1;
#        Warning: The trigger definition used is "evil" and not for use
#                 some production environment.
CREATE TRIGGER trig_t1_ins BEFORE INSERT ON t1 FOR EACH ROW
SET @aux = GET_LOCK(new.id,7200);
SELECT GET_LOCK(CAST(2 AS CHAR),0);
GET_LOCK(CAST(2 AS CHAR),0)
1
#          Success == No collision with own user lock.
#          Get a second with same name and two with other names.
INSERT INTO t1 VALUES(1),(2),(3);
SELECT RELEASE_LOCK(1) = 1 AS expect_1;
expect_1
1
SELECT RELEASE_LOCK(2) = 1 AS expect_1;
expect_1
1
# Get again 1.
SELECT RELEASE_LOCK(2) = 1 AS expect_1;
expect_1
1
SELECT RELEASE_LOCK(3) = 1 AS expect_1;
expect_1
1
SELECT RELEASE_ALL_LOCKS() = 0 AS expect_1;
expect_1
1
SELECT COUNT(*) FROM t1;
COUNT(*)
3
# FT-12.2: GET_LOCK in an INSERT TRIGGER loses against a MDL lock request.
connection default;
DELETE FROM t1;
SELECT GET_LOCK(2,0);
GET_LOCK(2,0)
1
connection con1;
# Send statement and reap result later.
INSERT INTO t1 VALUES(1),(2),(3);
connection default;
RENAME TABLE t1 TO t1x;
RENAME TABLE t1x TO t1;
connection con1;
# Reap result of "INSERT INTO t1 VALUES(1),(2),(3)".
ERROR HY000: Deadlock found when trying to get user-level lock; try rolling back transaction/releasing locks and restarting lock acquisition.
SELECT RELEASE_ALL_LOCKS();
RELEASE_ALL_LOCKS()
1
SELECT COUNT(*) FROM t1;
COUNT(*)
0
connection default;
SELECT RELEASE_ALL_LOCKS();
RELEASE_ALL_LOCKS()
1
# FT-12.3: GET_LOCK in a better designed INSERT TRIGGER loses.
# FT-12.3.1: Session with INSERT loses via user lock deadlock.
connection default;
DELETE FROM t1;
DROP TRIGGER trig_t1_ins;
CREATE TRIGGER trig_t1_ins BEFORE INSERT ON t1 FOR EACH ROW
BEGIN
SET @aux = GET_LOCK(2,1);
IF @aux <> 1 THEN
SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'LOCK 2 not got. Abort.', MYSQL_ERRNO = 9999;
END IF;
END;|
SELECT GET_LOCK(CAST(2 AS CHAR),0);
GET_LOCK(CAST(2 AS CHAR),0)
1
connection con1;
SELECT GET_LOCK(CAST(1 AS CHAR),0);
GET_LOCK(CAST(1 AS CHAR),0)
1
connection default;
# Send statement and reap result later.
SELECT GET_LOCK(CAST(1 AS CHAR),7200);
connection con1;
INSERT INTO t1 VALUES(1),(2),(3);
ERROR HY000: Deadlock found when trying to get user-level lock; try rolling back transaction/releasing locks and restarting lock acquisition.
SELECT RELEASE_ALL_LOCKS();
RELEASE_ALL_LOCKS()
1
SELECT COUNT(*) FROM t1;
COUNT(*)
0
connection default;
# Reap result of "SELECT GET_LOCK(CAST(1 AS CHAR),7200)".
GET_LOCK(CAST(1 AS CHAR),7200)
1
SELECT RELEASE_ALL_LOCKS();
RELEASE_ALL_LOCKS()
2
# FT-12.3.2: INSERT fails in TRIGGER because not getting a user lock in time.
connection default;
DELETE FROM t1;
SELECT GET_LOCK(CAST(2 AS CHAR),0);
GET_LOCK(CAST(2 AS CHAR),0)
1
connection con1;
INSERT INTO t1 VALUES(1),(2),(3);
ERROR 45000: LOCK 2 not got. Abort.
SELECT @aux;
@aux
0
SELECT RELEASE_ALL_LOCKS();
RELEASE_ALL_LOCKS()
0
SELECT COUNT(*) FROM t1;
COUNT(*)
0
connection default;
SELECT RELEASE_ALL_LOCKS();
RELEASE_ALL_LOCKS()
1
DROP TRIGGER trig_t1_ins;
# FT-12.4: User locks and violation of uniqueness.
connection default;
CREATE TABLE t2 (col1 INT, col2 INT, PRIMARY KEY(col1));
DELETE FROM t1;
INSERT INTO t1 VALUES(1),(2),(1);
INSERT INTO t2 SELECT id, GET_LOCK(id,0) FROM t1;
ERROR 23000: Duplicate entry '1' for key 't2.PRIMARY'
SELECT RELEASE_ALL_LOCKS();
RELEASE_ALL_LOCKS()
3
SELECT * FROM t2;
col1	col2
DELETE FROM t1;
DELETE FROM t2;
INSERT INTO t1 VALUES(1),(1),(2);
INSERT INTO t2 SELECT id, GET_LOCK(id,0) FROM t1;
ERROR 23000: Duplicate entry '1' for key 't2.PRIMARY'
SELECT RELEASE_ALL_LOCKS();
RELEASE_ALL_LOCKS()
2
SELECT * FROM t2;
col1	col2
DROP TABLE t2;
# FT-12.5: GET_LOCK in a statement having wrong syntax anyway.
#          We get no lock because full syntax check comes first.
SELECT GET_LOCK('test', 0) ORDER BY oscar;
ERROR 42S22: Unknown column 'oscar' in 'order clause'
SELECT RELEASE_ALL_LOCKS() = 0 AS expect_1;
expect_1
1
# Cleanup.
connection default;
DROP TABLE t1;
# FT-12.6: Mix of GET_LOCK and RELEASE*LOCK*
#          Execution is from the left to the right.
SELECT GET_LOCK('test', 0), RELEASE_LOCK('test');
GET_LOCK('test', 0)	RELEASE_LOCK('test')
1	1
SELECT IS_FREE_LOCK('test') = 1 AS expect_1;
expect_1
1
SELECT GET_LOCK('test', 0), RELEASE_LOCK('test'), GET_LOCK('test', 0);
GET_LOCK('test', 0)	RELEASE_LOCK('test')	GET_LOCK('test', 0)
1	1	1
SELECT IS_FREE_LOCK('test') = 0 AS expect_1;
expect_1
1
SELECT RELEASE_LOCK('test') = 1 AS expect_1;
expect_1
1
SELECT GET_LOCK('test', 0), GET_LOCK('test1', 0), RELEASE_ALL_LOCKS(),
GET_LOCK('test', 0);
GET_LOCK('test', 0)	GET_LOCK('test1', 0)	RELEASE_ALL_LOCKS()	GET_LOCK('test', 0)
1	1	2	1
SELECT RELEASE_ALL_LOCKS() = 1 AS expect_1;
expect_1
1
# FT-13: Check types of function results.
CREATE TABLE t1 AS SELECT GET_LOCK('test', 0) AS g, RELEASE_LOCK('test') AS r,
RELEASE_ALL_LOCKS() AS ra, IS_USED_LOCK('test') AS isu,
IS_FREE_LOCK('test') AS isf;
DESCRIBE t1;
Field	Type	Null	Key	Default	Extra
g	int	YES		NULL
r	int	YES		NULL
ra	bigint unsigned	NO		0
isu	bigint unsigned	YES		NULL
isf	int	YES		NULL
DROP TABLE t1;
# FT-14: Check the handling of user-level lock related function parameters.
# FT-14.1: Lock names with NULL or "" assigned.
SELECT GET_LOCK(NULL, 0);
ERROR 42000: Incorrect user-level lock name 'NULL'.
SELECT GET_LOCK("", 0);
ERROR 42000: Incorrect user-level lock name ''.
SELECT RELEASE_LOCK(NULL);
ERROR 42000: Incorrect user-level lock name 'NULL'.
SELECT RELEASE_LOCK("");
ERROR 42000: Incorrect user-level lock name ''.
SELECT IS_USED_LOCK(NULL);
ERROR 42000: Incorrect user-level lock name 'NULL'.
SELECT IS_USED_LOCK("");
ERROR 42000: Incorrect user-level lock name ''.
SELECT IS_FREE_LOCK(NULL);
ERROR 42000: Incorrect user-level lock name 'NULL'.
SELECT IS_FREE_LOCK("");
ERROR 42000: Incorrect user-level lock name ''.
# FT-14.2: Length of lock name at the limit of 64.
SELECT GET_LOCK(REPEAT('a', 64), 0) = 1 AS expect_1;
expect_1
1
SELECT IS_USED_LOCK(REPEAT('a', 64)) = CONNECTION_ID() AS expect_1;
expect_1
1
SELECT IS_FREE_LOCK(REPEAT('a', 64)) = 0 AS expect_1;
expect_1
1
SELECT RELEASE_LOCK(REPEAT('a', 64)) = 1 AS expect_1;
expect_1
1
# FT-14.3: Lock name too long.
SELECT GET_LOCK(REPEAT('a', 65), 0);
ERROR 42000: Incorrect user-level lock name 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'.
SELECT IS_USED_LOCK(REPEAT('a', 65));
ERROR 42000: Incorrect user-level lock name 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'.
SELECT IS_FREE_LOCK(REPEAT('a', 65));
ERROR 42000: Incorrect user-level lock name 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'.
SELECT RELEASE_LOCK(REPEAT('a', 65));
ERROR 42000: Incorrect user-level lock name 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'.
# FT-14.4: Check that lock names are case-insensitive.
SELECT GET_LOCK('A', 0);
GET_LOCK('A', 0)
1
connection con1;
SELECT GET_LOCK('a', 0);
GET_LOCK('a', 0)
0
connection default;
SELECT IS_USED_LOCK('a') = CONNECTION_ID();
IS_USED_LOCK('a') = CONNECTION_ID()
1
SELECT IS_FREE_LOCK('a');
IS_FREE_LOCK('a')
0
SELECT RELEASE_LOCK('a');
RELEASE_LOCK('a')
1
# FT-14.5: Check that lock names are converted and compared in utf-8,
# to do this use 'тест' in various encodings as lock name.
SELECT GET_LOCK(_cp1251 0xf2e5f1f2, 0);
GET_LOCK(_cp1251 0xf2e5f1f2, 0)
1
connection con1;
SELECT GET_LOCK(_utf8 0xd182d0b5d181d182, 0);
GET_LOCK(_utf8 0xd182d0b5d181d182, 0)
0
Warnings:
Warning	3719	'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
connection default;
SELECT IS_USED_LOCK(_koi8r 0xd4c5d3d4) = CONNECTION_ID();
IS_USED_LOCK(_koi8r 0xd4c5d3d4) = CONNECTION_ID()
1
SELECT IS_FREE_LOCK(_utf8 0xd182d0b5d181d182);
IS_FREE_LOCK(_utf8 0xd182d0b5d181d182)
0
Warnings:
Warning	3719	'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
SELECT RELEASE_LOCK(_utf8 0xd182d0b5d181d182);
RELEASE_LOCK(_utf8 0xd182d0b5d181d182)
1
Warnings:
Warning	3719	'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
# FT-14.6: Check wrong number of parameters.
SELECT GET_LOCK('test');
ERROR 42000: Incorrect parameter count in the call to native function 'GET_LOCK'
SELECT GET_LOCK('test', 0, 1);
ERROR 42000: Incorrect parameter count in the call to native function 'GET_LOCK'
SELECT RELEASE_LOCK();
ERROR 42000: Incorrect parameter count in the call to native function 'RELEASE_LOCK'
SELECT RELEASE_LOCK('test', 1);
ERROR 42000: Incorrect parameter count in the call to native function 'RELEASE_LOCK'
SELECT RELEASE_ALL_LOCKS('test');
ERROR 42000: Incorrect parameter count in the call to native function 'RELEASE_ALL_LOCKS'
SELECT IS_USED_LOCK();
ERROR 42000: Incorrect parameter count in the call to native function 'IS_USED_LOCK'
SELECT IS_USED_LOCK('test', 'test2');
ERROR 42000: Incorrect parameter count in the call to native function 'IS_USED_LOCK'
SELECT IS_FREE_LOCK();
ERROR 42000: Incorrect parameter count in the call to native function 'IS_FREE_LOCK'
SELECT IS_FREE_LOCK('test', 'test2');
ERROR 42000: Incorrect parameter count in the call to native function 'IS_FREE_LOCK'
# FT-14.7: NULL timeout is interpreted as 0 timeout.
connection con1;
SELECT GET_LOCK("test", 0);
GET_LOCK("test", 0)
1
connection default;
#        Interpretation as 0 leads to use of send/reap is not required.
SELECT GET_LOCK("test", NULL) = 0 AS expect_1;
expect_1
1
connection con1;
SELECT RELEASE_LOCK("test");
RELEASE_LOCK("test")
1
connection default;
# FT-14.8: Check that a negative timeout is interpreted as infinite wait.
#         Test case for
#         BUG#11764049 GET_LOCK() TIMEOUT BEHAVES DIFFERENTLY ON
#                      DIFFERING PLATFORMS
connection default;
SELECT GET_LOCK('test', 0);
GET_LOCK('test', 0)
1
connection con1;
# Send statement and reap result later.
SELECT GET_LOCK('test', -1);
connection default;
SELECT RELEASE_LOCK('test');
RELEASE_LOCK('test')
1
connection con1;
# Reap result of "SELECT GET_LOCK('test', -1)".
GET_LOCK('test', -1)
1
SELECT RELEASE_LOCK('test');
RELEASE_LOCK('test')
1
disconnect con1;
connection default;
CREATE TABLE t1 (conn CHAR(7), connection_id INT);
INSERT INTO t1 VALUES ('default', CONNECTION_ID());
SELECT GET_LOCK('bug16501',600);
GET_LOCK('bug16501',600)
1
connect  con1,localhost,root,,;
INSERT INTO t1 VALUES ('con1', CONNECTION_ID());
SELECT IS_USED_LOCK('bug16501') = connection_id
FROM t1
WHERE conn = 'default';
IS_USED_LOCK('bug16501') = connection_id
1
# Send statement and reap result later.
SELECT GET_LOCK('bug16501',600);
connection default;
SELECT IS_USED_LOCK('bug16501') = CONNECTION_ID();
IS_USED_LOCK('bug16501') = CONNECTION_ID()
1
SELECT RELEASE_LOCK('bug16501');
RELEASE_LOCK('bug16501')
1
connection con1;
# Reap result of "SELECT GET_LOCK('bug16501',600)".
GET_LOCK('bug16501',600)
1
connection default;
SELECT IS_USED_LOCK('bug16501') = connection_id
FROM t1
WHERE conn = 'con1';
IS_USED_LOCK('bug16501') = connection_id
1
connection con1;
SELECT IS_USED_LOCK('bug16501') = CONNECTION_ID();
IS_USED_LOCK('bug16501') = CONNECTION_ID()
1
SELECT RELEASE_LOCK('bug16501');
RELEASE_LOCK('bug16501')
1
SELECT IS_USED_LOCK('bug16501');
IS_USED_LOCK('bug16501')
NULL
connection default;
SELECT RELEASE_ALL_LOCKS();
RELEASE_ALL_LOCKS()
0
DROP TABLE t1;
connection con1;
disconnect con1;
connection default;
#
# Bug#20031761 ASSERTION `SCALE >= 0 && PRECISION > 0 &&
#              SCALE <= PRECISION' FAILED
#
SET @@session.div_precision_increment=0;
select * from(SELECT MIN(GET_LOCK(0,0) / 1 ^ 0)) as a;
MIN(GET_LOCK(0,0) / 1 ^ 0)
1
select * from(SELECT MAX(RELEASE_LOCK(0) / 1 ^ 0)) as a;
MAX(RELEASE_LOCK(0) / 1 ^ 0)
1
select * from(SELECT MAX(RELEASE_ALL_LOCKS() / 1 ^ 0)) as a;
MAX(RELEASE_ALL_LOCKS() / 1 ^ 0)
0
SET @@session.div_precision_increment=default;
#
# End of tests
#