If you run multiple batch updates in postgres, you may run into deadlocks.

We’ll look into why this happens and how we can prevent it.

For example, if we try to run 2 batches of inserts together: [0,1,2,3,4,5,6,7,8,9] and [9,8,7,6,5,4,3,2,1,0]

object DatabaseDeadlockSpec extends ZIOSpecDefault {
  final case class NumberRow(id: Int, text: String)

  private def insertQuery(row: NumberRow) =
    sql"""
         |INSERT INTO numbers(id, text)
         |VALUES (${row.id}, ${row.text})
         |ON CONFLICT(id) DO UPDATE SET
         |  text = EXCLUDED.text;
         |""".stripMargin

  // details to be filled in depending on your framework of choice
  private def insert(rows: Seq[NumberRow]): Task[Unit] = ???
  private val testLayer = ???

  private val numbers = Seq(
    NumberRow(0, "zero"),
    NumberRow(1, "one"),
    NumberRow(2, "two"),
    NumberRow(3, "three"),
    NumberRow(4, "four"),
    NumberRow(5, "five"),
    NumberRow(6, "six"),
    NumberRow(7, "seven"),
    NumberRow(8, "eight"),
    NumberRow(9, "nine"),
  )

  override def spec: Spec[TestEnvironment with Scope, Any] = suite(s"PostgresqlDatabase")(
      test("create deadlock") {
        for {
          _ <- ZIO.foreachPar(Seq(numbers, numbers.reverse))(insert)
        } yield assertCompletes
      }
    ).provideLayerShared(testLayer)
}

This looks fine enough at first glance.

but problems!

Exception in thread "zio-fiber-144" java.sql.BatchUpdateException: Batch entry 6
INSERT INTO numbers(id, text)
VALUES ( ('6'::int4) ,  ('six') )
ON CONFLICT(id) DO UPDATE SET
text = EXCLUDED.text was aborted: ERROR: deadlock detected
Detail: Process 1354 waits for ShareLock on transaction 1526; blocked by process 1353.
Process 1353 waits for ShareLock on transaction 1525; blocked by process 1354.

The problem is that the transaction keeps locks on the changed rows. This happens even if we don’t explicitly add locks.

In this example. The first batch has done 0, 1, 2, 3, 4, 5 (keeping locks on these)

The seconds batch has done 9, 8, 7, 6 (keeping locks on all these)

Now the first batch wants to get a lock for 6, which the second batch has, so it will wait for it to be released.

And the second batch wants to get a lock for 5, which the first batch has, and will wait.

deadlock!

How to prevent this?

The simplest way to prevent this is to not batch inserts (or updates).

Another is to not run multiple batches in parallel. That might mean you can only run one thread in your application. not great.

The last way is to sort your batch on the primary key. This is what happens then:

The sorted first batch will start on 0, keeping that lock.

The sorted second batch will also try to start with 0, but has to wait on the lock. The second batch will wait until batch1 is completely done, then lock 0 is released and it can run.

In this example it will hardly do anything in parallel, but if you have less clashes in your domain it will work well enough.

shadow-left