I have a document management system in symfony2. Once user establishes my document entity it will be assigned a serial number.
This is how I find the serial to assign 1) Check in DB for highest serial in use 2) Assign it and flush entity.
I use this serial as an identifier for multiple versions of this entity so it is not unique to the DB.
How can I guarantee that the number assigned is truly unique? I would lock tables in flat php but not sure how to or if it's best practice in symfony2.
/**
* Establishes a analysi entity.
*
*/
public function establishAction(Request $request, Analysis $analysi)
{
...
$analysi->setSerial( $this->getNewSerial() );//set a new serial number
//TODO: How to confirm this is really not in use since there is no transaction locking going on here?
$em->flush($analysi);
...
}
private function getNewSerial()
{
$em = $this->getDoctrine()->getManager();
//get highest serial nb from established analysises
$results = $em->createQuery("SELECT MAX(a.serial) FROM HazardlogBundle:Analysis a WHERE a.currentVersion = true")->getResult();
$temp = $results[0];
$max_serial = $temp[1];
$new_serial = $max_serial + 1;
return $new_serial;
}
---------------- UPDATE
I must obviously clearify: I can have...
entity A with serial 123 and version 1
entity B with serial 123 and version 2
entity C with serial 124 and version 1
entity D with serial 125 and version 1
What I am afraid of is that users simultaneously creates entity C and D, and so my controller could fetch the currently highest serial number in use for creating entity C, and before entity C is flushed to DB, the thread serving the user creating entity D will read highest number 123 out of the DB before entity C with serial 124 is written to DB. Thus I will end up with Entity C & D both having serial 124.
Perhaps I could define a unique index consisting of serial and version number together to sidestep this issue?