March 12, 2003 at 4:39 pm
I'm trying to locate the gremlins in our Production database servers. One line of attack is working on locks, and to track them I'm using SQL Profiler and the various LOCK events (in particular, Timeout).
One of the data columns set by this event is Mode, and in here Profiler loads an intriguing integer value. Now, I'd assume that this value correlates with the various lock types (S-Shared, X-Exclusive, IX-Intent Exclusive, and so forth). However, I can find nothing (and I've Googled and MS Technetted this) that explains what these values actually are.
Does anyone have any information or insight on this?
Philip
March 14, 2003 at 7:29 pm
I found a partial answer. BOL does not indicate that the TEXT data column is pertinent to this event, but when it gets included the "name" of the lock" (key lock, page lock, etc.) appears--not in the text column , but in the text view pane.
Most confusing.
Philip
March 14, 2003 at 8:54 pm
Well, I for one completely disagree with the preceding post -- clearly that bozo doesn't know what he's talking about. (And since he's me, I think I'm qualified to say this.)
Yes, the object being locked is shown in the lower pane, but that isn't the type of lock, nor does it require the presence of the TEXT column to appear.
I found the Mode lookup info in BOL, under the entry for sysLockInfo. Copied here for posterity.
Philip
0 = NULL. No access is granted to the resource. Serves as a placeholder.
1 = Sch-S (Schema stability). Ensures that a schema element, such as a table or index, is not dropped while any session holds a schema stability lock on the schema element.
2 = Sch-M (Schema modification). Must be held by any session that wants to change the schema of the specified resource. Ensures that no other sessions are referencing the indicated object.
3 = S (Shared). The holding session is granted shared access to the resource.
4 = U (Update). Indicates an update lock acquired on resources that may eventually be updated. It is used to prevent a common form of deadlock that occurs when multiple sessions lock resources for potential update at a later time.
5= X (Exclusive). The holding session is granted exclusive access to the resource.
6 = IS (Intent Shared). Indicates the intention to place S locks on some subordinate resource in the lock hierarchy.
7= IU (Intent Update). Indicates the intention to place U locks on some subordinate resource in the lock hierarchy.
8= IX (Intent Exclusive). Indicates the intention to place X locks on some subordinate resource in the lock hierarchy.
9 = SIU (Shared Intent Update). Indicates shared access to a resource with the intent of acquiring update locks on subordinate resources in the lock hierarchy.
10 = SIX (Shared Intent Exclusive). Indicates shared access to a resource with the intent of acquiring exclusive locks on subordinate resources in the lock hierarchy.
11 = UIX (Update Intent Exclusive). Indicates an update lock hold on a resource with the intent of acquiring exclusive locks on subordinate resources in the lock hierarchy.
12 = BU. Used by bulk operations.
13 = RangeS_S (Shared Key-Range and Shared Resource lock). Indicates serializable range scan.
14 = RangeS_U (Shared Key-Range and Update Resource lock). Indicates serializable update scan.
15 = RangeI_N (Insert Key-Range and Null Resource lock). Used to test ranges before inserting a new key into an index.
16 = RangeI_S. Key-Range Conversion lock, created by an overlap of RangeI_N and S locks.
17 = RangeI_U. Key-Range Conversion lock, created by an overlap of RangeI_N and U locks.
18 = RangeI_X. Key-Range Conversion lock, created by an overlap of RangeI_N and X locks.
19 = RangeX_S. Key-Range Conversion lock, created by an overlap of RangeI_N and RangeS_S. locks.
20 = RangeX_U. Key-Range Conversion lock, created by an overlap of RangeI_N and RangeS_U locks.
21 = RangeX_X (Exclusive Key-Range and Exclusive Resource lock). This is a conversion lock used when updating a key in a range.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply