How to lock a whole table in symfony2 with doctrine2?

Question

I need to lock a whole table (not a single row) with doctrine, I would like to do this without native queries if possible.

The documentation for pessimistic locking only describes how to lock specific entities through these methods:

• EntityManager#find
• EntityManager#lock
• Query#setLockMode

I have a transaction that needs to insert a row whose values depend on the values of the rest of the rows in the table, so I need to prevent two transactions performing at the same time on that table.

I'm using explicit transaction demarcation, which is supposed to work well with locking (according to the documentation above).

NOTE: Optimistic locking is not good enough in this case, I can't afford retrying the transaction. Besides the query is not supposed to be slow so performance isn't an issue.

EDIT: I'll give an example. Imagine you want to hand build an auto_increment, and you have to select max() from the table to get the previous result in order to insert the next one. You have to make sure that no two transactions try to insert the same value in case they select max() at the same time.

I'm looking for a general solution to this problem when auto_increment is not good, for example with strings, or multiple columns, hashes or whatever calculation you have to make on the previous row set.

Locking is a solid solution and, unlike optimistic locking, you don't have to retry on errors.

So, is there any way to use table locking in doctrine?

Answer 1

Following advice so far, I tried this:

$em->getConnection()->exec('LOCK TABLES table_name WRITE;'); //lock for write access

// calculate $new_number...

// persist $new_number on table_name...
$table_name->setCalculatedNumber($new_number);
$em->persist($table_name);
$em->flush();

$em->getConnection()->exec('UNLOCK TABLES;');

I tested it with JMeter, and the locking wasn't working with a heavy load (16 requests/sec). Tracing showed that other instances got the lock before it had been explicitly given up. The problem (as suggested by Jens) was that flush() implicitly begins with a START TRANSACTION, which drops the table lock. Using a native Update fixed the problem for me:

$em->getConnection()->exec('LOCK TABLES table_name WRITE;'); //lock for write access

// calculate $new_number...

// persist $new_number on table_name...
$em->getConnection()->executeUpdate("UPDATE table_name set ...;");    

$em->getConnection()->exec('UNLOCK TABLES;');
$em->refresh($table_name);

The trailing refresh() was needed to make the calculated number available in subsequent query results