Laravel 4具有更新和选择的独家交易 [英] Laravel 4 exclusive transaction with update and select

问题描述

我需要从设置为免费的SQLite数据库中选择一行，然后将该行标记为已使用.至关重要的是，选择和更新是原子操作，并且不存在两个用户免费获得同一行的风险.

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.

不幸的是，在调用DB::transaction时，Laravel似乎没有提供事务类型的选择，如果我将其作为DB::statement运行，则不会获得返回的值.

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.

此外，由于SQLite不支持变量，因此我无法在选择和更新之间存储主键，所以我不确定是否有可能完全在SQLite数据库上运行它.

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.

是否可以使用Laravel和SQLite?

Is it possible to do using Laravel and SQLite?

如果没有，那么我将需要在与SQLite数据库相关的MySQL表中存储下一个空闲ID，并在该行上使用锁定.在MySQL中逐行锁定是否存在主要语法差异? (我不能无意间锁定整个表)

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)

我正在寻找与以下类似的语法:(当然SQLite不支持变量)

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!?!":

SQLite数据库包含需要灵活列数的数据.这些数据将在一周的几天内使用几个月，然后可以将其存档.这些迷你数据库"需要很多.这些数据无法有效地存储在MySQL中(我要替换的先前的系统对此进行了尝试，并且变得非常慢).每个SQLite数据库的并发性都将很低，但是至关重要的是绝对不能让两个用户获得同一行-在旧系统中已经发生过两次.

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.

推荐答案

我会忘记尝试锁定表和/或在事务中进行操作，对此有更好的模式:

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

• 生成唯一令牌
• 将该令牌分配给下一个可用记录
• 读取获得令牌的那一行，并对其进行处理

那样，您根本不需要锁定和事务(因为数据库只会将您的令牌分配给一个未使用的记录).

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

伪代码:

$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);

这篇关于Laravel 4具有更新和选择的独家交易的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！