Debian Codegraph @

mysql-test/suite/innodb/t/innodb_bug33405696.test

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