MySQL SELECT FOR UPDATE Lock block all threads?

Question

I just wonder mysql "SELECT FOR UPDATE" lock block all my threads in a process, and how to by pass it if I need to grant this lock in multi-threaded Applications.

To keep it simple, I just give a simple test code in ruby:

t1 = Thread.new do
  db = Sequel.connect("mysql://abcd:abcd@localhost:3306/test_db")
  db.transaction do
    db["select * from tables where id = 1 for update"].first
    10.times { |t| p 'babababa' }
  end
end

t2 = Thread.new do
  db = Sequel.connect("mysql://abcd:abcd@localhost:3306/test_db")
  db.transaction do
    db["select * from tables where id = 1 for update"].first
    10.times { |t| p 'lalalala' }
  end
end

t1.join
t2.join
p 'done'

Actually, the result will be:

thread 1 and thread 2 hang for at least 50 sec after one of the thread get the "FOR UPDATE" LOCK ( Lock wait time is 50 sec in Mysql Setting ), and then one thread raise "Lock wait timeout exceeded" error and quit, another thread successfully print its 'baba' or 'lala' out.

innodb, with mysql 5.1 version – Simon Iong Jan 31 '13 at 15:03 — Simon Iong, Jan 31 '13 at 15:03

score 2 · Answer 1 · answered Jan 31 '13 at 16:51

This is related to the fact that the mysql driver locks the entire ruby VM for every query. When the second thread hits the FOR UPDATE lock and freezes, nothing in ruby happens until it times out, causing the behavior you are seeing.

Either install the mysqlplus gem (which I doesn't blocks the entire interpreter, and Sequel's mysql adapter will use automatically if installed), or install the mysql2 gem and use the mysql2 adapter instead of the mysql adapter.

MySQL SELECT FOR UPDATE Lock block all threads?

1 Answers1