CODEWITHSUNDEEP
mariadbinnodbdeadlock
Emosewaj
Emosewaj

Reputation: 35

Weird deadlocks on simple queries

I receive data from a remote server via HTTP POST requests in JSON format which I save for later processing in a database table. Occasionally I encounter deadlock errors doing so. The data is unique per an identifier, so before inserting a new data set, I delete any old ones already present in the table. To avoid data loss in this process, I use a transaction, so the queries look something like this:

BEGIN TRANSACTION;
DELETE FROM ChangeEvent WHERE rId = 'someid';
INSERT INTO ChangeEvent (records, rId) VALUES ('...', 'someid');
COMMIT;

The ChangeEvent table has an index on an auto-increment id column and another index on the rId column.

During the insertion, this sometimes causes a deadlock, where it appears that two datasets for the same id get inserted at the same time.

------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-06-11 13:59:38 0x7f9a6804a700
*** (1) TRANSACTION:
TRANSACTION 7915, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 20, OS thread handle 140301147133696, query id 35 172.20.0.4 db3 Update
INSERT INTO `ChangeEvent` (`records`, `rId`)
VALUES ('[]', '153181')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 256 page no 4 n bits 72 index rId of table `usr`.`ChangeEvent` trx id 7915 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 7916, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 21, OS thread handle 140301146826496, query id 36 172.20.0.4 db3 Update
INSERT INTO `ChangeEvent` (`records`, `rId`)
VALUES ('...', '153181')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 256 page no 4 n bits 72 index rId of table `usr`.`ChangeEvent` trx id 7916 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 256 page no 4 n bits 72 index rId of table `usr`.`ChangeEvent` trx id 7916 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

From my understanding of transactions and deadlocks, this shouldn't be happening. As far as I can tell, it seems the issue is connected to the rId index, as without it, the issue stops happening, however, I fear that the DELETE query could slow down significantly without the index, as occasionally the number of datasets in the table could go up to ten thousand.

My questions are

  1. What is causing these deadlocks in the first place?
  2. What can I do to avoid them whilst keeping the queries fast?

Upvotes: 0

Views: 100

Answers (3)

Rick James
Rick James

Reputation: 142503

REPLACE INTO ChangeEvent (records, rId) VALUES ('...', 'someid');

Is an atomic DELETE + INSERT. Yes, it will create an new auto_inc id.

Upvotes: 0

Diego Dupin
Diego Dupin

Reputation: 1348

Why not get ride of explicit transaction, using ON DUPLICATE KEY ? like:

INSERT INTO ChangeEvent (records, rId) VALUES ('...', 'someid') ON DUPLICATE KEY UPDATE records='...';

This will insert data, or if keys already exists, update value.

Upvotes: 2

Magmatic
Magmatic

Reputation: 1949

You could try locking, like this, so only one process is allowed to execute those lines at one time. (This is untested.) The "30" is the number of seconds to wait for the lock to be available if another process has it.

SELECT GET_LOCK('mylock', 30);
BEGIN TRANSACTION;
DELETE FROM ChangeEvent WHERE rId = 'someid';
INSERT INTO ChangeEvent (records, rId) VALUES ('...', 'someid');
COMMIT;
SELECT RELEASE_LOCK('mylock');

Also see documentation here: https://mariadb.com/kb/en/get_lock/

Upvotes: -1

Related Questions