Bug #95049 Modified rows are not locked after rolling back to savepoint
Submitted: 18 Apr 2019 7:52 Modified: 22 Apr 2019 9:11
Reporter: John Lin Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.7.25, 8.0.15 OS:MacOS
Assigned to: CPU Architecture:x86
Tags: lock, rollback, savepoint

[18 Apr 2019 7:52] John Lin
Description:
The documentation in https://dev.mysql.com/doc/refman/5.7/en/savepoint.html states that:

> Modifications that the current transaction made to rows after the savepoint was set are undone in the rollback, but InnoDB does not release the row locks that were stored in memory after the savepoint.

However, when I tried to rollback a modified row with "ROLLBACK TO", the modified row is not locked and can be modified directly.

How to repeat:
Assume that there are 2 transactions: left and right, I executed the following SQL commands:

    # connect command
    connect: mysql -ujohnlinp -psecret somedb

    # create table and populate data
    left: DROP TABLE IF EXISTS person;
    left: CREATE TABLE person (id INT NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, age INT NOT NULL, PRIMARY KEY (id));
    left: INSERT INTO person (name, age) VALUES ('John Lin', 29);

    # start left transaction
    left: BEGIN;
    left: SAVEPOINT somepoint;
    left: UPDATE person SET age = 30 WHERE id = 1;
    left: ROLLBACK TO somepoint;

    # start right transaction
    right: BEGIN;
    right: UPDATE person SET age = 40 WHERE id = 1;

I expected the last SQL command on the right should freeze until timeout, but it was successfully executed right away.

Note: the code block above can be executed by duosql: https://github.com/johnlinp/duosql

Suggested fix:
The documentation and the actual behavior seem not matched. However, I am not sure whether we should modify the documentation or the actual behavior. Please advice. Thanks.
[19 Apr 2019 12:14] MySQL Verification Team
Hello John Lin,

Thank you for the report.

regards,
Umesh
[22 Apr 2019 9:11] John Lin
Hi Umesh,

Do you have any idea of whether we should fix the actual behavior or the documentation? Thanks.