php—基于多个字段创建序列的正确方法

qeeaahzv  于 2021-06-21  发布在  Mysql
关注(0)|答案(1)|浏览(317)

我使用laravel和迁移来构建我的整个数据库结构。
问题描述
在schema中,我有一个 pack table,那是属于 user 以及 group 并且需要为这些表的每个不同组合保留一种唯一的“索引”。
它的意思是:一个序列号,根据不同的 user_id 以及 group_id . 例如:

| id | user_id | group_id | sequence |
| 1  | 1       | 1        | 1        |
| 2  | 1       | 2        | 1        |
| 3  | 1       | 3        | 1        |
| 4  | 1       | 1        | 2        |
| 5  | 1       | 2        | 2        |
| 6  | 1       | 3        | 2        |
| 7  | 2       | 1        | 1        |
| 8  | 2       | 2        | 1        |
| 9  | 2       | 3        | 1        |

这将用于引用视图层上的包:
用户1,这是第一组的第一组。
用户1,这是第1组的第2组。
用户1,这是第2组的第1组。
我设计了我的迁移(在 up )比如:

Schema::create('pack', function (Blueprint $table) {
    $table->increments('id');
    $table->integer('user_id')->unsigned();
    $table->foreign('user_id')->references('id')->on('user');
    $table->integer('group_id')->unsigned();
    $table->foreign('group_id')->references('id')->on('group');
    $table->integer('sequence')->unsigned();
});

并使用此业务逻辑来填充 $pack->sequence 模型层上的字段。
问题1:理论上,这应该被认为是在所描述的场景中使用的最佳策略?
问题2:有一些模式/方法可以用来填充 sequence 数据库层上的字段?

p4rjhz4m

p4rjhz4m1#

似乎您已经有了一个自动递增列 id . mysql不支持每个表有多个自动增量列。
通常,在允许对表进行并发插入时,无法获得所描述的行为。原因是您必须读取某个用户/组对的最大序列值,然后在插入新行时插入下一个值。
但这会创建一个竞争条件,因为其他一些并发会话可能也在做同样的事情,它会偷偷进入并在会话的读取和插入步骤之间插入一行,其中包含下一个序列值。
解决方案是使用锁来防止同时插入相同的用户id和组id。innodb将使用间隙锁来帮助实现这一点。
例子:
打开两个mysql客户端。在第一个会话中,请尝试以下操作:

mysql> begin;
mysql> select max(sequence) from pack where user_id=1 and group_id=1 for update;
+---------------+
| max(sequence) |
+---------------+
|             2 |
+---------------+

这个 FOR UPDATE 锁定所检查的行,并锁定“间隙”,即插入具有相同用户标识和组标识的其他行的位置。
为了证明这一点,请在第二节课中尝试:

mysql> begin;
mysql> insert into pack set user_id=1, group_id=1, sequence=3;

它挂着。它无法执行插入,因为这与第一个会话仍然保持的间隙锁冲突。竞赛条件已被避免。
现在在第一节课,完成这项工作。

mysql> insert into pack set user_id=1, group_id=1, sequence=3;
mysql> commit;

注意,在提交之后,会话1的锁立即被释放。第二个会话解析其阻止的插入,但它正确地得到一个错误:

ERROR 1062 (23000): Duplicate entry '1-1-3' for key 'user_id'

当然,会话2应该做同样的选择…进行更新。在解决锁冲突之前,它也会被阻止。一旦解析,它将返回正确的新max sequence值。
当且仅当您有合适的索引时,锁才是每个用户\ id/组\ id组合。我用过:

ALTER TABLE pack ADD UNIQUE KEY (user_id, group_id, sequence);

一旦您有了这个键,select…for update就能够在锁定行时特定于正确的行集。
这意味着,即使user\u id=1,group\u id=1被锁定,您仍然可以为user\u id或group\u id的任何其他值插入一个新条目。它们锁定索引的不同部分,因此没有冲突。
我鼓励你自己做一些实验来证明你了解它的工作原理。您不需要编写任何php代码就可以做到这一点。我刚刚打开了两个终端窗口,运行了mysql命令行客户机,并开始在 mysql> 提示。你也可以!

相关问题