UnitOfWork should run UPDATES and DELETES in specific order to avoid ShareLocks on concurrent db transactions during commit() #7340

New Issue

Open

opened 2026-01-22 15:50:16 +01:00 by admin · 2 comments

admin commented 2026-01-22 15:50:16 +01:00

Owner

Copy Link

Originally created by @d-ph on GitHub (Mar 8, 2024).

Feature Request

Q	A
New Feature	no
RFC	yes
BC Break	no

Summary

I would like to propose that the UnitOfWork::commit() should run UPDATEs (and ideally DELETEs too) in a certain order, so that the risk of running into deadlocks on "waiting for ShareLock on transaction X" (in even moderately concurrent situations) is vastly reduced. I would like to propose that the UPDATE statements should be ordered alphabetically by the FQCNs of the entities being updated, and then ascending by the ids of the entities.

In other words, I would like to propose that the UnitOfWork should run the following updates in the following order:

// contents of $this->entityUpdates

MyApp\Entity\User, id: 10
MyApp\Entity\Order, id: 1
MyApp\Entity\Job, id: 3
MyApp\Entity\Job, id: 2
MyApp\Entity\Order\Voucher, id: 1
MyApp\Entity\Job, id: 1
MyApp\Entity\Order\Voucher, id: 2

// contents of $this->entityUpdates, ordered by FQCN and ids

MyApp\Entity\Job, id: 1
MyApp\Entity\Job, id: 2
MyApp\Entity\Job, id: 3
MyApp\Entity\Order, id: 1
MyApp\Entity\Order\Voucher, id: 1
MyApp\Entity\Order\Voucher, id: 2
MyApp\Entity\User, id: 10

By ordering the statements as described above, an attempt is made to avoid the following error from happening (error log from postgres, but there is a rumour that it would happen in mysql/mariadb also):

An exception occurred while executing a query: SQLSTATE[40P01]: Deadlock detected: 7 ERROR: deadlock detected
DETAIL: Process 6258 waits for ShareLock on transaction 10320; blocked by process 6259.
Process 6259 waits for ShareLock on transaction 10319; blocked by process 6258.
HINT: See server log for query details.
CONTEXT: while updating tuple (70,3) in relation "users"

Reproduction of the problem using a real-world scenario

The problem can be reproduced by simulating a "long flush" using the sleep() instruction in the following way:

public function index(Request $request)
{
  $case = $request->query->get('case', 'a');

  /** @var EntityManager $em */
  $em = $this->managerRegistry->getManager();
  $em->wrapInTransaction(function () use ($em, $case) {
      $dbal = $em->getConnection();

      if ($case === 'a') {
          $dbal->executeStatement('UPDATE "users" SET points_balance = 100 WHERE id = 1');
      } else {
          $dbal->executeStatement('UPDATE "order" SET total_voucher_discount = 200 WHERE id = 1');
      }

      $em->flush();

      if ($case === 'a') {
          sleep(4);
      }

      if ($case === 'a') {
          $dbal->executeStatement('UPDATE "order" SET total_voucher_discount = 100 WHERE id = 1');
      } else {
          $dbal->executeStatement('UPDATE "users" SET points_balance = 200 WHERE id = 1');
      }

      $em->flush();
  });
  
  return new Response("done, case '{$case}'");
}

Running the code in two browser tabs, one with the case query-parameter set to 'a', and one with the same parameter set to 'b', leads to a situation where both tabs run for 4 seconds ("tab a" sleeps for 4 seconds, and "tab b" is sync-blocked by postgres when the tab attempts to run the "UPDATE users SET 200" statement), and then one of the tabs crashes, and the other one succeeds. The crash happens because "tab a" wakes up and attempts to run "UPDATE order SET 100" for a row which is already "locked for write" in a db transaction owned by "tab b". Postgres detects that deadlock and immediately terminates one of the conflicting db transaction.

This is also explained in postgres documentation (however again: please note that this is not only postgres-specific): https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-DEADLOCKS

Note that deadlocks can also occur as the result of row-level locks (and thus, they can occur even if explicit locking is not used) (...) Transaction one attempts to acquire a row-level lock on the specified row, but it cannot: transaction two already holds such a lock. So it waits for transaction two to complete. Thus, transaction one is blocked on transaction two, and transaction two is blocked on transaction one: a deadlock condition. PostgreSQL will detect this situation and abort one of the transactions. (...) The best defense against deadlocks is generally to avoid them by being certain that all applications using a database acquire locks on multiple objects in a consistent order. In the example above, if both transactions had updated the rows in the same order, no deadlock would have occurred.

The severity of this problem

This problem is bound to happen sooner than later, when a dev starts using cron scripts or implements a webhook events retriever from a 3rd party api. This leads to a situation where the same rows in the database (especially of the "user" or "order" kind) may be attempted to be UPDATEd concurrently (because e.g. the website user makes changes in some sort of "My Account" panel, but at the same time a webhook event is received from a 3rd party api that attempts to UPDATE the same user record).

Note how this is not only about updating fields that are indexed or that are primary/foreign keys. A simultaneous update of a "plain & boring" text field in a record will lock that record for writing for as long a db transaction lasts (and the UnitOfWork runs commit() in a db transaction (for a very good reason)).

Also, note how currently the order of UPDATEs issued by the UnitOfWork::commit() is determined by the order of when entities are hydrated in the app. This makes it quite difficult to reproduce reliably in complex codebases, but the db error logs don't lie, and the "ShareLock deadlocks" do happen.

Further notes

As mentioned in the summary, the DELETE statements are also likely to be subject to the same problem, and therefore would also benefit from a similar ordering. However, I do see how DELETE statements would be harder to order than UPDATEs because DELETEs are already being ordered. I wouldn't say that it's impossible to "order them even further", but just ordering UPDATES would alone reduce the problem significantly.

I would love to hear your comments to this proposition.

Thank you.

Originally created by @d-ph on GitHub (Mar 8, 2024). ### Feature Request | Q | A |------------ | ------ | New Feature | no | RFC | yes | BC Break | no ### Summary I would like to propose that the `UnitOfWork::commit()` should run UPDATEs (and ideally DELETEs too) in a certain order, so that the risk of running into deadlocks on "waiting for ShareLock on transaction X" (in even moderately concurrent situations) is vastly reduced. I would like to propose that the UPDATE statements should be ordered alphabetically by the FQCNs of the entities being updated, and then ascending by the ids of the entities. In other words, I would like to propose that the UnitOfWork should run the following updates in the following order: ``` // contents of $this->entityUpdates MyApp\Entity\User, id: 10 MyApp\Entity\Order, id: 1 MyApp\Entity\Job, id: 3 MyApp\Entity\Job, id: 2 MyApp\Entity\Order\Voucher, id: 1 MyApp\Entity\Job, id: 1 MyApp\Entity\Order\Voucher, id: 2 ``` ``` // contents of $this->entityUpdates, ordered by FQCN and ids MyApp\Entity\Job, id: 1 MyApp\Entity\Job, id: 2 MyApp\Entity\Job, id: 3 MyApp\Entity\Order, id: 1 MyApp\Entity\Order\Voucher, id: 1 MyApp\Entity\Order\Voucher, id: 2 MyApp\Entity\User, id: 10 ``` By ordering the statements as described above, an attempt is made to avoid the following error from happening (error log from postgres, but there is a rumour that it would happen in mysql/mariadb also): ``` An exception occurred while executing a query: SQLSTATE[40P01]: Deadlock detected: 7 ERROR: deadlock detected DETAIL: Process 6258 waits for ShareLock on transaction 10320; blocked by process 6259. Process 6259 waits for ShareLock on transaction 10319; blocked by process 6258. HINT: See server log for query details. CONTEXT: while updating tuple (70,3) in relation "users" ``` ### Reproduction of the problem using a real-world scenario The problem can be reproduced by simulating a "long flush" using the `sleep()` instruction in the following way: ```php public function index(Request $request) { $case = $request->query->get('case', 'a'); /** @var EntityManager $em */ $em = $this->managerRegistry->getManager(); $em->wrapInTransaction(function () use ($em, $case) { $dbal = $em->getConnection(); if ($case === 'a') { $dbal->executeStatement('UPDATE "users" SET points_balance = 100 WHERE id = 1'); } else { $dbal->executeStatement('UPDATE "order" SET total_voucher_discount = 200 WHERE id = 1'); } $em->flush(); if ($case === 'a') { sleep(4); } if ($case === 'a') { $dbal->executeStatement('UPDATE "order" SET total_voucher_discount = 100 WHERE id = 1'); } else { $dbal->executeStatement('UPDATE "users" SET points_balance = 200 WHERE id = 1'); } $em->flush(); }); return new Response("done, case '{$case}'"); } ``` Running the code in two browser tabs, one with the `case` query-parameter set to 'a', and one with the same parameter set to 'b', leads to a situation where both tabs run for 4 seconds ("tab a" sleeps for 4 seconds, and "tab b" is sync-blocked by postgres when the tab attempts to run the "UPDATE users SET 200" statement), and then one of the tabs crashes, and the other one succeeds. The crash happens because "tab a" wakes up and attempts to run "UPDATE order SET 100" for a row which is already "locked for write" in a db transaction owned by "tab b". Postgres detects that deadlock and immediately terminates one of the conflicting db transaction. This is also explained in postgres documentation (however again: please note that this is not only postgres-specific): https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-DEADLOCKS > Note that deadlocks can also occur as the result of row-level locks (and thus, they can occur even if explicit locking is not used) (...) Transaction one attempts to acquire a row-level lock on the specified row, but it cannot: transaction two already holds such a lock. So it waits for transaction two to complete. Thus, transaction one is blocked on transaction two, and transaction two is blocked on transaction one: a deadlock condition. PostgreSQL will detect this situation and abort one of the transactions. (...) The best defense against deadlocks is generally to avoid them by being certain that all applications using a database acquire locks on multiple objects in a consistent order. In the example above, if both transactions had updated the rows in the same order, no deadlock would have occurred. ### The severity of this problem This problem is bound to happen sooner than later, when a dev starts using cron scripts or implements a webhook events retriever from a 3rd party api. This leads to a situation where the same rows in the database (especially of the "user" or "order" kind) may be attempted to be UPDATEd concurrently (because e.g. the website user makes changes in some sort of "My Account" panel, but at the same time a webhook event is received from a 3rd party api that attempts to UPDATE the same user record). Note how this is not only about updating fields that are indexed or that are primary/foreign keys. A simultaneous update of a "plain & boring" text field in a record will lock that record for writing for as long a db transaction lasts (and the UnitOfWork runs commit() in a db transaction (for a very good reason)). Also, note how currently the order of UPDATEs issued by the `UnitOfWork::commit()` is determined by the order of when entities are hydrated in the app. This makes it quite difficult to reproduce reliably in complex codebases, but the db error logs don't lie, and the "ShareLock deadlocks" do happen. ### Further notes As mentioned in the summary, the DELETE statements are also likely to be subject to the same problem, and therefore would also benefit from a similar ordering. However, I do see how DELETE statements would be harder to order than UPDATEs because DELETEs are already being ordered. I wouldn't say that it's impossible to "order them even further", but just ordering UPDATES would alone reduce the problem significantly. I would love to hear your comments to this proposition. Thank you.

admin commented 2026-01-22 15:50:17 +01:00

Author

Owner

Copy Link

@greg0ire commented on GitHub (Mar 9, 2024):

I don't see an issue with this proposal except for DELETE statements, where this cannot be enforced in all situations. Cc @mpdude

@greg0ire commented on GitHub (Mar 9, 2024): I don't see an issue with this proposal except for DELETE statements, where this cannot be enforced in all situations. Cc @mpdude

admin commented 2026-01-22 15:50:17 +01:00

Author

Owner

Copy Link

@Shaddix666 commented on GitHub (Dec 24, 2024):

@d-ph

Although I fear that this is a bit late after 9 months, I have a little Christmas present.

We had the same problem and came to the conclusion that we simply have to do the locks ourselves in the correct order. This way it doesn't matter whether Doctrine takes care of a specific lock order or not.

Pseudocode:

// begin transaction
// do the locks in correct order
// flush via Doctrine
// commit (Release the locks)

The fix has not yet been implemented in production for us, but we are confident that this is a good way to go.

I hope these tips will be helpful.

Merry Christmas and good luck!

@Shaddix666 commented on GitHub (Dec 24, 2024): @d-ph Although I fear that this is a bit late after 9 months, I have a little Christmas present. We had the same problem and came to the conclusion that we simply have to do the locks ourselves in the correct order. This way it doesn't matter whether Doctrine takes care of a specific lock order or not. Pseudocode: ``` // begin transaction // do the locks in correct order // flush via Doctrine // commit (Release the locks) ``` The fix has not yet been implemented in production for us, but we are confident that this is a good way to go. I hope these tips will be helpful. Merry Christmas and good luck!

admin referenced this issue 2026-01-22 16:07:32 +01:00

