Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
5.5, 10.0, 10.1, 10.2.12, 10.2, 10.3
-
OS: CentOS Linux release 7.4.1708 (Core)
Kernel: 3.10.0-693.11.1.el7.x86_64
DB: MariaDB-server-10.2.12-1.el7.centos.x86_64
Description
Create table and Insert initial data.
create table customers (
|
company_id bigint not null, |
customer_id bigint not null, |
primary key (company_id, customer_id)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
|
insert into customers (company_id, customer_id) values
|
(1, 1), (1, 2), (1, 3); |
Set transaction isolation level 'READ COMMITTED' because Deadlock occurs in 'REPEATABLE READ' mode.
tx1) set transaction isolation level read committed;
|
tx2) set transaction isolation level read committed;
|
tx1) start transaction;
|
tx2) start transaction;
|
tx1) select max(customer_id), max(customer_id) from customers where company_id = 1 for update; |
+------------------+------------------+
|
| max(customer_id) | max(customer_id) |
|
+------------------+------------------+
|
| 3 | 3 | |
+------------------+------------------+
|
tx2) select max(customer_id), max(customer_id) from customers where company_id = 1 for update; |
Transaction 2 is blocked.
tx1) insert into customers (company_id, customer_id) values (1, 4); |
tx1) commit;
|
 |
tx2)
|
+------------------+------------------+
|
| max(customer_id) | max(customer_id) |
|
+------------------+------------------+
|
| 3 | 4 | |
+------------------+------------------+
|
After commit Transaction 1, Select query in Transaction 2 is executed. But two max values are not equal.
Expected result is:
+------------------+------------------+
|
| max(customer_id) | max(customer_id) |
|
+------------------+------------------+
|
| 4 | 4 | |
+------------------+------------------+
|