Debian Codegraph @

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

raw
1 set global innodb_table_locks=1;
2 select @@innodb_table_locks;
3 @@innodb_table_locks
4 1
5 drop table if exists t1;
6 set @@innodb_table_locks=1;
7 create table t1 (id integer, x integer) engine=INNODB;
8 insert into t1 values(0, 0);
9 set autocommit=0;
10 SELECT * from t1 where id = 0 FOR UPDATE;
11 id x
12 0 0
13 set autocommit=0;
14 lock table t1 write;
15 update t1 set x=1 where id = 0;
16 select * from t1;
17 id x
18 0 1
19 commit;
20 update t1 set x=2 where id = 0;
21 commit;
22 unlock tables;
23 select * from t1;
24 id x
25 0 2
26 commit;
27 drop table t1;
28 #
29 # Old lock method (where LOCK TABLE was ignored by InnoDB) no longer
30 # works when LOCK TABLE ... WRITE is used due to fix for bugs #46272
31 # "MySQL 5.4.4, new MDL: unnecessary and bug #37346 "innodb does not
32 # detect deadlock between update and alter table".
33 # After WL#6671 "Improve scalability by not using thr_lock.c locks
34 # for InnoDB tables" was implemented it no longer works for LOCK TABLES
35 # ,,, READ as well.
36 # LOCK TABLES locks are now completely handled by MDL subsystem.
37 #
38 set @@innodb_table_locks=0;
39 create table t1 (id integer primary key, x integer) engine=INNODB;
40 insert into t1 values(0, 0),(1,1),(2,2);
41 commit;
42 SELECT * from t1 where id = 0 FOR UPDATE;
43 id x
44 0 0
45 # Connection 'con2'.
46 set autocommit=0;
47 set @@innodb_table_locks=0;
48 # The following statement should block because SQL-level lock
49 # is taken on t1 which will wait until concurrent transaction
50 # is commited.
51 # Sending:
52 lock table t1 write;;
53 # Connection 'con1'.
54 # Wait until LOCK TABLE is blocked on SQL-level lock.
55 # We should be able to do UPDATEs and SELECTs within transaction.
56 update t1 set x=1 where id = 0;
57 select * from t1;
58 id x
59 0 1
60 1 1
61 2 2
62 # Unblock LOCK TABLE.
63 commit;
64 # Connection 'con2'.
65 # Reap LOCK TABLE.
66 unlock tables;
67 # Connection 'con1'.
68 select * from t1 where id = 0 for update;
69 id x
70 0 1
71 # Connection 'con2'.
72 # The following statement should block because SQL-level lock
73 # is taken on t1 which will wait until concurrent transaction
74 # is commited.
75 # Sending:
76 lock table t1 read;;
77 # Connection 'con1'.
78 # Wait until LOCK TABLE is blocked on SQL-level lock.
79 # We should be able to do UPDATEs and SELECTs within transaction.
80 update t1 set x=2 where id = 0;
81 select * from t1;
82 id x
83 0 2
84 1 1
85 2 2
86 # Unblock LOCK TABLE.
87 commit;
88 # Connection 'con2'.
89 # Reap LOCK TABLE.
90 unlock tables;
91 # Connection 'default'.
92 drop table t1;
93 #
94 #Bug#12842206 INNODB LOCKING REGRESSION FOR INSERT IGNORE
95 #fixed by re-fixing Bug#7975
96 #aka Bug#11759688 52020: InnoDB can still deadlock on just INSERT...
97 #
98 CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL) ENGINE=InnoDB;
99 INSERT INTO t1 VALUES(3,1);
100 BEGIN;
101 INSERT IGNORE INTO t1 VALUES(3,14);
102 Warnings:
103 Warning 1062 Duplicate entry '3' for key 't1.PRIMARY'
104 BEGIN;
105 INSERT IGNORE INTO t1 VALUES(3,23);
106 Warnings:
107 Warning 1062 Duplicate entry '3' for key 't1.PRIMARY'
108 SELECT * FROM t1 FOR UPDATE;
109 COMMIT;
110 a b
111 3 1
112 COMMIT;
113 DROP TABLE t1;
114 Bug#19803418 ASSERTION: TABLE->QUIESCE == QUIESCE_NONE
115 IN ROW0QUIESCE.CC LINE 683
116 CREATE TABLE t1(C TEXT CHARACTER SET UJIS) ENGINE=INNODB;
117 LOCK TABLES t1 WRITE,t1 AS t0 READ,t1 AS t2 READ;
118 FLUSH TABLE t1;
119 LOCK TABLES t1 READ,t1 AS t0 WRITE,t1 AS t2 READ;
120 FLUSH TABLE t1;
121 LOCK TABLES t1 READ,t1 AS t0 READ,t1 AS t2 READ;
122 FLUSH TABLE t1;
123 ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
124 UNLOCK TABLES;
125 FLUSH TABLE t1;
126 DROP TABLE t1;