0

My colleague at work and I were wondering if, during an update, a column is being updated while the same column is used in where clause, there are chances of deadlock.

For ex:

UPDATE EMPLOYEES
SET DEPT_ID = NULL
WHERE DEPT_ID = 13;

So if the table EMPLOYEES contains about a million records, are there chances of deadlock?

arunwithasmile
  • 300
  • 4
  • 16
  • 2
    Absolutely, not. This is a perfectly legitimate update query. – redneb Sep 29 '16 at 11:47
  • 2
    I don't think there is a chance of a deadlock if only *one* query is running. Any time you are updating multiple rows, you have a chance that other queries may try to update or delete those rows, leading to a potential deadlock situation, but using the update column in the `where` isn't the cause. – Gordon Linoff Sep 29 '16 at 11:47
  • 2
    A deadlock **always** involves _at least_ **two** transactions. A single statement will never "deadlock itself". –  Sep 29 '16 at 11:49
  • So do you have your answer here? – Erwin Brandstetter Mar 29 '18 at 18:32

1 Answers1

0

There is no chance for a deadlock at all. Not only will a single query never deadlock itself in Postgres (see comments), there is also no chance for a deadlock in combination with the same query in a concurrent transactions.

The minimum "requirements" for a deadlock:

  • At least two competing concurrent transactions.
  • Each of both must lock a resource that one of the others will try to access later.
  • Each of both must later try to access a resource locked by the other transaction. So that at least two wait for the other to finish.

In theory two concurrent, identical calls like you display have the potential for a deadlock if there are multiple rows with the same DEPT_IT. Since there is no ORDER BY for a DELETE, it can take an exclusive row lock on rows to delete in any arbitrary order. Two identical commands might start with different rows and end up deadlocking each other.

In practice, this is not going to happen because both concurrent deletes will take locks in the same order thereby voiding any potential for deadlocks. We would need additional concurrent transactions or more commands in the same transaction trying to lock resources out of order.

But all of this is completely unrelated to the fact that a column to be updated is also in the WHERE clause. (Even if indexes on the column are involved.) Due to the MVCC model of Postgres, it writes a new row version anyway, no matter which columns are actually updated.

If you should run into deadlocks involving out-of-order row locks, you can solve it using SELECT .. FOR UPDATE with a deterministic ORDER BY in a subquery:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228