使用多執行緒時重複鍵違反唯一約束

我正在使用 ExecutorService，固定執行緒池為 50，固定數據庫連接池為 50，使用 HikariCP。每個工作執行緒處理一個數據包（“報告”），檢查它是否有效（其中每個報告必須有唯一的 unit_id、時間、緯度和經度），從連接池中獲取一個 db 連接，然後將報告插入報告表。唯一性約束是用 postgresql 創建的，被稱為“reports_uniqueness_index”。當我的音量很大時，我會收到大量以下錯誤：

org.postgresql.util.PSQLException: ERROR: duplicate key value 
 violates unique constraint "reports_uniqueness_index"

這就是我認為的問題所在。在插入數據庫之前，我執行檢查以確定表中是否已經存在具有相同unit_id、時間、緯度和經度的報表。如果不是，那麼報告是有效的，我執行插入。但是，我認為因為我正在使用並發性，所以我有 50 個執行緒同時檢查報告是否有效，並且由於尚未插入它們，每個執行緒都認為它有一個有效的報告，並且何時將它們插入同一時刻，也就是 postgresql 引發錯誤的時候。

我想要一個不會產生任何並發延遲的解決方案。我一直在嘗試避免使用同步語句或可重入鎖，因為數據庫插入需要盡快發生。這是這裡的插入：

private boolean save(){
       Connection conn=null;
       Statement stmt=null;
       int status=0;
       DbConnectionPool dbPool = DbConnectionPool.getInstance();
       String sql = = "INSERT INTO reports"
       sql += " (unit_id, time, time_secs, latitude, longitude, speed, created_at)";
       sql += " values (...)";
       try {
           conn = dbPool.getConnection();
           stmt = conn.createStatement();
           status = stmt.executeUpdate(sql);
       } catch (SQLException e) {
           return false;
       } finally {
           try {
               if (stmt != null)  
               {  
                   stmt.close();
               }  

               if (conn != null)  
               {  
                   conn.close();  
               }  
           } catch(SQLException e){}
       }

       if(status > 0){
           return true;            
       }

       return false;
}

我想到的一種解決方案是將 Class 對象本身用作鎖定對象：

synchronized(Report.class) {
   status = stmt.executeUpdate(sql);
}

但這會延遲其他執行緒的插入。有更好的解決方案嗎？

通過在您的表上創建一個唯一約束，您是在告訴數據庫“每次我嘗試在此表中插入一行時，請檢查是否存在與此列組合相同的現有行。哦，請確保您這樣做它以原子方式進行，因此如果其他人試圖與我同時插入一個，我們中只有一個人會成功”。

這樣做之後，在大多數情況下，在您的應用程序中複製相同的邏輯就沒有什麼意義了。它只會使過程效率降低。您最好接受@horse 的建議並抓住異常。

不過，有幾件事需要注意。一個是如果你在一個事務中這樣做，那麼事務將在錯誤時自動回滾。如果您在此之前在交易中做了任何其他事情，它將會被撤消。為避免這種情況，您需要在插入之前設置一個SAVEPOINT，然後根據需要釋放或回滾保存點。

另一個問題是 postgresql 日誌仍將包含這些錯誤，即使它們被 Java 程式碼擷取並忽略。用無意義的噪音淹沒你的日誌有助於隱藏可能潛伏在那裡的真正問題，所以這不是一件好事。

避免這兩個問題的更好的解決方案可能是創建一個儲存過程，該過程插入記錄，處理髮生的任何異常，並向您的應用程序返回有關記錄是否已儲存的指示。

例如，如果您有一個如下所示的表：

CREATE TABLE test(a INTEGER, b TEXT, UNIQUE(A,B));

然後你可以有這樣的功能：

CREATE FUNCTION test_insert(pa INTEGER, pb TEXT) RETURNS INTEGER AS $$
BEGIN
   INSERT INTO test(a,b) VALUES (pa, pb);
   RETURN 1;
EXCEPTION
   WHEN unique_violation THEN
      RETURN -1;
END;
$$ LANGUAGE plpgsql;

你可以像這樣使用它：

testdb=> SELECT test_insert(1,'foo');
test_insert
-------------
          1
(1 row)

testdb=> SELECT test_insert(1,'foo');
test_insert
-------------
         -1
(1 row)

如果嘗試插入違反約束的記錄，則不會引發或記錄異常，並且如果在事務內執行，則不會影響事務。該函式返回一個值，您可以使用該值來查看記錄是否已插入。這是一個非常基本的例子，但應該說明這一點。

引用自：https://dba.stackexchange.com/questions/72501