Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

locking in DML statement Expand / Collapse
Author
Message
Posted Sunday, December 2, 2012 4:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, September 27, 2014 3:29 AM
Points: 108, Visits: 1,098
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.
Post #1391711
Posted Sunday, December 2, 2012 4:54 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
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
    SQL Server MVP
    SQLblog.com
    @SQL_Kiwi
    Post #1391715
    Posted Sunday, December 2, 2012 5:37 AM
    SSC-Enthusiastic

    SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

    Group: General Forum Members
    Last Login: Saturday, September 27, 2014 3:29 AM
    Points: 108, Visits: 1,098
    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?
    Post #1391717
    Posted Sunday, December 2, 2012 6:45 AM


    SSCrazy Eights

    SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

    Group: General Forum Members
    Last Login: Friday, October 17, 2014 8:13 AM
    Points: 9,926, Visits: 11,188
    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
    SQL Server MVP
    SQLblog.com
    @SQL_Kiwi
    Post #1391720
    Posted Sunday, December 2, 2012 11:08 PM
    SSC-Enthusiastic

    SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

    Group: General Forum Members
    Last Login: Saturday, September 27, 2014 3:29 AM
    Points: 108, Visits: 1,098

    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?
    Post #1391773
    Posted Sunday, December 2, 2012 11:18 PM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Thursday, June 12, 2014 2:04 PM
    Points: 1,786, Visits: 1,014
    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
    Post #1391776
    Posted Sunday, December 2, 2012 11:49 PM


    SSCrazy Eights

    SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

    Group: General Forum Members
    Last Login: Friday, October 17, 2014 8:13 AM
    Points: 9,926, Visits: 11,188
    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
    SQL Server MVP
    SQLblog.com
    @SQL_Kiwi
    Post #1391783
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse