這里寫目錄標題

• 背景介紹
• 實現方式
• • 先判斷再操作
  • insert on duplicate
• 擴展
• • 返回值
  • 分庫分表返回主鍵
  • 執行步驟
  • death lock

背景介紹

有一批消息隊列任務要執行，需要記錄任務相關的數據，包括執行總數、成功數、失敗數。這里采用mysql統計表的方式記錄，每一批任務生成一條統計數據，但由于消息隊列是無序的，所以生成統計記錄的時候無法知道哪一個請求是第一次，也就不知道是 insert 還是 update。

簡單列一下統計表字段

CREATE TABLE `t_statistics` (`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵',`task_id` bigint(20) unsigned NOT NULL COMMENT '任務ID',`total_count` bigint(20) unsigned DEFAULT '0' COMMENT '總數',`success_count` bigint(20) unsigned DEFAULT '0' COMMENT '成功數',`fail_count` bigint(20) unsigned DEFAULT '0' COMMENT '失敗數',PRIMARY KEY (`id`),UNIQUE KEY `uk_task_id` (`task_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='任務統計表'

注意：

• primary key: id 自增主鍵
• unique key: task_id 任務ID

實現方式

先判斷再操作

既然不知道哪次請求是第一次，那就先判斷任務記錄是否存在，不存在則insert，存在則update

if ( (select * from t_statistics where task_id = %d) != null ) {update t_statistics set total_count = total_count + 1, ... ; } else {insert into t_statistics values(xxx...); }

存在的問題：
1.每一次處理都需有兩次數據庫操作，暫用數據庫連接且增加了RT
2.操作非原子性，會有并發問題

• 兩次不同任務的請求發現DB里沒有數據，決定都執行insert操作，第一個請求成功insert，第二個請求發送唯一鍵沖突將拋出 duplicate entry 異常
• 兩次不同任務的請求發現DB里有數據，決定都執行update操作，假設這時候DB里的total_count=1，第一次請求 +1后 total_count=2，第二次請求 +1 后 total_count=2，按理說數據庫最后 total_count應該為3，但是由于數據發生了臟寫最后為2

insert on duplicate

這種場景下可以使用 insert on duplicate 命令，簡單的說下該命令的原理：執行 insert 命令時發生主鍵沖突或者唯一鍵沖突，就執行后面的 update 命令，如果沒發生沖突就執行 insert 命令。

insert into t_statistics values(xxx...) on duplicate key update total_count = total_count + 1, ... ;

可以看到命令更為簡潔，操作也保持了原子性不會發生并發問題。

擴展

返回值

使用 insertOnDuplicate 的返回值是什么？

<insert id="insertOnDuplicate" parameterType="xxx.StatisticsDO"> insert into t_statistics values(xxx...) on duplicate key update total_count = total_count + 1, ... ; </insert>

• insert -> 1
• update -> 2
• nothing -> 0

分庫分表返回主鍵

嘗試過多種方法后發現不能返回歷史數據的主鍵，所以可以通過返回值來判斷是否是insert還是update，再判斷怎么獲取主鍵，偽代碼如下：

return pk; } else {return selectId(xxx); }

執行步驟

1.嘗試把新行插入到表中
2.當因為對于主鍵或唯一關鍵字出現重復關鍵字錯誤而造成插入失敗時，則對現有的行加上S（共享）鎖，然后返回該行數據給server層
3.server在內存對該行執行update操作
4.對該行記錄加上X（排他）鎖
5.將update后的結果寫入該行

death lock

總結

以上是生活随笔為你收集整理的insert on duplicate key update命令的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。