https://blog.kamipo.net/entry/2020/12/15/213359 for PostgreSQL

create_db.sql

-- psql -f create_db.sql postgres
CREATE DATABASE hogehoge;

deadlock.rb

require 'pg'

conn_0 = PG.connect(dbname: 'hogehoge')
conn_0.exec('DROP TABLE IF EXISTS t')
conn_0.exec(<<~SQL)
  CREATE TABLE t (
    id SERIAL,
    name TEXT,
    PRIMARY KEY (id),
    UNIQUE(name)
  )
SQL

conn_1 = PG.connect(dbname: 'hogehoge')
1000.downto(1) do |i|
  conn_1.query("INSERT INTO t (name) VALUES ('p#{i}')")
end

conn_2 = PG.connect(dbname: 'hogehoge')
t = Thread.new do
  100.times do |j|
    names = 1000.downto(1).map{|i|"'p#{i}'"}.join(',')
    conn_2.query('BEGIN')
    puts "conn_2 locking:#{j}"
    conn_2.query(<<~SQL)
      SELECT 1
        FROM t
       WHERE name IN (#{names})
         FOR UPDATE
    SQL
    puts "conn_2 locked:#{j}"
    conn_2.query('COMMIT')
  end
end

100.times do |j|
  ids = 1.upto(1000).to_a.join(',')
  names = 1000.downto(1).map{|i|"'p#{i}'"}.join(',')
  conn_1.query('BEGIN')
  puts "conn_1 locking:#{j}"
  conn_1.query(<<~SQL)
    SELECT 1
      FROM t
     WHERE id IN (#{ids}) -- name IN (#{names}) にするとデッドロックしない......
       FOR UPDATE
  SQL
  puts "conn_1 locked:#{j}"
  conn_1.query('COMMIT')
end

t.join

error.txt

Traceback (most recent call last):
	3: from hoge.rb:22:in `block in <main>'
	2: from hoge.rb:22:in `times'
	1: from hoge.rb:26:in `block (2 levels) in <main>'
hoge.rb:26:in `exec': ERROR:  deadlock detected (PG::TRDeadlockDetected)
DETAIL:  Process 29556 waits for ShareLock on transaction 14833; blocked by process 29555.
Process 29555 waits for ShareLock on transaction 14834; blocked by process 29556.
HINT:  See server log for query details.
CONTEXT:  while locking tuple (0,1) in relation "t"