0
  • 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

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.

Why this happens?

Rick James
  • 135,179
  • 13
  • 127
  • 222
percy
  • 1
  • 1
  • It looks like a bug, please report it at https://jira.mariadb.org – elenst Feb 05 '18 at 15:31
  • If possible, check on MySQL and provide a bug at bugs.mysql.com . This would also isolate whether it is in the base code, or some edit by MariaDB. – Rick James Feb 19 '18 at 15:58
  • Thanks for comments. I tested on mysql 5.7.21, and it was the same result. I reported to mariadb community (MDEV-15417). Please someone report this bug to mysql community if needed. – percy Feb 25 '18 at 01:35

0 Answers0