Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


locking in DML statement


locking in DML statement

Author
Message
mah_j
mah_j
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 1261
Hi

what is the difference between IX and X lock on a table?(Totally I plus s,x,u,etc.)
according to what i have read,'I' stands for intent.Intent locks are a means in which a transaction notifies other transaction that it is intending to lock the data. What this means is that before you obtain a lock on the page or the row level an intent lock is set on the table.(reference:Introduction to Locking in SQL Server By Mladen Prajdić )
I dont grasp the concept:-(


when we insert a single row the whole table is locked or just a row?I use :

SELECT resource_type, request_mode, resource_description
FROM sys.dm_tran_locks
WHERE resource_type <> 'DATABASE'

but from this output i cant recognize which type of lock(whole table or row)it is?

KEY    X (da9b91b6239e)
KEY    X   (23616c6a1ba3)
KEY    X   (dac08036affd)
PAGE   IX   1:5330
PAGE   S   1:13754
PAGE   IX   1:55796
PAGE   IX   1:6384
PAGE   IX   1:31868
OBJECT   IS   
OBJECT   IX   
KEY    X   (84e905c4a903)



I am drastically confused.Ermm
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
Books Online (http://msdn.microsoft.com/en-us/library/ms175519.aspx) explains the concept quite well:

The Database Engine uses intent locks to protect placing a shared (S) lock or exclusive (X) lock on a resource lower in the lock hierarchy. Intent locks are named intent locks because they are acquired before a lock at the lower level, and therefore signal intent to place locks at a lower level.

Intent locks serve two purposes:

  • To prevent other transactions from modifying the higher-level resource in a way that would invalidate the lock at the lower level.

  • To improve the efficiency of the Database Engine in detecting lock conflicts at the higher level of granularity.

  • For example, a shared intent lock is requested at the table level before shared (S) locks are requested on pages or rows within that table. Setting an intent lock at the table level prevents another transaction from subsequently acquiring an exclusive (X) lock on the table containing that page. Intent locks improve performance because the Database Engine examines intent locks only at the table level to determine if a transaction can safely acquire a lock on that table. This removes the requirement to examine every row or page lock on the table to determine if a transaction can lock the entire table.

    In your example:


    ╔═══════════╦════════════╦═════════════════╗
    ║ Lock Type ║ Lock Mode ║ Resource ║
    ╠═══════════╬════════════╬═════════════════╣
    ║ KEY ║ X ║ (da9b91b6239e) ║
    ║ KEY ║ X ║ (23616c6a1ba3) ║
    ║ KEY ║ X ║ (dac08036affd) ║
    ║ KEY ║ X ║ (84e905c4a903) ║
    ║ PAGE ║ IX ║ 1:5330 ║
    ║ PAGE ║ IX ║ 1:55796 ║
    ║ PAGE ║ IX ║ 1:6384 ║
    ║ PAGE ║ IX ║ 1:31868 ║
    ║ OBJECT ║ IX ║ ║
    ║ PAGE ║ S ║ 1:13754 ║
    ║ OBJECT ║ IS ║ ║
    ╚═══════════╩════════════╩═════════════════╝

    There are four row exclusive locks in an index (KEY-X), four intent-exclusive locks on the pages (PAGE-IX) that cover those locked keys, and a intent-exclusive table (OBJECT-IX) lock. There is also a single page locked with a shared lock (PAGE-S) and the corresponding table level shared lock (OBJECT-IS).

    Not all locks block other locks - see the Lock Compatibility Matrix in Books Online (http://msdn.microsoft.com/en-us/library/ms186396.aspx) for details of which locks are compatible.



    Paul White
    SQLPerformance.com
    SQLblog.com
    @SQL_Kiwi
    mah_j
    mah_j
    SSC-Enthusiastic
    SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

    Group: General Forum Members
    Points: 127 Visits: 1261
    Thanks for your reply

    so if i have a select on a table that takes long 25 seconds and an insert on the same table is running at the 10th second ,the insert will wait for select ?or it depends on the range of pages?
    Paul White
    Paul White
    SSChampion
    SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

    Group: General Forum Members
    Points: 10338 Visits: 11350
    mah_j (12/2/2012)
    so if i have a select on a table that takes long 25 seconds and an insert on the same table is running at the 10th second ,the insert will wait for select ?or it depends on the range of pages?

    It does depend on a number of things. For one, it depends on the transaction isolation level. At the default level of READ COMMITTED, SQL Server generally releases shared locks as soon as it has read a row or page. You can see a nice diagram of that in Craig Freedman's blog entry about the default isolation level:



    If the READ_COMMITTED_SNAPSHOT database option is enabled, SQL Server keeps versions of rows to avoid taking shared locks at all when the client requests (or does not change) the default isolation level.

    Whether an INSERT is blocked by the SELECT in your example also depends on the data range and granularity of locking. SQL Server decides whether to take locks on rows, pages, or the whole table at the start of the reading operation. As the operation progresses, it may decide to escalate finer-grained locks to the partition or table level, if the number of held locks reaches a particular threshold (it never escalates locks to the page level, it's partition/table or nothing). Most often, SQL Server starts with row or page-level locking. This would be very unlikely to block the INSERT in your example, not least because individual row or page locks would not be held for very long, as mentioned.

    Should SQL Server decide to start with a shared table-lock (not an intent shared lock, as I hope you now understand), that would obviously be held until the whole operation completed, and the INSERT would be blocked. SQL Server does not often do this of its own accord, however. It is much more likely that a table lock was explicitly requested by the query writer using a TABLOCK hint.



    Paul White
    SQLPerformance.com
    SQLblog.com
    @SQL_Kiwi
    mah_j
    mah_j
    SSC-Enthusiastic
    SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

    Group: General Forum Members
    Points: 127 Visits: 1261
    Tanks for your useful explanation.

    As you mentioned and if I understood correctly,if there is a shared table-lock , the INSERT would be blocked until the whole operation(select) completed .Is there any recognizable reason for the shared table-lock?

    In a select statement i use WITH (HOLDLOCK) to see the locks.

    BEGIN TRANSACTION

    SELECT COUNT(*) AS [Count],
    [t1].[h] AS [Hour],
    [t1].[Transk] AS [Kind]
    FROM (
    SELECT DATEPART(Hour, [t0].[Date]) AS [h],
    [t0].[Transk]
    FROM [dbo].[Trans] AS [t0] WITH (HOLDLOCK)
    WHERE ([t0].[Success] = 1)
    AND ([t0].[Type] = 1)
    AND ([t0].[Date] >= '2012-11-05 00:00:00')
    AND ([t0].[Date] < '2012-11-06 00:00:00')
    ) AS [t1]
    GROUP BY
    [t1].[h],
    [t1].[Transk]


    SELECT resource_type, request_mode, resource_description
    FROM sys.dm_tran_locks
    WHERE resource_type <> 'DATABASE'

    COMMIT



    and this is the output:

    resource_type   request_mode   resource_description
    OBJECT    S


    hence ,the INSERT would be blocked?how can i avoid this problem?
    I suppose the time out that I have in Inserts is because of this,am i true?
    in this case I even check indexes but they are properly created and there isn't any unused index.

    How can i monitor these time out a part from SQL Profiler(because of low performance) to see the locks either in the past(i mean a history) or at the moment?
    Jayanth_Kurup
    Jayanth_Kurup
    SSCrazy
    SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

    Group: General Forum Members
    Points: 2093 Visits: 1351
    Optimistic concurrency , will help prevent locking issues by creating a version store of the data to be modified in the temp db. This means a reader will no longer block a writer.

    http://msdn.microsoft.com/en-us/library/ms345124%28v=sql.90%29.aspx

    Pretty much everything u need to know about locking and concurrency can be found here

    Jayanth Kurup
    Paul White
    Paul White
    SSChampion
    SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

    Group: General Forum Members
    Points: 10338 Visits: 11350
    mah_j (12/2/2012)
    In a select statement i use WITH (HOLDLOCK) to see the locks.

    HOLDLOCK does not do what it seems you think it does. HOLDLOCK is a synonym for SERIALIZABLE - see http://msdn.microsoft.com/en-us/library/ms187373.aspx for the full description and explanation.

    By changing the isolation level to SERIALIZABLE you are changing the locks taken, and how long they are taken for. In this case, the engine decides to take and hold a table-level shared lock. Without the HOLDLOCK hint, the engine would likely take row- or page-level locks, just before reading a row (or page), and release them immediately afterward.



    Paul White
    SQLPerformance.com
    SQLblog.com
    @SQL_Kiwi
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search