Statement rollback vs transaction rollback in MySQL

Question

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

• 1205 (ER_LOCK_WAIT_TIMEOUT)

  Lock wait timeout expired. The statement that waited too long was rolled back (not the entire transaction). You can increase the value of the innodb_lock_wait_timeout configuration option if SQL statements should wait longer for other transactions to complete, or decrease it if too many long-running transactions are causing locking problems and reducing concurrency on a busy system.

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?

Answer 1

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.

Answer 2

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)