Is it possible to lock a single row on a SQL Server table using JDBC?
I'm using jTDS driver v1.2.7 and SQL Server 2005.
Trying with
UPDATE myTable SET timestamp=GETDATE() WHERE id='myid'
from SQL Server Management Studio the lock is acquired correctly on the row. I'm able to perform SELECT and UPDATE instructions on other rows.
If I try the same but using JDBC the entire table is locked.
Any help is appreciated
Look at the isolation level of your session.
select session_id,login_name,program_name,
date_format,quoted_identifier,arithabort,
ansi_null_dflt_on,ansi_defaults,ansi_warnings,
ansi_padding,ansi_nulls,concat_null_yields_null,
case transaction_isolation_level
when 0 then 'Unspecified'
when 1 then 'ReadUncomitted'
when 2 then 'ReadCommitted'
when 3 then 'Repeatable'
when 4 then 'Serializable'
when 5 then 'Snapshot'
end as transaction_isolation_level,
deadlock_priority
from sys.dm_exec_sessions
Maybe your SELECT becomes blocked because you are using READ COMMITTED. The UPDATE will block only that single row as you leave opened the transaction.
Best Regards.
