今天开发提交一个存储过程要发布到正式环境

call report.proc_zerobuy(20161222,20161222,0);

Lock wait timeout exceeded; try restarting transaction

请求锁超时了

再次执行这个存储过程，查看事务表：

一共有两个事务在运行

mysql> SELECT * FROM information_schema.INNODB_TRX\G;

*************************** 1. row ***************************

trx_id: 58543408

trx_state: LOCK WAIT

trx_started: 2016-12-23 15:16:40

trx_requested_lock_id: 58543408:2902030:9:2

trx_wait_started: 2016-12-23 15:16:40

trx_weight: 797

trx_mysql_thread_id: 4227583

trx_query: insert into trc_report.trc_zerobuy

select

ooo.pay_time,

ooo.tid,

ooo.receiver_name,

ooo.receiver_mobile,

ooo.bn,

ooo.barcode,

ooo.num,

ooo.mkt_price,

ooo.title,

ooo.cat_name1,

ooo.cat_name2,

ooo.cat_name3,

ooo.item_spec,

ooo.shop_name,

ooo.trade_type,

ooo.investment_amount,

o.id,

a.accounttyf_id,

cu.name,

o.pay_time as invest_time,

o.amount_in,

o.equ_def_name

FROM

(

SELECT

sozb.remote_order_id as remote_trade_id,

sozb.pay_time,-- 订单支付时间

null as tid,-- 订单号

null as receiver_name,-- 收件人姓名

null as receiver_mobile,-- 收货人电话,

null as bn,-- 商品货号

null as barcode,-- 条形码

null as num,-- 购买量

null as mkt_price,-- 商品售价

si.title,

aaaa.cat_name as cat_name1,-- 一级类目

bbbb.cat_name as cat_name2,-- 二级类目

sc.cat_nam

trx_operation_state: starting index read

trx_tables_in_use: 27

trx_tables_locked: 27

trx_lock_structs: 797

trx_lock_memory_bytes: 79400

trx_rows_locked: 42281

trx_rows_modified: 0

trx_concurrency_tickets: 0

trx_isolation_level: REPEATABLE READ

trx_unique_checks: 1

trx_foreign_key_checks: 1

trx_last_foreign_key_error: NULL

trx_adaptive_hash_latched: 0

trx_adaptive_hash_timeout: 10000

trx_is_read_only: 0

trx_autocommit_non_locking: 0

*************************** 2. row ***************************

trx_id: 58537811

trx_state: RUNNING

trx_started: 2016-12-23 14:53:44

trx_requested_lock_id: NULL

trx_wait_started: NULL

trx_weight: 914

trx_mysql_thread_id: 4227039

trx_query: SELECT tid, shop_id, user_id, dlytmpl_id, dlytmpl_ids, ziti_addr, status, cancel_status, cancel_reason, pay_type, payment, points_fee, total_fee, post_fee, payed_fee, seller_rate, receiver_name, created_time, pay_time, consign_time, end_time, modified_time, timeout_action_time, send_time, receiver_state, receiver_city, receiver_district, receiver_address, receiver_zip, receiver_mobile, receiver_idnumber, receiver_phone, title, discount_fee, consume_point_fee, buyer_message, need_invoice, invoice_name, invoice_type, invoice_main, adjust_fee, trade_from, itemnum, shop_flag, shop_memo, buyer_area, area_id, step_trade_status, total_weight, step_paid_fee, shipping_type, obtain_point_fee, trade_memo, buyer_rate, is_part_consign, real_point_fee, ip, anony, is_clearing, disabled, ziti_memo, is_del, receiver_idcard_front, receiver_idcard_back, total_tax, group_purchase_status, trade_type, installment FROM systrade_trade WHERE ( ( tid = 1611160122390064 ) )

trx_operation_state: NULL

trx_tables_in_use: 1

trx_tables_locked: 0

trx_lock_structs: 898

trx_lock_memory_bytes: 95784

trx_rows_locked: 25199

trx_rows_modified: 16

trx_concurrency_tickets: 0

trx_isolation_level: REPEATABLE READ

trx_unique_checks: 1

trx_foreign_key_checks: 1

trx_last_foreign_key_error: NULL

trx_adaptive_hash_latched: 0

trx_adaptive_hash_timeout: 10000

trx_is_read_only: 0

trx_autocommit_non_locking: 0

2 rows in set (0.00 sec)

第一个事务58543408就是正在运行的存储过程，trx_state状态一直在等待lock,但没告知等待哪个表哪行数据的锁。

一共就两个事务，那第二个事物肯定是阻塞第一个事务的凶手了，第二个事务一直runing状态执行很长一段时间了。

从执行语句来看，事务二只是在表systrade_trade上执行一个简单的查询，事务一篇幅太长就不把代码贴出来，其中也用到了systrade_trade这张表，大致情况如下：

transaction1:insert into d select * from b join c join a

transaction2: select * from a where id =1

transaction2一直在运行

transaction1请求表a锁超时

读居然阻塞读了，真奇怪了。

和开发人员交涉后，决定kill掉这个transaction2

mysql>kill 4227039；

kill掉transaction2后，transaction1执行完成。

虽然解决了，但是读阻塞读让人有点想不通，事后也进行了问题重现，不管对表systrade_trade进行start transaction或者select for update，都不会阻塞transaction1。这个问题先遗留在这！

ITPub博客

Lock wait timeout exceeded; try restarting transaction

最新文章