DDL in transactions

from the Artful MySQL Tips List


DDL statements within transactions issue implicit COMMITs:
drop table if exists t;
create table t(i int primary key auto_increment, j int);
insert into t (j) values(0);
select * from t;
+---+------+
| i | j    |
+---+------+
| 1 |    0 |
+---+------+

-- client 1 drops, recreates & inserts, but does not commit
begin work;
drop table if exists t;
create table t(i int primary key auto_increment, j int);
insert into t (j) values(1);
select * from t;
+---+------+
| i | j    |
+---+------+
| 1 |    1 |
+---+------+

-- client 2 can see result of client 1's changes even though
-- client 1 has not explicitly committed them: 
select * from t;
+---+------+
| i | j    |
+---+------+
| 1 |    1 |
+---+------+
Ditto for TRUNCATE, which MySQL implements as DROP then CREATE.



Return to the Artful MySQL Tips page