| 1 |
# Create a table with rows which can be small or large |
| 2 |
# depending on our wish: |
| 3 |
CREATE TABLE t1(id INT PRIMARY key, val VARCHAR(16000)); |
| 4 |
# Create 1024 records with id=0,1000,2000,... |
| 5 |
INSERT INTO t1 (id,val) VALUES (0, "x"); |
| 6 |
INSERT INTO t1 (id,val) |
| 7 |
SELECT (SELECT MAX(id) FROM t1)+1000+id, "x" FROM t1; |
| 8 |
INSERT INTO t1 (id,val) |
| 9 |
SELECT (SELECT MAX(id) FROM t1)+1000+id, "x" FROM t1; |
| 10 |
INSERT INTO t1 (id,val) |
| 11 |
SELECT (SELECT MAX(id) FROM t1)+1000+id, "x" FROM t1; |
| 12 |
INSERT INTO t1 (id,val) |
| 13 |
SELECT (SELECT MAX(id) FROM t1)+1000+id, "x" FROM t1; |
| 14 |
INSERT INTO t1 (id,val) |
| 15 |
SELECT (SELECT MAX(id) FROM t1)+1000+id, "x" FROM t1; |
| 16 |
INSERT INTO t1 (id,val) |
| 17 |
SELECT (SELECT MAX(id) FROM t1)+1000+id, "x" FROM t1; |
| 18 |
INSERT INTO t1 (id,val) |
| 19 |
SELECT (SELECT MAX(id) FROM t1)+1000+id, "x" FROM t1; |
| 20 |
INSERT INTO t1 (id,val) |
| 21 |
SELECT (SELECT MAX(id) FROM t1)+1000+id, "x" FROM t1; |
| 22 |
INSERT INTO t1 (id,val) |
| 23 |
SELECT (SELECT MAX(id) FROM t1)+1000+id, "x" FROM t1; |
| 24 |
INSERT INTO t1 (id,val) |
| 25 |
SELECT (SELECT MAX(id) FROM t1)+1000+id, "x" FROM t1; |
| 26 |
SELECT COUNT(*) FROM t1; |
| 27 |
COUNT(*) |
| 28 |
1024 |
| 29 |
# It so happens that 894000 is the largest record on its page, |
| 30 |
# which we can see by looking at engine_lock_id |
| 31 |
# being *:space_id:page_id:heap_no:* and verifying that |
| 32 |
# 894000 has a different page_id than 895000. We'll call them "left" |
| 33 |
# and "right" page. |
| 34 |
CREATE FUNCTION lock_page_no (engine_lock_id CHAR(100)) |
| 35 |
RETURNS CHAR(20) DETERMINISTIC |
| 36 |
RETURN SUBSTRING_INDEX(SUBSTRING_INDEX(engine_lock_id, ':', 4), ':', -1); |
| 37 |
BEGIN; |
| 38 |
SELECT * FROM t1 WHERE id=894000 FOR UPDATE; |
| 39 |
id val |
| 40 |
894000 x |
| 41 |
SELECT * FROM t1 WHERE id=895000 FOR UPDATE; |
| 42 |
id val |
| 43 |
895000 x |
| 44 |
SELECT lock_page_no(engine_lock_id) |
| 45 |
FROM performance_schema.data_locks |
| 46 |
WHERE LOCK_TYPE='RECORD' AND lock_data='894000' |
| 47 |
INTO @left; |
| 48 |
SELECT lock_page_no(engine_lock_id) |
| 49 |
FROM performance_schema.data_locks |
| 50 |
WHERE LOCK_TYPE='RECORD' AND lock_data='895000' |
| 51 |
INTO @right; |
| 52 |
COMMIT; |
| 53 |
include/assert.inc [The two records should be on two different pages] |
| 54 |
CREATE FUNCTION lock_page_name (engine_lock_id CHAR(100)) |
| 55 |
RETURNS CHAR(20) DETERMINISTIC |
| 56 |
RETURN CASE lock_page_no(engine_lock_id) |
| 57 |
WHEN @left THEN "left" |
| 58 |
WHEN @right THEN "right" |
| 59 |
ELSE "other" |
| 60 |
END; |
| 61 |
# Now, we stop the purge as we want to just delete-mark the record, |
| 62 |
# not physically remove it. |
| 63 |
SET GLOBAL innodb_purge_stop_now = ON; |
| 64 |
# We delete-mark the largest record on "left" page. |
| 65 |
DELETE FROM t1 WHERE id = 894000; |
| 66 |
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE; |
| 67 |
BEGIN; |
| 68 |
# This will return no result, as the record is delete-marked. |
| 69 |
SELECT * FROM t1 WHERE id=894000 FOR UPDATE; |
| 70 |
id val |
| 71 |
# But, should place an X,REC_NOT_GAP lock on the delete-marked record: |
| 72 |
SELECT lock_page_name(engine_lock_id),lock_mode,lock_status,lock_data |
| 73 |
FROM performance_schema.data_locks WHERE LOCK_TYPE='RECORD'; |
| 74 |
lock_page_name(engine_lock_id) lock_mode lock_status lock_data |
| 75 |
left X,REC_NOT_GAP GRANTED 894000 |
| 76 |
# We now, unpause the purge, so that the delete-marked record gets |
| 77 |
# physically removed, and the X,REC_NOT_GAP on it gets |
| 78 |
# converted/inherited into a lock on the gap before "next record". |
| 79 |
SET GLOBAL innodb_purge_run_now=ON; |
| 80 |
# However, 894000 was the largest on its page, so the "next record" is |
| 81 |
# the imaginary "supremum pseudo-record", and indeed we |
| 82 |
# expect to see a lock on this "supremum pseudo-record" (P_S displays |
| 83 |
# it as X, as opposed to X,GAP, because locks on supremum are always |
| 84 |
# implicitly "gap" locks, as the record itself doesn't really exist). |
| 85 |
# Note that the lock is *not* duplicated on the gap before first |
| 86 |
# record of the "right" page, which is "another" gap after 894000. |
| 87 |
SELECT lock_page_name(engine_lock_id),lock_mode,lock_status,lock_data |
| 88 |
FROM performance_schema.data_locks WHERE LOCK_TYPE='RECORD'; |
| 89 |
lock_page_name(engine_lock_id) lock_mode lock_status lock_data |
| 90 |
left X GRANTED supremum pseudo-record |
| 91 |
# In many cases this doesn't cause a problem: for example if some |
| 92 |
# other transaction tried to insert 894000 again, it would get |
| 93 |
# routed trough the B-tree to the "left" page, where the gap lock on |
| 94 |
# supremum would prevent it. And only values larger-equal than |
| 95 |
# the split point in the parent (895000) would get routed to the |
| 96 |
# "right" page, which doesn't have the GAP lock. |
| 97 |
# However, as we shall see, it's possible for us to "move the split |
| 98 |
# point to the left", by inserting 893007 to the "right" page, |
| 99 |
# which forces update of the split point from 895000 to 893007 - the |
| 100 |
# new minimum of "right" page. |
| 101 |
# As said before, by default, INSERT of a value smaller than 895000 |
| 102 |
# gets routed to "left" page, but if the row is so large that it |
| 103 |
# doesn't fit on the "left" page, we try to use |
| 104 |
# btr_insert_into_right_sibling() heuristic to first push it to the |
| 105 |
# "right" page, in hope to avoid the costly spliting of the "left" page |
| 106 |
# due to overflow. |
| 107 |
# So, let's see what happens if we insert a 2KB record: |
| 108 |
INSERT INTO t1 (id,val) VALUES (893007, REPEAT("x",2000)); |
| 109 |
SELECT lock_page_name(engine_lock_id),lock_mode,lock_status,lock_data |
| 110 |
FROM performance_schema.data_locks WHERE LOCK_TYPE='RECORD'; |
| 111 |
lock_page_name(engine_lock_id) lock_mode lock_status lock_data |
| 112 |
left X GRANTED supremum pseudo-record |
| 113 |
right X,GAP GRANTED 893007 |
| 114 |
right X,GAP GRANTED 895000 |
| 115 |
SET @@SESSION.innodb_lock_wait_timeout = 1; |
| 116 |
# Now we verify ACID properties. The below query is a |
| 117 |
# transaction, which should wait to insert 894000 and timeout, because |
| 118 |
# of conflicting lock on the gap before 895000. |
| 119 |
BEGIN; |
| 120 |
INSERT INTO t1 (id,val) VALUES (894000, "x"); |
| 121 |
ERROR HY000: Lock wait timeout exceeded; try restarting transaction |
| 122 |
COMMIT; |
| 123 |
# No transaction should see the 894000 record: |
| 124 |
BEGIN; |
| 125 |
SELECT * FROM t1 WHERE id = 894000 FOR UPDATE; |
| 126 |
id val |
| 127 |
COMMIT; |
| 128 |
# The default's transaction should still not see the record. |
| 129 |
SELECT * FROM t1 WHERE id = 894000; |
| 130 |
id val |
| 131 |
# Just to prove that default's transaction hasn't commited meanwhile, |
| 132 |
# let's look at its engine_transaction_id: |
| 133 |
SELECT lock_page_name(engine_lock_id),lock_mode,lock_status,lock_data |
| 134 |
FROM performance_schema.data_locks WHERE LOCK_TYPE='RECORD'; |
| 135 |
lock_page_name(engine_lock_id) lock_mode lock_status lock_data |
| 136 |
left X GRANTED supremum pseudo-record |
| 137 |
right X,GAP GRANTED 893007 |
| 138 |
right X,GAP GRANTED 895000 |
| 139 |
COMMIT; |
| 140 |
#cleanup |
| 141 |
DROP TABLE t1; |
| 142 |
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ; |
| 143 |
DROP FUNCTION lock_page_name; |
| 144 |
DROP FUNCTION lock_page_no; |