database - Transactions not working for my MySQL DB

Question

I'm using a MySQL DB for my site, which is hosted on a Linux shared server.

I wrote a test script which I run using 'mysql' to test if transactions are working ok. Running the script, I do not get any error, but the result of executing the scripts is as if transaction is not enabled.

I also made sure to grant ALL privileges to the admin MySQL user which runs the script.

In order to double check, I tried the same test script on PostgreSQL, and there - the result of the script indicated that transaction does work. So it's definitely something which is specific to MySQL.

The script runs on a simple table which I created as follows:

create table a ( id serial primary key);

Following is the test script:

delete from a;
set autocommit = 0;
start transaction;
insert into a(id) values(1);
rollback work;
select count(*) from a;

So the script makes sure the table is empty, Then it starts a transaction, insert a row and rollback the insert. As the "insert" is rolled back, the "select" should indicate that table contains 0 rows.

Running this on PostgreSQL:

$ psql db admin < test1
DELETE 0
START TRANSACTION
INSERT 0 1
ROLLBACK
count
-------
     0

This is the expected behavior, 0 rows in the table as the insert was rolled back.

Running the same on my MySQL DB:

$ mysql db -u admin < test1
count(*)
1

Having 1 row following the rollback indicate that the "insert" was not rolled back, just as in non-transaction mode.

As mentioned, admin is granted with ALL privileges to the DB.

Anything I've missed?

Answer 1

Probably the table is created with the MyISAM storage engine as default. MyISAM storage engine doesnt support transactions.

Create table

CREATE TABLE a ( id SERIAL PRIMARY KEY) ENGINE = MYISAM;

Query

DELETE FROM a;
SET autocommit = 0;
START TRANSACTION;
INSERT INTO a(id) VALUES(1);
ROLLBACK WORK;
SELECT COUNT(*) FROM a;

Result

count(*)

     1

Making the table InnoDB

Query

ALTER TABLE a ENGINE=INNODB; 

Query

DELETE FROM a;
SET autocommit = 0;
START TRANSACTION;
INSERT INTO a(id) VALUES(1);
ROLLBACK WORK;
SELECT COUNT(*) FROM a;

Result

count(*)  
----------
         0