Hey folks, I was wondering whether Ecto.Repo.transaction/2 locks the entire table if I update every row in the table.
Let’s say I have a table with 100.000 rows and need to update them one-by-one. I can’t use a custom UPDATE statement or Ecto.Repo.update_all/3 but need to do it in memory. I want my transaction to rollback if any update fails, so I think I need to wrap it in a Ecto.Repo.transaction/2. But the update might take a while and I can’t lock the table for a long time.
Does my update lock the entire table or will I be able to create/update/delete in the table even when the update job is running?
My update job looks roughly like this:
Repo.transaction(fn ->
from(r in Row)
|> Repo.stream()
|> Stream.map(&update_row/1)
|> Stream.run()
end)
defp update_row(row) do
case do_update(row) do
{:error, error} -> Repo.rollback(error)
success -> success
end
end
Assuming you use Postgres, the table will not be locked. Keep in mind that the selected rows could be modified by another transaction in parallel, so your update should not depend on the previous values. You could explicitly lock the rows with Ecto.Query.lock/3.
You might have to worry about deadlock if there are other updates happening on the same rows at the same time.
i.e. this transaction updates rows 1, 2, 3, 4, …, 10000 (in that order)
another transaction updates rows 10000, 9999, 9998, …, 1(in that order)
You might get a situation where transaction 1 is waiting for transaction 2 to finish updating row 9999 (for example) and transaction 2 is waiting for transaction 1 to finish updating row 2 (for example).
If this is a concern you would need to make sure both transactions are updating in the same order.
You should run some experiments with concurrent modification in a realistic-but-safe environment. While PG doesn’t automatically lock rows, other features like constraint enforcement can make a long-running transaction block other writes.
A common way that folks encounter this is with a unique constraint, the long-running transaction used by the Ecto sandbox, and async: true tests - the first test to insert a row with some_unique_column = 'foo' will force other transactions to wait until they know they can commit or not.
Another approach I’ve had success with in situations like this:
make a temporary place to put the “new” data (maybe extra columns on the target table, maybe a whole new table)
backfill the “new” data into that. Nothing is reading from it yet, so there’s no need for a long-running transaction. If things go completely wrong and need rolling back just drop the temporary place and try again.
swap the “new” data into position with either column or table renames; these are inexpensive metadata updates that happen atomically.
Part 2 is especially handy for investigating things when this process uncovers data the business analysts insist “can’t happen”.
In this case we mean safe from multiple transactions infinitely waiting on each other. This doesn’t mean safe from transactions failing; in fact the system explicitly forces one of the involved transactions to fail in the timeout case so that other processing can go on. So anytime we worry about deadlocking, we need to be sure that we have the ability to handle the failed transactions. Naturally, in the best case we write our code in such a way as to avoid deadlocks: but that’s not as simple as it sounds.
For the thread generally…this whole area about what locks when and under what circumstances can get pretty complex and it pays to have at the very least 1) a basic understanding of relational database mechanics (locking and transaction isolation concepts generally in this case); and 2) at least a read through of the transaction, locking, and transaction isolation documentation of the specific database you are working with. Over the years I’ve worked with a fair number of different databases and the general concepts are pretty broadly applicable to getting you to know what questions need to be asked for your specific database. After that, I don’t think you need immediate recall of all the locking/transaction isolation details for your particular database under your fingers, but you should know enough to get the gist of when you need to do a refresher on details that might apply to a particular scenario.
The simple use cases where you’re just issuing an update against a lot of records in a single transaction, and assuming the read requirements of other possible concurrent transactions don’t need to care too much about in progress updates, issuing a simple update without coding for a lot of transaction handling is fine. I’m going to bet this gets many of us through a lot of common cases which is why this kind of topic probably doesn’t get raised more.
For the case that @PJUllrich has posted, I suspect it’s just a larger version of the simpler case. But it’s probably worth pointing out that, while PostgreSQL won’t force you to do a table lock or do a lock escalation if you hit enough rows (looking at you MSSQL) with an update, there are other more operational considerations you need to have in mind. For example, updating a lot of rows in one update I would imagine could prompt some sizeable autovacuum runs if you catch enough and depending on your database configurations; once the updating transaction is no longer visible you have a bunch of effectively dead rows at once that vacuum will want to deal with. So not a logic problem or a locking problem per se, but a possible operational impact… 100K rows probably nothing to be too worried about, but if that update process hit more rows over time it could be a performance constraint that pops up later.
