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;
Warnings:
Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
set session transaction isolation level repeatable read;
set autocommit=0;
create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
insert into t1 values (1),(2),(3),(4),(5),(6),(7);
select * from t1 where a=3 lock in share mode;
a
3
set binlog_format=mixed;
Warnings:
Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
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;
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
14
2
3
4
10
11
7
delete from t1;
insert into t1 values (1),(2),(3),(4),(5),(6),(7);
create table t2 as select 1 as col;
commit;
select * from t1 where a=3 lock in share mode;
a
3
update t1 straight_join t2 set a=10 where a=5;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
commit;
commit;
flush status;
set session transaction isolation level read committed;
update t1 straight_join t2 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 straight_join t2 set a=11 where a=6;
update t1 straight_join t2 set a=12 where a=2;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
update t1 straight_join t2 set a=13 where a=1;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
commit;
update t1 straight_join t2 set a=14 where a=1;
commit;
show status like "created_tmp_tables";
Variable_name Value
Created_tmp_tables 0
select * from t1;
a
14
2
3
4
10
11
7
delete from t1;
insert into t1 values (1),(2),(3),(4),(5),(6),(7);
commit;
select * from t1 where a=3 lock in share mode;
a
3
update t1 straight_join t2 set a=10,t2.col=t2.col+1 where a+1=5+1 and t2.col>0*a;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
commit;
commit;
flush status;
set session transaction isolation level read committed;
update t1 straight_join t2 set a=10,t2.col=t2.col+1 where a+1=5+1 and t2.col>0*a;
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 straight_join t2 set a=11,t2.col=t2.col+1 where a+1=6+1 and t2.col>0*a;
update t1 straight_join t2 set a=12,t2.col=t2.col+1 where a+1=2+1 and t2.col>0*a;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
update t1 straight_join t2 set a=13,t2.col=t2.col+1 where a+1=1+1 and t2.col>0*a;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
commit;
update t1 straight_join t2 set a=14,t2.col=t2.col+1 where a+1=1+1 and t2.col>0*a;
commit;
show status like "created_tmp_tables";
Variable_name Value
Created_tmp_tables 10
select * from t1;
a
14
2
3
4
10
11
7
drop table t1,t2;
create table t1 (a int, b int) engine=myisam;
create table t2 (c int, d int, key (c)) engine=innodb;
insert into t1 values (1,1);
insert into t2 values (1,2);
set session transaction isolation level read committed;
delete from t1 using t1 join t2 on t1.a = t2.c where t2.d in (1);
drop table t1, t2;
|
