To be honest, as much as I love SQL databases, I have a rather poor understanding of how Transactions work and, about what conditions actually lead to deadlocks. When we were executing our ColdFusion code, this is the MySQL error was showing up in our logs:ĭeadlock found when trying to get lock try restarting transaction. However, I still have no idea what that was happening so, I wanted to see if I could find a way to get better logging around MySQL deadlocks in our Lucee CFML 5.2.9.40 application. Eventually - after may page refreshes - the page finally executed successfully. However, when we looked at the SQL statement that was failing to obtain the lock, it was a complete mystery (at least to us) as to why that SQL statement was consistently running into the locking problem. RECORD LOCKS space id 569 page no 6 n bits 440 index PRIMARY of table campaygn3.The other day, Josh Siok and I were running into a strange problem: we were executing a ColdFusion page that was immediately terminating in a Transaction Deadlock error in MySQL. *** (2) WAITING FOR THIS LOCK TO BE GRANTED: Record lock, heap no 151 PHYSICAL RECORD: n_fields 6 compact format info bits 0Ģ: len 00747926e1 asc W ty& ģ: len 7368696f6e asc fashion Record lock, heap no 54 PHYSICAL RECORD: n_fields 6 compact format info bits 0ģ: len 636f66746865646179 asc picoftheday RECORD LOCKS space id 569 page no 5 n bits 440 index PRIMARY of table campaygn3.tags trx id 407031425 lock mode S locks rec but not gap MySQL thread id 6521, OS thread handle 0x7fc9de391700, query id 20689580 localhost 127.0.0.1 campaygn3 update TRANSACTION 407031425, ACTIVE 0 sec insertingģ1 lock struct(s), heap size 6544, 49 row lock(s) Record lock, heap no 77 PHYSICAL RECORD: n_fields 6 compact format info bits 0 RECORD LOCKS space id 569 page no 5 n bits 440 index PRIMARY of table campaygn3.tags trx id 407031415 lock_mode X locks rec but not gap waiting *** (1) WAITING FOR THIS LOCK TO BE GRANTED: INSERT INTO tags (tag) VALUES ('paris'), ('pfw'), ('streetstyle'), ('trendycrew'), ('ootd'), ('outfit'), ('style'), ('fashion'), ('vsco'), ('vscoparis'), ('vscostyle') ON DUPLICATE KEY UPDATE tag = VALUES(tag) MySQL thread id 6539, OS thread handle 0x7fc9de734700, query id 20689564 localhost 127.0.0.1 campaygn3 update LOCK WAIT 12 lock struct(s), heap size 2936, 11 row lock(s), undo log entries 1 TRANSACTION 407031415, ACTIVE 0 sec starting index read I ran command show engine innodb status and result is shown below. Query I am using to do bulk insert is INSERT INTO tags (tag) VALUES ('jatin'),('test') ON DUPLICATE KEY UPDATE tag = VALUES(tag) ` ) ENGINE=InnoDB AUTO_INCREMENT=657163 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci` Updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, Tag varchar(190) COLLATE utf8mb4_unicode_ci NOT NULL,Ĭreated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, Id int(10) unsigned NOT NULL AUTO_INCREMENT, I need to do bulk insert into this table but I am getting error Deadlock found when trying to get lock try restarting transaction, not able to find what thing is creating problem ? CREATE TABLE tags ( I have a table structure as given below, this table is having 1 million records and having FK relationship with two and three tables having records also in millions.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |