Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3.13
-
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.
Attachments
Activity
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.
Please provide the output of SHOW CREATE TABLE and the output which shows the described difference.