Debian Codegraph @

mysql-test/suite/innodb/r/innodb_bug33405696.result

raw
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;