MySQL Bugs: #21694: DELETE FROM is not transaction safe

Bug #21694	DELETE FROM is not transaction safe
Submitted:	17 Aug 2006 10:53	Modified:	21 Aug 2006 11:30
Reporter:	Radek Maciaszek	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	4-5	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any
Tags:	delete from transaction safe

Description:
"DELETE FROM table" deletes all records from table even records from outside its transaction. In other words it deletes records which are inserted in separate transactions. I expect from DELETE to behave in the same way as SELECT. SELECT  doesn't see records from other transactions but DELETE apparently sees it and  remove them. 

I noticed this behaviour on MySQL 4.0.26 and 5.0.22 using InnoDB

How to repeat:
Create example table: 

CREATE TABLE test (
  id tinyint(4),
  PRIMARY KEY  (`id`)
) TYPE=InnoDB;

insert into it sample record:
INSERT INTO test VALUES(1);

Open two connections to database.

Connection 1:
Start transaction. We will try to delete records from this transaction.
mysql>START TRANSACTION;

Connection 2:
Insert new record into table.
mysql>INSERT INTO test VALUES(2);

Connection 1:
We are expecting to see only one record here, lets check it:
mysql> select * from test;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

Indeed record inserted in connection 2 is not visible in this transaction.

But let's delete all records from this transaction:
mysql> DELETE FROM test;
Query OK, 2 rows affected (0.00 sec)
          ^ - it deletes 2 records instead of one!
mysql>COMMIT;

Connection 2:
mysql> select * from test;
Empty set (0.00 sec)

Chnaged to InnoDB category

Radek,

yes, you are right that DELETE FROM should be transaction-safe. If you want fast removal of all records you can use TRUNCATE that is not transaction-safe.

But I am not able to verify this:

heikki@127:~/mysql-5.0/client$ ./mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.25

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table m(a int) engine=innodb;
Query OK, 0 rows affected (0.31 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into m values (10);
Query OK, 1 row affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.03 sec)

mysql> delete from m;
Query OK, 1 row affected (0.01 sec)

mysql>

Another client, run later:

heikki@127:~/mysql-5.0/client$ ./mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.25

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from m;
+------+
| a    |
+------+
|   10 |
+------+
1 row in set (0.01 sec)

mysql>

Please check that your tables really are InnoDB and not MyISAM:

SHOW CREATE TABLE ...

Regards,

Heikki

Hey Heikki,

What you wrote works for me. You are right that records which are deleted inside  the transaction are visible outside this transaction till you commit it. 

However my point is a little bit different. I noticed that if you insert a record to the table _after_ you created a transaction the "DELETE FROM" will delete that record even because it was inserted outside it's transaction.

Basically I noticed that I'm loosing some data and I didn't know why. What I'm doing is dumping data in transaction "START TRANSACTION; SELECT * from table INTO OUTFILE..; DELETE FROM table; COMMIT;" but in the meantime other records are inserted into this table. Looks like that DELETE FROM command deletes also those new records which are inserted between calling "SELECT * FROM INTO OUTFILE.." and "DELETE FROM". Please try to recreate it using the code I sent in last report. connection1 and connection2 are separate clients.

Radek,

but SELECT ... with no lock clause is a non-locking consistent read, and reads historical data. If you want to make sure no one deletes that data meanwhile, you must use

SELECT ... LOCK IN SHARE MODE;

I am marking this as not a bug.

Regards,

Heikki

Hey again Haikki,

So basically SELECT works in default mode as REPEATABLE READ but DELETE always works in READ COMMITTED mode which means it's possible that in the same transaction "DELETE FROM table" will delete more records than I will see using "SELECT * FROM table". Am I right?

cheers
Radek

Looks like it's not a bug maybe but I think it is worth to mention on documentation page that this is an expected behaviour. Anyway just a small reproduction 

                User A                 User B

           SET AUTOCOMMIT=0;      SET AUTOCOMMIT=0;
time
|          SELECT * FROM t;
|          empty set
|                                 INSERT INTO t VALUES (1, 2);
|
v          SELECT * FROM t;
           empty set
                                  COMMIT;

           SELECT * FROM t;
           empty set

                                  SELECT * FROM t;
                                  ---------------------
                                  |    1    |    2    |
                                  ---------------------
                                  1 row in set
                                  
           DELETE FROM t;
           Query OK,
           1 row affected
//         ^ it deletes rows from
//         outside it's transaction
                                  
           COMMIT;

           SELECT * FROM t;
           empty set