1

I was just checking the articles regarding the MySQL table locking system. I wanted to learn about how we can avoid the deadlocks in MySQL while UPDATE/DELETE operations.

What I have done so far:

Setup

CREATE TABLE `new_table` (
  `id` int NOT NULL AUTO_INCREMENT,
  `a` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx` (`a`)
);

START TRANSACTION;
truncate new_table;
insert into new_table(a) values(2),(3),(4),(5),(10),(11),(15),(19),(20),(25),(27),(35);
COMMIT;
  • Trial 1:

STEP 1:

* SESSION 1:
    START TRANSACTION;
    delete from new_table where a in (2, 3, 4);
    insert into new_table (a) values (55);
    // Both the queries will be executed. (NOT COMMITTED YET)

* SESSION 2:
    START TRANSACTION;
    delete from new_table where a in (5, 10);insert into new_table (a) values(105);
    // waiting for lock

* SESSION 3:
    START TRANSACTION;
    insert into new_table (a) values(7); delete from new_table where a in (11, 15);
    // waiting for lock

STEP 2:

* SESSION 1:
    COMMIT;

* SESSION 2:
    ** DEADLOCK HERE **

* SESSION 3:
    // Both the queries will be executed. (NOT COMMITTED YET)

You see that there is deadlock in SESSION 2. What I think is, it is due to GAP locking in the MySQL (I might be wrong, correct me if I am wrong here).

To avoid Gap locking, I changed the GLOBAL Transaction isolation to READ COMMITTED (default is REPEATABLE READ). But still the same issue.

  1. Trial 2:

STEP 1:

* SESSION 1:
    SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
    START TRANSACTION;
    delete from new_table where a in (2, 3, 4);
    insert into new_table (a) values (55);
    // Both the queries will be executed. (NOT COMMITTED YET)

* SESSION 2:
    SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
    START TRANSACTION;
    delete from new_table where a in (5, 10);insert into new_table (a) values(105);

* SESSION 3:
    SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
    START TRANSACTION;
    insert into new_table (a) values(7); delete from new_table where a in (11, 15);

STEP 2:

* SESSION 1:
    COMMIT;

* SESSION 2:
    // Both the queries will be executed. (NOT COMMITTED YET)

* SESSION 3:
    ** DEADLOCK HERE **

Notice the change, the deadlock shifted to the SESSION 3. As you can see, there are no overlapping rows which I am trying to delete. But still there is the deadlock anyhow.

When I try to execute above delete queries with PRIMARY KEY in WHERE clause, everything just works fine.

Can anyone explain me what is happening here? How can we handle such situations and avoid the deadlocks in MySQL?

EDIT: Adding the results of EXPLAIN ANALYZE

  • Query: EXPLAIN ANALYZE SELECT * from new_table where a in (2, 3, 4);

** With index column

-> Filter: (new_table.a in (2,3,4))  (cost=1.45 rows=4) (actual time=0.059..0.069 rows=3 loops=1)
    -> Covering index scan on new_table using idx  (cost=1.45 rows=12) (actual time=0.048..0.060 rows=12 loops=1)

** After removing index from the column

-> Filter: (new_table.a in (2,3,4))  (cost=1.45 rows=4) (actual time=0.034..0.043 rows=3 loops=1)
    -> Table scan on new_table  (cost=1.45 rows=12) (actual time=0.031..0.037 rows=12 loops=1)

** With PK

  • Query: EXPLAIN ANALYZE SELECT * from new_table where id in (2, 3, 4);
-> Filter: (new_table.id in (2,3,4))  (cost=1.36 rows=3) (actual time=0.040..0.050 rows=3 loops=1)
    -> Index range scan on new_table using PRIMARY over (id = 2) OR (id = 3) OR (id = 4)  (cost=1.36 rows=3) (actual time=0.038..0.047 rows=3 loops=1)
12
  • "When I try to execute above delete queries with PRIMARY KEY in WHERE clause, everything just works fine." - What does the EXPLAIN ANALYZE show for each query (both when you use the primary key and when you don't)? This sounds like when you use column a you may be getting an index scan (or similar) operation that results in it needing to touch more rows than just the one's you're deleting, perhaps resulting in a larger lock. Whereas with the primary key you're likely getting a seek operation, which would potentially result in a smaller lock. The EXPLAIN ANALYZE should show this.
    – J.D.
    Aug 7, 2022 at 13:07
  • I have tried applying an index to column a and without it. Same results with both of them. Aug 7, 2022 at 13:39
  • @J.D. BTW, I have added the response from EXPLAIN ANALYZE queries. Aug 7, 2022 at 14:06
  • When you use SET GLOBAL, it does not affect current sessions. It affects subsequent sessions. Read the section "Transaction Characteristic Scope" in dev.mysql.com/doc/refman/8.0/en/set-transaction.html. Aug 7, 2022 at 14:29
  • @BillKarwin I have tried setting GLOBAL and SESSION both the isolation scopes multiple times. I can see no effect of them. And anyways, setting isolation scope to GLOBAL is not the solution of my problem, I was just playing around it. Aug 7, 2022 at 14:38

1 Answer 1

1

Comparing a VARCHAR to a numeric literal is a pitfall that I sometimes see in SELECT ... WHEREs. You seem to be experiencing the same thing in UPDATE...WHEREs.

The varchar is converted to numeric before testing. This tends to cause full table (or index) scans. In the case of UPDATEs, that tends to lead to locking all the rows in the table.

Quoting the literals would also solve the problem.

WHERE vc = 2   -- bad
WHERE int = 2   -- good
WHERE vc = "2"   -- good
WHERE int = "2"   -- good (the literal is interpreted as a number)

Fix the datatypes (as you noted) in order to fix the problem. Or is there still a case?

Functions and sargability

  • WHERE column = function(constant) may be able to use an index on column
  • WHERE function(column) = anythingcannot use an index on column. See sargable
  • WHERE column + INTERVAL 7 DAY > NOW() cannot use an index; the + is essentially a "function" in this context
  • WHERE FROM_UNIXTIME(column) > CURDATE() - INTERVAL 3 MONTH cannot use an index because of the [builtin] function FROM_UNIXTIME. The right-hand side is a "constant expression" that is evaluated before executing the query; hence not a problem.
6
  • What about having a Datetime column and in the query comparing it against the Long (Timestamp) value? Example: WHERE dateTimeToMilliSec(<date time col>) >= [some epoch timestamp]. Will this still lock all the rows as in the case of comparing INT value against the VARCHAR column? Aug 8, 2022 at 7:30
  • Is dateTimeToMilliSec a BIGINT in the table definition? Then it is like INT in my list of 4 things.
    – Rick James
    Aug 8, 2022 at 23:56
  • dateTimeToMilliSec is a function which will convert DATETIME value to BIGINT value (i.e. Epoch TimeStamp) Aug 9, 2022 at 8:47
  • So let's say I have a DATETIME column in the database table, but I want to compare it against the Epoch Timestamp, and I choose to convert DATETIME instead of converting Epoch Timestamp to DATETIME, then will it work? Aug 9, 2022 at 8:49
  • And also, another question: if I execute same delete queries with INDEX columns in WHERE condition, from multiple sessions where transactions are still not committed, then it does not give any deadlock (though all the queries are exactly same). But if the column in WHERE clause are not INDEX, then it gives the deadlock. I am not able to understand why this happens. Aug 9, 2022 at 10:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.