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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
|
drop table if exists t1;
set binlog_format=mixed;
set session transaction isolation level repeatable read;
create table t1(a int not null)
engine=innodb
DEFAULT CHARSET=latin1
PARTITION BY RANGE(a)
(PARTITION p0 VALUES LESS THAN (20),
PARTITION p1 VALUES LESS THAN MAXVALUE);
insert into t1 values (1),(2),(3),(4),(5),(6),(7);
set autocommit=0;
select * from t1 where a=3 lock in share mode;
a
3
set binlog_format=mixed;
set session transaction isolation level repeatable read;
set autocommit=0;
update t1 set a=10 where a=5;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
commit;
set session transaction isolation level read committed;
update t1 set a=10 where a=5;
select * from t1 where a=2 for update;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
select * from t1 where a=2 limit 1 for update;
a
2
update t1 set a=11 where a=6;
update t1 set a=12 where a=2;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
update t1 set a=13 where a=1;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
commit;
update t1 set a=14 where a=1;
commit;
select * from t1;
a
10
11
14
2
3
4
7
drop table t1;
SET SESSION AUTOCOMMIT = 0;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
set binlog_format=mixed;
# Switch to connection con1
CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(256))
ENGINE = InnoDB
PARTITION BY RANGE (a)
(PARTITION p0 VALUES LESS THAN (300),
PARTITION p1 VALUES LESS THAN MAXVALUE);
INSERT INTO t1 VALUES (1,2);
# 1. test for locking:
BEGIN;
UPDATE t1 SET b = 12 WHERE a = 1;
affected rows: 1
info: Rows matched: 1 Changed: 1 Warnings: 0
SELECT * FROM t1;
a b
1 12
# Switch to connection con2
UPDATE t1 SET b = 21 WHERE a = 1;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
# Switch to connection con1
SELECT * FROM t1;
a b
1 12
ROLLBACK;
# 2. test for serialized update:
CREATE TABLE t2 (a INT);
TRUNCATE t1;
INSERT INTO t1 VALUES (1,'init');
CREATE PROCEDURE p1()
BEGIN
UPDATE t1 SET b = CONCAT(b, '+con2') WHERE a = 1;
INSERT INTO t2 VALUES ();
END|
BEGIN;
UPDATE t1 SET b = CONCAT(b, '+con1') WHERE a = 1;
affected rows: 1
info: Rows matched: 1 Changed: 1 Warnings: 0
SELECT * FROM t1;
a b
1 init+con1
# Switch to connection con2
CALL p1;;
# Switch to connection con1
SELECT * FROM t1;
a b
1 init+con1
COMMIT;
SELECT * FROM t1;
a b
1 init+con1
# Switch to connection con2
SELECT * FROM t1;
a b
1 init+con1+con2
# Switch to connection con1
# 3. test for updated key column:
TRUNCATE t1;
TRUNCATE t2;
INSERT INTO t1 VALUES (1,'init');
BEGIN;
UPDATE t1 SET a = 2, b = CONCAT(b, '+con1') WHERE a = 1;
affected rows: 1
info: Rows matched: 1 Changed: 1 Warnings: 0
SELECT * FROM t1;
a b
2 init+con1
# Switch to connection con2
CALL p1;;
# Switch to connection con1
SELECT * FROM t1;
a b
2 init+con1
COMMIT;
SELECT * FROM t1;
a b
2 init+con1
# Switch to connection con2
SELECT * FROM t1;
a b
2 init+con1
DROP PROCEDURE p1;
DROP TABLE t1, t2;
|
