Symfony and Doctrine’s version of MySQL’s “SELECT FOR UPDATE” queries

Sometimes, you may find yourself needing to select a value or record from a database and hold that record so you can update it without any other process intervening. There are a lot of instances you may need to “lock” the record to a process to ensure no other process can “claim” it as well.

Perhaps your website has users who can “claim” a limited number of free giveaway items. Without “locking” the record, you can have duplicates “claiming” the giveaway and go over the number of free items you’re giving away. That’s just one practical example, so in MySQL you would use “SELECT FOR UPDATE” instead of just the “SELECT” statement.

If you are using Symfony and Doctrine, here’s the “SELECT FOR UPDATE” version for “DQL” (Doctrine Query Language). You set the “Lock Mode” to PESSIMISTIC_WRITE:

$query = $this->em->createQuery('SELECT …'); 
$em->find($class, $id, LockMode::PESSIMISTIC_WRITE);

About Author:

Senior Cloud Software Engineer and 25+ years experienced video production, video editing and 3D animation services for a variety of global clients including local video production here in Jacksonville, Florida.

Leave a Comment

Your email address will not be published. Required fields are marked *