DDC-178: Query Hint for LOCK mechanisms plus support in $em->find() #218

New Issue

2026-01-22T12:31:04+01:00

admin commented

2026-01-22 12:31:04 +01:00

Originally created by @doctrinebot on GitHub (Nov 26, 2009).

Originally assigned to: @beberlei on GitHub.

Jira issue originally created by user @beberlei:

In some scenarios it is necessary to explicitly lock rows for update in a select query. My idea would be to support it twofold:

Add a LockMode Class:

final abstract class Lock
{
    const NONE = 0;
    const OPTIMISTIC = 1;
    const PESSIMISTIC_READ = 2;
    const PESSIMISTIC_WRITE = 4;
}

Add methods to platforms that can add necessary READ/WRITE Lock query additions like FOR UPDATE/SHARED which afaik are supported by all rmdbs in some way.
Add a query hint "lockMode" which takes a Lock constant.
Add a query hint "lockVersion" which takes an integer or timestamp versioning value.
Change DQL Parser to apply lock mode if set, and if lock_mode = optimistic, add a where clause for the only top-level class (more not supported? Reread Evans DDD / Aggregates)
Change the $em->find() method to the following signature:

public function find($class, $identifier, $lockMode=0, $lockVersion=null);

And if the values are set, set the appropriate query hints.

Updated API specification

LockModes

final abstract class LockMode
{
    const NONE = 0;
    const OPTIMISTIC = 1;
    const PESSIMISTIC_READ = 2;
    const PESSIMISTIC_WRITE = 4;
}

Constraints

LockMode::OPTIMISTIC requires entities to be versioned
LockMode::PESSIMISTIC_READ/WRITE works similarly for versioned as well as non-versioned entities. However, lock() after read only works for versioned entities.

API spec

$query->setLockMode(LockMode::OPTIMISTIC)

Effects:

Throw OptimisticLockException if any of the entities fetched (or fetch-joined) by the query are not versioned.
Otherwise proceed normally, SQL is not modified.

$query->setLockMode(LockMode::PESSIMISTIC_READ/WRITE)

Effects:

Throw TransactionRequiredException if there is no running transaction.
Modify the SQL with an appropriate locking clause (i.e. FOR UPDATE) that can be platform-specific, to acquire a pessimistic lock on all read entities.
Throw PessimisticLockException if lock(s) could not be obtained.

$em->find($entity, LockMode::OPTIMISTIC) (or findBy et al)

Effects:

Throw OptimisticLockException if entity is not versioned.
Otherwise proceeed normally.

$em->find($entity, LockMode::OPTIMISTIC, $version) (or findBy et al)

Effects:

Throw OptimisticLockException if entity is not versioned.
find() entity normally, no SQL modification.
Throw OptimisticLockException if there is a version mismatch ($version != $entity->version)
Otherwise proceed normally.

$em->find($entity, LockMode::PESSIMISTIC_READ/WRITE)

Effects:

Throw TransactionRequiredException if there is no active transaction.
Modify the SQL to incude an appropriate platform-specific pessimistic lock (i.e. FOR UPDATE)
throw PessimisticLockException If lock could not be obtained
Otherwise proceed normally
Refresh entity with lock when entity with id exists in identity map already

$em->refresh($entity, LockMode::OPTIMISTIC)

Effects:

Throw OptimisticLockException if entity is not versioned.
Otherwise proceed normally. (What about cascades here? Need to take that into account probably)

$em->refresh($entity, LockMode::PESSIMISTIC_READ/WRITE)

Effects:

Throw TransactionRequiredException if there is no active transaction.
Ensure the SQL used for refreshing is modified accordingly with the platform-specific pessimistic locking clause.
Throw PessimisticLockException if the lock could not be obtained.
Otherwise proceed normally.

$em->lock($entity, LockMode::OPTIMISTIC)

Effects:

Throw OptimisticLockException if entity is not versioned.
Otherwise do nothing (NOOP).

$em->lock($entity, LockMode::PESSIMISTIC_READ/WRITE)

Effects:

Throw TransactionRequiredException if there is no active transaction.
Throw PessimisticLockException if entity is not versioned (this is always a "lock after read")
** Issue straight, minimal locking SQL (we probably must* include the version column in the select), platform-specific. Note: Probably get the SQL from the persisters to account for different inheritance mapping strategies. The last part of the SQL, the locking clause, is taken from the platforms.
Throw PessimisticLockException if the lock could not be obtained.
Compare the newly read version with the old version. If they dont match throw PessimisticLockException (this means the entity was changed since it was read).
Otherwise proceed normally.

