How to lock a row in a database table?
1 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   Uday_Kishore
Posted On:   Thursday, November 6, 2003 02:05 AM

I want to access a particular row in a database table for the purposing of retrieving a number stored there and then updating it with an incremented value. This sequence of operations can happen from multiple threads within a process and also across processes simultaneously. My code for this logic looks like this: long l_temp = -1; boolean bSuccess = false; Connection con = null; PreparedStatement selectStmt = null; ResultSet rs = null; con = dataSource.getConnection(); // Set the transaction on this connection to TRANSACTION_SERIALIZABLE con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);    More>>

I want to access a particular row in a database table for the purposing of retrieving a number stored there and then updating it with an incremented value.


This sequence of operations can happen from multiple threads within a process and also across processes simultaneously.


My code for this logic looks like this:



			
long l_temp = -1;
boolean bSuccess = false;
Connection con = null;
PreparedStatement selectStmt = null;
ResultSet rs = null;


con = dataSource.getConnection();
// Set the transaction on this connection to TRANSACTION_SERIALIZABLE
con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
selectStmt = con.prepareStatement(
"SELECT * FROM MY_TABLE WHERE IDTYPE=?",
ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
while (!bSuccess) {
con.setAutoCommit(false);
selectStmt.setString(1, "abcd");
rs = selectStmt.executeQuery();
try {
if (rs.next()) {
l_temp = rs.getLong("ID");
l_temp++;
rs.updateLong("ID", l_temp);
rs.updateRow();
} else {
// No row existed so far for ID Type = "abcd" in the database.
// So starting with the value as 0.
l_temp = 0;
rs.moveToInsertRow();
rs.updateString("IDType", "abcd");
rs.updateLong("ID", l_temp);
rs.insertRow();
}
bSuccess = true;
} catch(SQLException e) {
e.printStackTrace();
} finally {
if (rs != null) rs.close();
rs = null;
con.commit();
con.setAutoCommit(true);
if (!bSuccess) {
// Add a short sleep here to ensure that the calling thread does not lead to starvation of other threads
Thread.sleep(100);
}
}
}


try {
if (rs != null) rs.close();
if (selectStmt != null) selectStmt.close();
if (con != null) con.close();
} catch (SQLException ex) {}
return l_temp;





I added the while(!bSuccess) because while one thread is accessing the table, update operation by another process/thread fails with an SQLException.


This code works most of the times, but is still failing quite often.


I use either Oracle 9i or Inforix 7.3 database.


Can somebody suggest a better way to lock a row in a database table across processes/threads to solve my problem?

   <<Less

Re: How to lock a row in a database table?

Posted By:   Nick_Maiorano  
Posted On:   Thursday, November 6, 2003 05:46 PM

Uday,



Try replacing "SELECT * FROM MY_TABLE WHERE IDTYPE=?" with "SELECT * FROM MY_TABLE FOR UPDATE WHERE IDTYPE=?"

About | Sitemap | Contact