JdbcSQLIntegrityConstraintViolationException: Unique index or primary key violation #3950
Description
Referring to issue #3944, I encountered the same problem in my business. In the embedded mode(server mode is OK), multiple thread insertions can corrupt the database file, but my business code is relatively complex, so I rewrote the code to reproduce the issue. However, I was unable to reproduce the file corruption issue, but I did encounter a duplicate primary key issue. The code is as follows:
environment：
- jdk21
- h2 2.2.224
import org.h2.jdbcx.JdbcConnectionPool;
import java.sql.Connection;
import java.sql.SQLException;
public class Tester {
private static final String DATABASE_URL = "jdbc:h2:file:./data/test1;AUTO_SERVER=TRUE;MODE=MYSQL";
private static JdbcConnectionPool pool;
private static final String SQL_CREATE_TABLE = "CREATE TABLE IF NOT EXISTS MY_TABLE (" +
" id int not null auto_increment primary key," +
" thread varchar(20)" +
")";
public static void main(String[] args) throws SQLException {
new Tester().run();
}
public void run() throws SQLException {
pool = createConnectionPool(50);
var stmt = pool.getConnection().prepareStatement(SQL_CREATE_TABLE);
stmt.execute();
stmt.close();
for(var i = 0; i < 50; i++) {
var conn = pool.getConnection();
new Thread(() -> {
insertRow(conn);
}).start();
}
}
private void insertRow(Connection conn) {
var thread = Thread.currentThread().getName();
try {
while (true) {
var sql = "insert into MY_TABLE (thread) values (?)";
var stmt = conn.prepareStatement(sql);
stmt.setNString(1, Thread.currentThread().getName());
stmt.execute();
stmt.close();
}
} catch (Exception e) {
e.printStackTrace();
System.exit(999);
}
}
private JdbcConnectionPool createConnectionPool(int maxConnections) {
JdbcConnectionPool connectionPool = JdbcConnectionPool.create(DATABASE_URL, null, null);
connectionPool.setMaxConnections(maxConnections);
return connectionPool;
}
}
stacktrace:
org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: Unique index or primary key violation: "PRIMARY KEY ON PUBLIC.MY_TABLE(ID) ( /* key:10806 */ 10806, 'Thread-14')"; SQL statement:
insert into MY_TABLE (thread) values (?) [23505-224]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:520)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:489)
at org.h2.message.DbException.get(DbException.java:223)
at org.h2.message.DbException.get(DbException.java:199)
at org.h2.mvstore.db.MVPrimaryIndex.add(MVPrimaryIndex.java:120)
at org.h2.mvstore.db.MVTable.addRow(MVTable.java:519)
at org.h2.command.dml.Insert.insertRows(Insert.java:174)
at org.h2.command.dml.Insert.update(Insert.java:135)
at org.h2.command.dml.DataChangeStatement.update(DataChangeStatement.java:74)
at org.h2.command.CommandContainer.update(CommandContainer.java:169)
at org.h2.command.Command.executeUpdate(Command.java:256)
at org.h2.jdbc.JdbcPreparedStatement.execute(JdbcPreparedStatement.java:265)