![]() ![]() Unfortunately, the second transaction has an exclusive lock on it, so the first transaction has to wait for the second transaction. Since it is indexed, the corresponding entry in the index (evento, strato) has to be modified (which requires an exclusive lock). Your first transaction now wants to update that row. Since it is already locked, it has to wait. Your second transaction uses the index (evento, strato) to find entries with evento=66, places an exclusive lock on that entry (in the secondary index), and then tries to get an exclusive lock in the row in the primary key. Your first transaction uses the primary key to find the row with id=1, and places an exclusive lock on it. This double step can cause your deadlock. When you use a secondary index to locate and lock a row, MySQL will first lock the entry in the secondary index, and, after that, the corresponding row in the primary key. Why the second connection doesn't wait and it was deadlocked? The 43 and 39 connection are JDBC connection of a Spring-Java application using a connection pool. (conn= 39) Deadlock found when trying to get lock try restarting Just after the last query the deadlock error is thrown. This is the query log (Please mind the connection): 43 Query SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTEDĤ3 Query SELECT stato FROM ingressi WHERE id=1 FOR UPDATEģ9 Query SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTEDģ9 Query SELECT count(*) FROM ingressi WHERE evento=66 FOR UPDATEĤ3 Query UPDATE `ingressi` SET stato=0 WHERE id=1 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 KEY `evento` (`evento`,`stato`) USING BTREE, ![]() `id` int(10) unsigned NOT NULL AUTO_INCREMENT, This is the table: CREATE TABLE `ingressi` ( ![]() FOR UPDATE steatment, I cannot understand why they run into a deadlock. I have two threads, they have to update the same table but the first one is using a primary key to lock a single record, the second thread have to lock a set of records using another index. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |