首页 \ 问答 \ 与InnoDB的MySQL死锁问题(MySQL deadlocking issue with InnoDB)

与InnoDB的MySQL死锁问题(MySQL deadlocking issue with InnoDB)

 我有一个中央数据库服务器和几个同时执行这样的查询的“工作”服务器： 
 

UPDATE job_queue 
SET
  worker = '108.166.81.112',
  attempts = attempts + 1,
  started = '2014-01-14 10:34:03',
  token = '13eb3e6a8c3e1becb34051e08f19fd62'
WHERE completed = '0000-00-00 00:00:00'
  AND (started = '0000-00-00 00:00:00' OR started < '2014-01-14 10:29:03')
  AND attempts < 2
ORDER BY priority DESC, inserted
LIMIT 1

 

 偶尔我的job_queue表会锁定，如果我运行“SHOW ENGINE INNODB STATUS”，我会得到这样的结果： 
 

------------------------
LATEST DETECTED DEADLOCK
------------------------
140114 10:34:15
*** (1) TRANSACTION:
TRANSACTION 0 46984514, ACTIVE 0 sec, process no 590, OS thread id 140366633146112 fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 20 lock struct(s), heap size 3024, 545 row lock(s)
MySQL thread id 677401, query id 19385205 10.179.103.110 root init
UPDATE job_queue SET worker='108.166.81.112', attempts=attempts+1, started='2014-01-14 10:34:03', token='13eb3e6a8c3e1becb34051e08f19fd62' WHERE completed='0000-00-00 00:00:00' AND (started='0000-00-00 00:00:00' OR started<'2014-01-14 10:29:03') AND attempts<2 ORDER BY priority DESC, inserted LIMIT 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 245767 n bits 128 index `PRIMARY` of table `database`.`job_queue` trx id 0 46984514 lock_mode X waiting
Record lock, heap no 34 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
 0: len 3; hex 800210; asc    ;; 1: len 6; hex 000002cced25; asc      %;; 2: len 7; hex 000003c00f1970; asc       p;; 3: len 30; hex 4f3a31343a2243425343616368654170704a6f62223a363a7b733a31393a; asc O:14:"CBSCacheAppJob":6:{s:19:;...(truncated); 4: len 1; hex 80; asc  ;; 5: len 8; hex 800012513c58bf24; asc    Q<X $;; 6: len 8; hex 800012513c58cc17; asc    Q<X  ;; 7: len 14; hex 31302e3137392e3130332e313333; asc 10.179.103.133;; 8: len 1; hex 81; asc  ;; 9: len 8; hex 800012513c58cc32; asc    Q<X 2;; 10: len 0; hex ; asc ;; 11: len 30; hex 353264393033616162656634346239626536306463346438666432303066; asc 52d903aabef44b9be60dc4d8fd200f;...(truncated);

*** (2) TRANSACTION:
TRANSACTION 0 46984485, ACTIVE 17 sec, process no 590, OS thread id 140366633547520 starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1216, 2 row lock(s), undo log entries 1
MySQL thread id 676723, query id 19385209 10.179.103.133 root init
UPDATE job_queue SET worker='10.179.103.133', attempts=attempts+1, started='2014-01-14 10:34:03', token='efd21d0d34f44badbc30386db4dd252e' WHERE completed='0000-00-00 00:00:00' AND (started='0000-00-00 00:00:00' OR started<'2014-01-14 10:29:03') AND attempts<2 ORDER BY priority DESC, inserted LIMIT 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 245767 n bits 128 index `PRIMARY` of table `database`.`job_queue` trx id 0 46984485 lock_mode X locks rec but not gap
Record lock, heap no 34 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
 0: len 3; hex 800210; asc    ;; 1: len 6; hex 000002cced25; asc      %;; 2: len 7; hex 000003c00f1970; asc       p;; 3: len 30; hex 4f3a31343a2243425343616368654170704a6f62223a363a7b733a31393a; asc O:14:"CBSCacheAppJob":6:{s:19:;...(truncated); 4: len 1; hex 80; asc  ;; 5: len 8; hex 800012513c58bf24; asc    Q<X $;; 6: len 8; hex 800012513c58cc17; asc    Q<X  ;; 7: len 14; hex 31302e3137392e3130332e313333; asc 10.179.103.133;; 8: len 1; hex 81; asc  ;; 9: len 8; hex 800012513c58cc32; asc    Q<X 2;; 10: len 0; hex ; asc ;; 11: len 30; hex 353264393033616162656634346239626536306463346438666432303066; asc 52d903aabef44b9be60dc4d8fd200f;...(truncated);

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 57 n bits 120 index `PRIMARY` of table `database`.`job_queue` trx id 0 46984485 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
 0: len 3; hex 800001; asc    ;; 1: len 6; hex 000002ccdab1; asc       ;; 2: len 7; hex 000003c0352b3f; asc     5+?;; 3: len 30; hex 4f3a31323a224175746f50696c6f744a6f62223a363a7b733a31383a2200; asc O:12:"AutoPilotJob":6:{s:18:" ;...(truncated); 4: len 1; hex 82; asc  ;; 5: len 8; hex 800012513c58af57; asc    Q<X W;; 6: len 8; hex 800012513c58bf22; asc    Q<X ";; 7: len 14; hex 3130382e3136362e38312e313132; asc 108.166.81.112;; 8: len 1; hex 81; asc  ;; 9: len 8; hex 800012513c58bf23; asc    Q<X #;; 10: len 0; hex ; asc ;; 11: len 30; hex 616331376430346339326163613366323330646164323239363764336266; asc ac17d04c92aca3f230dad22967d3bf;...(truncated);

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 0 46989905
Purge done for trx's n:o < 0 46986227 undo n:o < 0 0
History list length 24
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 590, OS thread id 140366628529920
MySQL thread id 703864, query id 20047015 localhost root
SHOW ENGINE INNODB STATUS
---TRANSACTION 0 46989894, not started, process no 590, OS thread id 140366636758784
MySQL thread id 702822, query id 20046897 10.179.1.63 root
---TRANSACTION 0 46986223, ACTIVE 39782 sec, process no 590, OS thread id 140366626322176
25 lock struct(s), heap size 3024, 710 row lock(s), undo log entries 9
MySQL thread id 677706, query id 19994505 10.179.103.114 root
Trx read view will not see trx with id >= 0 46986224, sees < 0 46986224

 

 对表的任何进一步写入然后超时我，直到我重新启动MySQL服务器（或手动终止死锁作业）： 
 

PHP Fatal error:  Lock wait timeout exceeded; try restarting transaction(Query: "UPDATE job_queue SET worker='108.166.81.250', attempts=attempts+1, started='2014-01-14 21:27:45', token='369eae55a7f0eacad3b678a3410de8e4' WHERE completed='0000-00-00 00:00:00' AND (started='0000-00-00 00:00:00' OR started<'2014-01-14 21:22:45') AND attempts<2 ORDER BY priority DESC, inserted LIMIT 1") in /utilities/Database.php on line 53

 

 任何人都可以向我解释为什么这个查询导致死锁？ 我的印象是InnoDB表上的所有查询都是原子地发生的。 有任何想法吗？ 

I have a central database server and several "worker" servers which are executing queries like this concurrently:
 

UPDATE job_queue 
SET
  worker = '108.166.81.112',
  attempts = attempts + 1,
  started = '2014-01-14 10:34:03',
  token = '13eb3e6a8c3e1becb34051e08f19fd62'
WHERE completed = '0000-00-00 00:00:00'
  AND (started = '0000-00-00 00:00:00' OR started < '2014-01-14 10:29:03')
  AND attempts < 2
ORDER BY priority DESC, inserted
LIMIT 1

 

Occasionally my job_queue table locks up and if I run "SHOW ENGINE INNODB STATUS", I get something like this:
 

------------------------
LATEST DETECTED DEADLOCK
------------------------
140114 10:34:15
*** (1) TRANSACTION:
TRANSACTION 0 46984514, ACTIVE 0 sec, process no 590, OS thread id 140366633146112 fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 20 lock struct(s), heap size 3024, 545 row lock(s)
MySQL thread id 677401, query id 19385205 10.179.103.110 root init
UPDATE job_queue SET worker='108.166.81.112', attempts=attempts+1, started='2014-01-14 10:34:03', token='13eb3e6a8c3e1becb34051e08f19fd62' WHERE completed='0000-00-00 00:00:00' AND (started='0000-00-00 00:00:00' OR started<'2014-01-14 10:29:03') AND attempts<2 ORDER BY priority DESC, inserted LIMIT 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 245767 n bits 128 index `PRIMARY` of table `database`.`job_queue` trx id 0 46984514 lock_mode X waiting
Record lock, heap no 34 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
 0: len 3; hex 800210; asc    ;; 1: len 6; hex 000002cced25; asc      %;; 2: len 7; hex 000003c00f1970; asc       p;; 3: len 30; hex 4f3a31343a2243425343616368654170704a6f62223a363a7b733a31393a; asc O:14:"CBSCacheAppJob":6:{s:19:;...(truncated); 4: len 1; hex 80; asc  ;; 5: len 8; hex 800012513c58bf24; asc    Q<X $;; 6: len 8; hex 800012513c58cc17; asc    Q<X  ;; 7: len 14; hex 31302e3137392e3130332e313333; asc 10.179.103.133;; 8: len 1; hex 81; asc  ;; 9: len 8; hex 800012513c58cc32; asc    Q<X 2;; 10: len 0; hex ; asc ;; 11: len 30; hex 353264393033616162656634346239626536306463346438666432303066; asc 52d903aabef44b9be60dc4d8fd200f;...(truncated);

*** (2) TRANSACTION:
TRANSACTION 0 46984485, ACTIVE 17 sec, process no 590, OS thread id 140366633547520 starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1216, 2 row lock(s), undo log entries 1
MySQL thread id 676723, query id 19385209 10.179.103.133 root init
UPDATE job_queue SET worker='10.179.103.133', attempts=attempts+1, started='2014-01-14 10:34:03', token='efd21d0d34f44badbc30386db4dd252e' WHERE completed='0000-00-00 00:00:00' AND (started='0000-00-00 00:00:00' OR started<'2014-01-14 10:29:03') AND attempts<2 ORDER BY priority DESC, inserted LIMIT 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 245767 n bits 128 index `PRIMARY` of table `database`.`job_queue` trx id 0 46984485 lock_mode X locks rec but not gap
Record lock, heap no 34 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
 0: len 3; hex 800210; asc    ;; 1: len 6; hex 000002cced25; asc      %;; 2: len 7; hex 000003c00f1970; asc       p;; 3: len 30; hex 4f3a31343a2243425343616368654170704a6f62223a363a7b733a31393a; asc O:14:"CBSCacheAppJob":6:{s:19:;...(truncated); 4: len 1; hex 80; asc  ;; 5: len 8; hex 800012513c58bf24; asc    Q<X $;; 6: len 8; hex 800012513c58cc17; asc    Q<X  ;; 7: len 14; hex 31302e3137392e3130332e313333; asc 10.179.103.133;; 8: len 1; hex 81; asc  ;; 9: len 8; hex 800012513c58cc32; asc    Q<X 2;; 10: len 0; hex ; asc ;; 11: len 30; hex 353264393033616162656634346239626536306463346438666432303066; asc 52d903aabef44b9be60dc4d8fd200f;...(truncated);

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 57 n bits 120 index `PRIMARY` of table `database`.`job_queue` trx id 0 46984485 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
 0: len 3; hex 800001; asc    ;; 1: len 6; hex 000002ccdab1; asc       ;; 2: len 7; hex 000003c0352b3f; asc     5+?;; 3: len 30; hex 4f3a31323a224175746f50696c6f744a6f62223a363a7b733a31383a2200; asc O:12:"AutoPilotJob":6:{s:18:" ;...(truncated); 4: len 1; hex 82; asc  ;; 5: len 8; hex 800012513c58af57; asc    Q<X W;; 6: len 8; hex 800012513c58bf22; asc    Q<X ";; 7: len 14; hex 3130382e3136362e38312e313132; asc 108.166.81.112;; 8: len 1; hex 81; asc  ;; 9: len 8; hex 800012513c58bf23; asc    Q<X #;; 10: len 0; hex ; asc ;; 11: len 30; hex 616331376430346339326163613366323330646164323239363764336266; asc ac17d04c92aca3f230dad22967d3bf;...(truncated);

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 0 46989905
Purge done for trx's n:o < 0 46986227 undo n:o < 0 0
History list length 24
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 590, OS thread id 140366628529920
MySQL thread id 703864, query id 20047015 localhost root
SHOW ENGINE INNODB STATUS
---TRANSACTION 0 46989894, not started, process no 590, OS thread id 140366636758784
MySQL thread id 702822, query id 20046897 10.179.1.63 root
---TRANSACTION 0 46986223, ACTIVE 39782 sec, process no 590, OS thread id 140366626322176
25 lock struct(s), heap size 3024, 710 row lock(s), undo log entries 9
MySQL thread id 677706, query id 19994505 10.179.103.114 root
Trx read view will not see trx with id >= 0 46986224, sees < 0 46986224

 

Any further writes to the table then time out on me until I restart my MySQL server (or manually kill the deadlocked jobs):
 

PHP Fatal error:  Lock wait timeout exceeded; try restarting transaction(Query: "UPDATE job_queue SET worker='108.166.81.250', attempts=attempts+1, started='2014-01-14 21:27:45', token='369eae55a7f0eacad3b678a3410de8e4' WHERE completed='0000-00-00 00:00:00' AND (started='0000-00-00 00:00:00' OR started<'2014-01-14 21:22:45') AND attempts<2 ORDER BY priority DESC, inserted LIMIT 1") in /utilities/Database.php on line 53

 

Can anyone explain to me why this query causes a deadlock? I was under the impression that all queries on an InnoDB table happened atomically. Any ideas? 

原文：https://stackoverflow.com/questions/21130232
更新时间：2022-03-24 09:03

最满意答案

 您可以重新加载忽略缓存的客户端浏览器： window.location.reload(true) 

You can reload the client browser ignoring the cache: window.location.reload(true)

相关问答

更多

  • 如何缓存Symfony 2？(How to cache in Symfony 2?)[2021-10-21]

    如果您正在使用Doctrine已经只使用这些缓存类。 向config.yml添加一个服务： services: cache: class: Doctrine\Common\Cache\ApcCache 并在您的控制器中使用它： if ($fooString = $this->get('cache')->fetch('foo')) { $foo = unserialize($fooString); } else { // do the work $this-> ...

  • 在Laravel 4中刷新所有缓存(Flush all cache in Laravel 4)[2023-09-20]

    如果你运行php artisan list那么你可以找到所有的命令可用于artisan ，无论如何，有一个命令来清除cache ，它的 php artisan cache:clear 此外，您可以使用 foreach (Cache::getMemory() as $cacheKey => $cacheValue) { Cache::forget($cacheKey); } 更新： Cache::flush(); If you run php artisan list then you can ...

  • 如何确定网站访问者的原籍国？(How to determine a web visitor's country of origin? [closed])[2022-07-27]

    看一下PHP的ip2country类。 Take a look at the ip2country class for PHP.

  • 如何在注销时清除/过期浏览器缓存？(How to clear/expire browser cache on log off?)[2024-03-21]

    对于HTML页面来说，这很困难。 我出于同样的原因关闭了客户端缓存，并尝试使服务器缓存尽可能高效。 我现在使用OutputCache并将VaryByCustom设置为登录状态。 我们在该系统上运行了一些负载测试，唯一的瓶颈是它产生的带宽。 并且旁注：我使用donut-caching来登录状态。 但我无法使用动态压缩（以减少上面提到的带宽瓶颈） 另见这个问题 For HTML pages it's difficult. I turned off client caching for that same rea ...

  • apt的缓存在apt-cache定义中意味着什么？(What does apt's cache mean in apt-cache defination?)[2021-10-19]

    APT下载包信息（使用apt-get update ）并将它们存储在位于/var/cache/apt的本地数据库中。 这样，APT不必每次都能从Internet下载内容，例如搜索包。 该本地数据库是APT的缓存。 APT downloads packages information (with apt-get update) and stores them in a local database located at /var/cache/apt. This way, APT doesn't have to ...

  • Java：多个自刷新缓存对象(Java: multiple self refreshing cache object)[2023-01-04]

    是的，你可以简单地调用load() ： private void onTimer() { timer = new Timer(); timer.schedule(new TimerTask() { @Override public void run() { load(); // Call the containing class method } }, TTL * 60000); } 因为每个匿名类实际上也 ...

  • 那是缓存还是缓存？(Is that caches or cache?)[2023-08-06]

    window.caches是一个CacheStorage接口，用于存储所有已命名的Cache对象。 例如， window.caches.open()方法返回解析为Cache对象的promise。 // Get a named Cache object from CacheStorage window.caches.open('cachename').then(cache => { // Work with resolved cache object (instance of Cache) }); ...

  • 自动刷新IIS服务器上的缓存(Automatically refreshing cache on a IIS server)[2022-09-16]

    如果你不能重现它那么没有问题; 虽然我承认可能存在缓存问题。 但是我不知道你的应用程序的更多信息，我建议的是grep你的源代码以获取对System.Web.Caching.Cache类的引用（如果不是HttpContext.Current通常直接通过Page类访问）。 删除缓存负责的想法，我认为这只是忘记密码功能的错误编码的情况。 我猜是忘记密码功能会重置某种“帐户禁用”标志，这就是允许用户进入的标志。 If you can't reproduce it then there is no problem; ...

  • 刷新我的访问者缓存(Refreshing My Visitor's Cache)[2024-03-06]

    您可以重新加载忽略缓存的客户端浏览器： window.location.reload(true) You can reload the client browser ignoring the cache: window.location.reload(true)

  • 用访问者的时区获取昨天的mySQL数据(Fetching yesterday's mySQL data with visitor's Time Zone)[2022-02-23]

    不要将时间存储为UNIX时间戳，而是存储为TIMESTAMP 。 然后你可以简单地设置time_zone ，一切都将按你的意愿转换： SET time_zone = '+10:00'; SELECT COUNT(*) AS total FROM track WHERE date > NOW() - INTERVAL 2 DAY AND date < NOW() - INTERVAL 1 DAY 否则，您可以使用CONVERT_TZ() ： SELECT COUNT(*) AS total F ...

相关文章

更多
  • 大家帮忙分析mysql的死锁问题［根据下面的innodb status信息］
  • MySQL最佳实践
  • Solr与Mysql集成指南
  • Nutch2.1+mysql+solr3.6.1+中文网站抓取
  • Solr与Mysql集成指南
  • mysql问题
  • MySQL 5.6 my.cnf 配置文件详解
  • Setting up Nutch 2.1 with MySQL to handle UTF-8
  • Hadoop 中利用 MapReduce 读写 MySQL 数据
  • mysql千万级测试1亿数据的分页分析测试

    • 最新问答

    更多
  • 您如何使用git diff文件，并将其应用于同一存储库的副本的本地分支？(How do you take a git diff file, and apply it to a local branch that is a copy of the same repository?)
  • 将长浮点值剪切为2个小数点并复制到字符数组(Cut Long Float Value to 2 decimal points and copy to Character Array)
  • OctoberCMS侧边栏不呈现(OctoberCMS Sidebar not rendering)
  • 页面加载后对象是否有资格进行垃圾回收？(Are objects eligible for garbage collection after the page loads?)
  • codeigniter中的语言不能按预期工作(language in codeigniter doesn' t work as expected)
  • 在计算机拍照在哪里进入
  • 使用cin.get（）从c ++中的输入流中丢弃不需要的字符(Using cin.get() to discard unwanted characters from the input stream in c++)
  • No for循环将在for循环中运行。(No for loop will run inside for loop. Testing for primes)
  • 单页应用程序：页面重新加载(Single Page Application: page reload)
  • 在循环中选择具有相似模式的列名称(Selecting Column Name With Similar Pattern in a Loop)
  • System.StackOverflow错误(System.StackOverflow error)
  • KnockoutJS未在嵌套模板上应用beforeRemove和afterAdd(KnockoutJS not applying beforeRemove and afterAdd on nested templates)
  • 散列包括方法和/或嵌套属性(Hash include methods and/or nested attributes)
  • android - 如何避免使用Samsung RFS文件系统延迟/冻结？(android - how to avoid lag/freezes with Samsung RFS filesystem?)
  • TensorFlow：基于索引列表创建新张量(TensorFlow: Create a new tensor based on list of indices)
  • 企业安全培训的各项内容
  • 错误：RPC失败;(error: RPC failed; curl transfer closed with outstanding read data remaining)
  • C＃类名中允许哪些字符？(What characters are allowed in C# class name?)
  • NumPy：将int64值存储在np.array中并使用dtype float64并将其转换回整数是否安全？(NumPy: Is it safe to store an int64 value in an np.array with dtype float64 and later convert it back to integer?)
  • 注销后如何隐藏导航portlet？(How to hide navigation portlet after logout?)
  • 将多个行和可变行移动到列(moving multiple and variable rows to columns)
  • 提交表单时忽略基础href，而不使用Javascript(ignore base href when submitting form, without using Javascript)
  • 对setOnInfoWindowClickListener的意图(Intent on setOnInfoWindowClickListener)
  • Angular $资源不会改变方法(Angular $resource doesn't change method)
  • 在Angular 5中不是一个函数(is not a function in Angular 5)
  • 如何配置Composite C1以将.m和桌面作为同一站点提供服务(How to configure Composite C1 to serve .m and desktop as the same site)
  • 不适用：悬停在悬停时：在元素之前[复制](Don't apply :hover when hovering on :before element [duplicate])
  • 常见的python rpc和cli接口(Common python rpc and cli interface)
  • Mysql DB单个字段匹配多个其他字段(Mysql DB single field matching to multiple other fields)
  • 产品页面上的Magento Up出售对齐问题(Magento Up sell alignment issue on the products page)

    • Copyright ©2023 peixunduo.com All Rights Reserved.粤ICP备14003112号

    本站部分内容来源于互联网，仅供学习和参考使用，请莫用于商业用途。如有侵犯你的版权，请联系我们(neng862121861#163.com)，本站将尽快处理。谢谢合作！