Row locking using JDBC for SQL Server

[sbhavan]

Hi All,

In my development of an online application, I have to generage the serial no for the available number from the serial number table in sql server. Multiple users will hit the table to generate the serial numbers.

So the serial number should not be repeated.

I have to lock the row as a user reads the table and release it only when the transaction commits or rollbacks.

I am doing this as follows with no success.

Please help me in the correct way.

get the connection
Set the auto commit of the connection as false.
query the serial number table for getting the available serial number
update the appropriate tables with the corresponding data and with the serial number
Increment the serial number by one.
Update the serial number table with the new serial number(which will be used by the next transaction/other users)
set the auto commit of the connection as true
close the connection

Sample code

...
connection.setAutoCommit(false);
...
resultset=statement.executeQuery(select sl_no from srl_no where company_code='"+compCode+"' and branch_code='"+brCode+"' and year="+year+"");
...
...
statement.addBatch(q1);
...
statement.addbatch(qn);
statement.executeBatch();
connection.commit();
...
connection.close();
...

Thanks in advance.