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.
ReadUncomitted solves the problem with SELECT instructions. Still can't update other rows