[PR #7340] Fix config template for PHPUnit >= 7.2 #10436

Sign in to join this conversation.

No Branch/Tag Specified

Branches Tags

3.6.x

2.20.x

dependabot/github_actions/2.20.x/doctrine/dot-github/dot-github/workflows/composer-lint.yml-14.0.0

3.7.x

4.0.x

2.21.x

old-prototype-3.x

3.6.2

3.6.1

3.6.0

2.20.9

3.5.8

3.5.7

3.5.6

3.5.5

2.20.8

3.5.4

3.5.3

2.20.7

3.5.2

2.20.6

3.5.1

3.5.0

3.4.4

3.4.3

3.4.2

2.20.5

3.4.1

3.4.0

3.3.4

2.20.4

3.3.3

2.20.3

3.3.2

2.20.2

3.3.1

2.20.1

3.3.0

3.2.3

2.20.0

2.19.8

3.2.2

2.19.7

3.2.1

2.19.6

3.2.0

3.1.4

3.1.3

2.19.5

3.1.2

2.19.4

3.1.1

2.19.3

2.19.2

2.19.1

3.1.0

2.19.0

3.0.3

2.18.3

3.0.2

2.18.2

3.0.1

2.18.1

3.0.0

2.18.0

2.17.5

3.0.0-RC1

2.17.4

2.17.3

2.17.2

2.17.1

3.0.0-beta2

2.17.0

2.16.3

3.0.0-beta1

2.16.2

2.16.1

2.16.0

2.15.5

2.15.4

2.15.3

2.15.2

2.15.1

2.15.0

2.14.3

2.14.2

2.14.1

2.14.0

2.13.5

2.13.4

2.13.3

2.13.2

2.13.1

2.13.0

2.12.4

2.12.3

2.12.2

2.12.1

2.12.0

2.11.3

2.11.2

2.11.1

2.11.0

2.10.5

2.10.4

2.10.3

2.10.2

2.10.1

2.10.0

2.9.6

2.9.5

2.9.4

2.9.3

2.9.2

2.9.1

2.9.0

2.8.5

2.8.4

2.8.3

2.8.2

2.8.1

2.8.0

2.7.5

2.7.4

v2.7.3

v2.7.2

v2.7.1

v2.7.0

v2.6.6

v2.6.5

v2.6.4

v2.6.3

v2.6.2

v2.6.1

v2.6.0

v2.5.14

v2.5.13

v2.5.12

v2.5.11

v2.5.10

v2.5.9

v2.5.8

v2.5.7

v2.5.6

v2.5.5

v2.5.4

v2.5.3

v2.5.2

v2.4.8

v2.5.1

v2.5.0

v2.5.0-RC2

v2.5.0-RC1

v2.5.0-beta1

v2.5.0-alpha2

v2.5.0-alpha1

v2.4.7

v2.4.6

v2.4.5

v2.4.4

v2.4.3

v2.3.6

v2.4.2

2.3.5

v2.4.1

v2.4.0

2.4.0-RC2

2.4.0-RC1

2.4.0-BETA2

2.3.4

2.3.3

2.4.0-BETA1

2.3.2

2.3.1

2.3.0

2.3.0-RC4

2.3.0-RC3

2.3.0-RC2

2.2.3

2.3.0-RC1

2.3.0-BETA1

2.1.7

2.2.2

2.2.1

2.1.6

2.2.0

2.2.0-RC1

2.2.0-BETA2

2.2.0-BETA1

2.1.5

2.1.4

2.1.3

2.1.2

2.1.1

2.0.7

2.1.0

2.1.0RC3

2.1.0RC2

2.1.0RC1

2.0.6

2.1.0BETA1

2.0.5

2.0.4

2.0.3

2.0.2

2.0.1

2.0.0

2.0.0RC2

2.0.0RC1

2.0.0-BETA4

2.0.0-BETA3

2.0.0-BETA2

2.0.0-BETA1

Labels

Clear labels

BC Break

Bug

Can't Fix

Deprecation

Documentation

DQL

Duplicate

Failing Test

Hacktoberfest

Improvement

Improvement

Incomplete

Invalid

Missing Tests

New Feature

PR Created

pull-request

Mirrored from GitHub Pull Request

Question

Regression

Status: Waiting feedback

Waiting feedback

WIP

Won't Fix

No Label

Milestone

No items

No Milestone

Projects

Clear projects

No project

Assignees

Clear assignees

admin

No Assignees

1 Participants

Notifications

Subscribe

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#7340