CODEWITHSUNDEEP
mysqlsqlsqlitetransactionslaravel
William Lawn Stewart
William Lawn Stewart

Reputation: 1205

Laravel 4 exclusive transaction with update and select

I need to select a row from a SQLite database that is set as free, and then mark the row as taken. It is crucial that the select and the update are an atomic operation and that there is no risk that two users get the same row returned as free.

Unfortunately, Laravel doesn't appear to offer a choice of transaction types when calling DB::transaction, and if I run it as a DB::statement then I'm not going to get the returned values.

Also, since SQLite doesn't support variables I can't store the primary key between the select and the update, so I'm not sure if its possible to run this off the SQLite database at all.

Is it possible to do using Laravel and SQLite?

If not then I will need to store the next free ID in the MySQL table that relates to the SQLite databases, and use locking on that row instead. Is there any major syntax difference to lock by row in MySQL? (I can't afford to accidentally lock the entire table)

I'm looking at syntax similar to this: (except of course SQLite doesn't support variables)

BEGIN EXCLUSIVE TRANSACTION
  SET @free = SELECT InternalID FROM main WHERE Called = 0 LIMIT 1;
  UPDATE main SET Called = 1 WHERE InternalID = @free;
  SELECT * FROM main LEFT JOIN lead ON main.InternalID = lead.InternalID WHERE main.InternalID = @free;
END TRANSACTION

Because I'm sure someone is going to ask "Why are you using two database systems!?!":

The SQLite dbs contain data that requires a flexible number of columns. This data will be used a few days a week for several months, and then can be archived. There needs to be rather a lot of these "mini-databases". This data can't be efficiently stored in MySQL (the previous system I'm replacing tried this, and it gets extremely slow). The concurrency on each SQLite db will be quite low, but it is vital that there is absolutely no chance of two users getting the same row - It has happened in the old system a couple of times.

Upvotes: 1

Views: 1674

Answers (1)

Alexandre Danault
Alexandre Danault

Reputation: 8682

I'd forget trying to lock the table and/or do it in a transaction, there a better patterns for this:

  • Generate a unique token
  • Assign that token to the next available record
  • Read which row got the token, and process it

That way you need no locking and transactions at all (because the DB will assign your token ONLY to ONE unused record).

Pseudo-code:

$token = time(); //Only you have very low concurrency. Otherwise use something more unique, like a GUID or an identity value from a tokens table.

EXEC SQL: "UPDATE mytable SET token = $token WHERE token IS NULL LIMIT 1"

EXEC SQL: "SELECT id FROM mytable WHERE token = $token"

process($id);

Upvotes: 2

Related Questions