CODEWITHSUNDEEP
sql-serversql-server-2008stored-procedures
nwGCham
nwGCham

Reputation: 313

Stored procedure hangs when WITH (NOLOCK) uses inside cursor

I have a stored procedure in SQL Server 2008 R2 as follows;

DECLARE @uniqueId BIGINT

DECLARE TestCursor CURSOR FOR 
    SELECT em.uniqueId 
    FROM EMPLYEE_MASTER em WITH (NOLOCK)
    INNER JOIN EMP_DETAILS edt WITH (NOLOCK) ON em.uniqueId = edt.uniqueId
    INNER JOIN EMP_ATTENDANCE ea WITH (NOLOCK) ON em.uniqueId = ea.uniqueId

OPEN TestCursor

FETCH NEXT FROM TestCursor INTO @uniqueId

WHILE @@fetch_status = 0
BEGIN
    BEGIN TRANSACTION  purge        

    DELETE FROM EMPLYEE_MASTER where uniqueId = @uniqueId
    DELETE FROM EMP_DETAILS where uniqueId = @uniqueId
    DELETE FROM EMP_ATTENDANCE where uniqueId = @uniqueId

    DELETE FROM EMP_ADDRESS where uniqueId = @uniqueId
    DELETE FROM EMP_APPRAISALS where uniqueId = @uniqueId

    COMMIT TRANSACTION purge

    FETCH NEXT FROM TestCursor INTO @uniqueId
END

CLOSE TestCursor
DEALLOCATE TestCursor

This stored procedure hangs when it start running and blocks running other queries too (there are no referential integrity issues). I suspect the cause for the issue is due to (as you can see) SELECT statement inside the cursor uses WITH (NOLOCK) hint to dirty fetch. Then delete the data in the table that already uses in the select statement with WITH (NOLOCK) statement inside the cursor. Because once I commented the delete statements that are uses with WITH (NOLOCK) in the select statement in the cursor then stored procedure ran without any issue.

Can someone explain:

  1. What I suspect is correct?
  2. Cause for the issue is due to WITH (NOLOCK) use in the statement inside the cursor and lock the records for DELETE (which runs within transaction)
  3. How can I prevent this issue? Remove WITH (NOLOCK) from select statement?

Upvotes: 2

Views: 4491

Answers (2)

Ian
Ian

Reputation: 1

Three things spring to mind: The cursor, the explicit transaction and the DELETE statements.

The cursor uses row level locking by default unless the number of rows exceeds n rows or x size. Where (n rows OR x size) > default, use table locks!

Either way, as the delete statement is trying to delete rows that are locked by the cursor! Irrelevant what locking you specify in the select part of the cursor.

Answers to your questions:

1) No.

2) Not quite. Replace the words "WITH (NOLOCK)" from your statement with "the same tables":

Cause for the issue is due to use of [the same tables] in the statement inside the cursor and lock the records for DELETE (which runs within transaction)

3) See answer by Ivan Starostin!

OR

--DECLARE @uniqueId BIGINT
DECLARE @uniqueIds TABLE (uniqueId BIGINT NOT NULL PRIMARY KEY)
--DECLARE TestCursor CURSOR FOR 
INSERT INTO @uniqueIds
    SELECT em.uniqueId 
    FROM EMPLYEE_MASTER em WITH (NOLOCK)
    INNER JOIN EMP_DETAILS edt WITH (NOLOCK) ON em.uniqueId = edt.uniqueId
    INNER JOIN EMP_ATTENDANCE ea WITH (NOLOCK) ON em.uniqueId = ea.uniqueId
--If the above insert fails, this is due to uniqueId NOT being unique!!
--OPEN TestCursor
--FETCH NEXT FROM TestCursor INTO @uniqueId
--WHILE @@fetch_status = 0
--BEGIN
BEGIN TRANSACTION  purge        

DELETE FROM EMPLYEE_MASTER where uniqueId --= @uniqueId
 in (select uniqueId FROM @uniqueIds);
DELETE FROM EMP_DETAILS where uniqueId --= @uniqueId
 in (select uniqueId FROM @uniqueIds);
DELETE FROM EMP_ATTENDANCE where uniqueId --= @uniqueId
 in (select uniqueId FROM @uniqueIds);
DELETE FROM EMP_ADDRESS where uniqueId --= @uniqueId
 in (select uniqueId FROM @uniqueIds);
DELETE FROM EMP_APPRAISALS where uniqueId --= @uniqueId
 in (select uniqueId FROM @uniqueIds);

COMMIT TRANSACTION purge

--ROLLBACK?

--    FETCH NEXT FROM TestCursor INTO @uniqueId
END
--CLOSE TestCursor
--DEALLOCATE TestCursor

Upvotes: 0

IVNSTN
IVNSTN

Reputation: 9299

Remove absolutely unnecessary cursor, remove senseless NOLOCK.

DECLARE @del TABLE (uniqueId BIGINT NOT NULL PRIMARY KEY)

DELETE em
      OUTPUT DELETED.uniqueId
      INTO @del(uniqueId)
FROM EMPLYEE_MASTER em
WHERE EXISTS(SELECT 1 FROM EMP_DETAILS edt WHERE em.uniqueId = edt.uniqueId)
   AND EXISTS(SELECT 1 FROM EMP_ATTENDANCE ea WHERE em.uniqueId = ea.uniqueId)

DELETE t
FROM EMP_DETAILS t
WHERE EXISTS(SELECT 1 FROM @del d WHERE d.uniqueId = t.uniqueId)

and so on

side note about cursor

DECLARE TestCursor CURSOR STATIC FOR 
SELECT DISTINCT em.uniqueId 
FROM EMPLYEE_MASTER em
INNER JOIN EMP_DETAILS edt ON em.uniqueId = edt.uniqueId
INNER JOIN EMP_ATTENDANCE ea ON em.uniqueId = ea.uniqueId

DISTINCT - you are joining to DETAILS so the number of rows would be multiplied by details number which means you'd attempt to delete same uniqueId several times

STATIC - instead of nolocking you could do this: with this option server will create temp table and store selected values in it.

Upvotes: 5

Related Questions