SELECT ... FOR UPDATE同士でデッドロックさせる

最近SELECT ... FOR UPDATEでデッドロックする話を何度かしたので。

前職のときにUPDATE同士がデッドロックしてたときに、SELECT ... FOR UPDATEで排他ロックを取ってからUPDATEしてデッドロックを防ぎますってPRをレビューしてたときのことで、複数レコードの排他ロックは一瞬ですべてのレコードのロックを取れるわけではなく、ロックを取る順番が揃っていないと簡単にデッドロックしますよという話です。

https://gist.github.com/kamipo/0bb4e37d58ba18a8cefb8aa02f778231

# frozen_string_literal: true

require "mysql2"

def client
  Mysql2::Client.new(
    host: "localhost",
    username: "root",
    database: "test",
  )
end

c1 = client
c2 = client

c1.query("DROP TABLE IF EXISTS `user_tables`")
c1.query <<-SQL
CREATE TABLE `user_tables` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_user_tables_on_name` (`name`)
)
SQL

1000.downto(1) do |i|
  c1.query("INSERT INTO `user_tables` (`name`) VALUES ('p#{i}')")
end

t = Thread.new do
  100.times do |j|
    c2.query("BEGIN")
    #puts "c2 locking:#{j}"
    c2.query("SELECT 1 FROM `user_tables` FORCE INDEX(index_user_tables_on_name) WHERE `name` IN (#{1000.downto(1).map{|i|"'p#{i}'"}.join(",")}) FOR UPDATE")
    #puts "c2 locked:#{j}"
    c2.query("COMMIT")
  end
end

100.times do |j|
  c1.query("BEGIN")
  #puts "c1 locking:#{j}"
  c1.query("SELECT 1 FROM `user_tables` FORCE INDEX(PRIMARY) WHERE `id` IN (#{1.upto(1000).to_a.join(",")}) FOR UPDATE")
  #puts "c1 locked:#{j}"
  c1.query("COMMIT")
end

t.join

% be ruby lock.rb
/Users/kamipo/.rbenv/versions/2.7.0-dev/lib/ruby/gems/2.7.0/gems/bundler-1.17.3/lib/bundler/rubygems_integration.rb:200: warning: constant Gem::ConfigMap is deprecated
/Users/kamipo/.rbenv/versions/2.7.0-dev/lib/ruby/gems/2.7.0/gems/bundler-1.17.3/lib/bundler/rubygems_integration.rb:200: warning: constant Gem::ConfigMap is deprecated
#<Thread:0x00007fa6099b38c0@lock.rb:30 run> terminated with exception (report_on_exception is true):
Traceback (most recent call last):
    6: from lock.rb:31:in `block in <main>'
    5: from lock.rb:31:in `times'
    4: from lock.rb:34:in `block (2 levels) in <main>'
    3: from /Users/kamipo/src/github.com/brianmario/mysql2/lib/mysql2/client.rb:130:in `query'
    2: from /Users/kamipo/src/github.com/brianmario/mysql2/lib/mysql2/client.rb:130:in `handle_interrupt'
    1: from /Users/kamipo/src/github.com/brianmario/mysql2/lib/mysql2/client.rb:138:in `block in query'
/Users/kamipo/src/github.com/brianmario/mysql2/lib/mysql2/client.rb:138:in `_query': Deadlock found when trying to get lock; try restarting transaction (Mysql2::Error)
Traceback (most recent call last):
    6: from lock.rb:31:in `block in <main>'
    5: from lock.rb:31:in `times'
    4: from lock.rb:34:in `block (2 levels) in <main>'
    3: from /Users/kamipo/src/github.com/brianmario/mysql2/lib/mysql2/client.rb:130:in `query'
    2: from /Users/kamipo/src/github.com/brianmario/mysql2/lib/mysql2/client.rb:130:in `handle_interrupt'
    1: from /Users/kamipo/src/github.com/brianmario/mysql2/lib/mysql2/client.rb:138:in `block in query'
/Users/kamipo/src/github.com/brianmario/mysql2/lib/mysql2/client.rb:138:in `_query': Deadlock found when trying to get lock; try restarting transaction (Mysql2::Error)

これはどういう原理でデッドロックさせているかというと、プライマリキーとセカンダリキーで意図的に並び順が異なるようなデータを生成して、プライマリキーを使う実行計画のSELECT ... FOR UPDATEとセカンダリキーを使う実行計画のSELECT ... FOR UPDATEが真逆の順序でレコードのロックを取るように仕向けてデッドロックを引き起こさせています。

このように、複数レコードのロックは一瞬で同時に起きるわけではなく、順番に起きて途中の状態(ロックが取り終わったレコードとこれからロックを取るつもりのレコードがある状態)が存在するので、ロックを取る順番が一意になるようにクエリや実行計画を揃えるというのがこの手の問題に対する一般的な対処法になります。

ロックを取る順番って見えづらいので問題に気づきづらいですよね、かくいう僕も眼の良さを活かして気合いで対処してるので、なんかいい方法あったら教えてください。

かみぽわーる

kamipo's blog

SELECT ... FOR UPDATE同士でデッドロックさせる