MySQL Bugs: #96518: ROLLBACK TO SAVEPOINT does not release locked rows

Bug #96518	ROLLBACK TO SAVEPOINT does not release locked rows
Submitted:	13 Aug 2019 10:33	Modified:	13 Aug 2019 17:25
Reporter:	Tiberiu-Ionut Stan	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	8.0.17	OS:	Any (Tested on CentOS and Windows 10)
Assigned to:		CPU Architecture:	Any
Tags:	rollback savepoint lock transaction

Description:
In MySQL 8.0, ROLLBACK TO SAVEPOINT does NOT release newly inserted locked rows (newly inserted row locked by primary key uniqueness). So when the program executes ROLLBACK TO SAVEPOINT Lock2, the row with PK 'Lock2' incorrectly remains locked. In other words, the row inserted after the savepoint is not rolled back [completely?] by the ROLLBACK TO SAVEPOINT statement.

In MySQL 5.6 ROLLBACK TO SAVEPOINT would have unlocked the primary key index value created by inserting a row after the savepoint.

How to repeat:
Setup:
=========================

ROLLBACK;

CREATE TABLE IF NOT EXISTS `locks` (
	lock_name VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

First MySQL session:
=========================

ROLLBACK;

SET sql_mode = 'STRICT_ALL_TABLES';
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION innodb_lock_wait_timeout = 2147483;

BEGIN;

SAVEPOINT Lock1;
-- The `lock_name`='Lock1' row is locked.
REPLACE INTO `locks` SET `lock_name` = "Lock1";

SAVEPOINT Lock2;
-- The `lock_name`='Lock2' row is locked 
REPLACE INTO `locks` SET `lock_name` = "Lock2";
        
-- MySQL 5.6.45: the `lock_name`='Lock2' row is unlocked.
-- MySQL 8.0.17: the `lock_name`='Lock2' row is NOT unlocked, and should have been unlocked.
ROLLBACK TO SAVEPOINT Lock2;

SELECT SLEEP(100);

ROLLBACK;

The second MySQL session:
=========================

ROLLBACK;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION innodb_lock_wait_timeout = 2147483;

BEGIN;

-- MySQL 5.6.45: Can insert row with PK value "Lock2".
-- MySQL 8.0.17: Cannot insert row with PK value "Lock2", waiting for lock to be released by the first session.
REPLACE INTO `locks` SET `lock_name` = "Lock2";

Suggested fix:
Please ROLLBACK to the MySQL 5.6 behaviour. This is BLOCKING us from using MySQL 8.0 unfortunately because we relied on the [common sense] behaviour of MySQL 5.6.

Hi Mr. Stan,

Thank you for your bug report.

However, I can not see a bug here. That row that is not locked in 5.6 should have been lock, since there is a pending REPLACE. This REPLACE had to be transformed into DELETE and INSERT. Both of these statements require strict locking. There was a bug that was fixed interim 5.6 and 8.0. This fixed the problem.

If you are interested what bug it was, you can go to dev.mysql.com and read the release notes from 5.6 to 8.0.

Not a bug.

How is it a *pending* replace since that row has been rolled back by ROLLBACK TO SAVEPOINT?

Also, it is a new row, not replace is actually happening.

I stand corrected, your docs for MySQL 8.0 say ROLLBACK TO SAVEPOINT will not release locks.
So I guess its not a bug.

Could you promote this as a feature request?

Hi,

We can not have a feature that would go against valid SQL standard(s).