2
# We need the Debug Sync Facility.
4
--source include/have_debug_sync.inc
6
# We need InnoDB tables for some of the tests.
7
--source include/have_innodb.inc
9
# Save the initial number of concurrent sessions.
10
--source include/count_sessions.inc
13
# Clean up resources used in this test case.
15
SET DEBUG_SYNC= 'RESET';
19
# Test the case of when a exclusive lock request waits for a
20
# shared lock being upgraded to a exclusive lock.
23
connect (con1,localhost,root,,test,,);
24
connect (con2,localhost,root,,test,,);
25
connect (con3,localhost,root,,test,,);
30
drop table if exists t1,t2,t3;
33
create table t1 (i int);
34
create table t2 (i int);
36
--echo connection: default
40
--echo connection: con1
41
set debug_sync='mdl_upgrade_shared_lock_to_exclusive SIGNAL parked WAIT_FOR go';
42
--send alter table t1 rename t3
45
--echo connection: default
46
set debug_sync= 'now WAIT_FOR parked';
49
--echo connection: con2
50
set debug_sync='mdl_acquire_lock_wait SIGNAL go';
51
--send drop table t1,t2
54
--echo connection: con1
58
--echo connection: default
62
--echo connection: con2
63
--error ER_BAD_TABLE_ERROR
73
# Clean up resources used in this test case.
75
SET DEBUG_SYNC= 'RESET';
80
--echo # Basic test coverage for type-of-operation aware metadata locks.
83
drop table if exists t1, t2, t3;
85
connect(mdl_con1,localhost,root,,);
86
connect(mdl_con2,localhost,root,,);
87
connect(mdl_con3,localhost,root,,);
89
set debug_sync= 'RESET';
90
create table t1 (c1 int);
93
--echo # A) First let us check compatibility rules between differend kinds of
94
--echo # type-of-operation aware metadata locks.
95
--echo # Of course, these rules are already covered by the tests scattered
96
--echo # across the test suite. But it still makes sense to have one place
97
--echo # which covers all of them.
100
--echo # 1) Acquire S (simple shared) lock on the table (by using HANDLER):
104
--echo # Switching to connection 'mdl_con1'.
106
--echo # Check that S, SH, SR and SW locks are compatible with it.
109
select column_name from information_schema.columns where
110
table_schema='test' and table_name='t1';
111
select count(*) from t1;
112
insert into t1 values (1), (1);
113
--echo # Check that SNW lock is compatible with it. To do this use ALTER TABLE
114
--echo # which will fail after opening the table and thus obtaining SNW metadata
117
alter table t1 add primary key (c1);
118
--echo # Check that SNRW lock is compatible with S lock.
120
insert into t1 values (1);
122
--echo # Check that X lock is incompatible with S lock.
124
--send rename table t1 to t2;
126
--echo # Switching to connection 'mdl_con2'.
128
--echo # Check that the above RENAME is blocked because of S lock.
130
select count(*) = 1 from information_schema.processlist
131
where state = "Waiting for table metadata lock" and
132
info = "rename table t1 to t2";
133
--source include/wait_condition.inc
135
--echo # Switching to connection 'default'.
137
--echo # Unblock RENAME TABLE.
140
--echo # Switching to connection 'mdl_con1'.
142
--echo # Reaping RENAME TABLE.
144
--echo # Restore the original state of the things.
145
rename table t2 to t1;
147
--echo # Switching to connection 'default'.
151
--echo # Switching to connection 'mdl_con1'.
153
--echo # Check that upgrade from SNW to X is blocked by presence of S lock.
155
--send alter table t1 add column c2 int;
157
--echo # Switching to connection 'mdl_con2'.
159
--echo # Check that the above ALTER TABLE is blocked because of S lock.
161
select count(*) = 1 from information_schema.processlist
162
where state = "Waiting for table metadata lock" and
163
info = "alter table t1 add column c2 int";
164
--source include/wait_condition.inc
166
--echo # Switching to connection 'default'.
168
--echo # Unblock ALTER TABLE.
171
--echo # Switching to connection 'mdl_con1'.
173
--echo # Reaping ALTER TABLE.
175
--echo # Restore the original state of the things.
176
alter table t1 drop column c2;
178
--echo # Switching to connection 'default'.
182
--echo # Switching to connection 'mdl_con1'.
184
--echo # Check that upgrade from SNRW to X is blocked by presence of S lock.
187
--send alter table t1 add column c2 int;
189
--echo # Switching to connection 'mdl_con2'.
191
--echo # Check that the above upgrade of SNRW to X in ALTER TABLE is blocked
192
--echo # because of S lock.
194
select count(*) = 1 from information_schema.processlist
195
where state = "Waiting for table metadata lock" and
196
info = "alter table t1 add column c2 int";
197
--source include/wait_condition.inc
199
--echo # Switching to connection 'default'.
201
--echo # Unblock ALTER TABLE.
204
--echo # Switching to connection 'mdl_con1'.
206
--echo # Reaping ALTER TABLE.
208
--echo # Restore the original state of the things.
209
alter table t1 drop column c2;
212
--echo # Switching to connection 'default'.
215
--echo # 2) Acquire SH (shared high-priority) lock on the table.
216
--echo # We have to involve DEBUG_SYNC facility for this as usually
217
--echo # such kind of locks are short-lived.
219
set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
221
--send select table_name, table_type, auto_increment, table_comment from information_schema.tables where table_schema='test' and table_name='t1';
223
--echo # Switching to connection 'mdl_con1'.
225
set debug_sync= 'now WAIT_FOR locked';
226
--echo # Check that S, SH, SR and SW locks are compatible with it.
229
select column_name from information_schema.columns where
230
table_schema='test' and table_name='t1';
231
select count(*) from t1;
232
insert into t1 values (1);
233
--echo # Check that SNW lock is compatible with it. To do this use ALTER TABLE
234
--echo # which will fail after opening the table and thus obtaining SNW metadata
237
alter table t1 add primary key (c1);
238
--echo # Check that SNRW lock is compatible with SH lock.
240
delete from t1 limit 1;
242
--echo # Check that X lock is incompatible with SH lock.
244
--send rename table t1 to t2;
246
--echo # Switching to connection 'mdl_con2'.
248
--echo # Check that the above RENAME is blocked because of SH lock.
250
select count(*) = 1 from information_schema.processlist
251
where state = "Waiting for table metadata lock" and
252
info = "rename table t1 to t2";
253
--source include/wait_condition.inc
254
--echo # Unblock RENAME TABLE.
255
set debug_sync= 'now SIGNAL finish';
257
--echo # Switching to connection 'default'.
259
--echo # Reaping SELECT ... FROM I_S.
262
--echo # Switching to connection 'mdl_con1'.
264
--echo # Reaping RENAME TABLE.
266
--echo # Restore the original state of the things.
267
rename table t2 to t1;
269
--echo # Switching to connection 'default'.
271
set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
273
--send select table_name, table_type, auto_increment, table_comment from information_schema.tables where table_schema='test' and table_name='t1';
275
--echo # Switching to connection 'mdl_con1'.
277
set debug_sync= 'now WAIT_FOR locked';
278
--echo # Check that upgrade from SNW to X is blocked by presence of SH lock.
280
--send alter table t1 add column c2 int;
282
--echo # Switching to connection 'mdl_con2'.
284
--echo # Check that the above ALTER TABLE is blocked because of SH lock.
286
select count(*) = 1 from information_schema.processlist
287
where state = "Waiting for table metadata lock" and
288
info = "alter table t1 add column c2 int";
289
--source include/wait_condition.inc
290
--echo # Unblock RENAME TABLE.
291
set debug_sync= 'now SIGNAL finish';
293
--echo # Switching to connection 'default'.
295
--echo # Reaping SELECT ... FROM I_S.
298
--echo # Switching to connection 'mdl_con1'.
300
--echo # Reaping ALTER TABLE.
302
--echo # Restore the original state of the things.
303
alter table t1 drop column c2;
305
--echo # Switching to connection 'default'.
307
set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
308
--send select table_name, table_type, auto_increment, table_comment from information_schema.tables where table_schema='test' and table_name='t1';
310
--echo # Switching to connection 'mdl_con1'.
312
set debug_sync= 'now WAIT_FOR locked';
313
--echo # Check that upgrade from SNRW to X is blocked by presence of S lock.
316
--send alter table t1 add column c2 int;
318
--echo # Switching to connection 'mdl_con2'.
320
--echo # Check that the above upgrade of SNRW to X in ALTER TABLE is blocked
321
--echo # because of S lock.
323
select count(*) = 1 from information_schema.processlist
324
where state = "Waiting for table metadata lock" and
325
info = "alter table t1 add column c2 int";
326
--source include/wait_condition.inc
327
--echo # Unblock RENAME TABLE.
328
set debug_sync= 'now SIGNAL finish';
330
--echo # Switching to connection 'default'.
332
--echo # Reaping SELECT ... FROM I_S.
335
--echo # Switching to connection 'mdl_con1'.
337
--echo # Reaping ALTER TABLE.
339
--echo # Restore the original state of the things.
340
alter table t1 drop column c2;
343
--echo # Switching to connection 'default'.
347
--echo # 3) Acquire SR lock on the table.
351
select count(*) from t1;
353
--echo # Switching to connection 'mdl_con1'.
355
--echo # Check that S, SH, SR and SW locks are compatible with it.
358
select column_name from information_schema.columns where
359
table_schema='test' and table_name='t1';
360
select count(*) from t1;
361
insert into t1 values (1);
362
--echo # Check that SNW lock is compatible with it. To do this use ALTER TABLE
363
--echo # which will fail after opening the table and thus obtaining SNW metadata
366
alter table t1 add primary key (c1);
367
--echo # Check that SNRW lock is not compatible with SR lock.
369
--send lock table t1 write;
371
--echo # Switching to connection 'default'.
373
--echo # Check that the above LOCK TABLES is blocked because of SR lock.
375
select count(*) = 1 from information_schema.processlist
376
where state = "Waiting for table metadata lock" and
377
info = "lock table t1 write";
378
--source include/wait_condition.inc
379
--echo # Unblock LOCK TABLES.
382
--echo # Switching to connection 'mdl_con1'.
384
--echo # Reaping LOCK TABLES.
386
delete from t1 limit 1;
389
--echo # Switching to connection 'default'.
392
select count(*) from t1;
394
--echo # Switching to connection 'mdl_con1'.
396
--echo # Check that X lock is incompatible with SR lock.
398
--send rename table t1 to t2;
400
--echo # Switching to connection 'mdl_con2'.
402
--echo # Check that the above RENAME is blocked because of SR lock.
404
select count(*) = 1 from information_schema.processlist
405
where state = "Waiting for table metadata lock" and
406
info = "rename table t1 to t2";
407
--source include/wait_condition.inc
409
--echo # Switching to connection 'default'.
411
--echo # Unblock RENAME TABLE.
414
--echo # Switching to connection 'mdl_con1'.
416
--echo # Reaping RENAME TABLE.
418
--echo # Restore the original state of the things.
419
rename table t2 to t1;
421
--echo # Switching to connection 'default'.
424
select count(*) from t1;
426
--echo # Switching to connection 'mdl_con1'.
428
--echo # Check that upgrade from SNW to X is blocked by presence of SR lock.
430
--send alter table t1 add column c2 int;
432
--echo # Switching to connection 'mdl_con2'.
434
--echo # Check that the above ALTER TABLE is blocked because of SR lock.
436
select count(*) = 1 from information_schema.processlist
437
where state = "Waiting for table metadata lock" and
438
info = "alter table t1 add column c2 int";
439
--source include/wait_condition.inc
441
--echo # Switching to connection 'default'.
443
--echo # Unblock ALTER TABLE.
446
--echo # Switching to connection 'mdl_con1'.
448
--echo # Reaping ALTER TABLE.
450
--echo # Restore the original state of the things.
451
alter table t1 drop column c2;
453
--echo # There is no need to check that upgrade from SNRW to X is blocked
454
--echo # by presence of SR lock because SNRW is incompatible with SR anyway.
457
--echo # Switching to connection 'default'.
461
--echo # 4) Acquire SW lock on the table.
465
insert into t1 values (1);
467
--echo # Switching to connection 'mdl_con1'.
469
--echo # Check that S, SH, SR and SW locks are compatible with it.
472
select column_name from information_schema.columns where
473
table_schema='test' and table_name='t1';
474
--echo # Disable result log to make test robust against
475
--echo # effects of concurrent insert.
479
insert into t1 values (1);
480
--echo # Check that SNW lock is not compatible with SW lock.
481
--echo # Again we use ALTER TABLE which fails after opening
482
--echo # the table to avoid upgrade of SNW -> X.
484
--send alter table t1 add primary key (c1);
486
--echo # Switching to connection 'default'.
488
--echo # Check that the above ALTER TABLE is blocked because of SW lock.
490
select count(*) = 1 from information_schema.processlist
491
where state = "Waiting for table metadata lock" and
492
info = "alter table t1 add primary key (c1)";
493
--source include/wait_condition.inc
494
--echo # Unblock ALTER TABLE.
497
--echo # Switching to connection 'mdl_con1'.
499
--echo # Reaping ALTER TABLE.
503
--echo # Switching to connection 'default'.
506
insert into t1 values (1);
508
--echo # Switching to connection 'mdl_con1'.
510
--echo # Check that SNRW lock is not compatible with SW lock.
512
--send lock table t1 write;
514
--echo # Switching to connection 'default'.
516
--echo # Check that the above LOCK TABLES is blocked because of SW lock.
518
select count(*) = 1 from information_schema.processlist
519
where state = "Waiting for table metadata lock" and
520
info = "lock table t1 write";
521
--source include/wait_condition.inc
522
--echo # Unblock LOCK TABLES.
525
--echo # Switching to connection 'mdl_con1'.
527
--echo # Reaping LOCK TABLES.
529
delete from t1 limit 2;
532
--echo # Switching to connection 'default'.
535
insert into t1 values (1);
537
--echo # Switching to connection 'mdl_con1'.
539
--echo # Check that X lock is incompatible with SW lock.
541
--send rename table t1 to t2;
543
--echo # Switching to connection 'mdl_con2'.
545
--echo # Check that the above RENAME is blocked because of SW lock.
547
select count(*) = 1 from information_schema.processlist
548
where state = "Waiting for table metadata lock" and
549
info = "rename table t1 to t2";
550
--source include/wait_condition.inc
552
--echo # Switching to connection 'default'.
554
--echo # Unblock RENAME TABLE.
557
--echo # Switching to connection 'mdl_con1'.
559
--echo # Reaping RENAME TABLE.
561
--echo # Restore the original state of the things.
562
rename table t2 to t1;
564
--echo # There is no need to check that upgrade from SNW/SNRW to X is
565
--echo # blocked by presence of SW lock because SNW/SNRW is incompatible
566
--echo # with SW anyway.
569
--echo # Switching to connection 'default'.
573
--echo # 5) Acquire SNW lock on the table. We have to use DEBUG_SYNC for
574
--echo # this, to prevent SNW from being immediately upgraded to X.
576
set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
578
--send alter table t1 add primary key (c1);
580
--echo # Switching to connection 'mdl_con1'.
582
set debug_sync= 'now WAIT_FOR locked';
583
--echo # Check that S, SH and SR locks are compatible with it.
586
select column_name from information_schema.columns where
587
table_schema='test' and table_name='t1';
588
select count(*) from t1;
589
--echo # Check that SW lock is incompatible with SNW lock.
591
--send delete from t1 limit 2;
593
--echo # Switching to connection 'mdl_con2'.
595
--echo # Check that the above DELETE is blocked because of SNW lock.
597
select count(*) = 1 from information_schema.processlist
598
where state = "Waiting for table metadata lock" and
599
info = "delete from t1 limit 2";
600
--source include/wait_condition.inc
601
--echo # Unblock ALTER and thus DELETE.
602
set debug_sync= 'now SIGNAL finish';
604
--echo # Switching to connection 'default'.
606
--echo # Reaping ALTER TABLE.
610
--echo # Switching to connection 'mdl_con1'.
612
--echo # Reaping DELETE.
615
--echo # Switching to connection 'default'.
617
set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
619
--send alter table t1 add primary key (c1);
621
--echo # Switching to connection 'mdl_con1'.
623
set debug_sync= 'now WAIT_FOR locked';
624
--echo # Check that SNW lock is incompatible with SNW lock.
626
--send alter table t1 add primary key (c1);
628
--echo # Switching to connection 'mdl_con2'.
630
--echo # Check that the above ALTER is blocked because of SNW lock.
632
select count(*) = 1 from information_schema.processlist
633
where state = "Waiting for table metadata lock" and
634
info = "alter table t1 add primary key (c1)";
635
--source include/wait_condition.inc
636
--echo # Unblock ALTERs.
637
set debug_sync= 'now SIGNAL finish';
639
--echo # Switching to connection 'default'.
641
--echo # Reaping first ALTER TABLE.
645
--echo # Switching to connection 'mdl_con1'.
647
--echo # Reaping another ALTER TABLE.
651
--echo # Switching to connection 'default'.
653
set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
655
--send alter table t1 add primary key (c1);
657
--echo # Switching to connection 'mdl_con1'.
659
set debug_sync= 'now WAIT_FOR locked';
660
--echo # Check that SNRW lock is incompatible with SNW lock.
662
--send lock table t1 write;
664
--echo # Switching to connection 'mdl_con2'.
666
--echo # Check that the above LOCK TABLES is blocked because of SNW lock.
668
select count(*) = 1 from information_schema.processlist
669
where state = "Waiting for table metadata lock" and
670
info = "lock table t1 write";
671
--source include/wait_condition.inc
672
--echo # Unblock ALTER and thus LOCK TABLES.
673
set debug_sync= 'now SIGNAL finish';
675
--echo # Switching to connection 'default'.
677
--echo # Reaping ALTER TABLE.
681
--echo # Switching to connection 'mdl_con1'.
683
--echo # Reaping LOCK TABLES
685
insert into t1 values (1);
688
--echo # Switching to connection 'default'.
690
set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
692
--send alter table t1 add primary key (c1);
694
--echo # Switching to connection 'mdl_con1'.
696
set debug_sync= 'now WAIT_FOR locked';
697
--echo # Check that X lock is incompatible with SNW lock.
699
--send rename table t1 to t2;
701
--echo # Switching to connection 'mdl_con2'.
703
--echo # Check that the above RENAME is blocked because of SNW lock.
705
select count(*) = 1 from information_schema.processlist
706
where state = "Waiting for table metadata lock" and
707
info = "rename table t1 to t2";
708
--source include/wait_condition.inc
709
--echo # Unblock ALTER and thus RENAME TABLE.
710
set debug_sync= 'now SIGNAL finish';
712
--echo # Switching to connection 'default'.
714
--echo # Reaping ALTER TABLE.
718
--echo # Switching to connection 'mdl_con1'.
720
--echo # Reaping RENAME TABLE
722
--echo # Revert back to original state of things.
723
rename table t2 to t1;
725
--echo # There is no need to check that upgrade from SNW/SNRW to X is
726
--echo # blocked by presence of another SNW lock because SNW/SNRW is
727
--echo # incompatible with SNW anyway.
729
--echo # Switching to connection 'default'.
733
--echo # 6) Acquire SNRW lock on the table.
738
--echo # Switching to connection 'mdl_con1'.
740
--echo # Check that S and SH locks are compatible with it.
743
select column_name from information_schema.columns where
744
table_schema='test' and table_name='t1';
745
--echo # Check that SR lock is incompatible with SNRW lock.
747
--send select count(*) from t1;
749
--echo # Switching to connection 'default'.
751
--echo # Check that the above SELECT is blocked because of SNRW lock.
753
select count(*) = 1 from information_schema.processlist
754
where state = "Waiting for table metadata lock" and
755
info = "select count(*) from t1";
756
--source include/wait_condition.inc
757
--echo # Unblock SELECT.
760
--echo # Switching to connection 'mdl_con1'.
762
--echo # Reaping SELECT.
765
--echo # Switching to connection 'default'.
769
--echo # Switching to connection 'mdl_con1'.
771
--echo # Check that SW lock is incompatible with SNRW lock.
773
--send delete from t1 limit 1;
775
--echo # Switching to connection 'default'.
777
--echo # Check that the above DELETE is blocked because of SNRW lock.
779
select count(*) = 1 from information_schema.processlist
780
where state = "Waiting for table metadata lock" and
781
info = "delete from t1 limit 1";
782
--source include/wait_condition.inc
783
--echo # Unblock DELETE.
786
--echo # Switching to connection 'mdl_con1'.
788
--echo # Reaping DELETE.
791
--echo # Switching to connection 'default'.
795
--echo # Switching to connection 'mdl_con1'.
797
--echo # Check that SNW lock is incompatible with SNRW lock.
799
--send alter table t1 add primary key (c1);
801
--echo # Switching to connection 'default'.
803
--echo # Check that the above ALTER is blocked because of UNWR lock.
805
select count(*) = 1 from information_schema.processlist
806
where state = "Waiting for table metadata lock" and
807
info = "alter table t1 add primary key (c1)";
808
--source include/wait_condition.inc
809
--echo # Unblock ALTER.
812
--echo # Switching to connection 'mdl_con1'.
814
--echo # Reaping ALTER TABLE.
818
--echo # Switching to connection 'default'.
822
--echo # Switching to connection 'mdl_con1'.
824
--echo # Check that SNRW lock is incompatible with SNRW lock.
826
--send lock table t1 write;
828
--echo # Switching to connection 'default'.
830
--echo # Check that the above LOCK TABLES is blocked because of SNRW lock.
832
select count(*) = 1 from information_schema.processlist
833
where state = "Waiting for table metadata lock" and
834
info = "lock table t1 write";
835
--source include/wait_condition.inc
836
--echo # Unblock waiting LOCK TABLES.
839
--echo # Switching to connection 'mdl_con1'.
841
--echo # Reaping LOCK TABLES
843
insert into t1 values (1);
846
--echo # Switching to connection 'default'.
850
--echo # Switching to connection 'mdl_con1'.
852
--echo # Check that X lock is incompatible with SNRW lock.
854
--send rename table t1 to t2;
856
--echo # Switching to connection 'default'.
858
--echo # Check that the above RENAME is blocked because of SNRW lock.
860
select count(*) = 1 from information_schema.processlist
861
where state = "Waiting for table metadata lock" and
862
info = "rename table t1 to t2";
863
--source include/wait_condition.inc
864
--echo # Unblock RENAME TABLE
867
--echo # Switching to connection 'mdl_con1'.
869
--echo # Reaping RENAME TABLE
871
--echo # Revert back to original state of things.
872
rename table t2 to t1;
874
--echo # There is no need to check that upgrade from SNW/SNRW to X is
875
--echo # blocked by presence of another SNRW lock because SNW/SNRW is
876
--echo # incompatible with SNRW anyway.
878
--echo # Switching to connection 'default'.
882
--echo # 7) Now do the same round of tests for X lock. We use additional
883
--echo # table to get long-lived lock of this type.
885
create table t2 (c1 int);
887
--echo # Switching to connection 'mdl_con2'.
889
--echo # Take a lock on t2, so RENAME TABLE t1 TO t2 will get blocked
890
--echo # after acquiring X lock on t1.
893
--echo # Switching to connection 'default'.
896
--send rename table t1 to t2;
898
--echo # Switching to connection 'mdl_con1'.
900
--echo # Check that RENAME has acquired X lock on t1 and is waiting for t2.
902
select count(*) = 1 from information_schema.processlist
903
where state = "Waiting for table metadata lock" and
904
info = "rename table t1 to t2";
905
--source include/wait_condition.inc
906
--echo # Check that S lock in incompatible with X lock.
908
--send handler t1 open;
910
--echo # Switching to connection 'mdl_con2'.
912
--echo # Check that the above HANDLER statement is blocked because of X lock.
914
select count(*) = 1 from information_schema.processlist
915
where state = "Waiting for table metadata lock" and
916
info = "handler t1 open";
917
--source include/wait_condition.inc
918
--echo # Unblock RENAME TABLE
921
--echo # Switching to connection 'default'.
923
--echo # Reaping RENAME TABLE.
924
--error ER_TABLE_EXISTS_ERROR
927
--echo # Switching to connection 'mdl_con1'.
929
--echo # Reaping HANDLER.
933
--echo # Switching to connection 'mdl_con2'.
935
--echo # Prepare for blocking RENAME TABLE.
938
--echo # Switching to connection 'default'.
941
--send rename table t1 to t2;
943
--echo # Switching to connection 'mdl_con1'.
945
--echo # Check that RENAME has acquired X lock on t1 and is waiting for t2.
947
select count(*) = 1 from information_schema.processlist
948
where state = "Waiting for table metadata lock" and
949
info = "rename table t1 to t2";
950
--source include/wait_condition.inc
951
--echo # Check that SH lock in incompatible with X lock.
953
--send select column_name from information_schema.columns where table_schema='test' and table_name='t1';
955
--echo # Switching to connection 'mdl_con2'.
957
--echo # Check that the above SELECT ... FROM I_S ... statement is blocked
958
--echo # because of X lock.
960
select count(*) = 1 from information_schema.processlist
961
where state = "Waiting for table metadata lock" and
962
info like "select column_name from information_schema.columns%";
963
--source include/wait_condition.inc
964
--echo # Unblock RENAME TABLE
967
--echo # Switching to connection 'default'.
969
--echo # Reaping RENAME TABLE.
970
--error ER_TABLE_EXISTS_ERROR
973
--echo # Switching to connection 'mdl_con1'.
975
--echo # Reaping SELECT ... FROM I_S.
978
--echo # Switching to connection 'mdl_con2'.
980
--echo # Prepare for blocking RENAME TABLE.
983
--echo # Switching to connection 'default'.
986
--send rename table t1 to t2;
988
--echo # Switching to connection 'mdl_con1'.
990
--echo # Check that RENAME has acquired X lock on t1 and is waiting for t2.
992
select count(*) = 1 from information_schema.processlist
993
where state = "Waiting for table metadata lock" and
994
info = "rename table t1 to t2";
995
--source include/wait_condition.inc
996
--echo # Check that SR lock in incompatible with X lock.
998
--send select count(*) from t1;
1000
--echo # Switching to connection 'mdl_con2'.
1001
connection mdl_con2;
1002
--echo # Check that the above SELECT statement is blocked
1003
--echo # because of X lock.
1004
let $wait_condition=
1005
select count(*) = 1 from information_schema.processlist
1006
where state = "Waiting for table metadata lock" and
1007
info = "select count(*) from t1";
1008
--source include/wait_condition.inc
1009
--echo # Unblock RENAME TABLE
1012
--echo # Switching to connection 'default'.
1014
--echo # Reaping RENAME TABLE.
1015
--error ER_TABLE_EXISTS_ERROR
1018
--echo # Switching to connection 'mdl_con1'.
1019
connection mdl_con1;
1020
--echo # Reaping SELECT.
1023
--echo # Switching to connection 'mdl_con2'.
1024
connection mdl_con2;
1025
--echo # Prepare for blocking RENAME TABLE.
1026
lock tables t2 read;
1028
--echo # Switching to connection 'default'.
1031
--send rename table t1 to t2;
1033
--echo # Switching to connection 'mdl_con1'.
1034
connection mdl_con1;
1035
--echo # Check that RENAME has acquired X lock on t1 and is waiting for t2.
1036
let $wait_condition=
1037
select count(*) = 1 from information_schema.processlist
1038
where state = "Waiting for table metadata lock" and
1039
info = "rename table t1 to t2";
1040
--source include/wait_condition.inc
1041
--echo # Check that SW lock in incompatible with X lock.
1043
--send delete from t1 limit 1;
1045
--echo # Switching to connection 'mdl_con2'.
1046
connection mdl_con2;
1047
--echo # Check that the above DELETE statement is blocked
1048
--echo # because of X lock.
1049
let $wait_condition=
1050
select count(*) = 1 from information_schema.processlist
1051
where state = "Waiting for table metadata lock" and
1052
info = "delete from t1 limit 1";
1053
--source include/wait_condition.inc
1054
--echo # Unblock RENAME TABLE
1057
--echo # Switching to connection 'default'.
1059
--echo # Reaping RENAME TABLE.
1060
--error ER_TABLE_EXISTS_ERROR
1063
--echo # Switching to connection 'mdl_con1'.
1064
connection mdl_con1;
1065
--echo # Reaping DELETE.
1068
--echo # Switching to connection 'mdl_con2'.
1069
connection mdl_con2;
1070
--echo # Prepare for blocking RENAME TABLE.
1071
lock tables t2 read;
1073
--echo # Switching to connection 'default'.
1076
--send rename table t1 to t2;
1078
--echo # Switching to connection 'mdl_con1'.
1079
connection mdl_con1;
1080
--echo # Check that RENAME has acquired X lock on t1 and is waiting for t2.
1081
let $wait_condition=
1082
select count(*) = 1 from information_schema.processlist
1083
where state = "Waiting for table metadata lock" and
1084
info = "rename table t1 to t2";
1085
--source include/wait_condition.inc
1086
--echo # Check that SNW lock is incompatible with X lock.
1088
--send alter table t1 add primary key (c1);
1090
--echo # Switching to connection 'mdl_con2'.
1091
connection mdl_con2;
1092
--echo # Check that the above ALTER statement is blocked
1093
--echo # because of X lock.
1094
let $wait_condition=
1095
select count(*) = 1 from information_schema.processlist
1096
where state = "Waiting for table metadata lock" and
1097
info = "alter table t1 add primary key (c1)";
1098
--source include/wait_condition.inc
1099
--echo # Unblock RENAME TABLE
1102
--echo # Switching to connection 'default'.
1104
--echo # Reaping RENAME TABLE
1105
--error ER_TABLE_EXISTS_ERROR
1108
--echo # Switching to connection 'mdl_con1'.
1109
connection mdl_con1;
1110
--echo # Reaping ALTER.
1111
--error ER_DUP_ENTRY
1114
--echo # Switching to connection 'mdl_con2'.
1115
connection mdl_con2;
1116
--echo # Prepare for blocking RENAME TABLE.
1117
lock tables t2 read;
1119
--echo # Switching to connection 'default'.
1122
--send rename table t1 to t2;
1124
--echo # Switching to connection 'mdl_con1'.
1125
connection mdl_con1;
1126
--echo # Check that RENAME has acquired X lock on t1 and is waiting for t2.
1127
let $wait_condition=
1128
select count(*) = 1 from information_schema.processlist
1129
where state = "Waiting for table metadata lock" and
1130
info = "rename table t1 to t2";
1131
--source include/wait_condition.inc
1132
--echo # Check that SNRW lock is incompatible with X lock.
1134
--send lock table t1 write;
1136
--echo # Switching to connection 'mdl_con2'.
1137
connection mdl_con2;
1138
--echo # Check that the above LOCK TABLE statement is blocked
1139
--echo # because of X lock.
1140
let $wait_condition=
1141
select count(*) = 1 from information_schema.processlist
1142
where state = "Waiting for table metadata lock" and
1143
info = "lock table t1 write";
1144
--source include/wait_condition.inc
1145
--echo # Unblock RENAME TABLE
1148
--echo # Switching to connection 'default'.
1150
--echo # Reaping RENAME TABLE
1151
--error ER_TABLE_EXISTS_ERROR
1154
--echo # Switching to connection 'mdl_con1'.
1155
connection mdl_con1;
1156
--echo # Reaping LOCK TABLE.
1160
--echo # Switching to connection 'mdl_con2'.
1161
connection mdl_con2;
1162
--echo # Prepare for blocking RENAME TABLE.
1163
lock tables t2 read;
1165
--echo # Switching to connection 'default'.
1168
--send rename table t1 to t2;
1170
--echo # Switching to connection 'mdl_con1'.
1171
connection mdl_con1;
1172
--echo # Check that RENAME has acquired X lock on t1 and is waiting for t2.
1173
let $wait_condition=
1174
select count(*) = 1 from information_schema.processlist
1175
where state = "Waiting for table metadata lock" and
1176
info = "rename table t1 to t2";
1177
--source include/wait_condition.inc
1178
--echo # Check that X lock is incompatible with X lock.
1180
--send rename table t1 to t3;
1182
--echo # Switching to connection 'mdl_con2'.
1183
connection mdl_con2;
1184
--echo # Check that the above RENAME statement is blocked
1185
--echo # because of X lock.
1186
let $wait_condition=
1187
select count(*) = 1 from information_schema.processlist
1188
where state = "Waiting for table metadata lock" and
1189
info = "rename table t1 to t3";
1190
--source include/wait_condition.inc
1191
--echo # Unblock RENAME TABLE
1194
--echo # Switching to connection 'default'.
1196
--echo # Reaping RENAME TABLE
1197
--error ER_TABLE_EXISTS_ERROR
1200
--echo # Switching to connection 'mdl_con1'.
1201
connection mdl_con1;
1202
--echo # Reaping RENAME.
1204
rename table t3 to t1;
1207
--echo # B) Now let us test compatibility in cases when both locks
1208
--echo # are pending. I.e. let us test rules for priorities between
1209
--echo # different types of metadata locks.
1213
--echo # Switching to connection 'mdl_con2'.
1214
connection mdl_con2;
1216
--echo # 1) Check compatibility for pending SNW lock.
1218
--echo # Acquire SW lock in order to create pending SNW lock later.
1220
insert into t1 values (1);
1222
--echo # Switching to connection 'default'.
1224
--echo # Add pending SNW lock.
1226
--send alter table t1 add primary key (c1);
1228
--echo # Switching to connection 'mdl_con1'.
1229
connection mdl_con1;
1230
--echo # Check that ALTER TABLE is waiting with pending SNW lock.
1231
let $wait_condition=
1232
select count(*) = 1 from information_schema.processlist
1233
where state = "Waiting for table metadata lock" and
1234
info = "alter table t1 add primary key (c1)";
1235
--source include/wait_condition.inc
1236
--echo # Check that S, SH and SR locks are compatible with pending SNW
1239
select column_name from information_schema.columns where
1240
table_schema='test' and table_name='t1';
1241
select count(*) from t1;
1242
--echo # Check that SW is incompatible with pending SNW
1244
--send delete from t1 limit 1;
1246
--echo # Switching to connection 'mdl_con2'.
1247
connection mdl_con2;
1248
--echo # Check that the above DELETE is blocked because of pending SNW lock.
1249
let $wait_condition=
1250
select count(*) = 1 from information_schema.processlist
1251
where state = "Waiting for table metadata lock" and
1252
info = "delete from t1 limit 1";
1253
--source include/wait_condition.inc
1254
--echo # Unblock ALTER TABLE.
1257
--echo # Switching to connection 'default'.
1259
--echo # Reaping ALTER.
1260
--error ER_DUP_ENTRY
1263
--echo # Switching to connection 'mdl_con1'.
1264
connection mdl_con1;
1265
--echo # Reaping DELETE.
1268
--echo # We can't do similar check for SNW, SNRW and X locks because
1269
--echo # they will also be blocked by active SW lock.
1272
--echo # Switching to connection 'mdl_con2'.
1273
connection mdl_con2;
1275
--echo # 2) Check compatibility for pending SNRW lock.
1277
--echo # Acquire SR lock in order to create pending SNRW lock.
1279
select count(*) from t1;
1281
--echo # Switching to connection 'default'.
1283
--echo # Add pending SNRW lock.
1285
--send lock table t1 write;
1287
--echo # Switching to connection 'mdl_con1'.
1288
connection mdl_con1;
1289
--echo # Check that LOCK TABLE is waiting with pending SNRW lock.
1290
let $wait_condition=
1291
select count(*) = 1 from information_schema.processlist
1292
where state = "Waiting for table metadata lock" and
1293
info = "lock table t1 write";
1294
--source include/wait_condition.inc
1295
--echo # Check that S and SH locks are compatible with pending SNRW
1298
select column_name from information_schema.columns where
1299
table_schema='test' and table_name='t1';
1300
--echo # Check that SR is incompatible with pending SNRW
1302
--send select count(*) from t1;
1304
--echo # Switching to connection 'mdl_con2'.
1305
connection mdl_con2;
1306
--echo # Check that the above SELECT is blocked because of pending SNRW lock.
1307
let $wait_condition=
1308
select count(*) = 1 from information_schema.processlist
1309
where state = "Waiting for table metadata lock" and
1310
info = "select count(*) from t1";
1311
--source include/wait_condition.inc
1312
--echo # Unblock LOCK TABLE.
1315
--echo # Switching to connection 'default'.
1317
--echo # Reaping LOCK TABLE.
1321
--echo # Switching to connection 'mdl_con1'.
1322
connection mdl_con1;
1323
--echo # Reaping SELECT.
1325
--echo # Restore pending SNRW lock.
1327
--echo # Switching to connection 'mdl_con2'.
1328
connection mdl_con2;
1330
select count(*) from t1;
1332
--echo # Switching to connection 'default'.
1335
--send lock table t1 write;
1337
--echo # Switching to connection 'mdl_con1'.
1338
connection mdl_con1;
1339
--echo # Check that LOCK TABLE is waiting with pending SNRW lock.
1340
let $wait_condition=
1341
select count(*) = 1 from information_schema.processlist
1342
where state = "Waiting for table metadata lock" and
1343
info = "lock table t1 write";
1344
--source include/wait_condition.inc
1345
--echo # Check that SW is incompatible with pending SNRW
1347
--send insert into t1 values (1);
1349
--echo # Switching to connection 'mdl_con2'.
1350
connection mdl_con2;
1351
--echo # Check that the above INSERT is blocked because of pending SNRW lock.
1352
let $wait_condition=
1353
select count(*) = 1 from information_schema.processlist
1354
where state = "Waiting for table metadata lock" and
1355
info = "insert into t1 values (1)";
1356
--source include/wait_condition.inc
1357
--echo # Unblock LOCK TABLE.
1360
--echo # Switching to connection 'default'.
1362
--echo # Reaping LOCK TABLE.
1366
--echo # Switching to connection 'mdl_con1'.
1367
connection mdl_con1;
1368
--echo # Reaping INSERT.
1370
--echo # Restore pending SNRW lock.
1372
--echo # Switching to connection 'mdl_con2'.
1373
connection mdl_con2;
1375
select count(*) from t1;
1377
--echo # Switching to connection 'default'.
1380
--send lock table t1 write;
1382
--echo # Switching to connection 'mdl_con1'.
1383
connection mdl_con1;
1384
--echo # Check that LOCK TABLE is waiting with pending SNRW lock.
1385
let $wait_condition=
1386
select count(*) = 1 from information_schema.processlist
1387
where state = "Waiting for table metadata lock" and
1388
info = "lock table t1 write";
1389
--source include/wait_condition.inc
1390
--echo # Check that SNW is compatible with pending SNRW
1391
--echo # So ALTER TABLE statements are not starved by LOCK TABLEs.
1392
--error ER_DUP_ENTRY
1393
alter table t1 add primary key (c1);
1395
--echo # Switching to connection 'mdl_con2'.
1396
connection mdl_con2;
1397
--echo # Unblock LOCK TABLE.
1400
--echo # Switching to connection 'default'.
1402
--echo # Reaping LOCK TABLE.
1406
--echo # We can't do similar check for SNRW and X locks because
1407
--echo # they will also be blocked by active SR lock.
1410
--echo # Switching to connection 'mdl_con2'.
1411
connection mdl_con2;
1413
--echo # 3) Check compatibility for pending X lock.
1415
--echo # Acquire SR lock in order to create pending X lock.
1417
select count(*) from t1;
1419
--echo # Switching to connection 'default'.
1421
--echo # Add pending X lock.
1423
--send rename table t1 to t2;
1425
--echo # Switching to connection 'mdl_con1'.
1426
connection mdl_con1;
1427
--echo # Check that RENAME TABLE is waiting with pending X lock.
1428
let $wait_condition=
1429
select count(*) = 1 from information_schema.processlist
1430
where state = "Waiting for table metadata lock" and
1431
info = "rename table t1 to t2";
1432
--source include/wait_condition.inc
1433
--echo # Check that SH locks are compatible with pending X
1434
select column_name from information_schema.columns where
1435
table_schema='test' and table_name='t1';
1436
--echo # Check that S is incompatible with pending X
1438
--send handler t1 open;
1440
--echo # Switching to connection 'mdl_con2'.
1441
connection mdl_con2;
1442
--echo # Check that the above HANDLER OPEN is blocked because of pending X lock.
1443
let $wait_condition=
1444
select count(*) = 1 from information_schema.processlist
1445
where state = "Waiting for table metadata lock" and
1446
info = "handler t1 open";
1447
--source include/wait_condition.inc
1448
--echo # Unblock RENAME TABLE.
1451
--echo # Switching to connection 'default'.
1453
--echo # Reaping RENAME TABLE.
1454
--error ER_TABLE_EXISTS_ERROR
1457
--echo # Switching to connection 'mdl_con1'.
1458
connection mdl_con1;
1459
--echo # Reaping HANDLER t1 OPEN.
1462
--echo # Restore pending X lock.
1464
--echo # Switching to connection 'mdl_con2'.
1465
connection mdl_con2;
1467
select count(*) from t1;
1469
--echo # Switching to connection 'default'.
1471
--echo # Add pending X lock.
1473
--send rename table t1 to t2;
1475
--echo # Switching to connection 'mdl_con1'.
1476
connection mdl_con1;
1477
--echo # Check that RENAME TABLE is waiting with pending X lock.
1478
let $wait_condition=
1479
select count(*) = 1 from information_schema.processlist
1480
where state = "Waiting for table metadata lock" and
1481
info = "rename table t1 to t2";
1482
--source include/wait_condition.inc
1483
--echo # Check that SR is incompatible with pending X
1485
--send select count(*) from t1;
1487
--echo # Switching to connection 'mdl_con2'.
1488
connection mdl_con2;
1489
--echo # Check that the above SELECT is blocked because of pending X lock.
1490
let $wait_condition=
1491
select count(*) = 1 from information_schema.processlist
1492
where state = "Waiting for table metadata lock" and
1493
info = "select count(*) from t1";
1494
--source include/wait_condition.inc
1495
--echo # Unblock RENAME TABLE.
1498
--echo # Switching to connection 'default'.
1500
--echo # Reaping RENAME TABLE.
1501
--error ER_TABLE_EXISTS_ERROR
1504
--echo # Switching to connection 'mdl_con1'.
1505
connection mdl_con1;
1506
--echo # Reaping SELECT.
1508
--echo # Restore pending X lock.
1510
--echo # Switching to connection 'mdl_con2'.
1511
connection mdl_con2;
1513
select count(*) from t1;
1515
--echo # Switching to connection 'default'.
1517
--echo # Add pending X lock.
1519
--send rename table t1 to t2;
1521
--echo # Switching to connection 'mdl_con1'.
1522
connection mdl_con1;
1523
--echo # Check that RENAME TABLE is waiting with pending X lock.
1524
let $wait_condition=
1525
select count(*) = 1 from information_schema.processlist
1526
where state = "Waiting for table metadata lock" and
1527
info = "rename table t1 to t2";
1528
--source include/wait_condition.inc
1529
--echo # Check that SW is incompatible with pending X
1531
--send delete from t1 limit 1;
1533
--echo # Switching to connection 'mdl_con2'.
1534
connection mdl_con2;
1535
--echo # Check that the above DELETE is blocked because of pending X lock.
1536
let $wait_condition=
1537
select count(*) = 1 from information_schema.processlist
1538
where state = "Waiting for table metadata lock" and
1539
info = "delete from t1 limit 1";
1540
--source include/wait_condition.inc
1541
--echo # Unblock RENAME TABLE.
1544
--echo # Switching to connection 'default'.
1546
--echo # Reaping RENAME TABLE.
1547
--error ER_TABLE_EXISTS_ERROR
1550
--echo # Switching to connection 'mdl_con1'.
1551
connection mdl_con1;
1552
--echo # Reaping DELETE.
1554
--echo # Restore pending X lock.
1556
--echo # Switching to connection 'mdl_con2'.
1557
connection mdl_con2;
1559
select count(*) from t1;
1561
--echo # Switching to connection 'default'.
1563
--echo # Add pending X lock.
1565
--send rename table t1 to t2;
1567
--echo # Switching to connection 'mdl_con1'.
1568
connection mdl_con1;
1569
--echo # Check that RENAME TABLE is waiting with pending X lock.
1570
let $wait_condition=
1571
select count(*) = 1 from information_schema.processlist
1572
where state = "Waiting for table metadata lock" and
1573
info = "rename table t1 to t2";
1574
--source include/wait_condition.inc
1575
--echo # Check that SNW is incompatible with pending X
1577
--send alter table t1 add primary key (c1);
1579
--echo # Switching to connection 'mdl_con2'.
1580
connection mdl_con2;
1581
--echo # Check that the above ALTER TABLE is blocked because of pending X lock.
1582
let $wait_condition=
1583
select count(*) = 1 from information_schema.processlist
1584
where state = "Waiting for table metadata lock" and
1585
info = "alter table t1 add primary key (c1)";
1586
--source include/wait_condition.inc
1587
--echo # Unblock RENAME TABLE.
1590
--echo # Switching to connection 'default'.
1592
--echo # Reaping RENAME TABLE.
1593
--error ER_TABLE_EXISTS_ERROR
1596
--echo # Switching to connection 'mdl_con1'.
1597
connection mdl_con1;
1598
--echo # Reaping ALTER TABLE.
1599
--error ER_DUP_ENTRY
1601
--echo # Restore pending X lock.
1603
--echo # Switching to connection 'mdl_con2'.
1604
connection mdl_con2;
1607
--echo # Switching to connection 'default'.
1609
--echo # Add pending X lock.
1611
--send rename table t1 to t2;
1613
--echo # Switching to connection 'mdl_con1'.
1614
connection mdl_con1;
1615
--echo # Check that RENAME TABLE is waiting with pending X lock.
1616
let $wait_condition=
1617
select count(*) = 1 from information_schema.processlist
1618
where state = "Waiting for table metadata lock" and
1619
info = "rename table t1 to t2";
1620
--source include/wait_condition.inc
1621
--echo # Check that SNRW is incompatible with pending X
1623
--send lock table t1 write;
1625
--echo # Switching to connection 'mdl_con3'.
1626
connection mdl_con3;
1627
--echo # Check that the above LOCK TABLES is blocked because of pending X lock.
1628
let $wait_condition=
1629
select count(*) = 1 from information_schema.processlist
1630
where state = "Waiting for table metadata lock" and
1631
info = "lock table t1 write";
1632
--source include/wait_condition.inc
1634
--echo # Switching to connection 'mdl_con2'.
1635
connection mdl_con2;
1636
--echo # Unblock RENAME TABLE.
1639
--echo # Switching to connection 'default'.
1641
--echo # Reaping RENAME TABLE.
1642
--error ER_TABLE_EXISTS_ERROR
1645
--echo # Switching to connection 'mdl_con1'.
1646
connection mdl_con1;
1647
--echo # Reaping LOCK TABLES.
1651
--echo # Switching to connection 'default'.
1656
--echo # C) Now let us test how type-of-operation locks are handled in
1657
--echo # transactional context. Obviously we are mostly interested
1658
--echo # in conflicting types of locks.
1662
--echo # 1) Let us check how various locks used within transactional
1663
--echo # context interact with active/pending SNW lock.
1665
--echo # We start with case when we are acquiring lock on the table
1666
--echo # which was not used in the transaction before.
1668
select count(*) from t1;
1670
--echo # Switching to connection 'mdl_con1'.
1671
connection mdl_con1;
1672
--echo # Create an active SNW lock on t2.
1673
--echo # We have to use DEBUG_SYNC facility as otherwise SNW lock
1674
--echo # will be immediately released (or upgraded to X lock).
1675
insert into t2 values (1), (1);
1676
set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
1678
--send alter table t2 add primary key (c1);
1680
--echo # Switching to connection 'default'.
1682
set debug_sync= 'now WAIT_FOR locked';
1683
--echo # SR lock should be acquired without any waiting.
1684
select count(*) from t2;
1686
--echo # Now let us check that we will wait in case of SW lock.
1688
select count(*) from t1;
1690
--send insert into t2 values (1);
1692
--echo # Switching to connection 'mdl_con2'.
1693
connection mdl_con2;
1694
--echo # Check that the above INSERT is blocked.
1695
let $wait_condition=
1696
select count(*) = 1 from information_schema.processlist
1697
where state = "Waiting for table metadata lock" and
1698
info = "insert into t2 values (1)";
1699
--source include/wait_condition.inc
1700
--echo # Unblock ALTER TABLE and thus INSERT.
1701
set debug_sync= 'now SIGNAL finish';
1703
--echo # Switching to connection 'mdl_con1'.
1704
connection mdl_con1;
1705
--echo # Reap ALTER TABLE.
1706
--error ER_DUP_ENTRY
1709
--echo # Switching to connection 'default'.
1711
--echo # Reap INSERT.
1715
--echo # Now let us see what happens when we are acquiring lock on the table
1716
--echo # which is already used in transaction.
1718
--echo # *) First, case when transaction which has SR lock on the table also
1719
--echo # locked in SNW mode acquires yet another SR lock and then tries
1720
--echo # to acquire SW lock.
1722
select count(*) from t1;
1724
--echo # Switching to connection 'mdl_con1'.
1725
connection mdl_con1;
1726
--echo # Create an active SNW lock on t1.
1727
set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
1729
--send alter table t1 add primary key (c1);
1731
--echo # Switching to connection 'default'.
1733
set debug_sync= 'now WAIT_FOR locked';
1734
--echo # We should still be able to get SR lock without waiting.
1735
select count(*) from t1;
1736
--echo # Since the above ALTER TABLE is not upgrading SNW lock to X by waiting
1737
--echo # for SW lock we won't create deadlock.
1738
--echo # So the below INSERT should not end-up with ER_LOCK_DEADLOCK error.
1740
--send insert into t1 values (1);
1742
--echo # Switching to connection 'mdl_con2'.
1743
connection mdl_con2;
1744
--echo # Check that the above INSERT is blocked.
1745
let $wait_condition=
1746
select count(*) = 1 from information_schema.processlist
1747
where state = "Waiting for table metadata lock" and
1748
info = "insert into t1 values (1)";
1749
--source include/wait_condition.inc
1750
--echo # Unblock ALTER TABLE and thus INSERT.
1751
set debug_sync= 'now SIGNAL finish';
1753
--echo # Switching to connection 'mdl_con1'.
1754
connection mdl_con1;
1755
--echo # Reap ALTER TABLE.
1756
--error ER_DUP_ENTRY
1759
--echo # Switching to connection 'default'.
1761
--echo # Reap INSERT.
1765
--echo # **) Now test in which transaction that has SW lock on the table
1766
--echo # against which there is pending SNW lock acquires SR and SW
1767
--echo # locks on this table.
1770
insert into t1 values (1);
1772
--echo # Switching to connection 'mdl_con1'.
1773
connection mdl_con1;
1774
--echo # Create pending SNW lock on t1.
1776
--send alter table t1 add primary key (c1);
1778
--echo # Switching to connection 'default'.
1780
--echo # Wait until ALTER TABLE starts waiting for SNW lock.
1781
let $wait_condition=
1782
select count(*) = 1 from information_schema.processlist
1783
where state = "Waiting for table metadata lock" and
1784
info = "alter table t1 add primary key (c1)";
1785
--source include/wait_condition.inc
1786
--echo # We should still be able to get both SW and SR locks without waiting.
1787
select count(*) from t1;
1788
delete from t1 limit 1;
1789
--echo # Unblock ALTER TABLE.
1792
--echo # Switching to connection 'mdl_con1'.
1793
connection mdl_con1;
1794
--echo # Reap ALTER TABLE.
1795
--error ER_DUP_ENTRY
1798
--echo # Switching to connection 'default'.
1801
--echo # 2) Now similar tests for active SNW lock which is being upgraded
1804
--echo # Again we start with case when we are acquiring lock on the
1805
--echo # table which was not used in the transaction before.
1807
select count(*) from t1;
1809
--echo # Switching to connection 'mdl_con2'.
1810
connection mdl_con2;
1811
--echo # Start transaction which will prevent SNW -> X upgrade from
1812
--echo # completing immediately.
1814
select count(*) from t2;
1816
--echo # Switching to connection 'mdl_con1'.
1817
connection mdl_con1;
1818
--echo # Create SNW lock pending upgrade to X on t2.
1820
--send alter table t2 add column c2 int;
1822
--echo # Switching to connection 'default'.
1824
--echo # Wait until ALTER TABLE starts waiting X lock.
1825
let $wait_condition=
1826
select count(*) = 1 from information_schema.processlist
1827
where state = "Waiting for table metadata lock" and
1828
info = "alter table t2 add column c2 int";
1829
--source include/wait_condition.inc
1830
--echo # Check that attempt to acquire SR lock on t2 causes waiting.
1832
--send select count(*) from t2;
1834
--echo # Switching to connection 'mdl_con2'.
1835
connection mdl_con2;
1836
--echo # Check that the above SELECT is blocked.
1837
let $wait_condition=
1838
select count(*) = 1 from information_schema.processlist
1839
where state = "Waiting for table metadata lock" and
1840
info = "select count(*) from t2";
1841
--source include/wait_condition.inc
1842
--echo # Unblock ALTER TABLE.
1845
--echo # Switching to connection 'mdl_con1'.
1846
connection mdl_con1;
1847
--echo # Reap ALTER TABLE.
1850
--echo # Switching to connection 'default'.
1852
--echo # Reap SELECT.
1855
--echo # Do similar check for SW lock.
1857
select count(*) from t1;
1859
--echo # Switching to connection 'mdl_con2'.
1860
connection mdl_con2;
1861
--echo # Start transaction which will prevent SNW -> X upgrade from
1862
--echo # completing immediately.
1864
select count(*) from t2;
1866
--echo # Switching to connection 'mdl_con1'.
1867
connection mdl_con1;
1868
--echo # Create SNW lock pending upgrade to X on t2.
1870
--send alter table t2 drop column c2;
1872
--echo # Switching to connection 'default'.
1874
--echo # Wait until ALTER TABLE starts waiting X lock.
1875
let $wait_condition=
1876
select count(*) = 1 from information_schema.processlist
1877
where state = "Waiting for table metadata lock" and
1878
info = "alter table t2 drop column c2";
1879
--source include/wait_condition.inc
1880
--echo # Check that attempt to acquire SW lock on t2 causes waiting.
1882
--send insert into t2 values (1);
1884
--echo # Switching to connection 'mdl_con2'.
1885
connection mdl_con2;
1886
--echo # Check that the above INSERT is blocked.
1887
let $wait_condition=
1888
select count(*) = 1 from information_schema.processlist
1889
where state = "Waiting for table metadata lock" and
1890
info = "insert into t2 values (1)";
1891
--source include/wait_condition.inc
1892
--echo # Unblock ALTER TABLE.
1895
--echo # Switching to connection 'mdl_con1'.
1896
connection mdl_con1;
1897
--echo # Reap ALTER TABLE.
1900
--echo # Switching to connection 'default'.
1902
--echo # Reap INSERT.
1906
--echo # Test for the case in which we are acquiring lock on the table
1907
--echo # which is already used in transaction.
1910
select count(*) from t1;
1912
--echo # Switching to connection 'mdl_con1'.
1913
connection mdl_con1;
1914
--echo # Create SNW lock pending upgrade to X.
1916
--send alter table t1 add column c2 int;
1918
--echo # Switching to connection 'default'.
1920
--echo # Wait until ALTER TABLE starts waiting X lock.
1921
let $wait_condition=
1922
select count(*) = 1 from information_schema.processlist
1923
where state = "Waiting for table metadata lock" and
1924
info = "alter table t1 add column c2 int";
1925
--source include/wait_condition.inc
1926
--echo # Check that transaction is still able to acquire SR lock.
1927
select count(*) from t1;
1928
--echo # Waiting trying to acquire SW lock will cause deadlock and
1929
--echo # therefore should cause an error.
1930
--error ER_LOCK_DEADLOCK
1931
delete from t1 limit 1;
1932
--echo # Unblock ALTER TABLE.
1935
--echo # Switching to connection 'mdl_con1'.
1936
connection mdl_con1;
1937
--echo # Reap ALTER TABLE.
1940
--echo # Switching to connection 'default'.
1943
--echo # 3) Check how various locks used within transactional context
1944
--echo # interact with active/pending SNRW lock.
1946
--echo # Once again we start with case when we are acquiring lock on
1947
--echo # the table which was not used in the transaction before.
1949
select count(*) from t1;
1951
--echo # Switching to connection 'mdl_con1'.
1952
connection mdl_con1;
1953
lock table t2 write;
1955
--echo # Switching to connection 'default'.
1957
--echo # Attempt to acquire SR should be blocked. It should
1958
--echo # not cause errors as it does not creates deadlock.
1960
--send select count(*) from t2;
1962
--echo # Switching to connection 'mdl_con1'.
1963
connection mdl_con1;
1964
--echo # Check that the above SELECT is blocked
1965
let $wait_condition=
1966
select count(*) = 1 from information_schema.processlist
1967
where state = "Waiting for table metadata lock" and
1968
info = "select count(*) from t2";
1969
--source include/wait_condition.inc
1970
--echo # Unblock SELECT.
1973
--echo # Switching to connection 'default'.
1975
--echo # Reap SELECT.
1978
--echo # Repeat the same test for SW lock.
1980
select count(*) from t1;
1982
--echo # Switching to connection 'mdl_con1'.
1983
connection mdl_con1;
1984
lock table t2 write;
1986
--echo # Switching to connection 'default'.
1988
--echo # Again attempt to acquire SW should be blocked and should
1989
--echo # not cause any errors.
1991
--send delete from t2 limit 1;
1993
--echo # Switching to connection 'mdl_con1'.
1994
connection mdl_con1;
1995
--echo # Check that the above DELETE is blocked
1996
let $wait_condition=
1997
select count(*) = 1 from information_schema.processlist
1998
where state = "Waiting for table metadata lock" and
1999
info = "delete from t2 limit 1";
2000
--source include/wait_condition.inc
2001
--echo # Unblock DELETE.
2004
--echo # Switching to connection 'default'.
2006
--echo # Reap DELETE.
2010
--echo # Now coverage for the case in which we are acquiring lock on
2011
--echo # the table which is already used in transaction and against
2012
--echo # which there is a pending SNRW lock request.
2014
--echo # *) Let us start with case when transaction has only a SR lock.
2017
select count(*) from t1;
2019
--echo # Switching to connection 'mdl_con1'.
2020
connection mdl_con1;
2022
--send lock table t1 write;
2024
--echo # Switching to connection 'default'.
2026
--echo # Wait until LOCK TABLE is blocked creating pending request for X lock.
2027
let $wait_condition=
2028
select count(*) = 1 from information_schema.processlist
2029
where state = "Waiting for table metadata lock" and
2030
info = "lock table t1 write";
2031
--source include/wait_condition.inc
2032
--echo # Check that another instance of SR lock is granted without waiting.
2033
select count(*) from t1;
2034
--echo # Attempt to wait for SW lock will lead to deadlock, thus
2035
--echo # the below statement should end with ER_LOCK_DEADLOCK error.
2036
--error ER_LOCK_DEADLOCK
2037
delete from t1 limit 1;
2038
--echo # Unblock LOCK TABLES.
2041
--echo # Switching to connection 'mdl_con1'.
2042
connection mdl_con1;
2043
--echo # Reap LOCK TABLES.
2047
--echo # Switching to connection 'default'.
2050
--echo # **) Now case when transaction has a SW lock.
2053
delete from t1 limit 1;
2055
--echo # Switching to connection 'mdl_con1'.
2056
connection mdl_con1;
2058
--send lock table t1 write;
2060
--echo # Switching to connection 'default'.
2062
--echo # Wait until LOCK TABLE is blocked creating pending request for X lock.
2063
let $wait_condition=
2064
select count(*) = 1 from information_schema.processlist
2065
where state = "Waiting for table metadata lock" and
2066
info = "lock table t1 write";
2067
--source include/wait_condition.inc
2068
--echo # Check that both SR and SW locks are granted without waiting
2069
--echo # and errors.
2070
select count(*) from t1;
2071
insert into t1 values (1, 1);
2072
--echo # Unblock LOCK TABLES.
2075
--echo # Switching to connection 'mdl_con1'.
2076
connection mdl_con1;
2077
--echo # Reap LOCK TABLES.
2081
--echo # Switching to connection 'default'.
2084
--echo # 4) Check how various locks used within transactional context
2085
--echo # interact with active/pending X lock.
2087
--echo # As usual we start with case when we are acquiring lock on
2088
--echo # the table which was not used in the transaction before.
2090
select count(*) from t1;
2092
--echo # Switching to connection 'mdl_con2'.
2093
connection mdl_con2;
2094
--echo # Start transaction which will prevent X lock from going away
2095
--echo # immediately.
2097
select count(*) from t2;
2099
--echo # Switching to connection 'mdl_con1'.
2100
connection mdl_con1;
2101
--echo # Create pending X lock on t2.
2103
--send rename table t2 to t3;
2105
--echo # Switching to connection 'default'.
2107
--echo # Wait until RENAME TABLE starts waiting with pending X lock.
2108
let $wait_condition=
2109
select count(*) = 1 from information_schema.processlist
2110
where state = "Waiting for table metadata lock" and
2111
info = "rename table t2 to t3";
2112
--source include/wait_condition.inc
2113
--echo # Check that attempt to acquire SR lock on t2 causes waiting.
2115
--send select count(*) from t2;
2117
--echo # Switching to connection 'mdl_con2'.
2118
connection mdl_con2;
2119
--echo # Check that the above SELECT is blocked.
2120
let $wait_condition=
2121
select count(*) = 1 from information_schema.processlist
2122
where state = "Waiting for table metadata lock" and
2123
info = "select count(*) from t2";
2124
--source include/wait_condition.inc
2125
--echo # Unblock RENAME TABLE.
2128
--echo # Switching to connection 'mdl_con1'.
2129
connection mdl_con1;
2130
--echo # Reap RENAME TABLE.
2133
--echo # Switching to connection 'default'.
2135
--echo # Reap SELECT.
2136
--error ER_NO_SUCH_TABLE
2139
rename table t3 to t2;
2140
--echo # The same test for SW lock.
2142
select count(*) from t1;
2144
--echo # Switching to connection 'mdl_con2'.
2145
connection mdl_con2;
2146
--echo # Start transaction which will prevent X lock from going away
2147
--echo # immediately.
2149
select count(*) from t2;
2151
--echo # Switching to connection 'mdl_con1'.
2152
connection mdl_con1;
2153
--echo # Create pending X lock on t2.
2155
--send rename table t2 to t3;
2157
--echo # Switching to connection 'default'.
2159
--echo # Wait until RENAME TABLE starts waiting with pending X lock.
2160
let $wait_condition=
2161
select count(*) = 1 from information_schema.processlist
2162
where state = "Waiting for table metadata lock" and
2163
info = "rename table t2 to t3";
2164
--source include/wait_condition.inc
2165
--echo # Check that attempt to acquire SW lock on t2 causes waiting.
2167
--send delete from t2 limit 1;
2169
--echo # Switching to connection 'mdl_con2'.
2170
connection mdl_con2;
2171
--echo # Check that the above DELETE is blocked.
2172
let $wait_condition=
2173
select count(*) = 1 from information_schema.processlist
2174
where state = "Waiting for table metadata lock" and
2175
info = "delete from t2 limit 1";
2176
--source include/wait_condition.inc
2177
--echo # Unblock RENAME TABLE.
2180
--echo # Switching to connection 'mdl_con1'.
2181
connection mdl_con1;
2182
--echo # Reap RENAME TABLE.
2185
--echo # Switching to connection 'default'.
2187
--echo # Reap DELETE.
2188
--error ER_NO_SUCH_TABLE
2191
rename table t3 to t2;
2193
--echo # Coverage for the case in which we are acquiring lock on
2194
--echo # the table which is already used in transaction and against
2195
--echo # which there is a pending X lock request.
2197
--echo # *) The first case is when transaction has only a SR lock.
2200
select count(*) from t1;
2202
--echo # Switching to connection 'mdl_con1'.
2203
connection mdl_con1;
2205
--send rename table t1 to t2;
2207
--echo # Switching to connection 'default'.
2209
--echo # Wait until RENAME TABLE is blocked creating pending request for X lock.
2210
let $wait_condition=
2211
select count(*) = 1 from information_schema.processlist
2212
where state = "Waiting for table metadata lock" and
2213
info = "rename table t1 to t2";
2214
--source include/wait_condition.inc
2215
--echo # Check that another instance of SR lock is granted without waiting.
2216
select count(*) from t1;
2217
--echo # Attempt to wait for SW lock will lead to deadlock, thus
2218
--echo # the below statement should end with ER_LOCK_DEADLOCK error.
2219
--error ER_LOCK_DEADLOCK
2220
delete from t1 limit 1;
2221
--echo # Unblock RENAME TABLE.
2224
--echo # Switching to connection 'mdl_con1'.
2225
connection mdl_con1;
2226
--echo # Reap RENAME TABLE.
2227
--error ER_TABLE_EXISTS_ERROR
2230
--echo # Switching to connection 'default'.
2233
--echo # **) The second case is when transaction has a SW lock.
2236
delete from t1 limit 1;
2238
--echo # Switching to connection 'mdl_con1'.
2239
connection mdl_con1;
2241
--send rename table t1 to t2;
2243
--echo # Switching to connection 'default'.
2245
--echo # Wait until RENAME TABLE is blocked creating pending request for X lock.
2246
let $wait_condition=
2247
select count(*) = 1 from information_schema.processlist
2248
where state = "Waiting for table metadata lock" and
2249
info = "rename table t1 to t2";
2250
--source include/wait_condition.inc
2251
--echo # Check that both SR and SW locks are granted without waiting
2252
--echo # and errors.
2253
select count(*) from t1;
2254
insert into t1 values (1, 1);
2255
--echo # Unblock RENAME TABLE.
2258
--echo # Switching to connection 'mdl_con1'.
2259
connection mdl_con1;
2260
--echo # Reap RENAME TABLE.
2261
--error ER_TABLE_EXISTS_ERROR
2264
--echo # Switching to connection 'default'.
2268
disconnect mdl_con1;
2269
disconnect mdl_con2;
2270
disconnect mdl_con3;
2271
set debug_sync= 'RESET';
2276
--echo # Additional coverage for some scenarios in which not quite
2277
--echo # correct use of S metadata locks by HANDLER statement might
2278
--echo # have caused deadlocks.
2281
drop table if exists t1, t2;
2283
connect(handler_con1,localhost,root,,);
2284
connect(handler_con2,localhost,root,,);
2286
create table t1 (i int);
2287
create table t2 (j int);
2288
insert into t1 values (1);
2291
--echo # First, check scenario in which we upgrade SNRW lock to X lock
2292
--echo # on a table while having HANDLER READ trying to acquire TL_READ
2293
--echo # on the same table.
2297
--echo # Switching to connection 'handler_con1'.
2298
connection handler_con1;
2299
lock table t1 write;
2300
--echo # Upgrade SNRW to X lock.
2302
--send alter table t1 add column j int;
2304
--echo # Switching to connection 'handler_con2'.
2305
connection handler_con2;
2306
--echo # Wait until ALTER is blocked during upgrade.
2307
let $wait_condition=
2308
select count(*) = 1 from information_schema.processlist
2309
where state = "Waiting for table metadata lock" and
2310
info = "alter table t1 add column j int";
2311
--source include/wait_condition.inc
2313
--echo # Switching to connection 'default'.
2315
--echo # The below statement should not cause deadlock.
2316
--send handler t1 read first;
2318
--echo # Switching to connection 'handler_con1'.
2319
connection handler_con1;
2320
--echo # Reap ALTER TABLE.
2324
--echo # Switching to connection 'default'.
2326
--echo # Reap HANDLER READ.
2331
--echo # Now, check scenario in which upgrade of SNRW lock to X lock
2332
--echo # can be blocked by HANDLER which is open in connection currently
2333
--echo # waiting to get table-lock owned by connection doing upgrade.
2337
--echo # Switching to connection 'handler_con1'.
2338
connection handler_con1;
2339
lock table t1 write, t2 read;
2341
--echo # Switching to connection 'default'.
2343
--echo # Execute statement which will be blocked on table-level lock
2344
--echo # owned by connection 'handler_con1'.
2346
--send insert into t2 values (1);
2348
--echo # Switching to connection 'handler_con1'.
2349
connection handler_con1;
2350
--echo # Wait until INSERT is blocked on table-level lock.
2351
let $wait_condition=
2352
select count(*) = 1 from information_schema.processlist
2353
where state = "Waiting for table level lock" and
2354
info = "insert into t2 values (1)";
2355
--source include/wait_condition.inc
2356
--echo # Sending 'alter table t1 drop column j'. It should not cause
2358
send alter table t1 drop column j;
2359
--echo # Switching to connection 'handler_con2'.
2360
connection handler_con2;
2361
--echo # Wait until ALTER is blocked during upgrade.
2362
let $wait_condition=
2363
select count(*) = 1 from information_schema.processlist
2364
where state = "Waiting for table metadata lock" and
2365
info = "alter table t1 drop column j";
2366
--source include/wait_condition.inc
2368
--echo # Switching to connection 'default'.
2370
--echo # Reap INSERT.
2371
--error ER_LOCK_ABORTED
2375
--echo # Switching to connection 'handler_con1'.
2376
connection handler_con1;
2377
--echo # Reaping 'alter table t1 drop column j'
2380
--echo # Switching to connection 'default'.
2383
--echo # Then, check the scenario in which upgrade of SNRW lock to X
2384
--echo # lock is blocked by HANDLER which is open in connection currently
2385
--echo # waiting to get SW lock on the same table.
2389
--echo # Switching to connection 'handler_con1'.
2390
connection handler_con1;
2391
lock table t1 write;
2393
--echo # Switching to connection 'default'.
2395
--echo # The below insert should be blocked because active SNRW lock on 't1'.
2397
--send insert into t1 values (1);
2399
--echo # Switching to connection 'handler_con1'.
2400
connection handler_con1;
2401
--echo # Wait until INSERT is blocked because of SNRW lock.
2402
let $wait_condition=
2403
select count(*) = 1 from information_schema.processlist
2404
where state = "Waiting for table metadata lock" and
2405
info = "insert into t1 values (1)";
2406
--source include/wait_condition.inc
2407
--echo # The below ALTER TABLE will be blocked because of presence of HANDLER.
2409
--send alter table t1 add column j int;
2411
--echo # Switching to connection 'default'.
2413
--echo # INSERT should be chosen as victim for resolving deadlock.
2414
--echo # Reaping INSERT.
2415
--error ER_LOCK_DEADLOCK
2417
--echo # Close HANDLER to unblock ALTER TABLE.
2420
--echo # Switching to connection 'handler_con1'.
2421
connection handler_con1;
2422
--echo # Reaping ALTER TABLE.
2426
--echo # Switching to connection 'default'.
2430
--echo # Finally, test in which upgrade of SNRW lock to X lock is blocked
2431
--echo # by HANDLER which is open in connection currently waiting to get
2432
--echo # SR lock on the table on which lock is upgraded.
2436
--echo # Switching to connection 'handler_con1'.
2437
connection handler_con1;
2438
lock table t1 write, t2 write;
2440
--echo # Switching to connection 'default'.
2442
--echo # The below insert should be blocked because active SNRW lock on 't1'.
2444
--send insert into t2 values (1);
2446
--echo # Switching to connection 'handler_con1'.
2447
connection handler_con1;
2448
--echo # Wait until INSERT is blocked because of SNRW lock.
2449
let $wait_condition=
2450
select count(*) = 1 from information_schema.processlist
2451
where state = "Waiting for table metadata lock" and
2452
info = "insert into t2 values (1)";
2453
--source include/wait_condition.inc
2454
--echo # The below ALTER TABLE will be blocked because of presence of HANDLER.
2456
--send alter table t1 drop column j;
2458
--echo # Switching to connection 'default'.
2460
--echo # INSERT should be chosen as victim for resolving deadlock.
2461
--echo # Reaping INSERT.
2462
--error ER_LOCK_DEADLOCK
2464
--echo # Close HANDLER to unblock ALTER TABLE.
2467
--echo # Switching to connection 'handler_con1'.
2468
connection handler_con1;
2469
--echo # Reaping ALTER TABLE.
2473
--echo # Switching to connection 'default'.
2477
disconnect handler_con1;
2478
disconnect handler_con2;
2483
--echo # Test coverage for basic deadlock detection in metadata
2484
--echo # locking subsystem.
2487
drop tables if exists t0, t1, t2, t3, t4, t5;
2489
set debug_sync= 'RESET';
2491
connect(deadlock_con1,localhost,root,,);
2492
connect(deadlock_con2,localhost,root,,);
2493
connect(deadlock_con3,localhost,root,,);
2495
create table t1 (i int);
2496
create table t2 (j int);
2497
create table t3 (k int);
2498
create table t4 (k int);
2501
--echo # Test for the case in which no deadlock occurs.
2505
--echo # Switching to connection 'deadlock_con1'.
2506
connection deadlock_con1;
2508
insert into t1 values (1);
2511
--echo # Switching to connection 'deadlock_con2'.
2512
connection deadlock_con2;
2514
insert into t2 values (1);
2517
--echo # Switching to connection 'default'.
2520
--send rename table t2 to t0, t3 to t2, t0 to t3;
2523
--echo # Switching to connection 'deadlock_con1'.
2524
connection deadlock_con1;
2525
--echo # Wait until the above RENAME TABLE is blocked because it has to wait
2526
--echo # for 'deadlock_con2' which holds shared metadata lock on 't2'.
2527
let $wait_condition=
2528
select count(*) = 1 from information_schema.processlist
2529
where state = "Waiting for table metadata lock" and
2530
info = "rename table t2 to t0, t3 to t2, t0 to t3";
2531
--source include/wait_condition.inc
2532
--echo # The below statement should wait for exclusive metadata lock
2533
--echo # on 't2' to go away and should not produce ER_LOCK_DEADLOCK
2534
--echo # as no deadlock is possible in this situation.
2536
--send select * from t2;
2539
--echo # Switching to connection 'deadlock_con2'.
2540
connection deadlock_con2;
2541
--echo # Wait until the above SELECT * FROM t2 is starts waiting
2542
--echo # for an exclusive metadata lock to go away.
2543
let $wait_condition=
2544
select count(*) = 1 from information_schema.processlist
2545
where state = "Waiting for table metadata lock" and
2546
info = "select * from t2";
2547
--source include/wait_condition.inc
2549
--echo # Unblock RENAME TABLE by releasing shared metadata lock on t2.
2553
--echo # Switching to connection 'default'.
2555
--echo # Reap RENAME TABLE.
2559
--echo # Switching to connection 'deadlock_con1'.
2560
connection deadlock_con1;
2561
--echo # Reap SELECT.
2565
--echo # Switching to connection 'default'.
2568
--echo # Let us check that in the process of waiting for conflicting lock
2569
--echo # on table 't2' to go away transaction in connection 'deadlock_con1'
2570
--echo # has not released metadata lock on table 't1'.
2572
--send rename table t1 to t0, t3 to t1, t0 to t3;
2575
--echo # Switching to connection 'deadlock_con1'.
2576
connection deadlock_con1;
2577
--echo # Wait until the above RENAME TABLE is blocked because it has to wait
2578
--echo # for 'deadlock_con1' which should still hold shared metadata lock on
2579
--echo # table 't1'.
2580
let $wait_condition=
2581
select count(*) = 1 from information_schema.processlist
2582
where state = "Waiting for table metadata lock" and
2583
info = "rename table t1 to t0, t3 to t1, t0 to t3";
2584
--source include/wait_condition.inc
2585
--echo # Commit transaction to unblock RENAME TABLE.
2589
--echo # Switching to connection 'default'.
2591
--echo # Reap RENAME TABLE.
2595
--echo # Test for case when deadlock occurs and should be detected immediately.
2599
--echo # Switching to connection 'deadlock_con1'.
2600
connection deadlock_con1;
2602
insert into t2 values (2);
2605
--echo # Switching to connection 'default'.
2608
--send rename table t2 to t0, t1 to t2, t0 to t1;
2611
--echo # Switching to connection 'deadlock_con1'.
2612
connection deadlock_con1;
2613
--echo # Wait until the above RENAME TABLE is blocked because it has to wait
2614
--echo # for 'deadlock_con1' which holds shared metadata lock on 't2'.
2615
let $wait_condition=
2616
select count(*) = 1 from information_schema.processlist
2617
where state = "Waiting for table metadata lock" and
2618
info = "rename table t2 to t0, t1 to t2, t0 to t1";
2619
--source include/wait_condition.inc
2621
--echo # The below statement should not wait as doing so will cause deadlock.
2622
--echo # Instead it should fail and emit ER_LOCK_DEADLOCK statement.
2623
--error ER_LOCK_DEADLOCK
2627
--echo # Let us check that failure of the above statement has not released
2628
--echo # metadata lock on table 't1', i.e. that RENAME TABLE is still blocked.
2629
let $wait_condition=
2630
select count(*) = 1 from information_schema.processlist
2631
where state = "Waiting for table metadata lock" and
2632
info = "rename table t2 to t0, t1 to t2, t0 to t1";
2633
--source include/wait_condition.inc
2634
--echo # Commit transaction to unblock RENAME TABLE.
2638
--echo # Switching to connection 'default'.
2640
--echo # Reap RENAME TABLE.
2644
--echo # Test for the case in which deadlock also occurs but not immediately.
2648
--echo # Switching to connection 'deadlock_con1'.
2649
connection deadlock_con1;
2651
insert into t2 values (1);
2654
--echo # Switching to connection 'default'.
2656
lock table t1 write;
2659
--echo # Switching to connection 'deadlock_con1'.
2660
connection deadlock_con1;
2661
--echo # The below SELECT statement should wait for metadata lock
2662
--echo # on table 't1' and should not produce ER_LOCK_DEADLOCK
2663
--echo # immediately as no deadlock is possible at the moment.
2664
--send select * from t1;
2667
--echo # Switching to connection 'deadlock_con2'.
2668
connection deadlock_con2;
2669
--echo # Wait until the above SELECT * FROM t1 is starts waiting
2670
--echo # for an UNRW metadata lock to go away.
2671
let $wait_condition=
2672
select count(*) = 1 from information_schema.processlist
2673
where state = "Waiting for table metadata lock" and info = "select * from t1";
2674
--source include/wait_condition.inc
2676
--echo # Send RENAME TABLE statement that will deadlock with the
2677
--echo # SELECT statement and thus should abort the latter.
2678
--send rename table t1 to t0, t2 to t1, t0 to t2;
2681
--echo # Switching to connection 'default'.
2683
--echo # Wait till above RENAME TABLE is blocked while holding
2684
--echo # pending X lock on t1.
2685
let $wait_condition=
2686
select count(*) = 1 from information_schema.processlist
2687
where state = "Waiting for table metadata lock" and
2688
info = "rename table t1 to t0, t2 to t1, t0 to t2";
2689
--source include/wait_condition.inc
2690
--echo # Allow the above RENAME TABLE to acquire lock on t1 and
2691
--echo # create pending lock on t2 thus creating deadlock.
2695
--echo # Switching to connection 'deadlock_con1'.
2696
connection deadlock_con1;
2697
--echo # Since the latest RENAME TABLE entered in deadlock with SELECT
2698
--echo # statement the latter should be aborted and emit ER_LOCK_DEADLOCK
2700
--echo # Reap SELECT * FROM t1.
2701
--error ER_LOCK_DEADLOCK
2705
--echo # Again let us check that failure of the SELECT statement has not
2706
--echo # released metadata lock on table 't2', i.e. that the latest RENAME
2707
--echo # is blocked.
2708
let $wait_condition=
2709
select count(*) = 1 from information_schema.processlist
2710
where state = "Waiting for table metadata lock" and
2711
info = "rename table t1 to t0, t2 to t1, t0 to t2";
2712
--source include/wait_condition.inc
2713
--echo # Commit transaction to unblock this RENAME TABLE.
2717
--echo # Switching to connection 'deadlock_con2'.
2718
connection deadlock_con2;
2719
--echo # Reap RENAME TABLE ... .
2723
--echo # Switching to connection 'default'.
2726
drop tables t1, t2, t3, t4;
2729
--echo # Now, test case which shows that deadlock detection empiric
2730
--echo # also takes into account requests for metadata lock upgrade.
2732
create table t1 (i int);
2733
insert into t1 values (1);
2734
--echo # Avoid race which occurs when SELECT in 'deadlock_con1' connection
2735
--echo # accesses table before the above INSERT unlocks the table and thus
2736
--echo # its result becomes visible to other connections.
2740
--echo # Switching to connection 'deadlock_con1'.
2741
connection deadlock_con1;
2746
--echo # Switching to connection 'default'.
2749
--send alter table t1 add column j int, rename to t2;
2752
--echo # Switching to connection 'deadlock_con1'.
2753
connection deadlock_con1;
2754
--echo # Wait until the above ALTER TABLE ... RENAME acquires exclusive
2755
--echo # metadata lock on 't2' and starts waiting for connection
2756
--echo # 'deadlock_con1' which holds shared lock on 't1'.
2757
let $wait_condition=
2758
select count(*) = 1 from information_schema.processlist
2759
where state = "Waiting for table metadata lock" and
2760
info = "alter table t1 add column j int, rename to t2";
2761
--source include/wait_condition.inc
2763
--echo # The below statement should not wait as it will cause deadlock.
2764
--echo # An appropriate error should be reported instead.
2765
--error ER_LOCK_DEADLOCK
2768
--echo # Again let us check that failure of the above statement has not
2769
--echo # released all metadata locks in connection 'deadlock_con1' and
2770
--echo # so ALTER TABLE ... RENAME is still blocked.
2771
let $wait_condition=
2772
select count(*) = 1 from information_schema.processlist
2773
where state = "Waiting for table metadata lock" and
2774
info = "alter table t1 add column j int, rename to t2";
2775
--source include/wait_condition.inc
2777
--echo # Commit transaction to unblock ALTER TABLE ... RENAME.
2781
--echo # Switching to connection 'default'.
2783
--echo # Reap ALTER TABLE ... RENAME.
2789
--echo # Test that in situation when MDL subsystem detects a deadlock
2790
--echo # but it turns out that it can be resolved by backing-off locks
2791
--echo # acquired by one of participating transactions (which is
2792
--echo # possible when one of transactions consists only of currently
2793
--echo # executed statement, e.g. in autocommit mode) no error is
2796
create table t1 (i int);
2797
create table t2 (j int);
2798
--echo # Ensure that the below SELECT stops once it has acquired metadata
2799
--echo # lock on table 't2'.
2800
set debug_sync= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
2802
--send select * from t2, t1
2805
--echo # Switching to connection 'deadlock_con1'.
2806
connection deadlock_con1;
2807
--echo # Wait till SELECT acquires MDL on 't2' and starts waiting for signal.
2808
set debug_sync= 'now WAIT_FOR locked';
2810
--send lock tables t1 write, t2 write
2813
--echo # Switching to connection 'deadlock_con2'.
2814
connection deadlock_con2;
2815
--echo # Wait until LOCK TABLES acquires SNRW lock on 't1' and is blocked
2816
--echo # while trying to acquire SNRW lock on 't1'.
2817
let $wait_condition=
2818
select count(*) = 1 from information_schema.processlist
2819
where state = "Waiting for table metadata lock" and
2820
info = "lock tables t1 write, t2 write";
2821
--source include/wait_condition.inc
2822
--echo # Resume SELECT execution, this should eventually unblock LOCK TABLES.
2823
set debug_sync= 'now SIGNAL finish';
2826
--echo # Switching to connection 'deadlock_con1'.
2827
connection deadlock_con1;
2828
--echo # Reaping LOCK TABLES.
2833
--echo # Switching to connection 'default'.
2835
--echo # Reaping SELECT. It succeed and not report ER_LOCK_DEADLOCK error.
2841
--echo # Test coverage for situation in which a race has happened
2842
--echo # during deadlock detection process which led to unwarranted
2843
--echo # ER_LOCK_DEADLOCK error.
2845
create table t1 (i int);
2847
--echo # Ensure that ALTER waits once it has acquired SNW lock.
2848
set debug_sync='after_open_table_mdl_shared SIGNAL parked1 WAIT_FOR go1';
2850
--send alter table t1 add column j int
2853
--echo # Switching to connection 'deadlock_con1'.
2854
connection deadlock_con1;
2855
--echo # Wait till ALTER acquires SNW lock and stops.
2856
set debug_sync='now WAIT_FOR parked1';
2857
--echo # Ensure that INSERT is paused once it detects that there is
2858
--echo # a conflicting metadata lock so it has to wait, but before
2859
--echo # deadlock detection is run.
2860
set debug_sync='mdl_acquire_lock_wait SIGNAL parked2 WAIT_FOR go2';
2862
--send insert into t1 values ()
2865
--echo # Switching to connection 'deadlock_con2'.
2866
connection deadlock_con2;
2867
--echo # Wait till INSERT is paused.
2868
set debug_sync='now WAIT_FOR parked2';
2869
--echo # Resume ALTER execution. Eventually it will release its
2870
--echo # metadata lock and INSERT's request for SW lock will be
2871
--echo # satisified.
2872
set debug_sync='now SIGNAL go1';
2875
--echo # Switching to connection 'default'.
2877
--echo # Reaping ALTER TABLE.
2879
--echo # Add a new request for SNW lock to waiting graph.
2881
--send alter table t1 drop column j
2884
--echo # Switching to connection 'deadlock_con2'.
2885
connection deadlock_con2;
2886
--echo # Wait until ALTER is blocked.
2887
let $wait_condition=
2888
select count(*) = 1 from information_schema.processlist
2889
where state = "Waiting for table metadata lock" and
2890
info = "alter table t1 drop column j";
2891
--source include/wait_condition.inc
2892
--echo # Resume INSERT so it can start deadlock detection.
2894
--echo # At this point there is a discrepancy between the fact that INSERT's
2895
--echo # SW lock is already satisfied, but INSERT's connection is still
2896
--echo # marked as waiting for it. Looking for a loop in waiters graph
2897
--echo # without additional checks has detected a deadlock (INSERT waits
2898
--echo # for SW lock; which is not granted because of pending SNW lock from
2899
--echo # ALTER; which waits for active SW lock from INSERT). Since requests
2900
--echo # for SW and SNW locks have same weight ALTER was selected as a victim
2901
--echo # and ended with ER_LOCK_DEADLOCK error.
2902
set debug_sync='now SIGNAL go2';
2905
--echo # Switching to connection 'deadlock_con1'.
2906
connection deadlock_con1;
2907
--echo # Reaping INSERT.
2911
--echo # Switching to connection 'default'.
2913
--echo # Reaping ALTER. It should succeed and not produce ER_LOCK_DEADLOCK.
2919
--echo # Now, test for a situation in which deadlock involves waiting not
2920
--echo # only in MDL subsystem but also for TDC. Such deadlocks should be
2921
--echo # successfully detected. If possible, they should be resolved without
2922
--echo # resorting to ER_LOCK_DEADLOCK error.
2924
create table t1(i int);
2925
create table t2(j int);
2928
--echo # First, let us check how we handle a simple scenario involving
2929
--echo # waits in MDL and TDC.
2931
set debug_sync= 'RESET';
2933
--echo # Switching to connection 'deadlock_con1'.
2934
connection deadlock_con1;
2935
--echo # Start a statement, which will acquire SR metadata lock on t1, open it
2936
--echo # and then stop, before trying to acquire SW lock on t2 and opening it.
2937
set debug_sync='open_tables_after_open_and_process_table SIGNAL parked WAIT_FOR go';
2939
--send select * from t1 where i in (select j from t2 for update)
2941
--echo # Switching to connection 'deadlock_con2'.
2942
connection deadlock_con2;
2943
--echo # Wait till the above SELECT stops.
2944
set debug_sync='now WAIT_FOR parked';
2945
--echo # The below FLUSH TABLES WITH READ LOCK should acquire
2946
--echo # SNW locks on t1 and t2 and wait till SELECT closes t1.
2948
send flush tables t1, t2 with read lock;
2950
--echo # Switching to connection 'deadlock_con3'.
2951
connection deadlock_con3;
2952
--echo # Wait until FLUSH TABLES WITH t1, t2 READ LOCK starts waiting
2953
--echo # for SELECT to close t1.
2954
let $wait_condition=
2955
select count(*) = 1 from information_schema.processlist
2956
where state = "Waiting for table flush" and
2957
info = "flush tables t1, t2 with read lock";
2958
--source include/wait_condition.inc
2960
--echo # Resume SELECT, so it tries to acquire SW lock on t1 and blocks,
2961
--echo # creating a deadlock. This deadlock should be detected and resolved
2962
--echo # by backing-off SELECT. As a result FTWRL should be able to finish.
2963
set debug_sync='now SIGNAL go';
2965
--echo # Switching to connection 'deadlock_con2'.
2966
connection deadlock_con2;
2967
--echo # Reap FLUSH TABLES WITH READ LOCK.
2971
--echo # Switching to connection 'deadlock_con1'.
2972
connection deadlock_con1;
2973
--echo # Reap SELECT.
2977
--echo # The same scenario with a slightly different order of events
2978
--echo # which emphasizes that setting correct deadlock detector weights
2979
--echo # for flush waits is important.
2981
set debug_sync= 'RESET';
2983
--echo # Switching to connection 'deadlock_con2'.
2984
connection deadlock_con2;
2985
set debug_sync='flush_tables_with_read_lock_after_acquire_locks SIGNAL parked WAIT_FOR go';
2987
--echo # The below FLUSH TABLES WITH READ LOCK should acquire
2988
--echo # SNW locks on t1 and t2 and wait on debug sync point.
2990
send flush tables t1, t2 with read lock;
2992
--echo # Switching to connection 'deadlock_con1'.
2993
connection deadlock_con1;
2994
--echo # Wait till FLUSH TABLE WITH READ LOCK stops.
2995
set debug_sync='now WAIT_FOR parked';
2997
--echo # Start statement which will acquire SR metadata lock on t1, open
2998
--echo # it and then will block while trying to acquire SW lock on t2.
3000
send select * from t1 where i in (select j from t2 for update);
3002
--echo # Switching to connection 'deadlock_con3'.
3003
connection deadlock_con3;
3004
--echo # Wait till the above SELECT blocks.
3005
let $wait_condition=
3006
select count(*) = 1 from information_schema.processlist
3007
where state = "Waiting for table metadata lock" and
3008
info = "select * from t1 where i in (select j from t2 for update)";
3009
--source include/wait_condition.inc
3011
--echo # Resume FLUSH TABLES, so it tries to flush t1, thus creating
3012
--echo # a deadlock. This deadlock should be detected and resolved by
3013
--echo # backing-off SELECT. As a result FTWRL should be able to finish.
3014
set debug_sync='now SIGNAL go';
3016
--echo # Switching to connection 'deadlock_con2'.
3017
connection deadlock_con2;
3018
--echo # Reap FLUSH TABLES WITH READ LOCK.
3022
--echo # Switching to connection 'deadlock_con1'.
3023
connection deadlock_con1;
3024
--echo # Reap SELECT.
3028
--echo # Now a more complex scenario involving two connections
3029
--echo # waiting for MDL and one for TDC.
3031
set debug_sync= 'RESET';
3033
--echo # Switching to connection 'deadlock_con1'.
3034
connection deadlock_con1;
3035
--echo # Start a statement which will acquire SR metadata lock on t2, open it
3036
--echo # and then stop, before trying to acquire SR on t1 and opening it.
3037
set debug_sync='open_tables_after_open_and_process_table SIGNAL parked WAIT_FOR go';
3039
send select * from t2, t1;
3041
--echo # Switching to connection 'deadlock_con2'.
3042
connection deadlock_con2;
3043
--echo # Wait till the above SELECT stops.
3044
set debug_sync='now WAIT_FOR parked';
3045
--echo # The below FLUSH TABLES WITH READ LOCK should acquire
3046
--echo # SNW locks on t2 and wait till SELECT closes t2.
3048
send flush tables t2 with read lock;
3050
--echo # Switching to connection 'deadlock_con3'.
3051
connection deadlock_con3;
3052
--echo # Wait until FLUSH TABLES WITH READ LOCK starts waiting
3053
--echo # for SELECT to close t2.
3054
let $wait_condition=
3055
select count(*) = 1 from information_schema.processlist
3056
where state = "Waiting for table flush" and
3057
info = "flush tables t2 with read lock";
3058
--source include/wait_condition.inc
3060
--echo # The below DROP TABLES should acquire X lock on t1 and start
3061
--echo # waiting for X lock on t2.
3063
send drop tables t1, t2;
3065
--echo # Switching to connection 'default'.
3067
--echo # Wait until DROP TABLES starts waiting for X lock on t2.
3068
let $wait_condition=
3069
select count(*) = 1 from information_schema.processlist
3070
where state = "Waiting for table metadata lock" and
3071
info = "drop tables t1, t2";
3072
--source include/wait_condition.inc
3074
--echo # Resume SELECT, so it tries to acquire SR lock on t1 and blocks,
3075
--echo # creating a deadlock. This deadlock should be detected and resolved
3076
--echo # by backing-off SELECT. As a result, FTWRL should be able to finish.
3077
set debug_sync='now SIGNAL go';
3079
--echo # Switching to connection 'deadlock_con2'.
3080
connection deadlock_con2;
3081
--echo # Reap FLUSH TABLES WITH READ LOCK.
3083
--echo # Unblock DROP TABLES.
3086
--echo # Switching to connection 'deadlock_con3'.
3087
connection deadlock_con3;
3088
--echo # Reap DROP TABLES.
3091
--echo # Switching to connection 'deadlock_con1'.
3092
connection deadlock_con1;
3093
--echo # Reap SELECT. It should emit error about missing table.
3094
--error ER_NO_SUCH_TABLE
3097
--echo # Switching to connection 'default'.
3100
set debug_sync= 'RESET';
3102
disconnect deadlock_con1;
3103
disconnect deadlock_con2;
3104
disconnect deadlock_con3;
3108
--echo # Test for a scenario in which FLUSH TABLES <list> WITH READ LOCK
3109
--echo # used to erroneously release metadata locks.
3111
connect(con1,localhost,root,,);
3112
connect(con2,localhost,root,,);
3115
drop tables if exists t1, t2;
3117
set debug_sync= 'RESET';
3118
create table t1(i int);
3119
create table t2(j int);
3121
--echo # Switching to connection 'con2'.
3123
set debug_sync='open_tables_after_open_and_process_table SIGNAL parked WAIT_FOR go';
3125
--echo # The below FLUSH TABLES <list> WITH READ LOCK should acquire
3126
--echo # SNW locks on t1 and t2, open table t1 and block on the debug
3127
--echo # sync point.
3129
send flush tables t1, t2 with read lock;
3131
--echo # Switching to connection 'con1'.
3133
--echo # Wait till FLUSH TABLES <list> WITH READ LOCK stops.
3134
set debug_sync='now WAIT_FOR parked';
3136
--echo # Start a statement which will flush all tables and thus
3137
--echo # invalidate table t1 open by FLUSH TABLES <list> WITH READ LOCK.
3141
--echo # Switching to connection 'default'.
3143
--echo # Wait till the above FLUSH TABLES blocks.
3144
let $wait_condition=
3145
select count(*) = 1 from information_schema.processlist
3146
where state = "Waiting for table flush" and
3147
info = "flush tables";
3148
--source include/wait_condition.inc
3150
--echo # Resume FLUSH TABLES <list> WITH READ LOCK, so it tries to open t2
3151
--echo # discovers that its t1 is obsolete and tries to reopen all tables.
3152
--echo # Such reopen should not cause releasing of SNW metadata locks
3153
--echo # which would result in assertion failures.
3154
set debug_sync='now SIGNAL go';
3156
--echo # Switching to connection 'con2'.
3158
--echo # Reap FLUSH TABLES <list> WITH READ LOCK.
3162
--echo # Switching to connection 'con1'.
3164
--echo # Reap FLUSH TABLES.
3168
--echo # Switching to connection 'default'.
3171
set debug_sync= 'RESET';
3177
--echo # Test for bug #46748 "Assertion in MDL_context::wait_for_locks()
3178
--echo # on INSERT + CREATE TRIGGER".
3181
drop tables if exists t1, t2, t3, t4, t5;
3183
--echo # Let us simulate scenario in which we open some tables from extended
3184
--echo # part of prelocking set but then encounter conflicting metadata lock,
3185
--echo # so have to back-off and wait for it to go away.
3186
connect (con1root,localhost,root,,test,,);
3187
connect (con2root,localhost,root,,test,,);
3189
create table t1 (i int);
3190
create table t2 (j int);
3191
create table t3 (k int);
3192
create table t4 (l int);
3193
create trigger t1_bi before insert on t1 for each row
3194
insert into t2 values (new.i);
3195
create trigger t2_bi before insert on t2 for each row
3196
insert into t3 values (new.j);
3198
--echo # Switching to connection 'con1root'.
3199
connection con1root;
3200
lock tables t4 read;
3202
--echo # Switching to connection 'con2root'.
3203
connection con2root;
3205
--send rename table t3 to t5, t4 to t3;
3207
--echo # Switching to connection 'default'.
3209
--echo # Wait until the above RENAME TABLE adds pending requests for exclusive
3210
--echo # metadata lock on its tables and blocks due to 't4' being used by LOCK
3212
let $wait_condition= select count(*)= 1 from information_schema.processlist
3213
where state= 'Waiting for table metadata lock' and
3214
info='rename table t3 to t5, t4 to t3';
3215
--source include/wait_condition.inc
3217
--send insert into t1 values (1);
3219
--echo # Switching to connection 'con1root'.
3220
connection con1root;
3221
--echo # Wait until INSERT statement waits due to encountering pending
3222
--echo # exclusive metadata lock on 't3'.
3223
let $wait_condition= select count(*)= 1 from information_schema.processlist
3224
where state= 'Waiting for table metadata lock' and
3225
info='insert into t1 values (1)';
3226
--source include/wait_condition.inc
3229
--echo # Switching to connection 'con2root'.
3230
connection con2root;
3231
--echo # Reap RENAME TABLE.
3234
--echo # Switching to connection 'default'.
3236
--echo # Reap INSERT.
3239
disconnect con1root;
3240
disconnect con2root;
3241
drop tables t1, t2, t3, t5;
3245
--echo # Bug#42546 - Backup: RESTORE fails, thinking it finds an existing table
3249
DROP TABLE IF EXISTS t1;
3251
set @save_log_output=@@global.log_output;
3252
set global log_output=file;
3254
connect(con2, localhost, root,,);
3257
--echo # Test 1: CREATE TABLE
3260
--echo # Connection 2
3262
--echo # Start insert on the not-yet existing table
3263
--echo # Wait after taking the MDL lock
3264
SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
3265
--send INSERT INTO t1 VALUES(1,"def")
3267
--echo # Connection 1
3269
SET DEBUG_SYNC= 'now WAIT_FOR locked';
3270
--echo # Now INSERT has a MDL on the non-existent table t1.
3273
--echo # Continue the INSERT once CREATE waits for exclusive lock
3274
SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL finish';
3275
--echo # Try to create that table.
3276
--send CREATE TABLE t1 (c1 INT, c2 VARCHAR(100), KEY(c1))
3278
--echo # Connection 2
3279
--echo # Insert fails
3281
--error ER_NO_SUCH_TABLE
3284
--echo # Connection 1
3287
SET DEBUG_SYNC= 'RESET';
3291
DROP TABLE IF EXISTS t1;
3295
--echo # Test 2: CREATE TABLE LIKE
3298
CREATE TABLE t2 (c1 INT, c2 VARCHAR(100), KEY(c1));
3300
--echo # Connection 2
3302
--echo # Start insert on the not-yet existing table
3303
--echo # Wait after taking the MDL
3304
SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL locked WAIT_FOR finish';
3305
--send INSERT INTO t1 VALUES(1,"def")
3307
--echo # Connection 1
3309
SET DEBUG_SYNC= 'now WAIT_FOR locked';
3310
--echo # Now INSERT has a MDL on the non-existent table t1.
3313
--echo # Continue the INSERT once CREATE waits for exclusive lock
3314
SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL finish';
3315
--echo # Try to create that table.
3316
--send CREATE TABLE t1 LIKE t2
3318
--echo # Connection 2
3319
--echo # Insert fails
3321
--error ER_NO_SUCH_TABLE
3324
--echo # Connection 1
3327
SET DEBUG_SYNC= 'RESET';
3333
DROP TABLE IF EXISTS t1;
3336
set global log_output=@save_log_output;
3340
--echo # Bug #46044 "MDL deadlock on LOCK TABLE + CREATE TABLE HIGH_PRIORITY
3341
--echo # FOR UPDATE"
3344
drop tables if exists t1, t2;
3346
connect (con46044, localhost, root,,);
3347
connect (con46044_2, localhost, root,,);
3349
create table t1 (i int);
3351
--echo # Let us check that we won't deadlock if during filling
3352
--echo # of I_S table we encounter conflicting metadata lock
3353
--echo # which owner is in its turn waiting for our connection.
3354
lock tables t1 read;
3356
--echo # Switching to connection 'con46044'.
3357
connection con46044;
3359
--send create table t2 select * from t1 for update;
3361
--echo # Switching to connection 'default'.
3363
--echo # Waiting until CREATE TABLE ... SELECT ... is blocked.
3364
let $wait_condition=
3365
select count(*) = 1 from information_schema.processlist
3366
where state = "Waiting for table level lock" and
3367
info = "create table t2 select * from t1 for update";
3368
--source include/wait_condition.inc
3370
--echo # First let us check that SHOW FIELDS/DESCRIBE doesn't
3371
--echo # gets blocked and emits and error.
3372
--error ER_WARN_I_S_SKIPPED_TABLE
3373
show fields from t2;
3375
--echo # Now test for I_S query which reads only .FRMs.
3377
--echo # Query below should only emit a warning.
3378
select column_name from information_schema.columns
3379
where table_schema='test' and table_name='t2';
3381
--echo # Finally, test for I_S query which does full-blown table open.
3383
--echo # Query below should not be blocked. Warning message should be
3384
--echo # stored in the 'table_comment' column.
3385
select table_name, table_type, auto_increment, table_comment
3386
from information_schema.tables where table_schema='test' and table_name='t2';
3388
--echo # Switching to connection 'default'.
3392
--echo # Switching to connection 'con46044'.
3393
connection con46044;
3394
--echo # Reaping CREATE TABLE ... SELECT ... .
3399
--echo # Let us also check that queries to I_S wait for conflicting metadata
3400
--echo # locks to go away instead of skipping table with a warning in cases
3401
--echo # when deadlock is not possible. This is a nice thing from compatibility
3402
--echo # and ease of use points of view.
3404
--echo # We check same three queries to I_S in this new situation.
3406
--echo # Switching to connection 'con46044_2'.
3407
connection con46044_2;
3408
lock tables t1 read;
3410
--echo # Switching to connection 'con46044'.
3411
connection con46044;
3413
--send create table t2 select * from t1 for update;
3415
--echo # Switching to connection 'default'.
3417
--echo # Waiting until CREATE TABLE ... SELECT ... is blocked.
3418
let $wait_condition=
3419
select count(*) = 1 from information_schema.processlist
3420
where state = "Waiting for table level lock" and
3421
info = "create table t2 select * from t1 for update";
3422
--source include/wait_condition.inc
3424
--echo # Let us check that SHOW FIELDS/DESCRIBE gets blocked.
3426
--send show fields from t2;
3428
--echo # Switching to connection 'con46044_2'.
3429
connection con46044_2;
3430
--echo # Wait until SHOW FIELDS gets blocked.
3431
let $wait_condition=
3432
select count(*) = 1 from information_schema.processlist
3433
where state = "Waiting for table metadata lock" and
3434
info = "show fields from t2";
3435
--source include/wait_condition.inc
3439
--echo # Switching to connection 'con46044'.
3440
connection con46044;
3441
--echo # Reaping CREATE TABLE ... SELECT ... .
3444
--echo # Switching to connection 'default'.
3446
--echo # Reaping SHOW FIELDS ...
3450
--echo # Switching to connection 'con46044_2'.
3451
connection con46044_2;
3452
lock tables t1 read;
3454
--echo # Switching to connection 'con46044'.
3455
connection con46044;
3457
--send create table t2 select * from t1 for update;
3459
--echo # Switching to connection 'default'.
3461
--echo # Waiting until CREATE TABLE ... SELECT ... is blocked.
3462
let $wait_condition=
3463
select count(*) = 1 from information_schema.processlist
3464
where state = "Waiting for table level lock" and
3465
info = "create table t2 select * from t1 for update";
3466
--source include/wait_condition.inc
3468
--echo # Check that I_S query which reads only .FRMs gets blocked.
3470
--send select column_name from information_schema.columns where table_schema='test' and table_name='t2';
3472
--echo # Switching to connection 'con46044_2'.
3473
connection con46044_2;
3474
--echo # Wait until SELECT COLUMN_NAME FROM I_S.COLUMNS gets blocked.
3475
let $wait_condition=
3476
select count(*) = 1 from information_schema.processlist
3477
where state = "Waiting for table metadata lock" and
3478
info like "select column_name from information_schema.columns%";
3479
--source include/wait_condition.inc
3483
--echo # Switching to connection 'con46044'.
3484
connection con46044;
3485
--echo # Reaping CREATE TABLE ... SELECT ... .
3488
--echo # Switching to connection 'default'.
3490
--echo # Reaping SELECT COLUMN_NAME FROM I_S.COLUMNS
3494
--echo # Switching to connection 'con46044_2'.
3495
connection con46044_2;
3496
lock tables t1 read;
3498
--echo # Switching to connection 'con46044'.
3499
connection con46044;
3501
--send create table t2 select * from t1 for update;
3503
--echo # Switching to connection 'default'.
3505
--echo # Waiting until CREATE TABLE ... SELECT ... is blocked.
3506
let $wait_condition=
3507
select count(*) = 1 from information_schema.processlist
3508
where state = "Waiting for table level lock" and
3509
info = "create table t2 select * from t1 for update";
3510
--source include/wait_condition.inc
3512
--echo # Finally, check that I_S query which does full-blown table open
3513
--echo # also gets blocked.
3515
--send select table_name, table_type, auto_increment, table_comment from information_schema.tables where table_schema='test' and table_name='t2';
3517
--echo # Switching to connection 'con46044_2'.
3518
connection con46044_2;
3519
--echo # Wait until SELECT ... FROM I_S.TABLES gets blocked.
3520
let $wait_condition=
3521
select count(*) = 1 from information_schema.processlist
3522
where state = "Waiting for table metadata lock" and
3523
info like "select table_name, table_type, auto_increment, table_comment from information_schema.tables%";
3524
--source include/wait_condition.inc
3528
--echo # Switching to connection 'con46044'.
3529
connection con46044;
3530
--echo # Reaping CREATE TABLE ... SELECT ... .
3533
--echo # Switching to connection 'default'.
3535
--echo # Reaping SELECT ... FROM I_S.TABLES
3539
--echo # Switching to connection 'default'.
3542
disconnect con46044;
3543
disconnect con46044_2;
3548
--echo # Test for bug #46273 "MySQL 5.4.4 new MDL: Bug#989 is not fully fixed
3549
--echo # in case of ALTER".
3552
drop table if exists t1;
3554
set debug_sync= 'RESET';
3555
connect (con46273,localhost,root,,test,,);
3557
create table t1 (c1 int primary key, c2 int, c3 int);
3558
insert into t1 values (1,1,0),(2,2,0),(3,3,0),(4,4,0),(5,5,0);
3561
select * from t1 where c2 = 3;
3564
--echo # Switching to connection 'con46273'.
3565
connection con46273;
3566
set debug_sync='after_lock_tables_takes_lock SIGNAL alter_table_locked WAIT_FOR alter_go';
3567
--send alter table t1 add column e int, rename to t2;
3570
--echo # Switching to connection 'default'.
3572
set debug_sync='now WAIT_FOR alter_table_locked';
3573
set debug_sync='mdl_acquire_lock_wait SIGNAL alter_go';
3574
--echo # The below statement should get ER_LOCK_DEADLOCK error
3575
--echo # (i.e. it should not allow ALTER to proceed, and then
3576
--echo # fail due to 't1' changing its name to 't2').
3577
--error ER_LOCK_DEADLOCK
3578
update t1 set c3=c3+1 where c2 = 3;
3581
--echo # Let us check that failure of the above statement has not released
3582
--echo # metadata lock on table 't1', i.e. that ALTER TABLE is still blocked.
3583
let $wait_condition=
3584
select count(*) = 1 from information_schema.processlist
3585
where state = "Waiting for table metadata lock" and
3586
info = "alter table t1 add column e int, rename to t2";
3587
--source include/wait_condition.inc
3589
--echo # Unblock ALTER TABLE by commiting transaction and thus releasing
3590
--echo # metadata lock on 't1'.
3594
--echo # Switching to connection 'con46273'.
3595
connection con46273;
3596
--echo # Reap ALTER TABLE.
3600
--echo # Switching to connection 'default'.
3602
disconnect con46273;
3604
set debug_sync= 'RESET';
3609
--echo # Test for bug #46673 "Deadlock between FLUSH TABLES WITH READ LOCK
3613
drop tables if exists t1;
3615
connect (con46673, localhost, root,,);
3617
create table t1 (i int);
3619
--echo # Switching to connection 'con46673'.
3620
connection con46673;
3622
insert into t1 values (1);
3624
--echo # Switching to connection 'default'.
3626
--echo # Statement below should not get blocked. And if after some
3627
--echo # changes to code it is there should not be a deadlock between
3628
--echo # it and transaction from connection 'con46673'.
3629
flush tables with read lock;
3632
--echo # Switching to connection 'con46673'.
3633
connection con46673;
3634
delete from t1 where i = 1;
3637
--echo # Switching to connection 'default'.
3640
disconnect con46673;
3645
--echo # Bug#48210 FLUSH TABLES WITH READ LOCK deadlocks
3646
--echo # against concurrent CREATE PROCEDURE
3649
connect (con2, localhost, root);
3651
--echo # Test 1: CREATE PROCEDURE
3653
--echo # Connection 1
3655
--echo # Start CREATE PROCEDURE and open mysql.proc
3656
SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL table_opened WAIT_FOR grlwait';
3657
--send CREATE PROCEDURE p1() SELECT 1
3659
--echo # Connection 2
3661
SET DEBUG_SYNC= 'now WAIT_FOR table_opened';
3662
--echo # Check that FLUSH must wait to get the GRL
3663
--echo # and let CREATE PROCEDURE continue
3664
SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL grlwait';
3665
--send FLUSH TABLES WITH READ LOCK
3667
--echo # Connection 1
3671
--echo # Connection 2
3676
--echo # Connection 1
3678
SET DEBUG_SYNC= 'RESET';
3680
--echo # Test 2: DROP PROCEDURE
3683
--echo # Start DROP PROCEDURE and open tables
3684
SET DEBUG_SYNC= 'after_open_table_mdl_shared SIGNAL table_opened WAIT_FOR grlwait';
3685
--send DROP PROCEDURE p1
3687
--echo # Connection 2
3689
SET DEBUG_SYNC= 'now WAIT_FOR table_opened';
3690
--echo # Check that FLUSH must wait to get the GRL
3691
--echo # and let DROP PROCEDURE continue
3692
SET DEBUG_SYNC= 'mdl_acquire_lock_wait SIGNAL grlwait';
3693
--send FLUSH TABLES WITH READ LOCK
3695
--echo # Connection 1
3697
--echo # Once FLUSH TABLES WITH READ LOCK starts waiting
3698
--echo # DROP PROCEDURE will be waked up and will drop
3699
--echo # procedure. Global read lock will be granted after
3700
--echo # this statement ends.
3702
--echo # Reaping DROP PROCEDURE.
3705
--echo # Connection 2
3707
--echo # Reaping FTWRL.
3711
--echo # Connection 1
3713
SET DEBUG_SYNC= 'RESET';
3719
--echo # Bug#50786 Assertion `thd->mdl_context.trans_sentinel() == __null'
3720
--echo # failed in open_ltable()
3723
--echo # Supress warnings written to the log file
3724
call mtr.add_suppression("Wait on a lock was aborted due to a pending exclusive lock");
3726
DROP TABLE IF EXISTS t1, t2;
3729
connect (con1,localhost,root);
3730
connect (con2,localhost,root);
3731
connect (con3,localhost,root);
3734
CREATE TABLE t1 (i INT);
3735
CREATE TABLE t2 (i INT);
3737
SET @old_general_log= @@global.general_log;
3738
SET @@global.general_log= 1;
3740
SET @old_log_output= @@global.log_output;
3741
SET @@global.log_output= 'TABLE';
3743
SET @old_sql_log_off= @@session.sql_log_off;
3744
SET @@session.sql_log_off= 1;
3746
--echo # connection: con1
3750
--echo # connection: con3
3752
SET @@session.sql_log_off= 1;
3754
--echo # connection: con2
3756
SET DEBUG_SYNC= 'thr_multi_lock_after_thr_lock SIGNAL parked WAIT_FOR go';
3758
# The below statement will block on the debug sync point
3759
# after it gets write lock on mysql.general_log table.
3763
--echo # connection: con3
3765
SET DEBUG_SYNC= 'now WAIT_FOR parked';
3767
--echo # connection: con1
3769
# This statement will block in open_ltable() when
3770
# trying to write into mysql.general_log.
3774
--echo # connection: con3
3776
let $wait_condition=
3777
SELECT COUNT(*) = 1 FROM information_schema.processlist
3778
WHERE state = "Waiting for table level lock" and info = "SELECT 1";
3779
--source include/wait_condition.inc
3780
# The ALTER below will try to abort the statement in connection con1,
3781
# since the latter waits on a table-level lock while having a HANDLER
3782
# open. This will cause mysql_lock_tables() in con1 fail which before
3783
# triggered the assert.
3784
ALTER TABLE t1 ADD COLUMN j INT;
3786
--echo # connection: default
3788
SET DEBUG_SYNC= 'now SIGNAL go';
3790
--echo # connection: con1
3792
--echo # Reaping SELECT 1
3796
--echo # connection: con2
3798
--echo # Reaping SELECT 1
3801
--echo # connection: default
3804
SET DEBUG_SYNC= 'RESET';
3808
SET @@global.general_log= @old_general_log;
3809
SET @@global.log_output= @old_log_output;
3810
SET @@session.sql_log_off= @old_sql_log_off;
3814
--echo # Additional coverage for bug #50913 "Deadlock between
3815
--echo # open_and_lock_tables_derived and MDL". The main test
3816
--echo # case is in lock_multi.test
3819
drop table if exists t1;
3821
set debug_sync= 'RESET';
3822
connect (con50913_1,localhost,root);
3823
connect (con50913_2,localhost,root);
3825
create table t1 (i int) engine=InnoDB;
3827
--echo # Switching to connection 'con50913_1'.
3828
connection con50913_1;
3829
set debug_sync= 'thr_multi_lock_after_thr_lock SIGNAL parked WAIT_FOR go';
3831
--send alter table t1 add column j int
3833
--echo # Switching to connection 'default'.
3835
--echo # Wait until ALTER TABLE gets blocked on a sync point after
3836
--echo # acquiring thr_lock.c lock.
3837
set debug_sync= 'now WAIT_FOR parked';
3838
--echo # The below statement should wait on MDL lock and not deadlock on
3839
--echo # thr_lock.c lock.
3841
--send truncate table t1
3843
--echo # Switching to connection 'con50913_2'.
3844
connection con50913_2;
3845
--echo # Wait until TRUNCATE TABLE is blocked on MDL lock.
3846
let $wait_condition=
3847
select count(*) = 1 from information_schema.processlist
3848
where state = "Waiting for table metadata lock" and
3849
info = "truncate table t1";
3850
--source include/wait_condition.inc
3851
--echo # Unblock ALTER TABLE.
3852
set debug_sync= 'now SIGNAL go';
3854
--echo # Switching to connection 'con50913_1'.
3855
connection con50913_1;
3856
--echo # Reaping ALTER TABLE.
3859
--echo # Switching to connection 'default'.
3861
--echo # Reaping TRUNCATE TABLE.
3863
disconnect con50913_1;
3864
disconnect con50913_2;
3865
set debug_sync= 'RESET';
3870
--echo # Test for bug #50998 "Deadlock in MDL code during test
3871
--echo # rqg_mdl_stability".
3872
--echo # Also provides coverage for the case when addition of
3873
--echo # waiting statement adds several loops in the waiters
3874
--echo # graph and therefore several searches for deadlock
3875
--echo # should be performed.
3877
drop table if exists t1;
3879
set debug_sync= 'RESET';
3880
connect (con1,localhost,root);
3881
connect (con2,localhost,root);
3882
connect (con3,localhost,root);
3884
create table t1 (i int);
3886
--echo # Switching to connection 'con1'.
3891
--echo # Switching to connection 'con2'.
3896
--echo # Switching to connection 'default'.
3898
--echo # Start ALTER TABLE which will acquire SNW lock and
3899
--echo # table lock and get blocked on sync point.
3900
set debug_sync= 'thr_multi_lock_after_thr_lock SIGNAL parked WAIT_FOR go';
3902
--send alter table t1 add column j int
3904
--echo # Switching to connection 'con1'.
3906
--echo # Wait until ALTER TABLE gets blocked on a sync point.
3907
set debug_sync= 'now WAIT_FOR parked';
3909
--send insert into t1 values (1)
3911
--echo # Switching to connection 'con2'.
3914
--send insert into t1 values (1)
3916
--echo # Switching to connection 'con3'.
3918
--echo # Wait until both 'con1' and 'con2' are blocked trying to acquire
3919
--echo # SW lock on the table.
3920
let $wait_condition=
3921
select count(*) = 2 from information_schema.processlist
3922
where state = "Waiting for table metadata lock" and
3923
info = "insert into t1 values (1)";
3924
--source include/wait_condition.inc
3925
--echo # Unblock ALTER TABLE. Since it will try to upgrade SNW to X lock
3926
--echo # deadlock with two loops in waiting graph will occur. Both loops
3927
--echo # should be found and DML statements in both 'con1' and 'con2'
3928
--echo # should be aborted with ER_LOCK_DEADLOCK errors.
3929
set debug_sync= 'now SIGNAL go';
3931
--echo # Switching to connection 'con1'.
3933
--echo # Reaping INSERT. It should end with ER_LOCK_DEADLOCK error and
3934
--echo # not wait indefinitely (as it happened before the bugfix).
3935
--error ER_LOCK_DEADLOCK
3939
--echo # Switching to connection 'con2'.
3941
--echo # Reaping INSERT.
3942
--error ER_LOCK_DEADLOCK
3946
--echo # Switching to connection 'default'.
3948
--echo # Reap ALTER TABLE.
3955
set debug_sync= 'RESET';
3959
--echo # Bug#42643: InnoDB does not support replication of TRUNCATE TABLE
3961
--echo # Ensure that a acquired lock is not given up due to a conflict.
3964
connect (con1,localhost,root,,test,,);
3965
connect (con2,localhost,root,,test,,);
3966
connect (con3,localhost,root,,test,,);
3971
DROP TABLE IF EXISTS t1;
3974
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
3975
INSERT INTO t1 VALUES (1),(2),(3);
3977
--echo # Connection: con1
3979
LOCK TABLES t1 WRITE;
3980
SET debug_sync='upgrade_lock_for_truncate SIGNAL parked_truncate WAIT_FOR go_truncate';
3981
send TRUNCATE TABLE t1;
3984
--echo # Connection: default
3985
SET debug_sync='now WAIT_FOR parked_truncate';
3988
--echo # Connection: con2
3989
SET debug_sync='after_open_table_ignore_flush SIGNAL parked_show WAIT_FOR go_show';
3990
send SHOW FIELDS FROM t1;
3993
--echo # Connection: default
3994
SET debug_sync='now WAIT_FOR parked_show';
3997
--echo # Connection: con3
3998
SET debug_sync='after_flush_unlock SIGNAL parked_flush WAIT_FOR go_flush';
3999
send FLUSH TABLES t1;
4002
--echo # Connection: default
4003
SET debug_sync='now WAIT_FOR parked_flush';
4004
SET debug_sync='now SIGNAL go_truncate';
4005
--echo # Ensure that truncate waits for a exclusive lock
4006
let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist
4007
WHERE state='Waiting for table metadata lock' AND info='TRUNCATE TABLE t1';
4008
--source include/wait_condition.inc
4009
SET debug_sync= 'now SIGNAL go_show';
4012
--echo # Connection: con1 (TRUNCATE)
4018
--echo # Connection: con2 (SHOW FIELDS FROM t1)
4023
--echo # Connection: default
4024
SET debug_sync= 'now SIGNAL go_flush';
4027
--echo # Connection: con3 (FLUSH TABLES t1)
4036
--echo # Connection: default
4037
SET debug_sync= 'RESET';
4042
--echo # Bug#52856 concurrent show columns or show full columns causes a crash!!!
4044
CREATE TABLE t1(a CHAR(255));
4046
connect(con1, localhost, root);
4047
SET DEBUG_SYNC= "get_schema_column SIGNAL waiting WAIT_FOR completed";
4048
--send SHOW FULL COLUMNS FROM t1
4051
SET DEBUG_SYNC= "now WAIT_FOR waiting";
4052
--replace_column 8 #
4053
SHOW FULL COLUMNS FROM t1;
4054
SET DEBUG_SYNC= "now SIGNAL completed";
4055
--replace_column 8 #
4064
--echo # Tests for schema-scope locks
4068
DROP DATABASE IF EXISTS db1;
4069
DROP DATABASE IF EXISTS db2;
4072
connect (con2, localhost, root);
4073
connect (con3, localhost, root);
4076
--echo # CREATE DATABASE blocks database DDL on the same database, but
4077
--echo # not database DDL on different databases. Tests X vs X lock.
4080
--echo # Connection default
4082
SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked';
4084
--send CREATE DATABASE db1
4086
--echo # Connection con2
4088
SET DEBUG_SYNC= 'now WAIT_FOR locked';
4090
# This should block.
4091
--send CREATE DATABASE db1
4093
--echo # Connection con3
4095
let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist
4096
WHERE state='Waiting for schema metadata lock' AND info='CREATE DATABASE db1';
4097
--source include/wait_condition.inc
4098
# This should not block.
4099
CREATE DATABASE db2;
4100
ALTER DATABASE db2 DEFAULT CHARACTER SET utf8;
4102
SET DEBUG_SYNC= 'now SIGNAL blocked';
4104
--echo # Connection default
4106
--echo # Reaping: CREATE DATABASE db1
4109
--echo # Connection con2
4111
--echo # Reaping: CREATE DATABASE db1
4112
--error ER_DB_CREATE_EXISTS
4116
--echo # ALTER DATABASE blocks database DDL on the same database, but
4117
--echo # not database DDL on different databases. Tests X vs X lock.
4120
--echo # Connection default
4122
SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked';
4124
--send ALTER DATABASE db1 DEFAULT CHARACTER SET utf8
4126
--echo # Connection con2
4128
SET DEBUG_SYNC= 'now WAIT_FOR locked';
4130
# This should block.
4131
--send ALTER DATABASE db1 DEFAULT CHARACTER SET utf8
4133
--echo # Connection con3
4135
let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist
4136
WHERE state='Waiting for schema metadata lock'
4137
AND info='ALTER DATABASE db1 DEFAULT CHARACTER SET utf8';
4138
--source include/wait_condition.inc
4139
# This should not block.
4140
CREATE DATABASE db2;
4141
ALTER DATABASE db2 DEFAULT CHARACTER SET utf8;
4143
SET DEBUG_SYNC= 'now SIGNAL blocked';
4145
--echo # Connection default
4147
--echo # Reaping: ALTER DATABASE db1 DEFAULT CHARACTER SET utf8
4150
--echo # Connection con2
4152
--echo # Reaping: ALTER DATABASE db1 DEFAULT CHARACTER SET utf8
4155
--echo # Connection default
4157
SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked';
4159
--send ALTER DATABASE db1 DEFAULT CHARACTER SET utf8
4161
--echo # Connection con2
4163
SET DEBUG_SYNC= 'now WAIT_FOR locked';
4165
# This should also block.
4166
--send DROP DATABASE db1
4168
--echo # Connection con3
4170
let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist
4171
WHERE state='Waiting for schema metadata lock' AND info='DROP DATABASE db1';
4172
--source include/wait_condition.inc
4173
SET DEBUG_SYNC= 'now SIGNAL blocked';
4175
--echo # Connection default
4177
--echo # Reaping: ALTER DATABASE db1 DEFAULT CHARACTER SET utf8
4180
--echo # Connection con2
4182
--echo # Reaping: DROP DATABASE db1
4184
# Recreate the database
4185
CREATE DATABASE db1;
4188
--echo # Two ALTER..UPGRADE of the same database are mutually exclusive, but
4189
--echo # two ALTER..UPGRADE of different databases are not. Tests X vs X lock.
4192
let $MYSQLD_DATADIR= `select @@datadir`;
4193
# Manually make a 5.0 database from the template
4194
--mkdir $MYSQLD_DATADIR/a-b-c
4195
--copy_file $MYSQLD_DATADIR/db1/db.opt $MYSQLD_DATADIR/a-b-c/db.opt
4196
--mkdir $MYSQLD_DATADIR/a-b-c-d
4197
--copy_file $MYSQLD_DATADIR/db1/db.opt $MYSQLD_DATADIR/a-b-c-d/db.opt
4199
--echo # Connection default
4201
SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked';
4203
--send ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME
4205
--echo # Connection con2
4207
SET DEBUG_SYNC= 'now WAIT_FOR locked';
4209
# This should block.
4210
--send ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME
4212
--echo # Connection con3
4214
let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist
4215
WHERE state='Waiting for schema metadata lock'
4216
AND info='ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME';
4217
--source include/wait_condition.inc
4218
# This should not block.
4219
ALTER DATABASE `#mysql50#a-b-c-d` UPGRADE DATA DIRECTORY NAME;
4220
SET DEBUG_SYNC= 'now SIGNAL blocked';
4222
--echo # Connection default
4224
--echo # Reaping: ALTER DATABASE '#mysql50#a-b-c' UPGRADE DATA DIRECTORY NAME
4227
--echo # Connection con2
4229
--echo # Reaping: ALTER DATABASE '#mysql50#a-b-c' UPGRADE DATA DIRECTORY NAME
4230
--error ER_BAD_DB_ERROR
4232
DROP DATABASE `a-b-c`;
4233
DROP DATABASE `a-b-c-d`;
4236
--echo # DROP DATABASE blocks database DDL on the same database, but
4237
--echo # not database DDL on different databases. Tests X vs X lock.
4240
--echo # Connection default
4242
SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked';
4244
--send DROP DATABASE db1
4246
--echo # Connection con2
4248
SET DEBUG_SYNC= 'now WAIT_FOR locked';
4250
# This should block.
4251
--send DROP DATABASE db1
4253
--echo # Connection con3
4255
let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist
4256
WHERE state='Waiting for schema metadata lock' AND info='DROP DATABASE db1';
4257
--source include/wait_condition.inc
4258
# This should not block.
4259
CREATE DATABASE db2;
4260
ALTER DATABASE db2 DEFAULT CHARACTER SET utf8;
4262
SET DEBUG_SYNC= 'now SIGNAL blocked';
4264
--echo # Connection default
4266
--echo # Reaping: DROP DATABASE db1
4269
--echo # Connection con2
4271
--echo # Reaping: DROP DATABASE db1
4272
--error ER_DB_DROP_EXISTS
4275
--echo # Connection default
4277
CREATE DATABASE db1;
4278
SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked';
4280
--send DROP DATABASE db1
4282
--echo # Connection con2
4284
SET DEBUG_SYNC= 'now WAIT_FOR locked';
4286
# This should also block.
4287
--send ALTER DATABASE db1 DEFAULT CHARACTER SET utf8
4289
--echo # Connection con3
4291
let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist
4292
WHERE state='Waiting for schema metadata lock'
4293
AND info='ALTER DATABASE db1 DEFAULT CHARACTER SET utf8';
4294
--source include/wait_condition.inc
4295
SET DEBUG_SYNC= 'now SIGNAL blocked';
4297
--echo # Connection default
4299
--echo # Reaping: DROP DATABASE db1
4302
--echo # Connection con2
4304
--echo # Reaping: ALTER DATABASE db1 DEFAULT CHARACTER SET utf8
4305
# Error 1 is from ALTER DATABASE when the database does not exist.
4306
# Listing the error twice to prevent result diffences based on filename.
4312
--echo # Locked database name prevents CREATE of tables in that database.
4313
--echo # Tests X vs IX lock.
4316
--echo # Connection default
4318
CREATE DATABASE db1;
4319
SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked';
4321
--send DROP DATABASE db1
4323
--echo # Connection con2
4325
SET DEBUG_SYNC= 'now WAIT_FOR locked';
4327
# This should block.
4328
--send CREATE TABLE db1.t1 (a INT)
4330
--echo # Connection con3
4332
let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist
4333
WHERE state='Waiting for schema metadata lock' AND
4334
info='CREATE TABLE db1.t1 (a INT)';
4335
--source include/wait_condition.inc
4336
SET DEBUG_SYNC= 'now SIGNAL blocked';
4338
--echo # Connection default
4340
--echo # Reaping: DROP DATABASE db1
4343
--echo # Connection con2
4345
--echo # Reaping: CREATE TABLE db1.t1 (a INT)
4346
--error ER_BAD_DB_ERROR
4350
--echo # Locked database name prevents RENAME of tables to/from that database.
4351
--echo # Tests X vs IX lock.
4354
--echo # Connection default
4356
CREATE DATABASE db1;
4357
CREATE TABLE db1.t1 (a INT);
4358
SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked';
4360
--send DROP DATABASE db1
4362
--echo # Connection con2
4364
SET DEBUG_SYNC= 'now WAIT_FOR locked';
4366
# This should block.
4367
--send RENAME TABLE db1.t1 TO test.t1
4369
--echo # Connection con3
4371
let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist
4372
WHERE state='Waiting for schema metadata lock' AND
4373
info='RENAME TABLE db1.t1 TO test.t1';
4374
--source include/wait_condition.inc
4375
SET DEBUG_SYNC= 'now SIGNAL blocked';
4377
--echo # Connection default
4379
--echo # Reaping: DROP DATABASE db1
4382
--echo # Connection con2
4384
--echo # Reaping: RENAME TABLE db1.t1 TO test.t1
4385
--error ER_FILE_NOT_FOUND, ER_FILE_NOT_FOUND
4388
--echo # Connection default
4390
CREATE DATABASE db1;
4391
CREATE TABLE test.t2 (a INT);
4392
SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked';
4394
--send DROP DATABASE db1
4396
--echo # Connection con2
4398
SET DEBUG_SYNC= 'now WAIT_FOR locked';
4400
# This should block.
4401
--send RENAME TABLE test.t2 TO db1.t2
4403
--echo # Connection con3
4405
let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist
4406
WHERE state='Waiting for schema metadata lock' AND
4407
info='RENAME TABLE test.t2 TO db1.t2';
4408
--source include/wait_condition.inc
4409
SET DEBUG_SYNC= 'now SIGNAL blocked';
4411
--echo # Connection default
4413
--echo # Reaping: DROP DATABASE db1
4416
--echo # Connection con2
4418
--echo # Reaping: RENAME TABLE test.t2 TO db1.t2
4419
# Error 7 is from RENAME TABLE where the target database does not exist.
4420
# Listing the error twice to prevent result diffences based on filename.
4427
--echo # Locked database name prevents DROP of tables in that database.
4428
--echo # Tests X vs IX lock.
4431
--echo # Connection default
4433
CREATE DATABASE db1;
4434
CREATE TABLE db1.t1 (a INT);
4435
SET DEBUG_SYNC= 'after_wait_locked_schema_name SIGNAL locked WAIT_FOR blocked';
4437
--send DROP DATABASE db1
4439
--echo # Connection con2
4441
SET DEBUG_SYNC= 'now WAIT_FOR locked';
4443
# This should block.
4444
--send DROP TABLE db1.t1
4446
--echo # Connection con3
4448
let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist
4449
WHERE state='Waiting for schema metadata lock' AND info='DROP TABLE db1.t1';
4450
--source include/wait_condition.inc
4451
SET DEBUG_SYNC= 'now SIGNAL blocked';
4453
--echo # Connection default
4455
--echo # Reaping: DROP DATABASE db1
4458
--echo # Connection con2
4460
--echo # Reaping: DROP TABLE db1.t1
4461
--error ER_BAD_TABLE_ERROR
4464
--echo # Connection default
4468
SET DEBUG_SYNC= 'RESET';
4471
--echo # End of tests for schema-scope locks
4475
--echo # Tests of granted global S lock (FLUSH TABLE WITH READ LOCK)
4478
CREATE DATABASE db1;
4479
CREATE TABLE db1.t1(a INT);
4480
connect(con2, localhost, root);
4481
connect(con3, localhost, root);
4483
--echo # Connection default
4485
FLUSH TABLE WITH READ LOCK;
4487
--echo # Connection con2
4489
# IX global lock should block
4490
--send CREATE TABLE db1.t2(a INT)
4492
--echo # Connection default
4494
let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist
4495
WHERE state='Waiting for global read lock'
4496
AND info='CREATE TABLE db1.t2(a INT)';
4497
--source include/wait_condition.inc
4500
--echo # Connection con2
4502
--echo # Reaping CREATE TABLE db1.t2(a INT)
4505
--echo # Connection default
4507
FLUSH TABLE WITH READ LOCK;
4509
--echo # Connection con2
4511
# X global lock should block
4512
--send ALTER DATABASE db1 DEFAULT CHARACTER SET utf8
4514
--echo # Connection default
4516
let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist
4517
WHERE state='Waiting for global read lock'
4518
AND info='ALTER DATABASE db1 DEFAULT CHARACTER SET utf8';
4519
--source include/wait_condition.inc
4522
--echo # Connection con2
4524
--echo # Reaping ALTER DATABASE db1 DEFAULT CHARACTER SET utf8
4527
--echo # Connection default
4529
FLUSH TABLE WITH READ LOCK;
4531
--echo # Connection con2
4533
# S global lock should not block
4534
FLUSH TABLE WITH READ LOCK;
4537
--echo # Connection default
4546
--echo # Bug#56292 Deadlock with ALTER TABLE and MERGE tables
4550
DROP TABLE IF EXISTS t1, t2, m1;
4553
CREATE TABLE t1(a INT) engine=MyISAM;
4554
CREATE TABLE t2(a INT) engine=MyISAM;
4555
CREATE TABLE m1(a INT) engine=MERGE UNION=(t1, t2);
4557
INSERT INTO t1 VALUES (1), (2);
4558
INSERT INTO t2 VALUES (3), (4);
4560
connect(con1, localhost, root);
4561
connect(con2, localhost, root);
4563
--echo # Connection con1
4565
SET DEBUG_SYNC= 'mdl_upgrade_shared_lock_to_exclusive SIGNAL upgrade WAIT_FOR continue';
4567
--send ALTER TABLE m1 engine=MERGE UNION=(t2, t1)
4569
--echo # Connection con2
4571
--echo # Waiting for ALTER TABLE to try lock upgrade
4572
SET DEBUG_SYNC= 'now WAIT_FOR upgrade';
4574
--send DELETE FROM t2 WHERE a = 3
4576
--echo # Connection default
4578
--echo # Check that DELETE is waiting on a metadata lock and not a table lock.
4579
let $wait_condition=
4580
SELECT COUNT(*) = 1 FROM information_schema.processlist
4581
WHERE state = "Waiting for table metadata lock" AND
4582
info = "DELETE FROM t2 WHERE a = 3";
4583
--source include/wait_condition.inc
4584
--echo # Now that DELETE blocks on a metadata lock, we should be able to do
4585
--echo # SELECT * FROM m1 here. SELECT used to be blocked by a DELETE table
4586
--echo # lock request.
4588
--echo # Resuming ALTER TABLE
4589
SET DEBUG_SYNC= 'now SIGNAL continue';
4591
--echo # Connection con1
4593
--echo # Reaping: ALTER TABLE m1 engine=MERGE UNION=(t2, t1)
4595
--echo # Connection con2
4597
--echo # Reaping: DELETE FROM t2 WHERE a = 3
4599
--echo # Connection default
4601
DROP TABLE m1, t1, t2;
4602
SET DEBUG_SYNC= 'RESET';
4607
# Check that all connections opened by test cases in this file are really
4608
# gone so execution of other tests won't be affected by their presence.
4609
--source include/wait_until_count_sessions.inc
added
removed
mysql-test/t/mdl_sync.test
