Question realetd to TABLOCK

Question

Question realetd to TABLOCK

itskanchanhere

SSC Eights!

Points: 802
More actions
April 16, 2012 at 2:04 am

#256412

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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

itskanchanhere SSC Eights! Points: 802 More actions · Answer 2

itskanchanhere

SSC Eights!

Points: 802

April 16, 2012 at 3:34 am

#1473902

thanks...

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

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...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

pooyan_pdm Hall of Fame Points: 3007 More actions · Answer 4

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