Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #2078

Re: Help needed in troubleshooting a lock

Show all headers | View raw

Anton Shepelev (anton.txt@g{oogle}mail.com) writes:
> 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

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 constant, 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?

> 
>   <Locks>
>      <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" 
request_status="GRANT" request_count="1" />
>          <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.

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Help needed in troubleshooting a lock Anton Shepelev <anton.txt@g{oogle}mail.com> - 2021-03-09 16:47 +0300
  Re: Help needed in troubleshooting a lock Erland Sommarskog <esquel@sommarskog.se> - 2021-03-09 20:57 +0100
    Re: Help needed in troubleshooting a lock Anton Shepelev <anton.txt@g{oogle}mail.com> - 2021-03-10 12:52 +0300
      Re: Help needed in troubleshooting a lock Anton Shepelev <anton.txt@g{oogle}mail.com> - 2021-03-10 13:05 +0300

csiph-web