1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
|
CREATE TABLE t (
id INT PRIMARY KEY,
val INT UNIQUE KEY
);
INSERT INTO t (val,id) VALUES (10,4);
INSERT INTO t (val,id) VALUES (20,7);
--connect (read_view_holder, localhost, root,,)
BEGIN;
SELECT * FROM t;
# +----+------+
# | id | val |
# +----+------+
# | 4 | 10 |
# | 7 | 20 |
# +----+------+
--connection default
DELETE FROM t WHERE val=10; # delete-mark (val=10 --> id=4)
BEGIN;
INSERT INTO t (val,id) VALUES (10,3);
# Make sure that there is no "S" lock placed on val index val=20, id=7.
# There should be an "S,GAP" lock though, but LOCK_DATA may contain NULLs
# if a page is missing from BP or latched, so to avoid nondeterminism, we
# will simply count :
# how many "S" locks we have - there should be just one on the val=10,id=4
# how many "S,GAP" locks we have - there should be two, on 20,7 and 10,3 :
# +------------+-----------+-----------+-------------+-----------+
# | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
# +------------+-----------+-----------+-------------+-----------+
# | NULL | TABLE | IX | GRANTED | NULL |
# | val | RECORD | S | GRANTED | 10, 4 |
# | val | RECORD | S,GAP | GRANTED | 20, 7 |
# | val | RECORD | S,GAP | GRANTED | 10, 3 |
# +------------+-----------+-----------+-------------+-----------+
--sorted_result
SELECT LOCK_MODE, COUNT(*)
FROM performance_schema.data_locks
WHERE OBJECT_SCHEMA='test'
AND OBJECT_NAME='t'
AND INDEX_NAME='val'
AND LOCK_TYPE='RECORD'
GROUP BY 1;
ROLLBACK;
--connection read_view_holder
ROLLBACK;
--connection default
--disconnect read_view_holder
DROP TABLE t;
|
