Russ Brown <pickscrape@gmail.com> writes:
> Harpreet Dhaliwal wrote:
>> Whats so different in postgresql then?
> Try doing the same test in MySQL (using InnoDB so you get a supposedly
> ACID compliant table type).
> Or even in Oracle.
Examples (using mysql 5.0.40, reasonably current):
mysql> create table t1 (f1 int) engine = innodb;
Query OK, 0 rows affected (0.05 sec)
mysql> insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+
| f1 |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values(2);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+
| f1 |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
+------+
| f1 |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
[ okay, so we can roll back an INSERT properly ]
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> drop table t1;
Query OK, 0 rows affected (0.01 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist
[ oops, DROP TABLE isn't transactional ]
mysql> create table t1 (f1 int) engine = innodb;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values(2);
Query OK, 1 row affected (0.00 sec)
mysql> create table t2 (f2 int) engine = innodb;
Query OK, 0 rows affected (0.01 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t2;
Empty set (0.00 sec)
[ so CREATE TABLE isn't transactional, and what's more, now
the INSERT wasn't either: ]
mysql> select * from t1;
+------+
| f1 |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
So it appears that mysql works just like Oracle on this point:
a DDL operation forces an immediate COMMIT.
regards, tom lane