Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-178

Query Hint for LOCK mechanisms plus support in $em->find()

    Details

    • Type: New Feature New Feature
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 2.0-BETA3
    • Component/s: None
    • Security Level: All
    • Labels:
      None

      Description

      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;
      }
      

      2. 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.
      3. Add a query hint "lockMode" which takes a Lock constant.
      4. Add a query hint "lockVersion" which takes an integer or timestamp versioning value.
      5. 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)
      6. 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.
      1. ddc178_locking.diff
        26 kB
        Benjamin Eberlei
      2. ddc178_pessimistic.diff
        7 kB
        Benjamin Eberlei

        Issue Links

          Activity

          Benjamin Eberlei created issue -
          Benjamin Eberlei made changes -
          Field Original Value New Value
          Summary Query Hint for LOCK mechanisms plus support in $em->load() Query Hint for LOCK mechanisms plus support in $em->find()
          Benjamin Eberlei made changes -
          Attachment ddc178_pessimistic.diff [ 10225 ]
          Benjamin Eberlei made changes -
          Attachment ddc178_pessimistic.diff [ 10225 ]
          Benjamin Eberlei made changes -
          Attachment ddc178_pessimistic.diff [ 10227 ]
          Roman S. Borschel made changes -
          Fix Version/s 2.0-BETA2 [ 10050 ]
          Benjamin Eberlei made changes -
          Link This issue relates to DDC-410 [ DDC-410 ]
          Roman S. Borschel made changes -
          Description 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:

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

          2. 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.
          3. Add a query hint "lockMode" which takes a Lock constant.
          4. Add a query hint "lockVersion" which takes an integer or timestamp versioning value.
          5. 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)
          6. Change the $em->find() method to the following signature:

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

          And if the values are set, set the appropriate query hints.
          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:

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

          2. 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.
          3. Add a query hint "lockMode" which takes a Lock constant.
          4. Add a query hint "lockVersion" which takes an integer or timestamp versioning value.
          5. 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)
          6. Change the $em->find() method to the following signature:

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

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

          ----

          h2. Updated API specification

          h3. LockModes

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

          h3. 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.

          h3. API spec

          1) $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.

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

          Effects:

           * 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.

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

          Effects:

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

          4) $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.

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

          Effects:

           * 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

          6) $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)

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

          Effects:

           * Throw PessimisticLockException if entity is not versioned. refresh() with a pessimistic lock is a "lock after read" so there might be stale data. Entity must be versioned in order to verify that. Alternatively we could compare *all* columns but thats going too far imho.
           * 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.
           * Throw PessimisticLockException if the refreshed version is not equal to the previous version which the entity had prior to the pessimistic refresh.

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

          Effects:

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

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

          Effects:

           * 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.
          Roman S. Borschel made changes -
          Description 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:

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

          2. 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.
          3. Add a query hint "lockMode" which takes a Lock constant.
          4. Add a query hint "lockVersion" which takes an integer or timestamp versioning value.
          5. 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)
          6. Change the $em->find() method to the following signature:

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

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

          ----

          h2. Updated API specification

          h3. LockModes

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

          h3. 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.

          h3. API spec

          1) $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.

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

          Effects:

           * 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.

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

          Effects:

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

          4) $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.

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

          Effects:

           * 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

          6) $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)

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

          Effects:

           * Throw PessimisticLockException if entity is not versioned. refresh() with a pessimistic lock is a "lock after read" so there might be stale data. Entity must be versioned in order to verify that. Alternatively we could compare *all* columns but thats going too far imho.
           * 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.
           * Throw PessimisticLockException if the refreshed version is not equal to the previous version which the entity had prior to the pessimistic refresh.

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

          Effects:

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

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

          Effects:

           * 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.
          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:

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

          2. 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.
          3. Add a query hint "lockMode" which takes a Lock constant.
          4. Add a query hint "lockVersion" which takes an integer or timestamp versioning value.
          5. 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)
          6. Change the $em->find() method to the following signature:

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

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

          ----

          h2. Updated API specification

          h3. LockModes

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

          h3. 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.

          h3. API spec

          h4. $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.

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

          Effects:

           * 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.

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

          Effects:

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

          h4. $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.

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

          Effects:

           * 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

          h4. $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)

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

          Effects:

           * Throw PessimisticLockException if entity is not versioned. refresh() with a pessimistic lock is a "lock after read" so there might be stale data. Entity must be versioned in order to verify that. Alternatively we could compare *all* columns but thats going too far imho.
           * 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.
           * Throw PessimisticLockException if the refreshed version is not equal to the previous version which the entity had prior to the pessimistic refresh.

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

          Effects:

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

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

          Effects:

           * 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.
          Roman S. Borschel made changes -
          Description 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:

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

          2. 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.
          3. Add a query hint "lockMode" which takes a Lock constant.
          4. Add a query hint "lockVersion" which takes an integer or timestamp versioning value.
          5. 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)
          6. Change the $em->find() method to the following signature:

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

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

          ----

          h2. Updated API specification

          h3. LockModes

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

          h3. 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.

          h3. API spec

          h4. $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.

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

          Effects:

           * 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.

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

          Effects:

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

          h4. $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.

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

          Effects:

           * 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

          h4. $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)

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

          Effects:

           * Throw PessimisticLockException if entity is not versioned. refresh() with a pessimistic lock is a "lock after read" so there might be stale data. Entity must be versioned in order to verify that. Alternatively we could compare *all* columns but thats going too far imho.
           * 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.
           * Throw PessimisticLockException if the refreshed version is not equal to the previous version which the entity had prior to the pessimistic refresh.

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

          Effects:

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

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

          Effects:

           * 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.
          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:

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

          2. 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.
          3. Add a query hint "lockMode" which takes a Lock constant.
          4. Add a query hint "lockVersion" which takes an integer or timestamp versioning value.
          5. 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)
          6. Change the $em->find() method to the following signature:

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

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

          ----

          h2. Updated API specification

          h3. LockModes

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

          h3. 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.

          h3. API spec

          h4. $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.

          h4. $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.

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

          Effects:

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

          h4. $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.

          h4. $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

          h4. $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)

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

          Effects:

           * Throw TransactionRequiredException if there is no active transaction.
           * Throw PessimisticLockException if entity is not versioned. refresh() with a pessimistic lock is a "lock after read" so there might be stale data. Entity must be versioned in order to verify that. Alternatively we could compare *all* columns but thats going too far imho.
           * 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.
           * Throw PessimisticLockException if the refreshed version is not equal to the previous version which the entity had prior to the pessimistic refresh.

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

          Effects:

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

          h4. $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.
          Roman S. Borschel made changes -
          Assignee Roman S. Borschel [ romanb ] Benjamin Eberlei [ beberlei ]
          Roman S. Borschel made changes -
          Description 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:

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

          2. 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.
          3. Add a query hint "lockMode" which takes a Lock constant.
          4. Add a query hint "lockVersion" which takes an integer or timestamp versioning value.
          5. 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)
          6. Change the $em->find() method to the following signature:

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

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

          ----

          h2. Updated API specification

          h3. LockModes

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

          h3. 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.

          h3. API spec

          h4. $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.

          h4. $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.

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

          Effects:

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

          h4. $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.

          h4. $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

          h4. $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)

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

          Effects:

           * Throw TransactionRequiredException if there is no active transaction.
           * Throw PessimisticLockException if entity is not versioned. refresh() with a pessimistic lock is a "lock after read" so there might be stale data. Entity must be versioned in order to verify that. Alternatively we could compare *all* columns but thats going too far imho.
           * 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.
           * Throw PessimisticLockException if the refreshed version is not equal to the previous version which the entity had prior to the pessimistic refresh.

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

          Effects:

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

          h4. $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.
          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:

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

          2. 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.
          3. Add a query hint "lockMode" which takes a Lock constant.
          4. Add a query hint "lockVersion" which takes an integer or timestamp versioning value.
          5. 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)
          6. Change the $em->find() method to the following signature:

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

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

          ----

          h2. Updated API specification

          h3. LockModes

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

          h3. 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.

          h3. API spec

          h4. $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.

          h4. $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.

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

          Effects:

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

          h4. $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.

          h4. $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

          h4. $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)

          h4. $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.

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

          Effects:

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

          h4. $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.
          Benjamin Eberlei made changes -
          Description 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:

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

          2. 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.
          3. Add a query hint "lockMode" which takes a Lock constant.
          4. Add a query hint "lockVersion" which takes an integer or timestamp versioning value.
          5. 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)
          6. Change the $em->find() method to the following signature:

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

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

          ----

          h2. Updated API specification

          h3. LockModes

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

          h3. 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.

          h3. API spec

          h4. $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.

          h4. $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.

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

          Effects:

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

          h4. $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.

          h4. $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

          h4. $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)

          h4. $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.

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

          Effects:

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

          h4. $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.
          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:

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

          2. 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.
          3. Add a query hint "lockMode" which takes a Lock constant.
          4. Add a query hint "lockVersion" which takes an integer or timestamp versioning value.
          5. 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)
          6. Change the $em->find() method to the following signature:

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

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

          ----

          h2. Updated API specification

          h3. LockModes

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

          h3. 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.

          h3. API spec

          h4. $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.

          h4. $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.

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

          Effects:

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

          h4. $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.

          h4. $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

          h4. $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)

          h4. $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.

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

          Effects:

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

          h4. $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.
          Benjamin Eberlei made changes -
          Attachment ddc178_locking.diff [ 10542 ]
          Roman S. Borschel made changes -
          Fix Version/s 2.0-BETA3 [ 10060 ]
          Fix Version/s 2.0-BETA2 [ 10050 ]
          Benjamin Eberlei made changes -
          Status Open [ 1 ] Resolved [ 5 ]
          Resolution Fixed [ 1 ]
          Benjamin Eberlei made changes -
          Workflow jira [ 10518 ] jira-feedback [ 14187 ]
          Benjamin Eberlei made changes -
          Workflow jira-feedback [ 14187 ] jira-feedback2 [ 16051 ]
          Benjamin Eberlei made changes -
          Workflow jira-feedback2 [ 16051 ] jira-feedback3 [ 18304 ]

            People

            • Assignee:
              Benjamin Eberlei
              Reporter:
              Benjamin Eberlei
            • Votes:
              2 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: