April 16, 2012 at 2:04 am
I have asked a question related to Nolock separetely. Here is a question related to tablock. I am trying to understand table hints and be in better position to provide solution to a locking problem in my application
Trans 1:
UPDATE Production.Product
WITH (TABLOCK)
SET ListPrice = ListPrice * 1.10
WHERE ProductNumber LIKE 'BK-%'
TABLOCK puts a shared lock on a entire table
Since select is compaible witn shared lock,in above case, will some other Select query on the Product table from some other session still be able to read data from Product table.
April 16, 2012 at 3:10 am
Yes.
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
April 16, 2012 at 3:34 am
thanks...
April 16, 2012 at 4:15 am
Just to add:
There is another locking hint: TABLOCKX - it will put an exclusive lock on the table, so it will not be available for selects from another transactions...
In general, you would not use locking hints unless you absolutely sure you do need them and fully understand all implications of them.
But they do exist and they do exist on purpose.
I would say that the main purpose of locking hints is to allow fine-custom control of concurrency vs performance balance:
The most granular locks give you better concurrency, but some time you don't need it (ETL to upload data warehouse or reporting), so you may want to reduce concurrency to increase performance...
September 2, 2014 at 2:19 am
No. With(TABLOCK) specifies the granularity of the lock (Key,Page,Object) and not the lock mode(shared,exclusive,...) . if you update the table with using WITH(TABLOCK) hint SQL would put "X" lock on the table and other sessions would not be able to access the table. you can query sys.dm_tran_locks to confirm that.
Pooyan
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply