I have a concurrency problem where 2 (or more) processes are making inserts at the same time and are all locking the same index page (I guess) among with a on a SELECT FOR UPDATE clause Here is a reproducible sample: CREATE TABLE U ( -- user id int not null primary key, name varchar(222) ); CREATE TABLE O ( -- object id int not null primary key, name varchar(222) ); CREATE TABLE OU ( -- user object id int not null auto_increment primary key, object_id int, user_id int, -- (object_id, user_id) should be UNIQUE (not enforced by a constraint) CONSTRAINT fk_object_id FOREIGN KEY (object_id) REFERENCES O(id) ON DELETE CASCADE, CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES U(id) ON DELETE CASCADE ); INSERT INTO U VALUES (1, 'foo'), (2, 'bar'); -- Start 2 concurrent transactions (A, B) BEGIN; -- A BEGIN; -- B INSERT INTO O VALUES (1, 'a'); -- A INSERT INTO O VALUES (2, 'b'); -- B SAVEPOINT s_a; -- A SAVEPOINT s_b; -- B SELECT * FROM OU WHERE object_id = 1 FOR UPDATE; -- A SELECT * FROM OU WHERE object_id = 2 FOR UPDATE; -- B INSERT INTO OU(object_id, user_id) VALUES (1, 1); -- A INSERT INTO OU(object_id, user_id) VALUES (2, 1); -- B one of the transactions will fail with a Deadlock found when trying to get lock; try restarting transaction error. The problem is that it rolls back the **whole** transaction without any way for me to gracefully handle the error. In that example, the object 2 is lost. I would **really** like for it to still persist and handle the second insert a few seconds / minutes after another transaction. The way the code is currently designed does not allow fine tuning of the transaction block (I cannot commit after the first insert then start another transaction) Is there any way to tell MySQL to not rollback on that deadlock error, or to rollback specifically to the savepoint? **edit** here is the innodb engine status *** (1) TRANSACTION: TRANSACTION 3727668996, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1 MySQL thread id 20145497, OS thread handle 0x7f4fdba2b700, query id 15334836096 xxxxxxxxxxxxx.xxxx.xxx.xxxx.xxx 10.150.15.33 user1 update INSERT INTO Tablename (product_id, product_type_id, contact_id, last_transaction_id, creation_date) VALUES (655988, 1, 26399, NULL, '2017-04-20 19:04:21') *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 201 page no 1012 n bits 840 index product_id of table dbname.Tablename trx id 3727668996 lock_mode X locks gap before rec insert intention waiting *** (2) TRANSACTION: TRANSACTION 3727668998, ACTIVE 0 sec inserting, thread declared inside InnoDB 5000 mysql tables in use 1, locked 1 3 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1 MySQL thread id 20145647, OS thread handle 0x7f5d6d253700, query id 15334836100 xxxxxxxxxxxxx.xxxx.xxx.xxxx.xxx 10.150.15.35 user1 update INSERT INTO Tablename (product_id, product_type_id, contact_id, last_transaction_id, creation_date) VALUES (655991, 1, 26399, NULL, '2017-04-20 19:04:21') *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 201 page no 1012 n bits 840 index product_id of table dbname.Tablename trx id 3727668998 lock_mode X locks gap before rec *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 201 page no 1012 n bits 840 index product_id of table dbname.Tablename trx id 3727668998 lock_mode X locks gap before rec insert intention waiting *** WE ROLL BACK TRANSACTION (2)

I have a problem with locking and not timing out in one of my Oracle databases. This session locks occur on certain transactions that come from Oracle Forms users. For instance, when some user requests to make a change on a locked object the transaction waits for the locked object to be released, but it waits indefinetely until killed by alter session. I've tried the same in my testing database and it does kill the locked session after 60s, so I thought it has to be a configuration issue. I've checked the v$parameters DISTRIBUTED_LOCK_TIMEOUT and it is set to 60s in both databases. Does anyone know where is the "locking session/transaction timeout" set? Any help is greatly appreciated.

=2023-09-15 15:16:35 0x1530b7bc17002023-09-15 15:16:35 0x1530b7bc1700
*** (1) TRANSACTION:
TRANSACTION 55060584750, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 8 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 1
MySQL thread id 10640581, OS thread handle 23400148567808, query id 455015502 10.1.13.206 admin update
INSERT IGNORE INTO product_config (merchant_id,config_path,value) VALUES ('58541','product_access_token','456670849.oo0Dm0YB-oV73AqMvcEf6Ysm3'),('58541','product_refresh_token','456670849.jxeCmCd8Sgvit7QlZLBTmqBjLO4dW-XsYv'),('58541','product_access_token_secret','456670849.jxeCmCd8SgvxSJS4-QlZLBTmqBjLO4dW-XsYv'),('58541','token_expire_time',1694790995) ON DUPLICATE KEY UPDATE value= VALUES(value)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3075 page no 6615 n bits 0 index PRIMARY of table marketplace_integration.product_config trx id 55060584750 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len=8; bufptr=0x153946e98070; hex= 73757072656d756d; asc supremum;;
*** (2) TRANSACTION:
TRANSACTION 55060584749, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 9 lock struct(s), heap size 1136, 7 row lock(s), undo log entries 1
MySQL thread id 10640966, OS thread handle 23400144643840, query id 455015500 10.1.10.11 admin update
INSERT IGNORE INTO product_config (merchant_id,config_path,value) VALUES ('112043','product_access_token','290096243.BFg3suqIMxIpb5u18AnXG41piKXKxJ7ZzHSXMBflaN8iRqER4-W7WUf-jr'),('112043','product_refresh_token','290096243.LPs2VNdIuI6xniq71DsMG5xXFXPnv8Y'),('112043','product_access_token_secret','290096243.LPs2VNdIuxXFXPnv8Y'),('112043','token_expire_time',1694790995) ON DUPLICATE KEY UPDATE value= VALUES(value)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3075 page no 6615 n bits 0 index PRIMARY of table marketplace_integration.product_config trx id 55060584749 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len=8; bufptr=0x153946e98070; hex= 73757072656d756d; asc supremum;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3075 page no 6615 n bits 0 index PRIMARY of table marketplace_integration.product_config trx id 55060584749 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len=8; bufptr=0x153946e98070; hex= 73757072656d756d; asc supremum;;
*** WE ROLL BACK TRANSACTION (1)

MySQL version 5.7 Table structure-

CREATE TABLE product_config (
  id int(11) NOT NULL AUTO_INCREMENT,
  merchant_id int(11) DEFAULT NULL,
  config_path varchar(255) DEFAULT NULL,
  value text,
  updated_at_new datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  created_at_new datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY post_product_config_idx (merchant_id,config_path),
  KEY merchant_id (merchant_id),
  CONSTRAINT fk_product_config_merchant_id FOREIGN KEY (merchant_id) REFERENCES product_shop_details (merchant_id) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1168788710 DEFAULT CHARSET=utf8

I am getting four deadlocks in an hour after the reboot of the MySQL server.

I have a SQL Managed Instance in Azure with some blocking/deadlocking going on. This DB was on-premise and had all the scripts installed, so I uninstalled them and then installed the Azure specific scripts (Install-Azure.sql), downloaded fresh from Brent Ozar's website. All the scripts seem to work ok, except sp_blitzlock. SpBlitz indeed confirms "94 average deadlocks per day. To find them, run sp_BlitzLock." I am running it as a system administrator, and both main tables are blank. (when I ran it as my EntraID account, I got a permissions issue: Msg 50000, Level 11, State 1, Procedure sp_blitzlock, Line 335 [Batch Start Line 0] A session with the name system_health does not exist or is not currently active. I do not get any error when running it as an instance sysadmin. The data is just blank. It's possible there's no data, but I doubt it given what we've observed elsewhere and what sp_blitz confirmed. I downloaded the latest First Responder Kit, and am using sp_BlitzLock version 8.21 and sp_Blitz version Jul 1 2024 12:00AM. This is a SQL Managed Instance in Azure, instance version is 12.0.2000.8. I am just executing sp_blitzlock; in SSMS to run it. It may be worth pointing out we recently upgraded the service tier from General Purpose to Business Critical. That overall improved performance and locking, but I'm wondering if it triggered a new instance behind the scene which is why no deadlocking data appears. (Although why would it show up in sp_blitz?) Any thoughts?

I have a repeating deadlock in my production environment. I cannot reproduce it in staging. Script 1 is a scheduled data import procedure. -- Create and populate new_TableImported if object_id('old_TableImported') is not null drop table old_TableImported set transaction isolation level serializable; set xact_abort on; begin try begin tran if object_id('TableImported') is not null exec sp_rename 'TableImported', 'old_TableImported' exec sp_rename 'new_TableImported', 'TableImported' commit end try begin catch if (xact_state() 0) rollback tran; throw; end catch set transaction isolation level read committed; if object_id('old_TableImported') is not null drop table old_TableImported Script 2 is a scheduled data export procedure: just select from a view. CREATE VIEW [export].[vSomeDataNeededOutside] AS select t.*, e.SomeField from dbo.OneOfMyTables t left join ( select ... from TableImported group by ... ) e on ... where ... According to data collected with SQL Server Profiler. 1. Select (2) starts. Sch-S lock is obtained on TableImported. 1. Data import (1) runs up to sp_rename block. Sch-M lock is requested, and the query waits. 1. (2) for some reason requests additional Sch-S lock on TableImported. 1. Deadlock. (1) waits (2) to release the first Sch-S lock, (2) waits (1) to acquire the second Sch-S lock. What is going on? Why do select try to obtain the second lock of the same type on the same table?

I have two machines, M1 and M2, through which I am connecting to **MySQL** Database on machine M3 using Perl script (DBI.pm). So, here is the scenario that I am confused about: M1 places a table level lock, and performs some operations. M2 also does the same. Table level locking mechanism is guarantying me that both of them will not be locking the table at the same time. But, what if M1 has locked the table, and suddenly after performing half hte updates, the machine crashes or the Perl script process crashes, what happens to the lock then? My updates were performed mid-way, do those get rolled back? And what is the best way to deal with these kind of locks?

I'm learning how to interpret the LATEST DETECTED DEADLOCK section in the output of SHOW ENGINE INNODB STATUS, and had a question about the output. Some sample output of SHOW ENGINE INNODB STATUS from our DB: ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2015-09-29 17:00:30 7f9eb7410700 *** (1) TRANSACTION: TRANSACTION 2842247584, ACTIVE 0 sec, thread declared inside InnoDB 4998 mysql tables in use 1, locked 1 LOCK WAIT 5 lock struct(s), heap size 1184, 26 row lock(s) MySQL thread id 69517881, OS thread handle 0x7f9ee07bd700, query id 11929163563 cfsender01.cf.intela.local 192.168.20.116 cfsender01 updating *** (1) WAITING FOR THIS LOCK TO BE GRANTED: ... Notice that there is no SQL statement listed in transaction (1). Does anyone know why this might happen, or what the lack of a sql statement in the show engine innodb status would indicate about the transaction? FWIW, transaction (1) was chosen as the victim by MySQL and rolled back. Also, we're running MySQL Server version: 5.6.19-67.0-log Percona Server (GPL), Release 67.0, Revision 618.

I'm bulk updating some rows using UPDATE ... FROM (values ... as described in this answer . Will the rows will be locked in the order that they appear in the values? Or do I need to do an explicit SELECT FOR UPDATE first? Here's an example statement

UPDATE stats as t set "individualCount" = new_values."individualCount"
  FROM (values (6::int,7::int),(3::int,15::int))
  as new_values("individualCount","id") 
  WHERE new_values."id" = t."id"

(id is the primary key of the table)

I'm getting occasional deadlocks on temp tables in two different connections (at least as far as I can tell, the deadlock graph shows different spids). The deadlock graph (lightly redacted, and with the rest of truncated queries added in) is below. I don't understand how this is possible, because temp tables shouldn't be shared between different connections. These are NOT global temp tables (as you can see). So they shouldn't be able to deadlock. Also confusing, and possibly related, one of the temp table names listed in the deadlock graph is a random string of letters and numbers, which isn't referenced in the stored procedure that is running, like "#B825DAD5". Also a little odd, and again possibly related, the lock mode causing the problem is Sch-M, but there are no schema modifications made to any tables or temp tables in the query. I suppose creating and dropping temp tables could count as schema modifications? But again, they are in different connections. Temp tables are created with CREATE, not SELECT INTO, if that matters. My top suspects right now are either a bug in SQL Server or something strange because there are some nolocks on some of the tables in the query. But I would think someone on the internet would have seen this issue before, and I've found nothing like this in the searches I've tried. There are some references to bugs in SQL server that caused deadlocks between temp tables, but those bus were fixed over ten years ago, and they didn't look exactly the same anyway. Has anyone seen this kind of thing? Can anyone explain those weird temp table names? Does anyone know a workaround or fix? UPDATE t SET Adv = 1 FROM dbo.SQ sq (nolock) JOIN #tmpSDTR t on t.SQUUID = sq.SQUUID and t.DeliverySequence = sq.DeliverySequence JOIN dbo.SQQMetaData m (nolock) on m.SQUUID = t.SQUUID and m.DeliverySequence = t.DeliverySequence WHERE m.Name = 'Adv' and m.Value = 1 Proc [Database Id = 7 Object Id = 1716201164] update t set SQQueueID = q.SQQueueId, SUUID = q.SUUID, SRUUID = r.SRUUID, ProjectKey = q.ProjectID, L = q.L, MQPID = q.MQPId, StatusID = q.StatusID, [Priority] = q.[Priority], CreatedDate = q.CreatedDate, RTID = r.RTID, OrdinalPosition = r.DisplayOrdinal, ProcessID = @ProcessID, SRMetaDataName = md.Name, SRMetaDataValue = md.Value --select * from #tmpSDTR t JOIN dbo.SQQueue q on q.SQUUID = t.SQUUID LEFT OUTER JOIN dbo.SRQueue r on r.SQUUID = q.SQUUID AND r.DeliverySequence = q.DeliverySequence LEFT OUTER JOIN dbo.SRQMetaData md on r.SRUUID = md.SRUUID AND md.DeliverySequence = r.DeliverySequence where t.PCId is null Proc [Database Id = 7 Object Id = 1716201164] **UPDATE 2025/04/07** It looks like we are behind a touch on cumulative updates (we're on 28, current is 32), so we'll be trying to get that upgraded ASAP, but I can't say yet when that will happen. If we get the error again after the CU, I'll post another update here. The stored proc has a bunch of different execution plans listed when I query sys.dm_exec_procedure_stats/sys.dm_exec_query_plan. There were 13 the last time I checked. I downloaded all of them, and they were all identical, EXCEPT for the table name in one small section: You can see that odd temp table name there, and it is the same in all of the plans, EXCEPT for one, where it instead said: Table = "[tmpSDTR_____________________________________________________________________________________________000000001FF8]" There were just 6 executions of this plan, compared to thousands to millions for most other plans. The query referenced in this section is not one of the two that trigger the deadlock, so I don't know if this is related. Anyway, since the plans are pretty much all identical, I don't think parameter sniffing is causing different execution plans. **UPDATE 2025/04/09** Well, we updated to the latest Cumulative Update this morning, and now we have a new mysterious deadlock, but at least it doesn't involve temp tables in different connections. Assuming we don't see this weird deadlock again soon, I'm ready to close out this chapter, and if Martin Smith feels like posting his suggestion as an answer, I'd be happy to accept it.

To replace the content of a test database from a production database mysqldump output the following command was used: cat mysqldump-db-thisdate.sql | mysql -p ... mydb There has never been any issue with this command. However the DB grew a lot and this command takes several minutes. In order to reduce this time, a Perl script was written that - takes the mysqldump output as input - creates a single file having all DROP TABLE ... CREATE TABLE for each table - run this drop-creation file on a single thread, before doing the tables feeding below - creates as many files (see below) as there are tables (about 100 tables) - makes a fork() for each table file that is injected into the DB (all tables are dropped and created + fed in "parallel". table1..100 The DROP-CREATION file is something like DROP TABLE IF EXISTS mytable1; CREATE TABLE mytable1 ( someid1 int NOT NULL, ... PRIMARY KEY (someid1) ) ENGINE=InnoDB AUTO_INCREMENT=111 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; DROP TABLE IF EXISTS mytable2; CREATE TABLE mytable2 ( someid2 int NOT NULL, ... PRIMARY KEY (someid2) ) ENGINE=InnoDB AUTO_INCREMENT=222 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; ... Each table file mytableI.sql is like this, for instance for mytable1.sql /*!40103 SET TIME_ZONE='+00:00' */; SET foreign_key_checks = 0; SET unique_checks = 0; SET autocommit = 0; START TRANSACTION; LOCK TABLES mytable1 WRITE; INSERT INTO mytable1 VALUES (...),(...),...; UNLOCK TABLES; COMMIT; It's like doing, in parallel (pseudo code) for each table 1 to 100 do cat mytableI.sql | mysql -p ... mydb /* I is 1 ... 100 */ end for This method works very well, and saves from 50% to 75% of the time compared to the simple cat whole-dump | mysql usual method. However, from time to time (maybe 1 / 10), doing this parallel method, mysql throws an error > Deadlock found when trying to get lock; try restarting transaction It happens rarely, so just restarting the command is not a big deal. But why? Each table is processed at once, foreign keys are not checked... Doesn't MySQL, thanks to "LOCK TABLES" (and other mechanisms) protect itself against deadlocks in this case? *Addendum*: The mydb test database is not being accesses otherwise. ***edit testing other methods*** Trying to perform the DROP / CREATE operations in parallel, (each DROP / CREATE in the same thread, for each table), not even filling the tables with data, plenty of Deadlocks occur... Could it be that MySQL does not handle very well DROP/CREATE operations performed simultaneously? (should be done by a single DB admin?) Note: "*simultaneously*" and "*in parallel*" meaning each thread has its own MySQL connection.

I have came across this term ***Orphan locks*** and ***Orphan sessions*** and I really have *no idea* what that is. I have tried to *search* it on *Google* and I still haven't got any useful info/results regarding what it mean. *All I get till now is that orphan locks are some kind of session locks which results in deadlocks after executing query or queries.* (please correct me if I get it wrong) *Can someone please explain this a bit ? What are Orphan Locks ? Are they the same as Orphan sessions ? How do they form and what they have to do with sessions ?* p.s: I am still newbie to this so I apologise if it sounds all too basic.

I'm getting the deadlock on MySQL v8.0.32 which is very strange for me because of the following: 1. I'm trying to insert the data: parent-child record (users, user_roles) - each transactions inserts new user and record in join table with some role. 2. The index that is causing deadlock (on user_roles) is not unique and I see that gap lock is not used. 3. Sequence of statements is the same and not like in classical deadlock example (when T1 changes table A, then B and T2 vice versa - table B than table A)

CREATE TABLE user_roles (
  id int NOT NULL AUTO_INCREMENT,
  role_id int DEFAULT NULL,
  user_id int DEFAULT NULL,
  context_type varchar(255) NOT NULL,
  context_id int NOT NULL,
  created_by int DEFAULT NULL,
  updated_by int DEFAULT NULL,
  deleted_at datetime DEFAULT NULL,
  created_at datetime DEFAULT NULL,
  updated_at datetime DEFAULT NULL,
  PRIMARY KEY (id),
  KEY fk_rails_dcd6720bf9 (created_by),
  KEY index_user_roles_on_deleted_at (deleted_at),
  KEY index_user_roles_on_role_id (role_id),
  KEY fk_rails_3c5703b833 (updated_by),
  KEY index_user_roles_on_user_id (user_id),
  CONSTRAINT fk_rails_318345354e FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
  CONSTRAINT fk_rails_3369e0d5fc FOREIGN KEY (role_id) REFERENCES roles (id) ON DELETE CASCADE,
  CONSTRAINT fk_rails_3c5703b833 FOREIGN KEY (updated_by) REFERENCES users (id),
  CONSTRAINT fk_rails_dcd6720bf9 FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB AUTO_INCREMENT=450 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

The code that is executed concurrently is the following (simplified):

BEGIN;

INSERT INTO users ...;
DELETE FROM user_roles WHERE user_roles.user_id = 'id of inserted user' AND user_roles.context_type = 'Trial' AND user_roles.context_id = 'some constant id'
INSERT INTO user_roles (role_id, user_id, context_type, context_id, created_at, updated_at) VALUES (...)


COMMIT;

Deadlock information:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-10-10 16:49:52 0x70000ed4b000
*** (1) TRANSACTION:
TRANSACTION 23884460, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 10 lock struct(s), heap size 1128, 6 row lock(s), undo log entries 3
MySQL thread id 1862, OS thread handle 123145570869248, query id 1067189 localhost 127.0.0.1 root update
INSERT INTO user_roles (role_id, user_id, context_type, context_id, created_at, updated_at) VALUES (85, 397, 'Trial', 6, '2023-10-10 13:49:52', '2023-10-10 13:49:52')

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 841074 page no 9 n bits 496 index index_user_roles_on_user_id of table tap-prototype-copy.user_roles trx id 23884460 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;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 841074 page no 9 n bits 496 index index_user_roles_on_user_id of table tap-prototype-copy.user_roles trx id 23884460 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 23884464, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 10 lock struct(s), heap size 1128, 6 row lock(s), undo log entries 3
MySQL thread id 1864, OS thread handle 123145565544448, query id 1067336 localhost 127.0.0.1 root update
INSERT INTO user_roles (role_id, user_id, context_type, context_id, created_at, updated_at) VALUES (85, 398, 'Trial', 6, '2023-10-10 13:49:52', '2023-10-10 13:49:52')

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 841074 page no 9 n bits 496 index index_user_roles_on_user_id of table tap-prototype-copy.user_roles trx id 23884464 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 841074 page no 9 n bits 496 index index_user_roles_on_user_id of table tap-prototype-copy.user_roles trx id 23884464 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)

As we can see we're inserting data for different users (397, 398) and gap locking is not used. So what is the reason for the deadlock then? Seems like a very common and simple situation, but I can't understand what can we improve here. Thanks in advance! --------- **UPDATE**: in respond to Rick James: 1. By joint (join) tables I mean user_roles table as it join (many-to many) users and roles. 2. Yeah it was a simplistic description, I omitted the following detail. So no SELECT .. FOR UPDATE, but I have in between DELETE, eg:

DELETE FROM user_roles WHERE user_roles.user_id = 398 AND user_roles.context_type = 'Trial' AND user_roles.context_id = 64

I also updated pseudo code above. When I remove this DELETE - deadlock goes away. Looks like a root of the problem, but for some reason it is not mentioned in deadlock report. Were you inserting user rows and getting back 397 and 398 for user_id? - yes, insert into users is the first statement. 3. I can't get rid of id in favor of composite primary key - and it doesn't look like it can help here... ----- **UPDATE (2023-10-17)** I was able to explore locks that these statements produce and discovered that supremum pseudo-record lock is utilized. This is what I've found in documentation : > The supremum is not a real index record, so, in effect, this next-key lock locks only the gap following the largest index value Strange that gap locks are not mentioned in deadlock report, but this explanation looks like an explanation here: because we are adding new user record and insert data into related join table user_roles. This involves incrementing user ID - as well as largest index value in user_roles. This is how I treat the docs. If this is the case, then I'm frustrated by the fact that we can't workaround the deadlocks in such very simple situation...

mysql> select * from performance_schema.data_locks \G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140575951646488:851842:140575916454504
ENGINE_TRANSACTION_ID: 24023562
            THREAD_ID: 4671
             EVENT_ID: 11
        OBJECT_SCHEMA: tap-test
          OBJECT_NAME: user_roles
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140575916454504
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140575951646488:851850:140575916454416
ENGINE_TRANSACTION_ID: 24023562
            THREAD_ID: 4671
             EVENT_ID: 9
        OBJECT_SCHEMA: tap-test
          OBJECT_NAME: users
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140575916454416
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 140575951646488:850032:9:1:140576159006240
ENGINE_TRANSACTION_ID: 24023562
            THREAD_ID: 4671
             EVENT_ID: 11
        OBJECT_SCHEMA: tap-test
          OBJECT_NAME: user_roles
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: index_user_roles_on_user_id
OBJECT_INSTANCE_BEGIN: 140576159006240
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
3 rows in set (0.00 sec)

I have a stored procedure that is running concurrently on schedule and is causing 100's of deadlocks a day. The transaction is managed by the application and the database is using Read-Committed Snapshot Isolation. The SQL that is deadlocking is UPDATE dbo.Job SET IsCalculated = 0 WHERE RfJobStatusID = 2 AND LineID IN (SELECT LineID FROM dbo.Line WHERE PlantID = @nJobPlantID) AND IsCalculated = 1; giving plan The deadlock graph is Deadlock XML is xml_report unknown unknown Proc [Database Id = 6 Object Id = 1160807643] UPDATE dbo.Job SET IsCalculated = 0 WHERE RfJobStatusID = 2 AND LineID IN (SELECT LineID FROM dbo.Line WHERE PlantID = @nJobPlantID) AND IsCalculated = unknown Proc [Database Id = 6 Object Id = 1160807643] Index is CREATE NONCLUSTERED INDEX IM_RfJobStatusID_IsCalculated ON dbo.Job(RfJobStatusID ASC,IsCalculated ASC) INCLUDE(WOID,EstimatedEndDT,LineID,EstimatedStartDT) And table structure is CREATE TABLE dbo.Job( JobID bigint NOT NULL, WOID bigint NOT NULL, RfJobStatusID tinyint NOT NULL, JobQualityStatusID tinyint NULL, DeadlineDT datetimeoffset(7) NOT NULL, ActualStartDT datetimeoffset(7) NOT NULL, ActualEndDT datetimeoffset(7) NOT NULL, PlannedStartDT datetimeoffset(7) NOT NULL, PlannedEndDT datetimeoffset(7) NOT NULL, PlannedDuration float NOT NULL, EstimatedStartDT datetimeoffset(7) NOT NULL, EstimatedEndDT datetimeoffset(7) NOT NULL, EstimatedDuration float NOT NULL, ScheduledSequence smallint NOT NULL, LineID int NOT NULL, ProductionStartDT datetimeoffset(7) NOT NULL, ProductionEndDT datetimeoffset(7) NOT NULL, TargetCycleTime float NULL, TargetCycleTimeQuantity float NOT NULL, TargetManPower float NULL, TargetSetup float NULL, TargetTearDown float NULL, TargetFixedTime float NULL, QuantityOrdered float NOT NULL, IsCalculated tinyint NOT NULL, LineGroupID int NOT NULL, PassName nvarchar(50) NOT NULL, WorkOrderReleaseStateID int NOT NULL, ProductionCampaignID bigint NULL, CampaignSequence smallint NULL, IsFirstInSequence tinyint NULL, IsLastInSequence tinyint NULL, ProductionSequence smallint NULL, DeadlineLocalDT AS (CONVERT(datetime2,DeadlineDT)) PERSISTED, ActualStartLocalDT AS (CONVERT(datetime2,ActualStartDT)) PERSISTED, ActualEndLocalDT AS (CONVERT(datetime2,ActualEndDT)) PERSISTED, PlannedStartLocalDT AS (CONVERT(datetime2,PlannedStartDT)) PERSISTED, PlannedEndLocalDT AS (CONVERT(datetime2,PlannedEndDT)) PERSISTED, EstimatedStartLocalDT AS (CONVERT(datetime2,EstimatedStartDT)) PERSISTED, EstimatedEndLocalDT AS (CONVERT(datetime2,EstimatedEndDT)) PERSISTED, ProductionStartLocalDT AS (CONVERT(datetime2,ProductionStartDT)) PERSISTED, ProductionEndLocalDT AS (CONVERT(datetime2,ProductionEndDT)) PERSISTED, CONSTRAINT PK_Job PRIMARY KEY CLUSTERED (JobID ASC)) GO ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_RfJobStatusID DEFAULT ((1)) FOR RfJobStatusID GO ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_DeadlineDT DEFAULT (CONVERT(datetimeoffset,'2050-12-31T00:00:00+00:00',(126))) FOR DeadlineDT GO ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_ActualStartDT DEFAULT (CONVERT(datetimeoffset,'2050-12-31T00:00:00+00:00',(126))) FOR ActualStartDT GO ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_ActualEndDT DEFAULT (CONVERT(datetimeoffset,'2050-12-31T00:00:00+00:00',(126))) FOR ActualEndDT GO ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_PlannedStartDT DEFAULT (CONVERT(datetimeoffset,'2050-12-31T00:00:00+00:00',(126))) FOR PlannedStartDT GO ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_PlannedEndDT DEFAULT (CONVERT(datetimeoffset,'2050-12-31T00:00:00+00:00',(126))) FOR PlannedEndDT GO ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_PlannedDuration DEFAULT ((0)) FOR PlannedDuration GO ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_EstimatedStartDT DEFAULT (CONVERT(datetimeoffset,'2050-12-31T00:00:00+00:00',(126))) FOR EstimatedStartDT GO ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_EstimatedEndDT DEFAULT (CONVERT(datetimeoffset,'2050-12-31T00:00:00+00:00',(126))) FOR EstimatedEndDT GO ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_EstimatedDuration DEFAULT ((0)) FOR EstimatedDuration GO ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_ScheduledSequence DEFAULT ((0)) FOR ScheduledSequence GO ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_LineID DEFAULT ((1)) FOR LineID GO ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_ProductionStartDT DEFAULT (CONVERT(datetimeoffset,'2050-12-31T00:00:00+00:00',(126))) FOR ProductionStartDT GO ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_ProductionEndDT DEFAULT (CONVERT(datetimeoffset,'2050-12-31T00:00:00+00:00',(126))) FOR ProductionEndDT GO ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_TargetCycleTimeQuantity DEFAULT ((1)) FOR TargetCycleTimeQuantity GO ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_QuantityOrdered DEFAULT ((0)) FOR QuantityOrdered GO ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_IsCalculated DEFAULT ((0)) FOR IsCalculated GO ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_LineGroupID DEFAULT ((1)) FOR LineGroupID GO ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_PassName DEFAULT ('1') FOR PassName GO ALTER TABLE dbo.Job ADD CONSTRAINT DF_Job_WorkOrderReleaseStateID DEFAULT ((1)) FOR WorkOrderReleaseStateID GO ALTER TABLE dbo.Job WITH CHECK ADD CONSTRAINT FK_Job_Line FOREIGN KEY(LineID) REFERENCES dbo.Line (LineID) ON UPDATE CASCADE GO ALTER TABLE dbo.Job CHECK CONSTRAINT FK_Job_Line GO ALTER TABLE dbo.Job WITH CHECK ADD CONSTRAINT FK_Job_LineGroup FOREIGN KEY(LineGroupID) REFERENCES dbo.LineGroup (LineGroupID) ON UPDATE CASCADE GO ALTER TABLE dbo.Job CHECK CONSTRAINT FK_Job_LineGroup GO ALTER TABLE dbo.Job WITH CHECK ADD CONSTRAINT FK_Job_ProductionCampaign FOREIGN KEY(ProductionCampaignID) REFERENCES dbo.ProductionCampaign (ProductionCampaignID) `ON UPDATE CASCADE GO ALTER TABLE dbo.Job CHECK CONSTRAINT FK_Job_ProductionCampaign GO ALTER TABLE dbo.Job WITH CHECK ADD CONSTRAINT FK_Job_RfJobStatus FOREIGN KEY(RfJobStatusID) REFERENCES dbo.RfJobStatus (RfJobStatusID) GO ALTER TABLE dbo.Job CHECK CONSTRAINT FK_Job_RfJobStatus GO ALTER TABLE dbo.Job WITH CHECK ADD CONSTRAINT FK_Job_WO FOREIGN KEY(WOID) REFERENCES dbo.WO (WOID) GO ALTER TABLE dbo.Job CHECK CONSTRAINT FK_Job_WO GO ALTER TABLE dbo.Job WITH CHECK ADD CONSTRAINT FK_Job_WorkOrderReleaseState FOREIGN KEY(WorkOrderReleaseStateID) REFERENCES dbo.WorkOrderReleaseState (WorkOrderReleaseStateID) ON UPDATE CASCADE GO ALTER TABLE dbo.Job CHECK CONSTRAINT FK_Job_WorkOrderReleaseState GO Is anyone able to point me in the right direction or give me some hints on how I maybe able to fix or at least reduce the number of deadlocks?

I'm working on some dead-lock troubleshooting, particularly involving a row-swap. Currently the statements look like this, all running under the same transaction: SELECT IDENTIFIER FROM tables WHERE (TABLEID = 4 OR TABLEID = 5) AND DATE = "2015-10-31" FOR UPDATE; /* Would imagine this would lock both of these rows.*/ UPDATE tables SET LINK=0 WHERE DATE = "2015-10-31" AND LINK = 5; UPDATE tables SET TABLEID = -1 WHERE TABLEID = 5 AND DATE = "2015-10-31"; UPDATE tables SET TABLEID = 5 WHERE TABLEID = 4 AND DATE = "2015-10-31"; /* Deadlock on 2nd connection occurs here */ UPDATE tables SET TABLEID = 4 WHERE TABLEID = -1 AND DATE = "2015-10-31"; COMMIT; /* Would imagine this would release locks and let the next transaction continue */ If I run two of these transactions at the same time (run query 1 in connection ONE, query 1 in connection TWO, then proceed with 2-3-4-5 in connection ONE) I get a deadlock every time on connection TWO. Why? Should the first query not lock BOTH rows returned (in this case there are *always* two) and then not let the later transaction perform any SELECTS for these two rows until the first transaction has COMMIT'ed Bear in mind the table name is tables in this case, just to avoid any confusion. Indexes look like: IDENTIFIER (primary, AI integer) LINK (regular index) DATE (regular index) TABLEID+DATE (unique composite, in that order) I can circumvent the issue by locking the entire table (LOCK TABLES tables WRITE, UNLOCK TABLES), but I'm trying to avoid an entire table lock. MySQL 5.6.23, default isolation level (repeatable read) In response to answer: If FOR UPDATE does not prevent other FOR UPDATEs to lock the same row, how come this happens: Connection 1: SELECT IDENTIFIER FROM tables WHERE (TABLEID = 4 OR TABLEID = 5) AND DATE = "2015-10-31" FOR UPDATE; /* THIS WILL WAIT */ Connection 2: SELECT IDENTIFIER FROM tables WHERE (TABLEID = 4 OR TABLEID = 5) AND DATE = "2015-10-31" FOR UPDATE; Connection 1: COMMIT; Connection 2 (statement runs): COMMIT; No errors. Both statements returned both rows, but the second one **waited for the first to commit**. This makes my problem seem irrational. If the FOR UPDATE does indeed wait for the commit, why do I get a deadlock error at all? MySQL Documentation says: "A kind of lock that prevents any other transaction from locking the same row" about X-locks, which FOR UPDATE is. See https://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_exclusive_lock Edit: LATEST DEADLOCK is ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2015-10-26 18:39:47 2b308cbde700 *** (1) TRANSACTION: TRANSACTION 12590262, ACTIVE 19 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s) MySQL thread id 199074, OS thread handle 0x2b308cc1f700, query id 5754285 172.31.39.158 awsroot Sending data SELECT IDENTIFIER,NAME,STATUS,PRICE,GUESTS,EMAIL,NOTE,BOOKER,EDITOR,SELLER,TABLEID,LINK,LASTCLIENT FROM tables WHERE (TABLEID = 4 OR TABLEID = 5) AND DATE = "2015-10-31" LIMIT 0, 1000 FOR UPDATE *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 2660 page no 4 n bits 232 index table_ident of table apple.tables trx id 12590262 lock_mode X locks rec but not gap waiting Record lock, heap no 157 PHYSICAL RECORD: n_fields 3; compact format; info bits 32 0: len 2; hex 8004; asc ;; 1: len 3; hex 8fbf5f; asc _;; 2: len 4; hex 80000004; asc ;; *** (2) TRANSACTION: TRANSACTION 12590261, ACTIVE 22 sec updating or deleting mysql tables in use 1, locked 1 9 lock struct(s), heap size 1184, 13 row lock(s), undo log entries 3 MySQL thread id 199078, OS thread handle 0x2b308cbde700, query id 5754318 172.31.39.158 awsroot updating UPDATE tables SET TABLEID = 4 WHERE TABLEID = -1 AND DATE = "2015-10-31" *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 2660 page no 4 n bits 232 index table_ident of table apple.tables trx id 12590261 lock_mode X locks rec but not gap Record lock, heap no 42 PHYSICAL RECORD: n_fields 3; compact format; info bits 32 0: len 2; hex 8005; asc ;; 1: len 3; hex 8fbf5f; asc _;; 2: len 4; hex 80000005; asc ;; Record lock, heap no 157 PHYSICAL RECORD: n_fields 3; compact format; info bits 32 0: len 2; hex 8004; asc ;; 1: len 3; hex 8fbf5f; asc _;; 2: len 4; hex 80000004; asc ;; Record lock, heap no 158 PHYSICAL RECORD: n_fields 3; compact format; info bits 32 0: len 2; hex 7fff; asc ;; 1: len 3; hex 8fbf5f; asc _;; 2: len 4; hex 80000005; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 2660 page no 4 n bits 232 index table_ident of table apple.tables trx id 12590261 lock mode S waiting Record lock, heap no 157 PHYSICAL RECORD: n_fields 3; compact format; info bits 32 0: len 2; hex 8004; asc ;; 1: len 3; hex 8fbf5f; asc _;; 2: len 4; hex 80000004; asc ;; *** WE ROLL BACK TRANSACTION (1) table_ident is the TABLEID+DATE unique index. SHOW CREATE TABLE for tables: CREATE TABLE tables ( IDENTIFIER int(11) NOT NULL AUTO_INCREMENT, TABLEID smallint(6) NOT NULL, NAME varchar(60) DEFAULT NULL, EMAIL varchar(255) DEFAULT NULL, PRICE int(11) unsigned NOT NULL DEFAULT '0', GUESTS tinyint(3) unsigned NOT NULL DEFAULT '0', STATUS tinyint(1) NOT NULL DEFAULT '1', NOTE varchar(1024) DEFAULT NULL, LINK smallint(6) NOT NULL DEFAULT '0', SELLER mediumint(8) NOT NULL DEFAULT '0', DATE date NOT NULL, EDITOR varchar(255) DEFAULT NULL, CUSTOMER mediumint(8) NOT NULL DEFAULT '0', LASTEDIT datetime NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP, BOOKER mediumint(8) NOT NULL DEFAULT '0', LASTCLIENT smallint(5) unsigned DEFAULT NULL, PRIMARY KEY (IDENTIFIER), UNIQUE KEY table_ident (TABLEID,DATE), KEY DATE (DATE), KEY SELLER (SELLER), KEY LINK (LINK), KEY BOOKER (BOOKER), KEY CUSTOMER (CUSTOMER) USING BTREE, CONSTRAINT tables_booker FOREIGN KEY (BOOKER) REFERENCES people (ID), CONSTRAINT tables_customer FOREIGN KEY (CUSTOMER) REFERENCES people (ID), CONSTRAINT tables_link FOREIGN KEY (LINK) REFERENCES layout (TABLEID) ON UPDATE CASCADE, CONSTRAINT tables_seller FOREIGN KEY (SELLER) REFERENCES people (ID), CONSTRAINT tables_tableid FOREIGN KEY (TABLEID) REFERENCES layout (TABLEID) ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=316 DEFAULT CHARSET=utf8

Table structure: CREATE TABLE extend_0 ( id bigint(20) NOT NULL, code varchar(30) NOT NULL, data_key varchar(50) NOT NULL, data_value varchar(200) NOT NULL, create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, yn tinyint(3) NOT NULL DEFAULT '1', PRIMARY KEY (id,create_time), UNIQUE KEY idx_unq_code_data_key (code,data_key,create_time) USING BTREE, KEY idx_code (code) USING BTREE ) ENGINE=InnoDB; When two transactions at the same time delete and insert the same data, sometimes a deadlock happened. The tx_isolation is RR. For example: DELETE FROM extend_0 WHERE code = '00226915986' AND yn = 1; INSERT INTO extend_0 (id, code, data_key, data_value, create_time) VALUES (1133296779049299970, '00226915986', 'consignWare', 'food', '2019-05-28 16:59:42.418'), (1133296779049299971, '00226915986', 'productType', '0001,0006', '2019-05-28 16:59:42.418'); Deadlock log: ------------------------------------------------------------------------------ 2019-05-30 14:48:07 0x7fbb7872c700 *** (1) TRANSACTION: TRANSACTION 125554670, ACTIVE 0 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 5148727, OS thread handle 140443189679872, query id 6111057236 192.168.162.16 waybill updating DELETE FROM extend_0 WHERE code = '00226915986' AND yn = 1 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 77 page no 3145 n bits 360 index idx_unq_code_data_key of table waybill_0.extend_0 trx id 125554670 lock_mode X waiting Record lock, heap no 285 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 0: len 15; hex 4a4456433030323236393135393836; asc 00226915986;; 1: len 11; hex 636f6e7369676e57617265; asc consignWare;; 2: len 4; hex 5cecf87e; asc \ ~;; 3: len 8; hex 911d8ce2e1ddf000; asc ;; *** (2) TRANSACTION: TRANSACTION 125554668, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 5 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 3 MySQL thread id 5148728, OS thread handle 140443156399872, query id 6111057237 192.168.162.16 waybill update INSERT INTO extend_0 (id, code, data_key, data_value, create_time) VALUES (i-1, '00226915986', 'consignWare', 'food', '2019-05-28 16:59:42.418'), (i, '00226915986', 'productType', '0001,0006', '2019-05-28 16:59:42.418') *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 77 page no 3145 n bits 360 index idx_unq_code_data_key of table waybill_0.extend_0 trx id 125554668 lock_mode X Record lock, heap no 285 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 0: len 15; hex 4a4456433030323236393135393836; asc 00226915986;; 1: len 11; hex 636f6e7369676e57617265; asc consignWare;; 2: len 4; hex 5cecf87e; asc \ ~;; 3: len 8; hex 911d8ce2e1ddf000; asc ;; Record lock, heap no 287 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 0: len 15; hex 4a4456433030323236393135393836; asc 00226915986;; 1: len 11; hex 70726f6475637454797065; asc productType;; 2: len 4; hex 5cecf87e; asc \ ~;; 3: len 8; hex 911d8ce2e1ddf020; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 77 page no 3145 n bits 360 index idx_unq_waybill_code_data_key of table waybill_0.extend_0 trx id 125554668 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 285 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 0: len 15; hex 4a4456433030323236393135393836; asc JDVC00226915986;; 1: len 11; hex 636f6e7369676e57617265; asc consignWare;; 2: len 4; hex 5cecf87e; asc \ ~;; 3: len 8; hex 911d8ce2e1ddf000; asc ;; *** WE ROLL BACK TRANSACTION (1) ------------------------------------------------------------------------------ The transaction2 has lock_mode X already. Why does "lock_mode X locks gap before rec insert intention waiting" happen? But when we execute the transactions one by one a deadlock does not happen. For example: step1: tx1 begin; delete.....; step2: tx2 begin; delete.....; step3: tx1 insert....; step4: tx2 insert....; step5: tx1 commit; The deadlock happens only when there is high concurrency; the deadlock looks to be caused by the concurrency of tx1's delete and tx2's insert.

I have implemented a ticketing system where when you join an event and placed an order depending on the quantity, a *n* of entries will be generated. Example: you place an order with 10 quantity, a 10 ticket number would be generated. The rule is, ticket number starts at 1 and would increment by 1 every time it is generated. The ticket number always starts with 1 on each event and each orders can have multiple entries. |event_id |ticket_no |order_id | |-----------|--------------|--------------| |1|1|1| |1|2|1| |1|3|2| |1|4|3| |1|5|6| |2|1|4| |2|2|5| So currently, in order to generate a ticket no. for each event. I used a MySQL trigger to increment its value: CREATE TRIGGER number_generator BEFORE INSERT ON entries FOR EACH ROW BEGIN SET NEW.ticket_no = (SELECT COALESCE(MAX(ticket_no), 0) + 1 FROM entries WHERE event_id = NEW.event_id); END; Here is a sample raw query of how I trigger ticket no. generation insert into entries (order_id, event_id, ticket_no) values (123, 1, 1), (123, 1, 1), (123, 1, 1), ... The problem: When multiple users place an order at the same time, the system would randomly throw a deadlock exception: > Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction In my old implementation, which uses MyISAM tables with auto increment on second column, I wouldn't encounter this issue, and the ticket number generation was faster. How can I resolve the deadlock and achieve MyIsam speed in inserting, but with InnoDB? ------------------------------------------------------------------------------------------- Orders Table Definition CREATE TABLE orders ( id bigint unsigned NOT NULL AUTO_INCREMENT, event_id bigint unsigned NOT NULL, first_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, last_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, email varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL PRIMARY KEY (id), KEY owning_event (event_id) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci Entries Table Definition CREATE TABLE entries ( event_id bigint unsigned NOT NULL, ticket_no bigint unsigned NOT NULL, order_id bigint unsigned NOT NULL, PRIMARY KEY (event_id,ticket_no), KEY entries_event_id_index (event_id), KEY entries_order_id_index (order_id), CONSTRAINT entries_order_id_foreign FOREIGN KEY (order_id) REFERENCES orders (id), CONSTRAINT entries_event_id_foreign FOREIGN KEY (event_id) REFERENCES events (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci Old entries Table Definition CREATE TABLE entries_old ( event_id BIGINT UNSIGNED NOT NULL, ticket_no MEDIUMINT NOT NULL AUTO_INCREMENT, order_id BIGINT NOT NULL, PRIMARY KEY (event_id, ticket_no) ) ENGINE=MyISAM;

I have a stored procedure which ran parallely by two processes. Code for sp : DECLARE @inputXML XML DECLARE @tempTable TABLE (afid INT, ccid INT) INSERT INTO @tempTable SELECT ref.value('@afid', 'INT'),ref.value('@ccid', 'INT') FROM @inputXML.nodes('/Ilist/aidA/afid') R ( ref ) DELETE FROM StylinInfoTemp FROM @tempTable AS temp WHERE temp.afid= StylinInfoTemp.afid AND temp.ccid= StylinInfoTemp.ccid this is the schema for StylinInfoTemp Columns : cisid int afid int ccid int entity int dtime int guid uniqueidentifier addFlags int Index is present on cisid, afid, ccid here is the schema for tempTable (afid INT, ccid INT) This tempTable is populated on the input received by the stored proc. tempTable in itself can never have duplicate entries. However StylinInfoTemp table may have duplicate entries on afId and ccid. But is unique on cisid, afid, ccid eg Check duplicate values on afid below However the stored procedure tempTable will always have unique afid. When this stored proc executes I am getting a deadlock on the same page, I am having trouble understanding how exactly is this leading to a deadlock and why do we have same page Id below Can someone help me understanding why deadlock, why same page id, how can two process hold and request for the same page id at the same time(image above) and how can I resolve this DeadLock XML : https://codebeautify.org/xmlviewer/y228f34a3 Query Plan: DELETE FROM StylinInfoTemp FROM @tempTable AS temp WHERE temp.afid = StylinInfoTemp.afid AND temp.ccid = StylinInfoTemp.ccid |--Clustered Index Delete(OBJECT:([Magneto].[dbo].[StylinInfoTemp].[StylinInfoTemp_cis_afid_ccid_PK])) |--Nested Loops(Left Semi Join, WHERE:(@tempTable.[afid] as [temp].[afid]=[Magneto].[dbo].[StylinInfoTemp].[afId] AND @temp.[ccid] as [temp].[ccid]=[Magneto].[dbo].[StylinInfoTemp].[ccid])) |--Clustered Index Scan(OBJECT:([Magneto].[dbo].[StylinInfoTemp].[StylinInfoTemp_cis_afid_ccidd_PK])) |--Table Scan(OBJECT:(@tempTable AS [temp]))

I am experiencing a deadlock scenario, and it's possible that I'm just not reading this correctly but here are the details, my interpretation is at the bottom. The players: **Transaction 1**: An ETL that is deleting records in batches. **Transaction 2**: A store front. The offending table is as such:

CREATE TABLE items
(
  id BIGINT PRIMARY KEY,
  secondary_key BIGINT UNIQUE
)

T1 is attempting to delete records by the secondary_key, meanwhile T2 is attempting to update the secondary_key on an existing record So T1 is doing:

DELETE FROM items where secondary_key IN (1,2,3,4,5,6);

And T2 is doing:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- This is set at the beginning of a larger transaction

UPDATE items SET secondary_key = 7 where id = 4;

The deadlock log that I the get is as follows: Transaction 1

*** (1) TRANSACTION:
TRANSACTION 42065889, ACTIVE 25 sec fetching rows
mysql tables in use 2, locked 2
LOCK WAIT 62416 lock struct(s), heap size 7577808, 9631608 row lock(s)
MySQL thread id 177980, OS thread handle 47144643110656, query id 45451329  preparing
DELETE FROM items
      WHERE secondary_id IN (
        SELECT
          li.id
        FROM staging.legacy_items li
        WHERE change_operation = 'D'
        AND li.id BETWEEN 9082453 AND 29059282
      )
      AND legacy_id IS NOT NULL

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 878323 page no 160719 n bits 96 index PRIMARY of table items trx id 42065889 lock_mode X waiting

Transaction 2

*** (2) TRANSACTION:
TRANSACTION 42065914, ACTIVE 1 sec starting index read
mysql tables in use 1, locked 1
34 lock struct(s), heap size 3520, 19 row lock(s), undo log entries 10
MySQL thread id 177524, OS thread handle 47137066059520, query id 45451721 updating
UPDATE items SET items.secondary_id = 29059300 WHERE items.id = 15558171

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 878323 page no 160719 n bits 96 index PRIMARY of table items trx id 42065914 lock mode S locks rec but not gap

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 878323 page no 160719 n bits 96 index PRIMARY of table items trx id 42065914 lock_mode X locks rec but not gap waiting

Result

*** WE ROLL BACK TRANSACTION (2)

---------- **SO..** to me it looks like: 1. Transaction 2 gets a shared lock on single record 2. Transaction 1 takes an exclusive lock on range of records 3. Transaction 2 tries to take an exclusive lock on the single record that it already has a shared lock for. I can't understand why Transaction 2 wouldn't be able to get an exclusive lock on the record that it already has a shared lock for. Am I interpreting this completely wrong? Thanks!

I'm trying to understand a scenario where an INSERT ... ON DUPLICATE KEY UPDATE statement is causing deadlocks under high concurrency. The two tables in question: hosts: "CREATE TABLE hosts (" + "id int(10) unsigned NOT NULL AUTO_INCREMENT," + "osquery_host_id varchar(255) NOT NULL," + "created_at timestamp DEFAULT CURRENT_TIMESTAMP," + "updated_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP," + "deleted_at timestamp NULL DEFAULT NULL," + "deleted tinyint(1) NOT NULL DEFAULT FALSE," + "detail_update_time timestamp NULL DEFAULT NULL," + "node_key varchar(255) DEFAULT NULL," + "host_name varchar(255) NOT NULL DEFAULT ''," + "uuid varchar(255) NOT NULL DEFAULT ''," + "platform varchar(255) NOT NULL DEFAULT ''," + "osquery_version varchar(255) NOT NULL DEFAULT ''," + "os_version varchar(255) NOT NULL DEFAULT ''," + "build varchar(255) NOT NULL DEFAULT ''," + "platform_like varchar(255) NOT NULL DEFAULT ''," + "code_name varchar(255) NOT NULL DEFAULT ''," + "uptime bigint(20) NOT NULL DEFAULT 0," + "physical_memory bigint(20) NOT NULL DEFAULT 0," + "cpu_type varchar(255) NOT NULL DEFAULT ''," + "cpu_subtype varchar(255) NOT NULL DEFAULT ''," + "cpu_brand varchar(255) NOT NULL DEFAULT ''," + "cpu_physical_cores int NOT NULL DEFAULT 0," + "cpu_logical_cores int NOT NULL DEFAULT 0," + "hardware_vendor varchar(255) NOT NULL DEFAULT ''," + "hardware_model varchar(255) NOT NULL DEFAULT ''," + "hardware_version varchar(255) NOT NULL DEFAULT ''," + "hardware_serial varchar(255) NOT NULL DEFAULT ''," + "computer_name varchar(255) NOT NULL DEFAULT ''," + "primary_ip_id INT(10) UNSIGNED DEFAULT NULL, " + "PRIMARY KEY (id)," + "UNIQUE KEY idx_host_unique_nodekey (node_key)," + "UNIQUE KEY idx_osquery_host_id (osquery_host_id)," + "FULLTEXT KEY hosts_search (host_name)" + ") ENGINE=InnoDB DEFAULT CHARSET=utf8;", networks_interfaces: "CREATE TABLE network_interfaces (" + "id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT," + "host_id INT(10) UNSIGNED NOT NULL," + "mac varchar(255) NOT NULL DEFAULT ''," + "ip_address varchar(255) NOT NULL DEFAULT ''," + "broadcast varchar(255) NOT NULL DEFAULT ''," + "ibytes BIGINT NOT NULL DEFAULT 0," + "interface VARCHAR(255) NOT NULL DEFAULT ''," + "ipackets BIGINT NOT NULL DEFAULT 0," + "last_change BIGINT NOT NULL DEFAULT 0," + "mask varchar(255) NOT NULL DEFAULT ''," + "metric INT NOT NULL DEFAULT 0," + "mtu INT NOT NULL DEFAULT 0," + "obytes BIGINT NOT NULL DEFAULT 0," + "ierrors BIGINT NOT NULL DEFAULT 0," + "oerrors BIGINT NOT NULL DEFAULT 0," + "opackets BIGINT NOT NULL DEFAULT 0," + "point_to_point varchar(255) NOT NULL DEFAULT ''," + "type INT NOT NULL DEFAULT 0," + "PRIMARY KEY (id), " + "FOREIGN KEY idx_network_interfaces_hosts_fk (host_id) " + "REFERENCES hosts(id) " + "ON DELETE CASCADE, " + "FULLTEXT KEY ip_address_search (ip_address)," + "UNIQUE KEY idx_network_interfaces_unique_ip_host_intf (ip_address, host_id, interface)" + ") ENGINE=InnoDB DEFAULT CHARSET=utf8;", Lastest deadlock info: ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2020-01-20 00:09:06 0x2b033abd2700 *** (1) TRANSACTION: TRANSACTION 78516922, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 5 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2 MySQL thread id 286926, OS thread handle 47297573750528, query id 1045761878 10.107.51.236 username update INSERT INTO network_interfaces ( host_id, mac, ip_address, broadcast, ibytes, interface, ipackets, last_change, mask, metric, mtu, obytes, ierrors, oerrors, opackets, point_to_point, type ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id), mac = VALUES(mac), broadcast = VALUES(broadcast), ibytes = VALUES(ibytes), ipackets = VALUES(ipackets), last_change = VALUES(last_change), mask = VALUES(mask), metric = VALUES(metric), mtu = VALUES(mtu), obytes = VALUES(obytes), ierrors = VALUES(ierrors), oerrors = VALUES(oerrors), opackets = VALUES(opackets), point_to_point = VALUES(point_to_point), type = VALUES(type) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 258 page no 2729 n bits 408 index FTS_DOC_ID_INDEX of table kolide.network_interfaces trx id 78516922 lock_mode Xinsert 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 78516915, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 18 lock struct(s), heap size 1136, 33 row lock(s), undo log entries 12 MySQL thread id 281276, OS thread handle 47292870371072, query id 1045761879 10.107.78.241 username update INSERT INTO network_interfaces ( host_id, mac, ip_address, broadcast, ibytes, interface, ipackets, last_change, mask, metric, mtu, obytes, ierrors, oerrors, opackets, point_to_point, type ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id), mac = VALUES(mac), broadcast = VALUES(broadcast), ibytes = VALUES(ibytes), ipackets = VALUES(ipackets), last_change = VALUES(last_change), mask = VALUES(mask), metric = VALUES(metric), mtu = VALUES(mtu), obytes = VALUES(obytes), ierrors = VALUES(ierrors), oerrors = VALUES(oerrors), opackets = VALUES(opackets), point_to_point = VALUES(point_to_point), type = VALUES(type) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 258 page no 2729 n bits 408 index FTS_DOC_ID_INDEX of table kolide.network_interfaces trx id 78516915 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 258 page no 2729 n bits 408 index FTS_DOC_ID_INDEX of table kolide.network_interfaces trx id 78516915 lock_mode Xinsert 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 (1) The program starts a transaction, updates a host row, and uses that same transaction in a loop through all the host's interfaces and issues a INSERT...ON DUPLICATE statement for each interface. As I understand it, because the transaction starts with a UPDATE (exclusive) lock on the host table, another transaction cannot be updating the same host. So I don't think this is a scenario where two connections are trying to update the same set of host interfaces (this could easily result in a deadlock). I think this might be due to different host updates competing over the AUTO_INCREMENT index of network_interfaces? I just don't understand how, even after staring at the MySQL docs about locks. I understand that transaction 1 is waiting on an exclusive insert lock, transaction 2 has an exclusive lock and is also waiting on an exclusive insert lock. What I'm specifically not understanding is why TRANSACTION 2 has the exclusive lock lock_mode X to begin with.

I'm running a 15GB database on SQL Server 12.0.5207. Server is a virtualized Windows Server 2012 R2 with 16GB RAM and 4 CPUs. Yesterday, we set ALLOW_SNAPSHOT_ISOLATION to ON, to check if it would help prevent some deadlocks we have seen. Performance decreased a LOT! Lock waits per seconds increased, and CPU went from 25% to 50% usage. We decided to set ALLOW_SNAPSHOT_ISOLATION back to OFF. The problem is this had **no effect**; we still have decreased performance. We restarted the SQL Server and related services, but it did not resolve the issue. Any suggestions? Indexes have been rebuilt using Ola Hallengren's MaintenanceSolution.sql script we run weekly. This is the execution command: EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30;