今天開發提交一個儲存過程要釋出到正式環境

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。這個問題先遺留在這！

Lock wait timeout exceeded; try restarting transaction

相關文章