1
# include/concurrent.inc
3
# Concurrent tests for transactional storage engines, mainly in UPDATE's
5
# Designed and tested by Sinisa Milivojevic, sinisa@mysql.com
7
# These variables have to be set before sourcing this script:
8
# TRANSACTION ISOLATION LEVEL REPEATABLE READ
9
# innodb_locks_unsafe_for_binlog 0 (default) or 1 (by
10
# --innodb_locks_unsafe_for_binlog)
11
# $engine_type storage engine to be tested
14
# 2009-02-13 HH "Release_lock("hello")" is now also successful when delivering NULL,
15
# replaced two sleeps by wait_condition. The last two "sleep 1" have not been
16
# replaced as all tried wait conditions leaded to nondeterministic results, especially
17
# to succeeding concurrent updates. To replace the sleeps there should be some time
18
# planned (or internal knowledge of the server may help).
19
# 2006-08-02 ML test refactored
20
# old name was t/innodb_concurrent.test
21
# main code went into include/concurrent.inc
22
# 2008-06-03 KP test refactored; removed name locks, added comments.
23
# renamed wrapper t/concurrent_innodb.test ->
24
# t/concurrent_innodb_unsafelog.test
25
# new wrapper t/concurrent_innodb_safelog.test
30
# Show prerequisites for this test.
32
SELECT @@global.tx_isolation;
33
SELECT @@global.innodb_locks_unsafe_for_binlog;
35
# When innodb_locks_unsafe_for_binlog is not set (zero), which is the
36
# default, InnoDB takes "next-key locks"/"gap locks". This means it
37
# locks the gap before the keys that it accessed to find the rows to
38
# use for a statement. In this case we have to expect some more lock
39
# wait timeouts in the tests below as if innodb_locks_unsafe_for_binlog
40
# is set (non-zero). In the latter case no "next-key locks"/"gap locks"
41
# are taken and locks on keys that do not match the WHERE conditon are
42
# released. Hence less lock collisions occur.
43
# We use the variable $keep_locks to set the expectations for
44
# lock wait timeouts accordingly.
46
let $keep_locks= `SELECT NOT @@global.innodb_locks_unsafe_for_binlog`;
47
--echo # keep_locks == $keep_locks
50
# Set up privileges and remove user level locks, if exist.
52
GRANT USAGE ON test.* TO mysqltest@localhost;
55
# Preparatory cleanup.
58
drop table if exists t1;
64
--echo ** two UPDATE's running and both changing distinct result sets
66
--echo ** connection thread1
67
connect (thread1, localhost, mysqltest,,);
69
--echo ** Set up table
70
eval SET SESSION STORAGE_ENGINE = $engine_type;
71
create table t1(eta int(11) not null, tipo int(11), c varchar(255));
72
insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa");
73
insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb");
74
insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc");
75
insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd");
76
insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee");
77
insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff");
78
insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg");
79
insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh");
80
insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii");
81
insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj");
82
insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk");
83
--echo ** Get user level lock (ULL) for thread 1
84
select get_lock("hello",10);
86
--echo ** connection thread2
87
connect (thread2, localhost, mysqltest,,);
89
--echo ** Start transaction for thread 2
91
--echo ** Update will cause a table scan and a new ULL will
92
--echo ** be created and blocked on the first row where tipo=11.
93
send update t1 set eta=1+get_lock("hello",10)*0 where tipo=11;
95
--echo ** connection thread1
97
let $wait_condition= select count(*)= 1 from information_schema.processlist where state= 'User lock';
98
--source include/wait_condition.inc
99
--echo ** Start new transaction for thread 1
101
--echo ** Update on t1 will cause a table scan which will be blocked because
102
--echo ** the previously initiated table scan applied exclusive key locks on
103
--echo ** all primary keys.
104
--echo ** Not so if innodb_locks_unsafe_for_binlog is set. The locks that
105
--echo ** do not match the WHERE condition are released.
108
--error ER_LOCK_WAIT_TIMEOUT
109
update t1 set eta=2 where tipo=22;
113
update t1 set eta=2 where tipo=22;
115
--echo ** Release user level name lock from thread 1. This will cause the ULL
116
--echo ** on thread 2 to end its wait.
117
# Due to Bug#32782 User lock hash fails to find lock, which probably also cause Bug#39484 (main.concurrent_innodb_safelog fails sporadically) the success of the following
118
# is also guaranteed for NULL. Replaced SELECT by DO (no result).
119
DO release_lock("hello");
120
--echo ** Table is now updated with a new eta on tipo=22 for thread 1.
123
--echo ** connection thread2
125
--echo ** Release the lock and collect result from update on thread 2
127
# Due to Bug#32782 User lock hash fails to find lock, which probably also cause Bug#39484 (main.concurrent_innodb_safelog fails sporadically) the success of the following
128
# is also guaranteed for NULL. Replaced SELECT by DO (no result).
129
DO release_lock("hello");
130
--echo ** Table should have eta updates where tipo=11 but updates made by
131
--echo ** thread 1 shouldn't be visible yet.
133
--echo ** Sending commit on thread 2.
136
--echo ** connection thread1
138
--echo ** Make sure table reads didn't change yet on thread 1.
140
--echo ** And send final commit on thread 1.
142
--echo ** Table should now be updated by both updates in the order of
143
--echo ** thread 1,2.
146
--echo ** connection thread2
148
--echo ** Make sure the output is similar for t1.
151
--echo ** connection thread1
155
--echo ** connection default
162
--echo ** two UPDATE's running and one changing result set
164
--echo ** connection thread1
165
#connect (thread1, localhost, mysqltest,,);
167
--echo ** Set up table
168
eval SET SESSION STORAGE_ENGINE = $engine_type;
169
create table t1(eta int(11) not null, tipo int(11), c varchar(255));
170
insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa");
171
insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb");
172
insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc");
173
insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd");
174
insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee");
175
insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff");
176
insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg");
177
insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh");
178
insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii");
179
insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj");
180
insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk");
181
--echo ** Get ULL "hello" on thread 1
182
select get_lock("hello",10);
184
--echo ** connection thread2
185
#connect (thread2, localhost, mysqltest,,);
187
--echo ** Start transaction on thread 2
189
--echo ** Update will cause a table scan.
190
--echo ** This will cause a hang on the first row where tipo=1 until the
191
--echo ** blocking ULL is released.
192
send update t1 set eta=1+get_lock("hello",10)*0 where tipo=1;
194
--echo ** connection thread1
196
let $wait_condition= select count(*)= 1 from information_schema.processlist where state= 'User lock';
197
--source include/wait_condition.inc
198
--echo ** Start transaction on thread 1
200
--echo ** Update on t1 will cause a table scan which will be blocked because
201
--echo ** the previously initiated table scan applied exclusive key locks on
202
--echo ** all primary keys.
203
--echo ** Not so if innodb_locks_unsafe_for_binlog is set. The locks that
204
--echo ** do not match the WHERE condition are released.
207
--error ER_LOCK_WAIT_TIMEOUT
208
update t1 set tipo=1 where tipo=2;
212
update t1 set tipo=1 where tipo=2;
214
--echo ** Release ULL. This will release the next waiting ULL on thread 2.
215
# Due to Bug#32782 User lock hash fails to find lock, which probably also cause Bug#39484 (main.concurrent_innodb_safelog fails sporadically)the success of the following
216
# is also guaranteed for NULL. Replaced SELECT by DO (no result).
217
DO release_lock("hello");
218
--echo ** The table should still be updated with updates for thread 1 only:
221
--echo ** connection thread2
223
--echo ** Release the lock and collect result from thread 2:
225
# Due to Bug#32782 User lock hash fails to find lock, which probably also cause Bug#39484 (main.concurrent_innodb_safelog fails sporadically) the success of the following
226
# is also guaranteed for NULL. Replaced SELECT by DO (no result).
227
DO release_lock("hello");
228
--echo ** Seen from thread 2 the table should have been updated on four
233
--echo ** connection thread1
235
--echo ** Thread 2 has committed but the result should remain the same for
236
--echo ** thread 1 (updated on three places):
239
--echo ** After a commit the table should be merged with the previous
241
--echo ** This select should show both updates:
244
--echo ** connection thread2
248
--echo ** connection thread1
252
--echo ** connection default
259
--echo ** One UPDATE and one INSERT .... Monty's test
261
--echo ** connection thread1
262
#connect (thread1, localhost, mysqltest,,);
264
--echo ** Set up table
265
eval SET SESSION STORAGE_ENGINE = $engine_type;
266
create table t1 (a int not null, b int not null);
267
insert into t1 values (1,1),(2,1),(3,1),(4,1);
268
--echo ** Create ULL 'hello2'
269
select get_lock("hello2",10);
271
--echo ** connection thread2
272
#connect (thread2, localhost, mysqltest,,);
274
--echo ** Begin a new transaction on thread 2
276
--echo ** Update will create a table scan which creates a ULL where a=2;
277
--echo ** this will hang waiting on thread 1.
278
send update t1 set b=10+get_lock(concat("hello",a),10)*0 where a=2;
280
--echo ** connection thread1
282
let $wait_condition= select count(*)= 1 from information_schema.processlist where state= 'User lock';
283
--source include/wait_condition.inc
284
--echo ** Insert new values to t1 from thread 1; this created an implicit
285
--echo ** commit since there are no on-going transactions.
286
insert into t1 values (1,1);
287
--echo ** Release the ULL (thread 2 updates will finish).
288
# Due to Bug#32782 User lock hash fails to find lock, which probably also cause Bug#39484 (main.concurrent_innodb_safelog fails sporadically) the success of the following
289
# is also guaranteed for NULL. Replaced SELECT by DO (no result).
290
DO release_lock("hello2");
291
--echo ** ..but thread 1 will still see t1 as if nothing has happend:
294
--echo ** connection thread2
296
--echo ** Collect results from thread 2 and release the lock.
298
# Due to Bug#32782 User lock hash fails to find lock, which probably also cause Bug#39484 (main.concurrent_innodb_safelog fails sporadically) the success of the following
299
# is also guaranteed for NULL. Replaced SELECT by DO (no result).
300
DO release_lock("hello2");
301
--echo ** The table should look like the original+updates for thread 2,
302
--echo ** and consist of new rows:
304
--echo ** Commit changes from thread 2
307
--echo ** connection default
314
--echo ** one UPDATE changing result set and SELECT ... FOR UPDATE
316
--echo ** connection thread1
317
#connect (thread1, localhost, mysqltest,,);
319
--echo ** Set up table
320
eval SET SESSION STORAGE_ENGINE = $engine_type;
321
create table t1(eta int(11) not null, tipo int(11), c varchar(255));
322
insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa");
323
insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb");
324
insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc");
325
insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd");
326
insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee");
327
insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff");
328
insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg");
329
insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh");
330
insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii");
331
insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj");
332
insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk");
334
--echo ** connection thread2
335
#connect (thread2, localhost, mysqltest,,);
337
--echo ** Begin a new transaction on thread 2
339
--echo ** Select a range for update.
340
select * from t1 where tipo=2 FOR UPDATE;
342
--echo ** connection thread1
344
--echo ** Begin a new transaction on thread 1
346
--echo ** Update the same range which is marked for update on thread 2; this
347
--echo ** will hang because of row locks.
348
--error ER_LOCK_WAIT_TIMEOUT
349
update t1 set tipo=1 where tipo=2;
350
--echo ** After the update the table will be unmodified because the previous
351
--echo ** transaction failed and was rolled back.
354
--echo ** connection thread2
356
--echo ** The table should look unmodified from thread 2.
358
--echo ** Sending a commit should release the row locks and enable
359
--echo ** thread 1 to complete the transaction.
362
--echo ** connection thread1
364
--echo ** Commit on thread 1.
367
--echo ** connection thread2
369
--echo ** The table should not have been changed.
372
--echo ** connection thread1
374
--echo ** Even on thread 1:
377
--echo ** connection default
384
--echo ** one UPDATE not changing result set and SELECT ... FOR UPDATE
386
--echo ** connection thread1
387
#connect (thread1, localhost, mysqltest,,);
389
--echo ** Set up table
390
eval SET SESSION STORAGE_ENGINE = $engine_type;
391
create table t1(eta int(11) not null, tipo int(11), c varchar(255));
392
insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa");
393
insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb");
394
insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc");
395
insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd");
396
insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee");
397
insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff");
398
insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg");
399
insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh");
400
insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii");
401
insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj");
402
insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk");
404
--echo ** connection thread2
405
#connect (thread2, localhost, mysqltest,,);
407
--echo ** Starting new transaction on thread 2.
409
--echo ** Starting SELECT .. FOR UPDATE
410
select * from t1 where tipo=2 FOR UPDATE;
412
--echo ** connection thread1
415
--echo ** Starting new transaction on thread 1
417
--echo ** Updating single row using a table scan. This will time out
418
--echo ** because of ongoing transaction on thread 1 holding lock on
419
--echo ** all primary keys in the scan.
420
--echo ** Not so if innodb_locks_unsafe_for_binlog is set. The locks that
421
--echo ** do not match the WHERE condition are released.
424
--error ER_LOCK_WAIT_TIMEOUT
425
update t1 set tipo=11 where tipo=22;
429
update t1 set tipo=11 where tipo=22;
431
--echo ** After the time out the transaction is aborted; no rows should
432
--echo ** have changed.
435
--echo ** connection thread2
437
--echo ** The same thing should hold true for the transaction on
442
--echo ** connection thread1
446
--echo ** connection thread2
448
--echo ** Even after committing:
452
--echo ** connection thread1
456
--echo ** connection default
463
--echo ** two SELECT ... FOR UPDATE
465
--echo ** connection thread1
466
#connect (thread1, localhost, mysqltest,,);
468
--echo ** Set up table
469
eval SET SESSION STORAGE_ENGINE = $engine_type;
470
create table t1(eta int(11) not null, tipo int(11), c varchar(255));
471
insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa");
472
insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb");
473
insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc");
474
insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd");
475
insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee");
476
insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff");
477
insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg");
478
insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh");
479
insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii");
480
insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj");
481
insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk");
483
--echo ** connection thread2
484
#connect (thread2, localhost, mysqltest,,);
486
--echo ** Begin a new transaction on thread 2
488
select * from t1 where tipo=2 FOR UPDATE;
490
--echo ** connection thread1
492
--echo ** Begin a new transaction on thread 1
494
--echo ** Selecting a range for update by table scan will be blocked
495
--echo ** because of on-going transaction on thread 2.
496
--error ER_LOCK_WAIT_TIMEOUT
497
select * from t1 where tipo=1 FOR UPDATE;
499
--echo ** connection thread2
501
--echo ** Table will be unchanged and the select command will not be
504
--echo ** Commit transacton on thread 2.
507
--echo ** connection thread1
509
--echo ** Commit transaction on thread 1.
512
--echo ** connection thread2
514
--echo ** Make sure table isn't blocked on thread 2:
517
--echo ** connection thread1
519
--echo ** Make sure table isn't blocked on thread 1:
522
--echo ** connection default
529
--echo ** one UPDATE changing result set and DELETE
531
--echo ** connection thread1
532
#connect (thread1, localhost, mysqltest,,);
534
--echo ** Set up table
535
eval SET SESSION STORAGE_ENGINE = $engine_type;
536
create table t1(eta int(11) not null, tipo int(11), c varchar(255));
537
insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa");
538
insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb");
539
insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc");
540
insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd");
541
insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee");
542
insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff");
543
insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg");
544
insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh");
545
insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii");
546
insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj");
547
insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk");
549
--echo ** connection thread2
550
#connect (thread2, localhost, mysqltest,,);
553
send delete from t1 where tipo=2;
554
# The sleep has not been replaced as all tried wait conditions leaded to sporadically
555
# succeding update in the following thread. Also the used status variables '%lock%' and
556
# 'innodb_deleted_rows' and infos in processlist where not sucessful.
559
--echo ** connection thread1
562
--error ER_LOCK_WAIT_TIMEOUT
563
update t1 set tipo=1 where tipo=2;
566
--echo ** connection thread2
572
--echo ** connection thread1
576
--echo ** connection thread2
581
--echo ** connection thread1
585
--echo ** connection default
592
--echo ** one UPDATE not changing result set and DELETE
594
--echo ** connection thread1
595
#connect (thread1, localhost, mysqltest,,);
597
--echo ** Set up table
598
eval SET SESSION STORAGE_ENGINE = $engine_type;
599
create table t1(eta int(11) not null, tipo int(11), c varchar(255));
600
insert into t1 values (7,7, "aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa");
601
insert into t1 values (8,8, "bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb");
602
insert into t1 values (10,1,"ccccccccccccccccccccccccccccccccccccccccccc");
603
insert into t1 values (20,2,"ddddddddddddddddddddddddddddddddddddddddddd");
604
insert into t1 values (30,1,"eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee");
605
insert into t1 values (40,2,"fffffffffffffffffffffffffffffffffffffffffff");
606
insert into t1 values (50,1,"ggggggggggggggggggggggggggggggggggggggggggg");
607
insert into t1 values (60,2,"hhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh");
608
insert into t1 values (70,1,"iiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii");
609
insert into t1 values (80,22,"jjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj");
610
insert into t1 values (90,11,"kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk");
612
--echo ** connection thread2
613
#connect (thread2, localhost, mysqltest,,);
616
send delete from t1 where tipo=2;
617
# The sleep has not been replaced as all tried wait conditions leaded to sporadically
618
# succeding update in the following thread. Also the used status variables '%lock%' and
619
# 'innodb_deleted_rows' and infos in processlist where not sucessful.
622
--echo ** connection thread1
625
--echo ** Update on t1 will cause a table scan which will be blocked because
626
--echo ** the previously initiated table scan applied exclusive key locks on
627
--echo ** all primary keys.
628
--echo ** Not so if innodb_locks_unsafe_for_binlog is set. The locks that
629
--echo ** do not match the WHERE condition are released.
632
--error ER_LOCK_WAIT_TIMEOUT
633
update t1 set tipo=1 where tipo=22;
637
update t1 set tipo=1 where tipo=22;
641
--echo ** connection thread2
647
--echo ** connection thread1
651
--echo ** connection thread2
656
--echo ** connection thread1
663
--source include/wait_until_disconnected.inc
664
--echo ** connection thread2
667
--source include/wait_until_disconnected.inc
668
--echo ** connection default
671
drop user mysqltest@localhost;