CODEWITHSUNDEEP
phpmysqlsymfonydoctrine
gridsane
gridsane

Reputation: 519

Restrict to add one row at time period with concurrency

Lets say we have some table

foo:
    message: STRING
    created: DATETIME

And we want to users may only add 1 row every 10 minutes.

$em = // \Doctrine\ORM\EntityManager
$repo = $em->getRepository('Foo');

$now = new \DateTime;
$tenMinutesAgo = $now->sub(new \DateInterval('PT10M'));

// See if there are any Foos in 10 minutes interval
// [SELECT]
$count = (int) $repo->createQueryBuilder('t')
    ->select('COUNT(t.id)')
    ->where('t.created > :ten_minutes_ago')
    ->setParameter('ten_minutes_ago', $tenMinutesAgo)
    ->getQuery()
    ->getSingleScalarResult();

// sleep(X) to simulate possible downtime

// [IF SELECT]
if (0 === $count) {
    $foo = new Foo;
    $foo->setMessage('bar')
        ->setCreated($now);

    $em->persist($foo);
    // [INSERT]
    $em->flush();

    echo "Added!";
} else {
    echo "Cannot add, wait around 10 min.";
}

Now, 2 users (Alice and Bob) perfoming request at same time:

I think it's pretty common problem. How i can solve it? (with Doctrine, if it possible)

Solution 1. Table locks.

Do LOCK TABLE before all queries, and release it while done.

  1. Actually, the example might be too simplified. ONE user cannot insert faster than 1 row at 10 min. With table locks all users have to wait, while another adds his row?
  2. Too sad, with Doctrine table locks might be very tricky. (DQL generates aliases, and we have to predict it in the native LOCK TABLE query.

Upvotes: 3

Views: 410

Answers (3)

Rick James
Rick James

Reputation: 142316

UPDATE tbl SET last = NOW() WHERE last < NOW() - INTERVAL 10 MINUTE;
$mod_ct = get number of rows modified
if ($mod_ct ...) then "wait..."

The update is atomic. $mod_ct tells you whether it succeeded. All that is left is to take one of two actions. (If you need to do some more SQL in one of the forks, then use InnoDB and use BEGIN...COMMIT.)

Upvotes: 1

Michael Blood
Michael Blood

Reputation: 1267

As long as you already have at least one row in t to start with. The following will work with a single sql statement (a single SQL statement = a single transaction , which should not have the same 'locking' issues you may be working to get around above

insert into t( created)  
 select  now() from foo
where not  exists(select 1 from t where created > now() - interval 10 minute )
limit 1

Upvotes: 0

Andrzej Reduta
Andrzej Reduta

Reputation: 767

1 Start transaction and lock table

2 run insert query

3 check if 10 minutes limitation is passed or not

4 commit or rollback transaction bassing on result from step 3 and unlock table

https://dev.mysql.com/doc/refman/5.0/en/lock-tables.html

Upvotes: 1

Related Questions