Last post Oct 28, 2016 04:54 PM by PatriceSc
Oct 28, 2016 10:12 AM|sudip_inn|LINK
UPDATE EMP2 WITH (HOLDLOCK,ROWLOCK)
SET SALARY=100 WHERE ID IN (1,2)
WAITFOR DELAY '00:02:00';
PRINT 'Job complete'
i use wait for to simulate some dealy.
when i execute the above code and from other query window when i issue select like
select * from Emp2
then i saw data was not coming. it means update statement place a lock on table not few rows.
but i want those rows should be coming which are excluded in update statement. update statement updating only employee whose id are 1 and 2 so i want other employee data should come when begin tran is running.
please tell me how could i achieve it. thanks
Oct 28, 2016 11:06 AM|mgebhard|LINK
Use the nolock hint.
select * from Emp2 with (nolock)
But this comes with a risk as nolock does a READ UNCOMMITED, meaning it will read the uncommitted salary field. if you have a highly transnational application this might not be a good approach as you end up with dirty data.
You can try ROWLOCK from the openly published MSDN docs
Specifies that row locks are taken when page or table locks are ordinarily taken. When specified in transactions operating at the SNAPSHOT isolation level, row locks are not taken unless ROWLOCK is combined with other table hints that require locks, such
as UPDLOCK and HOLDLOCK.
You might want to investigate READPAST too. This hint will skip a locked record but it comes with a price as well. You simply don't know if the result set is complete.
I suggest that you take some time to understand the technology and the problem you are trying to solve. The MSDN link above explains each hint.
Oct 28, 2016 11:25 AM|sudip_inn|LINK
where to use READPAST and how to use it with select statement ?
i want if 2 row is locked for the emp id 1 and 2 then when i issue select statement then 3 emp id data should come. just post the sample code how to do it. thanks
Oct 28, 2016 04:54 PM|PatriceSc|LINK
it means update statement place a lock on table not few rows.
IMO not yet. You selected ALL rows including those that are locked (maybe at the row level). My first move would be to see what happens if I select all rows EXCEPT those are locked.
Edit: Ah sorry. I missed regardless of the ealier conclusion it seems you want anyway to select automatically all rows expect those that are locked.