mysql DUPLICATE KEY UPDATE 問題

DUPLICATE KEY UPDATE batch執行時出死結錯誤

 

背景知識

一、 mysql  insert 與 duplicate key：

典型的插入語句：

多條：INSERT INTO tablename (columnA, columnB, columnC)

VALUES ('a', 1, 2), ('b', 7, 5)

單條：INSERT INTO tablename SET columnA='a', columnB=1, columnC=2

複製：INSERT [options1] [INTO] tablename [ (columnlist) ]
SELECT …

若表已設定主鍵如columnA，重複的插入無效

ERROR 1062 (23000): Duplicate entry 'value' for key 'PRIMARY'

如果資料庫中已有某條資料，以下的兩條語句可等同：

INSERT INTO tablename (id, data) VALUES (1, 10)ON DUPLICATE KEY UPDATE data=data+10;

UPDATE tablename SET data=data+10 WHERE id=1;

duplicate key語句一般應用在 格式化多條更新語句：

INSERT INTO tablename (id, data) VALUES (1, 10), (2, 15)
ON DUPLICATE KEY UPDATE data=data+VALUE(data)

 

二、innodb表提高插入效率

查詢表使用的引擎：  show create table tablename;

innodb 的儲存引擎提供行級鎖，支援共用鎖定和獨佔鎖定兩種鎖定模式，以及四種不同的隔離等級。

對於Innodb 類型的表，我們有以下幾種方式可以提高匯入的效率：
a. 因為Innodb 類型的表是按照主鍵的順序儲存的，所以將匯入的資料按照主鍵的順
序排列，可以有效提高匯入資料的效率。如果Innodb 表沒有主鍵，那麼系統會預設
建立一個內部列作為主鍵，所以如果可以給表建立一個主鍵，將可以利用這個優勢提高
匯入資料的效率。
b. 在匯入資料前執行SET UNIQUE_CHECKS=0，關閉唯一性校正，在匯入結束後執行SET
UNIQUE_CHECKS=1，恢複唯一性校正，可以提高匯入的效率。
c. 如果應用使用自動認可的方式，建議在匯入前執行SET AUTOCOMMIT=0，關閉自動
提交，匯入結束後再執行SET AUTOCOMMIT=1，開啟自動認可，也可以提高匯入的效率。

如果如果你同時從同一客戶插入很多行，使用多個值表的INSERT 語句。這比使用分開INSERT 語句快(在一些情況中幾倍)。

你從不同客戶插入很多行，能通過使用INSERT DELAYED 語句得到更高的速度。Delayed 的含義是讓insert 語句馬上執行，其實資料都被放在記憶體的隊列中，並沒有真正寫入磁碟；這比每條語句分別插入要快的多；LOW_PRIORITY 剛好相反，在所有其他使用者對錶的讀寫完後才進行插入；

 

在項目中遇到的問題時，使用了這種insert處理，但是分庫分表，資料表類型為innodb，  tablename各不相同，duplicate key只是用於合并update和insert語句。

 "java.sql.BatchUpdateException: Deadlock found when trying to get lock; try restarting transaction" 錯誤狀況為 第一個插入即出錯，或一個batch中重複一個key，插入多個值。 貌似mysql有這個bug（http://bugs.mysql.com/bug.php?id=52020）

使用threadlocal去擷取操作資料庫的對象，static對象，擷取pool的串連並執行批處理方法

 

PRIMARY KEY

UNIQUE KEY 的區別