0

I executed a few queries like below, where it INSERTS 1 row to the table then UPDATE it, and I didn't get any error.

But then I found out that it locked the table, where no one else can query the table.

Do you know why the query below would lock the table ?

Can I not SET vchB = vchNumber, vchC = vchNumber right after INSERT INTO ?

I read when/what locks are hold/released in READ COMMITTED isolation level, and it says "All lock will release only after committed/rollbacked".

The INSERT and UPDATE statements below were committed succesfully (it only INSERT and UPDATE 1 row in the table), and it only took a second to run, but yet when other people query the table, it just hang.

Thank you.

BEGIN TRANSACTION T1    
INSERT INTO myTbl(vchSN,vchNumber,vchName)  
SELECT 'AB12','1234','My Name'

UPDATE myTbl 
SET vchB = vchNumber,  vchC = vchNumber,  vchtab = 'N'  where vchSN = 'AB12'

COMMIT TRANSACTION T1
faujong
  • 949
  • 4
  • 24
  • 40
  • As mentioned in the last question you asked there has to be something else going on. Any locks generated inside your transaction will be released once the commit executes. If you are experiencing locking you need to look into other processes that could be causing the issue. – Sean Lange Feb 12 '20 at 16:16
  • If possible I would use a free tool like sp_WhoIsActive or sp_BlitzLock to see what is happening on your server. They will tell you if you track down open transactions – JMabee Feb 12 '20 at 19:01
  • OK. The weird thing is it was showing that this was the query that was locking the table. But, it was committed successfully. – faujong Feb 12 '20 at 19:10

0 Answers0