Mysql deadlock found11/16/2023 InnoDB also sets these locks in the case where the constraint fails. If a FOREIGN KEY constraint is defined on a table, any insert, update, or delete that requires the constraint condition to be checked sets shared record-level locks on the records that it looks at to check the constraint. 既然不是 select 造成,嫌疑犯就變成 insert order 了,orders table 中的 product_id 是引用 product table 中的 id 當作 foreign key,果然找到相關的描述 14.7.3 Locks Set by Different SQL Statements in InnoDB A consistent read does not set any locks on the tables it accesses, and therefore other sessions are free to modify those tables at the same time a consistent read is being performed on the table. 首先定位到 select from,根據官方文件,除非 isolation 是 serializable,否則一般的 select from 是沒有 lock 的,出處 15.7.2.3 Consistent Nonlocking ReadsĬonsistent read is the default mode in which InnoDB processes SELECT statements in READ COMMITTED and REPEATABLE READ isolation levels. RECORD LOCKS space id 3 page no 8 n bits 336 index PRIMARY of table `online-transaction`.`products` trx id 14052 lock_mode X locks rec but not gap waiting *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 3 page no 8 n bits 336 index PRIMARY of table `online-transaction`.`products` trx id 14052 lock mode S locks rec but not gap Update `products` set `sold` = 34 where `id` = '919' TRANSACTION 14052, ACTIVE 1 sec starting index read RECORD LOCKS space id 3 page no 8 n bits 336 index PRIMARY of table `online-transaction`.`products` trx id 14048 lock_mode X locks rec but not gap waiting *** (1) WAITING FOR THIS LOCK TO BE GRANTED: Record lock, heap no 259 PHYSICAL RECORD: n_fields 7 compact format info bits 0ģ: len 6163746963616c204672657368204d6f757365 asc Practical Fresh Mouse RECORD LOCKS space id 3 page no 8 n bits 336 index PRIMARY of table `online-transaction`.`products` trx id 14048 lock mode S locks rec but not gap Update `products` set `sold` = 32 where `id` = '919' LOCK WAIT 11 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 2 TRANSACTION 14048, ACTIVE 1 sec starting index read Per second averages calculated from the last 29 seconds 開啟設定的 SQL 為 > SET GLOBAL innodb_print_all_deadlocks=ON 具體的 Deadlock log MySQL 可以透過 SQL 指令 > SHOW ENGINE INNODB STATUS 看最後一筆發生 Deadlock 的原因,或是開啟 innodb_print_all_deadlocks 把每一次 Deadlock 原因都輸出到 error log 中 This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT … FOR SHARE if autocommit is disabled.ĭeadlock detection 預設是開啟,但如果在高流量下會有效能的影響,如果預期 Deadlock 狀況不多可以改透過 innodb_deadlock_detect 選項關閉,用 innodb_lock_wait_timeout 一直等不到 lock 發生 timeout 而觸發 rollback 取代 考慮降低 isolation level,高層級的 isolation level 會去改變 read 的操作,例如 MySQL 中 serializable 其實就是隱式把所有 select 都加上 lock for share,引自於官方文件 15.7.2.1 Transaction Isolation Levels.That is because these operations are not really “atomic” they automatically set locks on the (possibly several) index records of the row inserted or deleted. You can get deadlocks even in the case of transactions that just insert or delete a single row. 盡可能減少 Update / Delete 在單一 Transaction 中的數量.At tr圜atcher ((((((((: update ` products ` set ` sold ` = 34 where ` id ` = '919' - Deadlock found when trying to get lock try restarting transaction
0 Comments
Leave a Reply.AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |