CODEWITHSUNDEEP
mysqltransactionslocking
artaxerxe
artaxerxe

Reputation: 6411

Statement rollback vs transaction rollback in MySQL

From MySQL docs I get the following (on error codes):

In this statement, it clearly makes difference between statement roll back and transaction roll back. My question is how can a statement be rolled back, and transaction inside which runs does not roll back? Even more, I get such a lock timeout in a transaction, and the full transaction is rolled back. Can someone enlighten me on this?

Upvotes: 1

Views: 1117

Answers (2)

Xenos
Xenos

Reputation: 3507

Accepted answer is fully right and good, but I ended up on the same doc page, made a quick test setup, and since comments are too short, here's that setup if you're willing to test and see the behavior by yourself:

1) Initialize the tables

CREATE TABLE `x` (
    `id` INT(10) UNSIGNED NOT NULL,
    `xx` INT(11) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
INSERT INTO X (id, xx) VALUES (1,NULL),(2,NULL),(3,NULL);

2) Open up a client connection, start a transaction, fire an INSERT to lock a row in the newly create table

START TRANSACTION;
INSERT INTO X (id, xx) VALUES (1, 5) ON DUPLICATE KEY UPDATE xx = VALUES(xx);

Don't commit the transaction

3) Open up a second client connection, start a transaction, and let's fire another INSERT on a non-locked row

START TRANSACTION;
INSERT INTO X (id, xx) VALUES (2, 7) ON DUPLICATE KEY UPDATE xx = VALUES(xx);

Again, don't commit.

So far, if issuing a SELECT * FROM x then client 1 sees "1,5; 2,NULL, 3;NULL" and client 2 sees "1,NULL; 2,7; 3,NULL"

4) Fire a locked INSERT on client 2 and wait until it dies with SQL 1025:

INSERT INTO X (id, xx) VALUES (1, 6) ON DUPLICATE KEY UPDATE xx = VALUES(xx);

Now, if issuing the SELECT * FROM x on client 2, we still see "1,NULL; 2,7; 3,NULL" so the statement was rolled back. If we issue it on client 1, we also still see "1,5; 2,NULL; 3,NULL" so the client 2 transaction is still running (assuming your client is not configured to rollback automatically on every SQL error).

5) Now, if you issue ROLLBACK on both client, the table gets "restored" to all NULL values. If you COMMIT it, then you get "1,5; 2,7, 3,NULL" (the "1,6" is nowhere to be found as it was rolled back, but the client 2 transaction was commited)

Upvotes: 0

Zbynek Vyskovsky - kvr000
Zbynek Vyskovsky - kvr000

Reputation: 18845

Similarly to transaction, statement is atomic as well. Once the statement starts performing the data changes, the original state is saved (actually the changes are recorded). If statement fails for whatever reason (timeout, conflict with just committed transaction) the changes must be reverted to the original state. The statement will report a failure but the transaction is still open and you can continue with the transaction just like the statement was never executed.

This is actually similar to savepoint - you can imagine that before each statement savepoint is recorded and after statement is completed, the savepoint is committed. This has no influence on outer transaction or savepoints however.

Upvotes: 3

Related Questions