手动增量的复合键

Question

如何在多会话/事务环境中安全地将一行插入到包含带有（手动）增量键的主复合键的表中。

如何获取＜code＞column_c＜/code＞的最新增量值，＜code＞LAST_INSERT_ID（）＜/code>不返回所需值。

我已经研究了< code >选择更新...INSERT和< code>INSERT INTO SELECT，但无法决定使用哪一个。

从事务安全（锁）、隔离级别和性能角度来看，实现这一点的最佳方法是什么。

更新-对问题的另一种看法

假设两个事务/会话尝试插入相同的column_a，column_b对（示例 1，1）。我该怎么做;

>

按顺序执行插入查询。第一次插入（事务1）应产生1,1,1的复合密钥，第二次插入（交易2）应产生2,1,2的复合密钥

检索插入column_c值。我可能需要利用变量？

表格定义

CREATE TABLE `table` (
        `column_a` int(11) unsigned NOT NULL,
        `column_b` int(11) unsigned NOT NULL,
        `column_c` int(11) unsigned NOT NULL,
        PRIMARY KEY (column_a, column_b, column_c)
 ) ENGINE=InnoDB;

Exempel data

+----------+----------+----------+
| column_a | column_b | column_c |
+----------+----------+----------+
|        1 |        1 |        1 |
|        1 |        1 |        2 |
|        1 |        1 |        3 |
|        2 |        1 |        1 |
|        2 |        1 |        2 |
|        2 |        1 |        3 |
+----------+----------+----------+

接受插入到select查询

INSERT INTO `table` (`column_a`, `column_b`, `column_c`)
SELECT 2,1, IFNULL(MAX(`column_c`), 0) + 1 FROM `table` 
WHERE `column_a` = 2 and `column_b` = 1;

Answer 1

让我们将包含这3列的表称为ThreeClonTable，以避免因您给出的名称-table而引起混淆。

手动递增的是列column_c。将其拉出来并在另一个表中跟踪用于该列的最后一个值。

解决方案的步骤：

• 创建一个表，存储column_c使用的最后一个值。让我们将此表称为LastUsedIdTable。LastUsedIdTable将仅包含三列：
  • 要手动递增其列的表的名称（示例：ThreeClonTable）；
  • 列本身的名称（示例：column_c）；
  • 该列使用的最后一个值（示例：121）。

  证明解决方案的可行性:

  为了使演示更一般化，假设在< code>ThreeColumnTable中有n个并发的插入请求。

  所有n个请求都必须首先调用存储过程。由于Stored Proc在LastUsedIdTable上使用事务，因此一次只有1个请求将访问ThreeClonTable的行，目前如下所示：

  +-----------------------------------+
  | ThreeColumnTable | column_c | 121 |
  +-----------------------------------+
  

  现在，第一个请求将锁定这一行并获取< code>122作为值，还将表的行中的值更新为122。当n个请求完成时，< code>LastUsedIdTable的< code>ThreeColumnTable行将如下所示:

  +-----------------------------------------+
  | ThreeColumnTable | column_c | (121 + n) |
  +-----------------------------------------+
  

  现在，这些n个请求已经在ThreeColumnTable中执行插入的路上了。但是，由于它们都有自己独特的column_c值，因此无论插入的顺序如何，它们的插入都将没有冲突！您可以先插入值 121 n，最后插入值 122。事实上，您甚至不需要column_a、column_b column_c元组是唯一的，因为column_c将始终是唯一的。

  此解决方案适用于并行请求和串行请求。由于对性能的影响非常非常小（可以忽略不计），这将是您尝试达到的交易安全，隔离级别和性能的最佳点。

  要点： 使用 LastUsedId 将所有此类主键列的最后一个值存储在数据库的所有表中。由于此表包含的行数与所有数据库中要手动递增的键数一样多，因此它仅包含有限且固定的行数。这将是快速且无争用条件的。

Answer 2

BEGIN;
SELECT @c := MAX(c) + 1
    FROM t
    WHERE a = ? AND b = ?
    FOR UPDATE;           -- important
if row found              -- in application code (or Stored Proc)
then
    INSERT INTO t (a,b,c)
        VALUES
        (?, ?, @c);
else
    INSERT INTO t (a,b,c)
        VALUES
        (?, ?, 1);
COMMIT;

希望< code>FOR UPDATE会停止，直到它可以获得锁和所需的< code>c值。那么接下来的交易应该会很顺利。

我不认为transaction_isolation的设置很重要，但这值得研究。

Answer 3

您可以使用存储过程来实现此目的：

我从来没有遇到过这样的问题，如果我遇到过，我会这样做：

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_insert_when_duplicate`(val1 int, val2 int, val3 int)
BEGIN

     -- catch duplicate insert error
     DECLARE EXIT HANDLER FOR 1062
     BEGIN
        -- we could recursively try to insert the same val1 and val2 but increasing val3 by 1
        call sp_insert_when_duplicate(val1,val2,val3+1);
     END;

     -- by default mysql recursive limit is 0, you could set as 10 or 100 as per your wish
    SET max_sp_recursion_depth=10;

     -- [Trying] to insert the values, if no duplicate this should continue and end the script.. if duplicate, above handler should catch and try to insert again with 1+ value for val3
    INSERT INTO `table` (`column_a`, `column_b`, `column_c`) values (val1,val2,val3);


END

用法如下：

call sp_insert_when_duplicate(1,1,1);
call sp_insert_when_duplicate(1,1,1);
call sp_insert_when_duplicate(1,1,1);
call sp_insert_when_duplicate(2,1,1);
call sp_insert_when_duplicate(2,1,1);
call sp_insert_when_duplicate(2,2,1);
select * from `table`;

结果：

+----------+----------+----------+
| column_a | column_b | column_c |
+----------+----------+----------+
|        1 |        1 |        1 |
|        1 |        1 |        2 |
|        1 |        1 |        3 |
|        2 |        1 |        1 |
|        2 |        1 |        2 |
|        2 |        2 |        1 |
+----------+----------+----------+

同样适用于交易：

start transaction;
call sp_insert_when_duplicate(1,1,1);
call sp_insert_when_duplicate(1,1,1);
call sp_insert_when_duplicate(1,1,1);
call sp_insert_when_duplicate(2,1,1);
call sp_insert_when_duplicate(2,1,1);
call sp_insert_when_duplicate(2,2,1);
commit;

select * from `table`;


+----------+----------+----------+
| column_a | column_b | column_c |
+----------+----------+----------+
|        1 |        1 |        1 |
|        1 |        1 |        2 |
|        1 |        1 |        3 |
|        2 |        1 |        1 |
|        2 |        1 |        2 |
|        2 |        2 |        1 |
+----------+----------+----------+

然而，我还没有尝试过并行事务！