Nutz
  1. 主页/
  2. 问答
NutzCN Logo
问答 mysql表死锁了，不知道下面这个事务是做什么的，怎么追踪？
发布于 2900天前 作者 mingkaili 2036 次浏览 复制 上一个帖子 下一个帖子
标签: 无 
<Worksheet ss:Name="Sheet1">
  <ss:Table>
   <ss:Row>
    <ss:Cell  ss:StyleID="s27"><Data ss:Type="String">trx_id</Data></ss:Cell>
    <ss:Cell  ss:StyleID="s27"><Data ss:Type="String">trx_state</Data></ss:Cell>
    <ss:Cell  ss:StyleID="s27"><Data ss:Type="String">trx_started</Data></ss:Cell>
    <ss:Cell  ss:StyleID="s27"><Data ss:Type="String">trx_requested_lock_id</Data></ss:Cell>
    <ss:Cell  ss:StyleID="s27"><Data ss:Type="String">trx_wait_started</Data></ss:Cell>
    <ss:Cell  ss:StyleID="s27"><Data ss:Type="String">trx_weight</Data></ss:Cell>
    <ss:Cell  ss:StyleID="s27"><Data ss:Type="String">trx_mysql_thread_id</Data></ss:Cell>
    <ss:Cell  ss:StyleID="s27"><Data ss:Type="String">trx_query</Data></ss:Cell>
    <ss:Cell  ss:StyleID="s27"><Data ss:Type="String">trx_operation_state</Data></ss:Cell>
    <ss:Cell  ss:StyleID="s27"><Data ss:Type="String">trx_tables_in_use</Data></ss:Cell>
    <ss:Cell  ss:StyleID="s27"><Data ss:Type="String">trx_tables_locked</Data></ss:Cell>
    <ss:Cell  ss:StyleID="s27"><Data ss:Type="String">trx_lock_structs</Data></ss:Cell>
    <ss:Cell  ss:StyleID="s27"><Data ss:Type="String">trx_lock_memory_bytes</Data></ss:Cell>
    <ss:Cell  ss:StyleID="s27"><Data ss:Type="String">trx_rows_locked</Data></ss:Cell>
    <ss:Cell  ss:StyleID="s27"><Data ss:Type="String">trx_rows_modified</Data></ss:Cell>
    <ss:Cell  ss:StyleID="s27"><Data ss:Type="String">trx_concurrency_tickets</Data></ss:Cell>
    <ss:Cell  ss:StyleID="s27"><Data ss:Type="String">trx_isolation_level</Data></ss:Cell>
    <ss:Cell  ss:StyleID="s27"><Data ss:Type="String">trx_unique_checks</Data></ss:Cell>
    <ss:Cell  ss:StyleID="s27"><Data ss:Type="String">trx_foreign_key_checks</Data></ss:Cell>
    <ss:Cell  ss:StyleID="s27"><Data ss:Type="String">trx_last_foreign_key_error</Data></ss:Cell>
    <ss:Cell  ss:StyleID="s27"><Data ss:Type="String">trx_adaptive_hash_latched</Data></ss:Cell>
    <ss:Cell  ss:StyleID="s27"><Data ss:Type="String">trx_adaptive_hash_timeout</Data></ss:Cell>
   </ss:Row>
   <ss:Row>
    <ss:Cell><Data ss:Type="String">2E37B</Data></ss:Cell>
    <ss:Cell><Data ss:Type="String">LOCK WAIT</Data></ss:Cell>
    <ss:Cell ss:StyleID="s22"><Data ss:Type="DateTime">2016-05-16T15:50:18.000</Data></ss:Cell>
    <ss:Cell><Data ss:Type="String">2E37B:0:1902:26</Data></ss:Cell>
    <ss:Cell ss:StyleID="s22"><Data ss:Type="DateTime">2016-05-16T15:51:09.000</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">2</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">62</Data></ss:Cell>
    <ss:Cell><Data ss:Type="String">INSERT INTO mv_qizhou_search(goodsId,name,marketPr</Data></ss:Cell>
    <ss:Cell><Data ss:Type="String">inserting</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">1</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">1</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">2</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">376</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">1</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">0</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">0</Data></ss:Cell>
    <ss:Cell><Data ss:Type="String">REPEATABLE READ</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">1</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">1</Data></ss:Cell>
    <ss:Cell><Data ss:Type="String"></Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">0</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">10000</Data></ss:Cell>
   </ss:Row>
   <ss:Row>
    <ss:Cell><Data ss:Type="String">2E37A</Data></ss:Cell>
    <ss:Cell><Data ss:Type="String">LOCK WAIT</Data></ss:Cell>
    <ss:Cell ss:StyleID="s22"><Data ss:Type="DateTime">2016-05-16T15:50:15.000</Data></ss:Cell>
    <ss:Cell><Data ss:Type="String">2E37A:0:2244:19</Data></ss:Cell>
    <ss:Cell ss:StyleID="s22"><Data ss:Type="DateTime">2016-05-16T15:51:06.000</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">2</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">61</Data></ss:Cell>
    <ss:Cell><Data ss:Type="String">INSERT INTO mv_qizhou_search(goodsId,name,marketPr</Data></ss:Cell>
    <ss:Cell><Data ss:Type="String">inserting</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">1</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">1</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">2</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">376</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">1</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">0</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">0</Data></ss:Cell>
    <ss:Cell><Data ss:Type="String">REPEATABLE READ</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">1</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">1</Data></ss:Cell>
    <ss:Cell><Data ss:Type="String"></Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">0</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">10000</Data></ss:Cell>
   </ss:Row>
   <ss:Row>
    <ss:Cell><Data ss:Type="String">2E379</Data></ss:Cell>
    <ss:Cell><Data ss:Type="String">LOCK WAIT</Data></ss:Cell>
    <ss:Cell ss:StyleID="s22"><Data ss:Type="DateTime">2016-05-16T15:50:11.000</Data></ss:Cell>
    <ss:Cell><Data ss:Type="String">2E379:0:4159:2</Data></ss:Cell>
    <ss:Cell ss:StyleID="s22"><Data ss:Type="DateTime">2016-05-16T15:51:02.000</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">2</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">60</Data></ss:Cell>
    <ss:Cell><Data ss:Type="String">INSERT INTO mv_qizhou_search(goodsId,name,marketPr</Data></ss:Cell>
    <ss:Cell><Data ss:Type="String">inserting</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">1</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">1</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">2</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">376</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">1</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">0</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">0</Data></ss:Cell>
    <ss:Cell><Data ss:Type="String">REPEATABLE READ</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">1</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">1</Data></ss:Cell>
    <ss:Cell><Data ss:Type="String"></Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">0</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">10000</Data></ss:Cell>
   </ss:Row>
   <ss:Row>
    <ss:Cell><Data ss:Type="String">2E378</Data></ss:Cell>
    <ss:Cell><Data ss:Type="String">LOCK WAIT</Data></ss:Cell>
    <ss:Cell ss:StyleID="s22"><Data ss:Type="DateTime">2016-05-16T15:50:06.000</Data></ss:Cell>
    <ss:Cell><Data ss:Type="String">2E378:0:4272:16</Data></ss:Cell>
    <ss:Cell ss:StyleID="s22"><Data ss:Type="DateTime">2016-05-16T15:50:57.000</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">2</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">59</Data></ss:Cell>
    <ss:Cell><Data ss:Type="String">INSERT INTO mv_qizhou_search(goodsId,name,marketPr</Data></ss:Cell>
    <ss:Cell><Data ss:Type="String">inserting</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">1</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">1</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">2</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">1248</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">1</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">0</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">0</Data></ss:Cell>
    <ss:Cell><Data ss:Type="String">REPEATABLE READ</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">1</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">1</Data></ss:Cell>
    <ss:Cell><Data ss:Type="String"></Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">0</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">10000</Data></ss:Cell>
   </ss:Row>
   <ss:Row>
    <ss:Cell><Data ss:Type="String">2E377</Data></ss:Cell>
    <ss:Cell><Data ss:Type="String">RUNNING</Data></ss:Cell>
    <ss:Cell ss:StyleID="s22"><Data ss:Type="DateTime">2016-05-16T15:49:53.000</Data></ss:Cell>
    <ss:Cell><Data ss:Type="String"></Data></ss:Cell>
    <ss:Cell><Data ss:Type="String"></Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">5810</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">58</Data></ss:Cell>
    <ss:Cell><Data ss:Type="String"></Data></ss:Cell>
    <ss:Cell><Data ss:Type="String"></Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">0</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">0</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">178</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">31160</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">5087</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">5632</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">0</Data></ss:Cell>
    <ss:Cell><Data ss:Type="String">READ COMMITTED</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">1</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">1</Data></ss:Cell>
    <ss:Cell><Data ss:Type="String"></Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">0</Data></ss:Cell>
    <ss:Cell><Data ss:Type="Number">10000</Data></ss:Cell>
   </ss:Row>
  </ss:Table>
 </Worksheet>
</Workbook>
10 回复

mysql的lock的表的当前死锁情况。

最后那条 我不知道是什么事务操作。。。

某个没有SQL的调用,神奇...

那最后那个是什么玩意啊？我不知道怎么追踪啊。我的程序是个多线程，有一个线程池，往线程池添加线程任务，然后关闭线程池，在线程池的最后写一个判断所有所有线程是否执行完成的方法。结果就出现上面的情况了，死锁。如果去掉那个方法，就会发现主线程先执行完成了，子线程后执行完成，没有死锁的情况。

@wendal 这种死锁，不知道跟nutz框架的事务配置是否有影响啊 ？mysql的索引类型是Innodb。

@mingkaili 主线程在事务内？

@wendal 是的。怀疑是主线程的事务与子线程的事务冲突了，导致死锁，事务等待。
我不知道nutz框架上配置的事务，和数据库自身默认的事务，他们是怎么样的一个关系，还是他们是一体的？

有没有用到aop事务或Trans.exec或Trans.begin/close

@wendal 我找到问题了。是mysql的gap锁，给锁住了。我的主键不是自增的。谢哥了。

参考的这个。http://tech.meituan.com/innodb-lock.html

添加回复
请先登陆
回到顶部