1

DB Config

  • DB version: 8.0.23

  • Isolation Level: REPEATABLE READ

I found a strange behavior in the locking mechanism and I'm trying to understand the difference between 2 flows.

Flow 1:

Imagine you have 2 connections that operates in parallel:

Connection1:

START TRANSACTION;
INSERT INTO my_table (id,name) VALUES (101,'test');

Connection2:

START TRANSACTION;
SET @id = (SELECT id FROM my_table where id = 101);

NOTE:

  • In this flow (Flow 1) connection1 locks the row that have id=101 and connection2 will wait until connection1 will close the transaction.

Flow 2:

Imagine you have 2 connections that operates in parallel:

Connection1:

START TRANSACTION;
INSERT INTO my_table (id,name) VALUES (101,'test');

Connection2:

START TRANSACTION;
SET @id = NULL;
SELECT id INTO @id from my_table where id = 101;

NOTE:

  • In this flow (Flow 2) connection1 locks the row that have id=101 and connection2 will NOT wait for connection1 to close the transaction (We will just get NULL because of the isolation level).

So I'm trying to understand what is the difference between these 2 flows Why connection2 wait when using "SET" but doesn't wait when using "SELECT ... INTO ..."

FYI - For some reason it seems that using "SET" this way also locks the table (Flow1)

Thanks in advance!!

Tomerz
  • 93
  • 1
  • 9
  • See e.g. [In MySQL Why does setting a variable from a select acquire a lock when using read uncommitted?](https://stackoverflow.com/q/13151837) and the discussion in the bug report (which suggests the workaround you found too.) – Solarflare Aug 06 '22 at 10:39
  • If `Id` is `AUTO_INCREMENT`, why are you specifying the value? – Rick James Aug 06 '22 at 16:30
  • With default isolation mode and no `FOR UPDATE`, conn 2 cannot see 101 until conn 1 Commits. – Rick James Aug 06 '22 at 16:31

0 Answers0