如何在代码点火器模型中跨多个函数实现数据库锁定？

I'm creating a system that involves the reservation of tickets by many users within a short period of time with only a certain number of reservations possible in total. Say 600 tickets available, potentially all being reserved in 3 hour period or less.

我正在创建一个系统,该系统涉及许多用户在短时间内预订门票,总共只有一定数量的预订。说600张门票,可能全部在3小时或更短时间内预订。

Ideally I want to ensure the limit of reservations is not reached so before creating a reservation I am checking whether it's possible to make the reservation against the number of tickets available. Crucially I need to make sure no updates take places between that check and assigning the tickets to a user, to be sure the ticket limit won't be exceeded.

理想情况下,我希望确保未达到预订限制,因此在创建预订之前,我正在检查是否可以根据可用的门票数进行预订。至关重要的是,我需要确保在检查和为用户分配票证之间不进行更新,以确保不会超过票证限制。

I'm trying to use mysql table write locks to achieve this however am running into problems implementing this within the codeigniter framework. Within the model handling this I've created several functions, one for creating the reservation and others for counting numbers of different types of tickets. The problem is that they don't seem to be sharing the same database sessions as they ticket counting functions are locking up.

我正在尝试使用mysql表写锁来实现这一点,但是我遇到了在codeigniter框架中实现这个问题的问题。在处理这个模型的过程中,我创建了几个函数,一个用于创建预留,另一个用于计算不同类型票证的数量。问题是它们似乎没有共享相同的数据库会话,因为它们的票据计数功能正在锁定。

The order of execution is

执行顺序是

run $this->model_name->create_reservation in controller

在控制器中运行$ this-> model_name-> create_reservation

run lock query in model_name->create_reservation

在model_name-> create_reservation中运行锁定查询

call counting method in model_name->create_reservation

model_name-> create_reservation中的调用计数方法

counting function (which is a method in the model_name class) locks up, presumably because using different database session?

计数函数(这是model_name类中的方法)锁定,大概是因为使用不同的数据库会话?

The database library is loaded in the model __construct method with $this->load->database();

使用$ this-> load-> database();在模型__construct方法中加载数据库库。

Any ideas?

1 个解决方案

#1

In mysql, you run these commands on your DB handle before running your queries the tables will auto lock :

在mysql中,在运行查询之前,在数据库句柄上运行这些命令,表将自动锁定:

begin work;

You then run your queries or have code igniter run your various selects and updates using that db handle.

然后运行查询或让代码点火器使用该数据库句柄运行各种选择和更新。

Then you either

然后你要么

commit;

rollback;

Any rows you select from will be locked and can't be read by other processes. If you specifically want the rows to still be readable, you can do:

您选择的任何行都将被锁定,其他进程无法读取。如果您特别希望行仍然可读,您可以执行以下操作:

Select ... IN SHARE MODE

From Mysql docs:

来自Mysql文档:

http://dev.mysql.com/doc/refman/5.5/en/select.html

If you use FOR UPDATE with a storage engine that uses page or row locks, rows examined by the query are write-locked until the end of the current transaction. Using LOCK IN SHARE MODE sets a shared lock that permits other transactions to read the examined rows but not to update or delete them. See Section 13.3.9.3, “SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARE MODE Locking Reads”.

如果对使用页锁或行锁的存储引擎使用FOR UPDATE,则查询检查的行将被写入锁定,直到当前事务结束。使用LOCK IN SHARE MODE设置共享锁,允许其他事务读取已检查的行,但不允许更新或删除它们。请参见第13.3.9.3节“SELECT ... FOR UPDATE和SELECT ... LOCK IN SHARE MODE Locking Reads”。

Another person said this in comments already, but from the CI docs:

另一个人已在评论中说过,但是来自CI文档:

$this->db->trans_start();
$this->db->query('AN SQL QUERY...');
$this->db->query('ANOTHER QUERY...');
$this->db->query('AND YET ANOTHER QUERY...');
$this->db->trans_complete();

trans_start and trans_complete will run those queries for you on your handle...

trans_start和trans_complete将在您的句柄上为您运行这些查询...

there is probably a trans_rollback too...

也许还有一个trans_rollback ......

#1