symfony2 how to guarante serial number not in use

Question

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?

Answer 1

I found a way to create a unique index by combining two columns like this:

/**
 * Analysis
 *
 * @ORM\Table(name="analysis",uniqueConstraints={@ORM\UniqueConstraint(name="unique_version_serial", columns={"Version", "serial"})})
 * @ORM\Entity(repositoryClass="HazardlogBundle\Repository\AnalysisRepository")
 */
class Analysis

...