Restrict to add one row at time period with concurrency

Question

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:

Alice: Perfoming [SELECT] ... [IF SELECT] ok, count = 0
Bob: Perfoming [SELECT] ... [IF SELECT] ok, count = 0
Alice: [INSERT] ...
Bob: [INSERT] ...

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.

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?
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.

score 1 · Accepted Answer · answered Apr 10 '14 at 10:03

1

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

answered Apr 10 '14 at 10:03

Andrzej Reduta

767
1
7
15

Thanks for answer. Why i need transaction? Can I do just: LOCK TABLE table WRITE; SELECT .. INSERT .. UNLOCK TABLES: – gridsane Apr 10 '14 at 10:56

score 1 · Answer 2 · answered Apr 19 '14 at 05:10

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.)

score 0 · Answer 3 · answered Apr 18 '14 at 02:59

0

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

answered Apr 18 '14 at 02:59

Michael Blood

1,257
10
11

Thanks for the answer! Like I mentioned in the question I looked for a Doctrine-related answer. Can you provide an example for your answer using Doctrine? – gridsane Apr 18 '14 at 07:12
@gridsane unfortunately I do not know or use Doctrine. But a little research on how to execute a raw SQL statement using Doctrine brought me to this article - Good luck http://stackoverflow.com/questions/3325012/execute-raw-sql-using-doctrine-2 – Michael Blood Apr 20 '14 at 17:31

Restrict to add one row at time period with concurrency

3 Answers3