CODEWITHSUNDEEP
mysql
Rahul G
Rahul G

Reputation: 1

Is Deletion of rows work differently in Mysql 8.0.28 when isolation level changed from default "Repeatable Read" to "Read Committed"?

Question:

Is Deletion of rows work differently in Mysql 8.0.28 when isolation level changed from default "Repeatable Read" to "Read Committed" ?

Environment: RDS Mysql 8.0.28 Multi-AZ Enabled

Summary:

When we use the default isolation level i.e "Repeatable Read", delete query fails to get lock on a table & it gets killed automatically as a deadlock victim.

When we change the isolation level from "Repeatable Read" to "Read Committed", the same delete query gets a lock on the table & it deletes the rows in approx 2 minutes.

Note: Table has approx 7 million records & each delete transaction deletes approx. 1 million records. Also, this table is continously used for read purpose(Select statement). This table has 7 indices on it & has no partition.

Query: DELETE FROM testdb.operationtable WHERE StartTime <= CURTIME() - INTERVAL 20 DAY;

When we use the default isolation level i.e "Repeatable Read", delete query fails to get lock on a table & it gets killed automatically as a deadlock victim.

When we change the isolation level from "Repeatable Read" to "Read Committed", the same delete query gets a lock on the table & it deletes the rows in approx 2 minutes.

Upvotes: 0

Views: 79

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562310

Yes, there is a difference. In most cases, gap locks are not required in READ-COMMITTED level. It's less likely that a given query will need to lock a gap that conflicts with a gap locked by another concurrent session.

Here's a relevant section of the MySQL manual: https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-gap-locks

Upvotes: 0

Related Questions