Question realetd to TABLOCK

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

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

  • 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]

  • 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