locking in DML statement
-
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)
PAGEIX1:5330
PAGES1:13754
PAGEIX1:55796
PAGEIX1:6384
PAGEIX1:31868
OBJECTIS
OBJECTIX
KEY X(84e905c4a903)
I am drastically confused.:ermm:
-
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
SQLkiwi blog
@SQL_Kiwi -
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?
-
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
SQLkiwi blog
@SQL_Kiwi -
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_typerequest_moderesource_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?
-
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
-
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
SQLkiwi blog
@SQL_Kiwi
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply