May 18, 2016 at 4:52 am
Hi All,
I have query regarding locking during transactions. In below query the locks on TABELA is held till the completion of the transaction or just after the first update.
In the first query the lock is held on the entire table or specific row.
BEGIN TRANSACTION
UPDATE TABELA
SET PRODUCTNAME='NEW1'
WHERE TABLEA.ID = 34
UPDATE T
SET
T.GRANDTOTAL = SUM(T3.COSTS)
FROM
TBLCUSTOMER T JOIN TBLPRODUCTS T2 ON T.CUSTID = T2.CUSTID
JOIN TBLCOST T3.PID = T2.PID
COMMIT TRANSACTION
May 18, 2016 at 5:03 am
End of the transaction (it's an exclusive lock, so has to be held until commit/rollback)
As for whether it's a row, page or table lock, that will depend on the number of rows being affected by the update and the indexes on the table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 18, 2016 at 5:03 am
That'll depend on a number of things, such as the transaction isolation level. But you can find the answer for yourself by putting a few strategically-placed [font="Courier New"]EXEC sp_lock[/font] statements in your code.
John
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply