MySQL is not ACID compliant
MySQL 5.5.6 is not ACID compliant, here is an example:
mysql> drop table if exists mytable1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> create table mytable1 (idontfeellucky int) ;
Query OK, 0 rows affected (0.44 sec)
mysql> show create table mytable1;
+——-+—————————————————————————————-+
| Table | Create Table |
+——-+—————————————————————————————-+
| mytable1 | CREATE TABLE `mytable1` (
`idontfeellucky` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+——-+—————————————————————————————-+
1 row in set (0.00 sec)
mysql> insert into mytable1 values (1);
Query OK, 1 row affected (0.90 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+—————-+
| Tables_in_test |
+—————-+
| exmpl_tbl |
| mytable1 |
| users |
+—————-+
3 rows in set (0.00 sec)
mysql> select * from mytable1;
+——+
| idontfeellucky |
+——+
| 1 |
+——+
1 row in set (0.00 sec)
mysql> select version();
+———–+
| version() |
+———–+
| 5.5.6-rc |
+———–+
1 row in set (0.00 sec)
If you need your relational sql database to be ACID compliant in areas like this, you should consider other options such as PostgreSQL which is also free but fully supports this feature. Or, you could go with DB2, Oracle etc.
Thanks,
Brad Arrington, Unix/MySQL/PostgreSQL DBA
You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
DDL statements include an implicit commit.
When you create the table, this commits the transaction you started earlier.
MySQL command line operates in two modes, AUTOCOMMIT=0 and AUTOCOMMIT=1
There is now no open transaction. So, the subsequent behaviour (INSERT, ROLLBACK, SELECT) depends on your autocommit mode.
In the above example, you are running with “SET AUTOCOMMIT=1″. The INSERT is auto-committed. The ROLLBACK has no effect, as it is run in its own transaction.
If you “SET AUTOCOMMIT=0″ before running this script, you’ll get different behaviour. The INSERT will implicitly start a new transaction. The ROLLBACK will revert it, and the SELECT will fetch no rows.