90 lines
2.3 KiB
PHP
90 lines
2.3 KiB
PHP
#
|
|
# Check concurrent locking issues:
|
|
# Rows scanned but are not in the updated table should be locked when
|
|
# xengine_lock_scanned_rows is on but not locked otherwise.
|
|
#
|
|
# To call this, set $isolation_level and $lock_scanned_rows and call this file
|
|
#
|
|
# let $isolation_level = REPEATABLE READ;
|
|
# let $lock_scanned_rows = 0 (or 1)
|
|
# --source suite/xengine/include/locking_issues_case7.inc
|
|
#
|
|
|
|
--echo
|
|
--echo -----------------------------------------------------------------------
|
|
--echo - Locking issues case 7:
|
|
--echo - Rows that are scanned as part of a query but not in the table being
|
|
--echo - updated should not be locked unless xengine_lock_scanned_rows is on
|
|
--echo -----------------------------------------------------------------------
|
|
|
|
--disable_warnings
|
|
DROP TABLE IF EXISTS t1, t2;
|
|
--enable_warnings
|
|
|
|
SELECT @@global.xengine_lock_scanned_rows;
|
|
|
|
if ($lock_scanned_rows)
|
|
{
|
|
let $original_val=query_get_value(
|
|
select @@global.xengine_lock_scanned_rows as val, val, 1);
|
|
SET GLOBAL xengine_lock_scanned_rows=ON;
|
|
}
|
|
|
|
CREATE TABLE t1(id INT PRIMARY KEY, value INT) ENGINE=XENGINE;
|
|
CREATE TABLE t2(id INT PRIMARY KEY, value INT) ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES (1,1), (2,2), (3,3);
|
|
INSERT INTO t2 VALUES (1,1), (2,2), (3,3), (4,4), (5,5);
|
|
|
|
connect (con1,localhost,root,,);
|
|
connect (con2,localhost,root,,);
|
|
|
|
connection con1;
|
|
eval SET SESSION TRANSACTION ISOLATION LEVEL $isolation_level;
|
|
BEGIN;
|
|
|
|
connection con2;
|
|
eval SET SESSION TRANSACTION ISOLATION LEVEL $isolation_level;
|
|
BEGIN;
|
|
|
|
--echo lock_scanned_rows is $lock_scanned_rows
|
|
if ($lock_scanned_rows == 1)
|
|
{
|
|
connection con1;
|
|
# This is expected to leave a lock id=3 in t2;
|
|
UPDATE t1 JOIN t2 ON t1.id = t2.id SET t1.value=t1.value+100 WHERE t2.id=3;
|
|
|
|
connection con2;
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
UPDATE t2 SET value=value+100 WHERE id=3;
|
|
|
|
# No other row in t2 should be locked;
|
|
UPDATE t2 SET value=value+100 WHERE id IN (1,2,4,5);
|
|
SELECT * FROM t2;
|
|
}
|
|
|
|
if ($lock_scanned_rows == 0)
|
|
{
|
|
connection con1;
|
|
# This should leave no locks on any row in t2;
|
|
UPDATE t1 JOIN t2 ON t1.id = t2.id SET t1.value=t1.value+100 WHERE t2.id=3;
|
|
|
|
connection con2;
|
|
UPDATE t2 SET value=value+100;
|
|
SELECT * FROM t2;
|
|
}
|
|
|
|
connection con1;
|
|
COMMIT;
|
|
|
|
connection default;
|
|
disconnect con1;
|
|
disconnect con2;
|
|
|
|
DROP TABLE t1;
|
|
DROP TABLE t2;
|
|
|
|
if ($lock_scanned_rows == 1)
|
|
{
|
|
eval SET GLOBAL xengine_lock_scanned_rows=$original_val;
|
|
}
|