MySQL Bugs: #100369: Confusing use of rollback in manual page on deadlock detection

Bug #100369	Confusing use of rollback in manual page on deadlock detection
Submitted:	29 Jul 2020 20:31	Modified:	20 Aug 2020 17:05
Reporter:	Bill Karwin (Candidate Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S4 (Feature request)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
"When InnoDB performs a complete rollback of a transaction, all locks set by the transaction are released. However, if just a single SQL statement is rolled back as a result of an error, some of the locks set by the statement may be preserved."

This is confusing because as far as I know, individual SQL statements are not rolled back from errors. An SQL statement may fail due to an error, which leaves the transaction active and therefore locks survive.

Also the words are vague in that they describe "some" locks "may" be preserved. This leads to questions: which locks will be preserved? 

How to repeat:
Read documentation.

Suggested fix:
"When InnoDB performs a complete rollback of a transaction, all locks set by the transaction are released. However, if just a single SQL statement fails as a result of an error, the transaction is not rolled back, so locks acquired by the statement are preserved."

Apologies, I forgot to link to the manual page:

https://dev.mysql.com/doc/refman/8.0/en/innodb-deadlock-detection.html

When a statement executes such as:
insert into table xyz values ('a','b','c'); and 'c' generates a unique key violation, the values 'a' and 'b' have to be rolled back because statements are atomic.  In a non-atomic storage engine, the values 'a', and 'b' will be inserted but not 'c'.  Thus statements do indeed have to roll back to the state at the start of the statement, in order to maintain atomicity.

As to which locks will be preserved, InnoDB locks all the index entries traversed by a SQL statement and may take GAP locks.  Any such locks obtained by the statement won't be released by the rollback of the statement.  If secondary indexes are not defined, essentially all of the rows that were traversed in an UPDATE statement will have been locked up until the location where the statement triggered the error.

For example:
create table t1(c1 int primary key, c2 int) engine=innodb;
begin;
insert into t1 values (9), (6), (5), (4);
commit;
begin;
update t1 set c1 = c1 - 1 order by c1 desc;

This update statement will error out when it encounters the value (5) because it will trigger a duplicate key error.  The locks on the first three rows will be retained when the UPDATE completes but the fourth record will not be locked.

sorry, insert into t1 (c1) values ...

Sorry, again, the statement will error when it hits the second row with the insertions I gave, so the first and second row will remain locked.  I meant insert into t1 (c1) values (9), (7), (5), (4) to be consisting with the above explanation.

As for the 'may' language, it depends on if the statement is part of an implicit transaction (ie, autocommit) or an explicit transaction started with BEGIN.  In the former case, all locks are released as the transaction closes when the statement terminates.

Hi Bill, Justin,

Thank you for the bug reports and comments.

I think that documentation is 100 % correct in its description. However, I think that, in light of the facts that Justin has expounded upon, more verbosity would not hurt.

Verified as reported.

Funny, I was reading this documentation few days ago myself and also got confused about why this description of partial rollback (while accurate) is even mentioned at all in the article about deadlock detection?
As far as I know this can not happen as part of deadlock resolution, so why confuse the readers about something irrelevant to the topic of deadlocks?

Thank you, Jakub,

I think that we all agree on that point.

Posted by developer:
 
The reference content was moved an appropriate location:
START TRANSACTION, COMMIT, and ROLLBACK Statements
https://dev.mysql.com/doc/refman/8.0/en/commit.html

The text was revised as follows:

"When InnoDB performs a complete rollback of a transaction, all locks set
by the transaction are released. If a single SQL statement within a
transaction rolls back as a result of an error, such as a duplicate key
error, locks set by the statement are preserved while the transaction
remains active. This happens because InnoDB stores row locks in a format
such that it cannot know afterward which lock was set by which statement."

Changes should appear online soon.

Thank you for the bug report.

Thank you, Daniel.