Rails 已解决 Rails transaction 引起 MySQL 死锁问题

noob · June 17, 2016 · Last by noob replied at June 18, 2016 · 3553 hits

问题截图

出现问题之后:在 mysql shell 中执行 insert into fin_user_accounts(xx,xx,xx) values(xx,xx,xx)。

返回错误信息:Lock wait timeout exceeded; try restarting transaction

引起问题出现的代码如下:

def create_account profile
  Fin::UserAccount.transaction do
    # 创建资金账号
    ua = Fin::UserAccount.create_account profile
    # 更新会员关联表的ID
    profile.update_user_account_id(ua.id)
  end
end


# 如果还没有资金账户,创建资金账户
  if profile.user_account_id.nil?
    fin_account_service = UserFinAccountService.new
    fin_account_service.request = @request
    if !fin_account_service.create_account profile
      charge_request.errors.add_to_base("创建资金账户失败。")
      return order
    end
  end
  order.user_account_id = profile.user_account_id

之前直接在数据库中添加了一条资金账号数据但并没有更新对应的会员表数据,之后再进行充值时,会报上图出现错误,便手动在数据库中把会员表中的数据更新,暂时解决。 但是之后使用新用户有出现同样问题。

查询了:show processlist; +---------------+--------+-------------------------------------------------------------------------+ | command | state | info | +---------------+----------- +---------------------------------------------------------------------+ | QUERY | UPDATE | insert into fin_user_accounts(xx,xx,xx) values(xx,xx,xx) | +---------+-----+-------+---------------------------------------------------------------------------+ (猜想是否程序还在等待 profile.update_user_account_id(ua.id)操作)

show innodb status

*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
160618 11:42:40 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 46 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 3022, signal count 3022
Mutex spin waits 0, rounds 15701, OS waits 8
RW-shared spins 6958, OS waits 3009; RW-excl spins 5, OS waits 5
------------
TRANSACTIONS
------------
Trx id counter 0 29446433
Purge done for trx's n:o < 0 29446407 undo n:o < 0 0
History list length 40
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 2687, OS thread id 2708777872
MySQL thread id 19282, query id 133303 localhost root
show innodb status
---TRANSACTION 0 0, not started, process no 2687, OS thread id 2767657872
MySQL thread id 19278, query id 132619 127.0.0.1 root
---TRANSACTION 0 0, not started, process no 2687, OS thread id 2764352400
MySQL thread id 19266, query id 133301 127.0.0.1 root
---TRANSACTION 0 29446432, not started, process no 2687, OS thread id 2764553104
MySQL thread id 19263, query id 133302 127.0.0.1 root
---TRANSACTION 0 0, not started, process no 2687, OS thread id 2764954512
MySQL thread id 19259, query id 132249 127.0.0.1 root
---TRANSACTION 0 29446426, not started, process no 2687, OS thread id 2708978576
MySQL thread id 19257, query id 132791 192.168.1.150 root
---TRANSACTION 0 29446239, not started, process no 2687, OS thread id 2766052240
MySQL thread id 19207, query id 122339 127.0.0.1 root
---TRANSACTION 0 29445785, not started, process no 2687, OS thread id 2765355920
MySQL thread id 11, query id 1821 127.0.0.1 root
---TRANSACTION 0 29445848, ACTIVE 58874 sec, process no 2687, OS thread id 2768038800
2 lock struct(s), heap size 320, 1 row lock(s), undo log entries 1
MySQL thread id 10, query id 3725 127.0.0.1 root
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
26327 OS file reads, 350 OS file writes, 243 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 5, seg size 7,
3 inserts, 3 merged recs, 3 merges
Hash table size 276707, node heap has 2 buffer(s)
16.74 hash searches/s, 6.80 non-hash searches/s
---
LOG
---
Log sequence number 12 3680676575
Log flushed up to   12 3680676575
Last checkpoint at  12 3680676575
0 pending log writes, 0 pending chkp writes
155 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 81330832; in additional pool allocated 1048576
Dictionary memory allocated 359936
Buffer pool size   4096
Free buffers       0
Database pages     4094
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 128174, created 0, written 183
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 2687, id 2719468432, state: sleeping
Number of rows inserted 6, updated 36, deleted 0, read 2135875
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 22.22 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

还请各位前辈指点迷津,谢谢。

解决方法:show processlist;然后 kill 掉 status = sleep

transaction 本身和锁没关系 应该是别的地方锁了数据库没 commit

楼主,你提供的信息不全。在 db console 执行 SHOW INNODB STATUS 把死锁的 log 贴出来。

https://www.percona.com/blog/2006/07/17/show-innodb-status-walk-through/

我想知道是哪个 transaction 与他相争,导致死锁了。

#3 楼 @xiaoronglv 谢谢提醒已贴出

#1 楼 @ch3n 请问我改如何查看哪些没有 commit?对这块了解不够深刻,还请赐教

You need to Sign in before reply, if you don't have an account, please Sign up first.