Originally created by @doctrinebot on GitHub (Nov 26, 2009). Originally assigned to: @beberlei on GitHub. Jira issue originally created by user @beberlei: In some scenarios it is necessary to explicitly lock rows for update in a select query. My idea would be to support it twofold: 1. Add a LockMode Class: ``` final abstract class Lock { const NONE = 0; const OPTIMISTIC = 1; const PESSIMISTIC_READ = 2; const PESSIMISTIC_WRITE = 4; } ``` 1. Add methods to platforms that can add necessary READ/WRITE Lock query additions like FOR UPDATE/SHARED which afaik are supported by all rmdbs in some way. 2. Add a query hint "lockMode" which takes a Lock constant. 3. Add a query hint "lockVersion" which takes an integer or timestamp versioning value. 4. Change DQL Parser to apply lock mode if set, and if lock_mode = optimistic, add a where clause for the only top-level class (more not supported? Reread Evans DDD / Aggregates) 5. Change the $em->find() method to the following signature: ``` public function find($class, $identifier, $lockMode=0, $lockVersion=null); ``` And if the values are set, set the appropriate query hints. --- ## Updated API specification ### LockModes ``` final abstract class LockMode { const NONE = 0; const OPTIMISTIC = 1; const PESSIMISTIC_READ = 2; const PESSIMISTIC_WRITE = 4; } ``` ### Constraints - LockMode::OPTIMISTIC requires entities to be versioned - LockMode::PESSIMISTIC_READ/WRITE works similarly for versioned as well as non-versioned entities. However, lock() after read only works for versioned entities. ### API spec #### $query->setLockMode(LockMode::OPTIMISTIC) Effects: - Throw OptimisticLockException if any of the entities fetched (or fetch-joined) by the query are not versioned. - Otherwise proceed normally, SQL is not modified. #### $query->setLockMode(LockMode::PESSIMISTIC_READ/WRITE) Effects: - Throw TransactionRequiredException if there is no running transaction. - Modify the SQL with an appropriate locking clause (i.e. FOR UPDATE) that can be platform-specific, to acquire a pessimistic lock on all read entities. - Throw PessimisticLockException if lock(s) could not be obtained. #### $em->find($entity, LockMode::OPTIMISTIC) (or findBy et al) Effects: - Throw OptimisticLockException if entity is not versioned. - Otherwise proceeed normally. #### $em->find($entity, LockMode::OPTIMISTIC, $version) (or findBy et al) Effects: - Throw OptimisticLockException if entity is not versioned. - find() entity normally, no SQL modification. - Throw OptimisticLockException if there is a version mismatch ($version != $entity->version) - Otherwise proceed normally. #### $em->find($entity, LockMode::PESSIMISTIC_READ/WRITE) Effects: - Throw TransactionRequiredException if there is no active transaction. - Modify the SQL to incude an appropriate platform-specific pessimistic lock (i.e. FOR UPDATE) - throw PessimisticLockException If lock could not be obtained - Otherwise proceed normally - Refresh entity with lock when entity with id exists in identity map already #### $em->refresh($entity, LockMode::OPTIMISTIC) Effects: - Throw OptimisticLockException if entity is not versioned. - Otherwise proceed normally. (What about cascades here? Need to take that into account probably) #### $em->refresh($entity, LockMode::PESSIMISTIC_READ/WRITE) Effects: - Throw TransactionRequiredException if there is no active transaction. - Ensure the SQL used for refreshing is modified accordingly with the platform-specific pessimistic locking clause. - Throw PessimisticLockException if the lock could not be obtained. - Otherwise proceed normally. #### $em->lock($entity, LockMode::OPTIMISTIC) Effects: - Throw OptimisticLockException if entity is not versioned. - Otherwise do nothing (NOOP). #### $em->lock($entity, LockMode::PESSIMISTIC_READ/WRITE) Effects: - Throw TransactionRequiredException if there is no active transaction. - Throw PessimisticLockException if entity is not versioned (this is always a "lock after read") *\* Issue straight, minimal locking SQL (we probably _must_\* include the version column in the select), platform-specific. Note: Probably get the SQL from the persisters to account for different inheritance mapping strategies. The last part of the SQL, the locking clause, is taken from the platforms. - Throw PessimisticLockException if the lock could not be obtained. - Compare the newly read version with the old version. If they dont match throw PessimisticLockException (this means the entity was changed since it was read). - Otherwise proceed normally.

admin added the New Feature label 2026-01-22 12:31:04 +01:00

admin referenced this issue

2026-01-22 15:57:20 +01:00

[PR #218] [CLOSED] association override #7879

admin referenced this issue

2026-01-22 15:58:07 +01:00

[PR #335] [MERGED] association/attribute override #8043

Sign in to join this conversation.