[MDEV-18697] Table is locked for update with Row Constructor Expression Optimization Created: 2019-02-22  Updated: 2023-04-27

Status: Open
Project: MariaDB Server
Component/s: Locking, Optimizer
Affects Version/s: 10.3.13
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Major
Reporter: Oleksandr Porunov Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: lock, row, table, transactions
Environment:

CentOS 7.3


 Description   

`Row Constructor Expression Optimization` doesn't work as expected. During the raw update with `Row Constructor Expression Optimization` the whole table is locked.

This query locks the whole table with `repeatable read` transaction (even so it updates a single row).

update mytable set mycolumn=321 where (left_pk, right_pk) = (123, 456);

This query works as expected and locks only a single row with `repeatable read` transaction:

update mytable set mycolumn=321 where left_pk = 123 and right_pk = 456;

Expected result:
Both queries should lock only a single row.

An example which shows the bug:

1. Step one. Create table:

 
 
CREATE TABLE IF NOT EXISTS mytable (
 
  left_pk bigint,
 
  right_pk bigint,
 
  mycolumn bigint,
 
  PRIMARY KEY (left_pk, right_pk)
 
) ENGINE = InnoDB;

2. Step two. insert values into table:

INSERT INTO mytable(left_pk, right_pk, mycolumn) values(1,1,1);
 
INSERT INTO mytable(left_pk, right_pk, mycolumn) values(2,2,2);
 
INSERT INTO mytable(left_pk, right_pk, mycolumn) values(3,3,3);

3. Step three. Set repeatable read transaction isolation level for the session 1:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

4. Step four. Open another session with database and set repeatable read transaction isolation level for the session 2:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

5. Step five. Begin a new transaction in the session 1 and update a single raw using "row constructor expression optimization":

start transaction;
 
 
 
update mytable set mycolumn=444 where (left_pk, right_pk) = (1, 1);

6. Step six. Go into session 2 and execute an update query for another raw:

update mytable set mycolumn=555 where (left_pk, right_pk) = (2, 2);

As a result step 6 is locked and will through the lock timeout exception after the timeout (by default 60 seconds):

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Expected behavior:
Step 5 should not acquire locks for all raws. Step 6 should be processed immediately without a deadlock while transaction in step 5 is open.

 Comments   
Comment by Elena Stepanova [ 2019-02-27 ]

Please provide the output of SHOW CREATE TABLE and the output which shows the described difference.

Comment by Oleksandr Porunov [ 2019-02-28 ]

I've added asked information into the description. Duplicate information just in case:

An example which shows the bug:

1. Step one. Create table:

CREATE TABLE IF NOT EXISTS mytable (
left_pk bigint,
right_pk bigint,
mycolumn bigint,
PRIMARY KEY (left_pk, right_pk)
) ENGINE = InnoDB;

2. Step two. insert valudes into table:

INSERT INTO mytable(left_pk, right_pk, mycolumn) values(1,1,1);
INSERT INTO mytable(left_pk, right_pk, mycolumn) values(2,2,2);
INSERT INTO mytable(left_pk, right_pk, mycolumn) values(3,3,3);

3. Step three. Set repeatable read transaction isolation level for the session 1:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

4. Step four. Open another session with database and set repeatable read transaction isolation level for the session 2:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

5. Step five. Begin a new transaction in the session 1 and update a single raw using "row constructor expression optimization":

start transaction;

update mytable set mycolumn=444 where (left_pk, right_pk) = (1, 1);

6. Step six. Go into session 2 and execute an update query for another raw:

update mytable set mycolumn=555 where (left_pk, right_pk) = (2, 2);

As a result step 6 is locked and will through the lock timeout exception after the timeout (by default 60 seconds):

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Expected behavior:
Step 5 should not acquire locks for all raws. Step 6 should be processed immediately without a deadlock while transaction in step 5 is open.

Generated at Thu Feb 08 08:46:03 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.