Debian Codegraph @

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

raw
1 SET GLOBAL innodb_lock_wait_timeout=600;
2 DESCRIBE INFORMATION_SCHEMA.INNODB_TRX;
3 Field Type Null Key Default Extra
4 trx_id bigint unsigned NO
5 trx_state varchar(13) NO
6 trx_started datetime NO
7 trx_requested_lock_id varchar(126) YES
8 trx_wait_started datetime YES
9 trx_weight bigint unsigned NO
10 trx_mysql_thread_id bigint unsigned NO
11 trx_query varchar(1024) YES
12 trx_operation_state varchar(64) YES
13 trx_tables_in_use bigint unsigned NO
14 trx_tables_locked bigint unsigned NO
15 trx_lock_structs bigint unsigned NO
16 trx_lock_memory_bytes bigint unsigned NO
17 trx_rows_locked bigint unsigned NO
18 trx_rows_modified bigint unsigned NO
19 trx_concurrency_tickets bigint unsigned NO
20 trx_isolation_level varchar(16) NO
21 trx_unique_checks int NO
22 trx_foreign_key_checks int NO
23 trx_last_foreign_key_error varchar(256) YES
24 trx_adaptive_hash_latched int NO
25 trx_adaptive_hash_timeout bigint unsigned NO
26 trx_is_read_only int NO
27 trx_autocommit_non_locking int NO
28 trx_schedule_weight bigint unsigned YES
29 DROP TABLE IF EXISTS t1;
30 CREATE TABLE t1 (
31 c01 INT,
32 c02 INT,
33 PRIMARY KEY (c01)
34 ) ENGINE=INNODB STATS_AUTO_RECALC=0;
35 INSERT INTO t1 VALUES
36 (1,2),(2,4),(3,6),(4,8);
37 CREATE TABLE t2 (
38 c01 INT,
39 c02 INT,
40 PRIMARY KEY (c01),
41 CONSTRAINT fk1 FOREIGN KEY (c02) REFERENCES t1 (c01)
42 ) ENGINE=INNODB STATS_AUTO_RECALC=0;
43 INSERT INTO t2 VALUES
44 (1,1),(2,2),(3,3);
45 SET autocommit=0;
46 INSERT INTO t1 VALUES (5,10);
47 SELECT * FROM t1 FOR UPDATE;
48 c01 c02
49 1 2
50 2 4
51 3 6
52 4 8
53 5 10
54 SELECT trx_state, trx_weight, trx_tables_in_use, trx_tables_locked,
55 trx_rows_locked, trx_rows_modified, trx_concurrency_tickets,
56 trx_isolation_level, trx_unique_checks, trx_foreign_key_checks
57 FROM INFORMATION_SCHEMA.INNODB_TRX;
58 trx_state trx_weight trx_tables_in_use trx_tables_locked trx_rows_locked trx_rows_modified trx_concurrency_tickets trx_isolation_level trx_unique_checks trx_foreign_key_checks
59 RUNNING 5 0 1 7 1 0 REPEATABLE READ 1 1
60 ROLLBACK;
61 SET FOREIGN_KEY_CHECKS = 0;
62 SET UNIQUE_CHECKS = 0;
63 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
64 BEGIN;
65 INSERT INTO t1 VALUES (6,12);
66 SELECT trx_isolation_level, trx_unique_checks, trx_foreign_key_checks
67 FROM INFORMATION_SCHEMA.INNODB_TRX;
68 trx_isolation_level trx_unique_checks trx_foreign_key_checks
69 SERIALIZABLE 0 0
70 ROLLBACK;
71 SET FOREIGN_KEY_CHECKS = 1;
72 SET UNIQUE_CHECKS = 1;
73 BEGIN;
74 INSERT INTO t2 VALUES (4,10);
75 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk1` FOREIGN KEY (`c02`) REFERENCES `t1` (`c01`))
76 SELECT trx_state, trx_isolation_level, trx_last_foreign_key_error
77 FROM INFORMATION_SCHEMA.INNODB_TRX;
78 trx_state trx_isolation_level trx_last_foreign_key_error
79 RUNNING REPEATABLE READ `test`.`t2`, CONSTRAINT `fk1` FOREIGN KEY (`c02`) REFERENCES `t1` (`c01`)
80 CREATE TABLE t0 (
81 id INT PRIMARY KEY,
82 trx_mysql_thread_id BIGINT
83 ) ENGINE=InnoDB;
84 INSERT INTO t0 VALUES (1, CONNECTION_ID());
85 BEGIN;
86 SELECT * FROM t1 FOR SHARE;
87 c01 c02
88 1 2
89 2 4
90 3 6
91 4 8
92 INSERT INTO t0 VALUES (2, CONNECTION_ID());
93 BEGIN;
94 SELECT * FROM t1 FOR UPDATE;
95 INSERT INTO t0 VALUES (3, CONNECTION_ID());
96 BEGIN;
97 SELECT * FROM t1 FOR SHARE;
98 SELECT t0.id, trx_schedule_weight
99 FROM INFORMATION_SCHEMA.INNODB_TRX
100 JOIN t0 USING(trx_mysql_thread_id)
101 ORDER BY t0.id;
102 id trx_schedule_weight
103 1 NULL
104 2 2
105 3 1
106 ROLLBACK;
107 c01 c02
108 1 2
109 2 4
110 3 6
111 4 8
112 ROLLBACK;
113 c01 c02
114 1 2
115 2 4
116 3 6
117 4 8
118 ROLLBACK;
119 DROP TABLE t2;
120 DROP TABLE t1;
121 DROP TABLE t0;
122 SET GLOBAL innodb_lock_wait_timeout=default;