CODEWITHSUNDEEP
javamysqlmultithreadingjdbcauto-increment
Diptendu Dutta
Diptendu Dutta

Reputation: 341

Using the JDBC getGeneratedKeys function in multithreaded environment

I have an web application that uses the AUTO INCREMENT value of one table to insert into other tables. I need to ensure that the value read for the Auto-Increment column is correct in the presence of potential concurrent INSERTs into that table. Since each thread will have its own connection (from the container pool) do I still have to put the code within a transaction?

PreparedStatement ps = null;
ResultSet rs = null;
String sql = "INSERT INTO KYC_RECORD ....";
int autoIncKeyFromApi = -1;

Connection connection = ....

try {     
    connection.setAutoCommit(false);    
    ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);    
    ps.setString( ... );    
    ps.executeUpdate();    
    rs = ps.getGeneratedKeys();    
    if (rs.next()) {
        autoIncKeyFromApi = rs.getInt(1);
    } else {   
        // throw an exception from here
    }    
    connection.commit();
}

Upvotes: 2

Views: 680

Answers (1)

Roman C
Roman C

Reputation: 1

The value of autoincrement of the column is managed on database level. Therefore you can fetch the value to getGeneratedKeys() without worry in multithreaded environment.

The transaction is started as soon as you call the update SQL statement. It happens on database level. It stays open until you commit it manually or if autocommit is enabled.

If you need to get more info about transactions, see Java Tutorial.

Upvotes: 3

Related Questions