Record lock - SQL Server 2005

[hemaraj_dv.2005]

Hello

The back end of my Java application is SQL Server 2005, in multi user
environement.

There are few counters stored in a table. Each record that is
created , looks up the relevant counter field, uses it, updates it
(adds one) and writes it back to db.

To ensure that while a counter is read by some user, it can not be
read by another until the first user is done with it, is it enough to
use


conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED );



and this will create the record lock so two user can not access at
same time, or other process is also needed?

For example anything else from java side or any setting from SQL
Server 2005?

Thank you
Hemaraj

 

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

The back end of my Java application is SQL Server 2005, in multi user
environement.

There are few counters stored in a table. Each record that is
created , looks up the relevant counter field, uses it, updates it
(adds one) and writes it back to db.

To ensure that while a counter is read by some user, it can not be
read by another until the first user is done with it, is it enough to
use

conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED );

and this will create the record lock so two user can not access at
same time, or other process is also needed?

For example anything else from java side or any setting from SQL
Server 2005?

You should a SQLServer expert.

My understanding is that either:

you use transaction isolation level serializable

or:

you select WITH (TABLOCKX) in your sql statement.

Arne

 

[derek]

are you trying to generate unique ids for each record? is that why you are keeping a counter? if so, why not just use an identity column?