mysqldoctrinelockingdbal

DBAL Connection query() function and SELECT FOR UPDATE, LOCK


I'm trying to do a 'SELECT FOR UPDATE' query in mysql using the DBAL connection query function.

I'm in a symfony project and I made the following test :

I have this two functions in my controller :

/**
 * @Route("test", name="test")
 */
public function testAction()
{
    $em = $this->getDoctrine()->getManager();
    $em->getConnection()->beginTransaction();
    try {
        $sql = "SELECT *
            FROM CommandeDel
            WHERE IDCommande = 2 FOR UPDATE";
        $m = $em->getConnection()->query($sql)->fetch();
        sleep(20);
        $em->getConnection()->commit();
    }
    catch (\Exception $e) {
        $em->getConnection()->rollback();
        throw $e;
    }

    return new Response(json_encode($m));
}

/**
 * @Route("test2", name="test2")
 */
public function test2Action()
{
    $em = $this->getDoctrine()->getManager();
    $em->getConnection()->beginTransaction();
        $sql = "SELECT *
            FROM CommandeDel
            WHERE IDCommande = 2";
        $m = $em->getConnection()->query($sql)->fetch();
        $em->getConnection()->commit();
    }
    catch (\Exception $e) {
        $em->getConnection()->rollback();
        throw $e;
    }

    return new Response(json_encode($m));
}

I'm calling the first url (test) in my browser and immediatly the second one (test2) in an other tab.

My problem is that the second one return immediatly the answer without waiting the 20 second delay...

Do someone see where I'm wrong ?

Thanks.


Solution

  • I found my mistake, the SELECT FOR UPDATE query only lock the row for others SELECT FOR UPDATE queries and not for normal SELECT. I miss understood the basic definition. To make the test works the second Action should look like this :

    /**
     * @Route("test2", name="test2")
     */
    public function test2Action()
    {
        $em = $this->getDoctrine()->getManager();
        $em->getConnection()->beginTransaction();
        try {
            $sql = "SELECT *
                FROM CommandeDel
                WHERE IDCommande = 2 FOR UPDATE";
            $m = $em->getConnection()->query($sql)->fetch();
            $em->getConnection()->commit();
        }
        catch (\Exception $e) {
            $em->getConnection()->rollback();
            throw $e;
        }
    
        return new Response(json_encode($m));
    }