I am trying to write a
T-SQL script that shall output currently locked rows in @GT-
TABLEKEYS, assuming that write access to @GTTABLEKEYS is al-
ways made with (ROWLOCK). Here is what have come up with:
-- 1. Select the free (unlocked) rows:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT Code, U_CurrentKey INTO #FREE FROM [@GTTABLEKEYS] WITH
(READPAST)
-- 2. Select all the rows:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT Code INTO #ALL FROM [@GTTABLEKEYS]
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
-- 3. Report rows that are not free (i.e. locked):
SELECT #ALL.Code
FROM #ALL
LEFT JOIN #FREE ON #FREE.Code = #ALL.Code
WHERE #FREE.Code IS NULL
whereas the UPDATE is waiting:
<Locks>request_status="GRANT" request_count="1" />
<Lock request_mode="S" request_status="GRANT" request_count="1" />
</Locks>
<Objects>
<Object name="@GTTABLEKEYS" schema_name="dbo">
<Locks>
<Lock resource_type="OBJECT" request_mode="IS"
<Lock resource_type="PAGE" page_type="*"index_name="KGTTABLEKEYS_PR" request_mode="S" request_status="WAIT" request_count="1" />
</Locks>
</Object>
</Objects>
The SELECT wants a page lock but is blocked by the IX
lock held by the UPDATE statement. You need to add a
ROWLOCK hint to that query.
That solved it, thanks. Somehow it did not occur to me
that I needed a (ROWLOCK) for the SELECT as well.
-- 1. Select the free (unlocked) rows:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT Code, U_CurrentKey INTO #FREE FROM [@GTTABLEKEYS] WITH (READPAST)
-- 2. Select all the rows:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT Code INTO #ALL FROM [@GTTABLEKEYS]
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
-- 3. Report rows that are not free (i.e. locked):
SELECT #ALL.Code
FROM #ALL
LEFT JOIN #FREE ON #FREE.Code = #ALL.Code
WHERE #FREE.Code IS NULL
DROP TABLE #FREE
DROP TABLE #ALL
There is more than one way that this can go wrong. One
thing is that things may change quickly in a concurrent
environment.
Another is that a NOLOCK read amy skip already committed
rows, if you come right in the middle of page split or
something like this.
Then again, if the number of rows in this table are con-
stant, or rows are added or deleted very rarely, it could
work.
Yet, then again, in that case, you can keep a shadow copy
of the table, and keep it maintained from a trigger, and
you don't need the NOLOCK read at all.
whereas the UPDATE is waiting:
The SELECT is waiting?
The SELECT wants a page lock but is blocked by the IX lock
held by the UPDATE statement. You need to add a ROWLOCK
hint to that query.
Sysop: | Keyop |
---|---|
Location: | Huddersfield, West Yorkshire, UK |
Users: | 296 |
Nodes: | 16 (3 / 13) |
Uptime: | 69:53:15 |
Calls: | 6,656 |
Calls today: | 2 |
Files: | 12,200 |
Messages: | 5,332,146 |
Posted today: | 1 |