title: Stored procedure with cursor is permacycling
body: <p>This is the stored procedure for any reason this perma-looping </p>
  
  <p>I have to make some aclarations</p>
  
  <p>its the first time that i use <code>SCROLL KEYSET SCROLL_LOCKS</code> and <code>WHERE CURRENT OF</code></p>
  
  <p>the short explanation of what this does:
        replacing a temporal id for many rows with a permanent id that is the <code>MAX(num_subproceso)+1</code> but I want it to do in ACID fashion to solve possible concurrence iusses.</p>
  
  <p>I can't use transaction directly in my app, the reason for this is that I'm using a classes set of my authorship that abstract the structure of the database and the data behavior in this way, only of the downside of my specific implementation is that i can't use a transaction because my code often in rapid opening/closing connection cycles and i need to make a transaction of big chunk of sqls. </p>
  
  <p>To avoid this I finished generating a negative random id to write the rows in the database and later use a stored proc to use a transaction and use a cursor with row lock to avoid (maybe I'm wrong here) that other user get the same id using MAX(...)+1 and occurs a problem where 2 user write the same primary key.</p>
  
  <p>Why I'm saying that I'm preventing concurrency problem (I could be wrong): if I lock a row that I'm using and other user makes an operation (ex. MAX(...) to get a new id) over the whole set of rows in the same table, the lock that is blocking my row  prevent that the 2nd client can get the result and stay in wait state until my lock is released</p>
  
  <p>The process is long and get various steps in a way that pass a time between start and finish the whole process .</p>
  
  <p>... the permanent id is the max number of that type of id plus one ... in this way; while the temporal id is the same at first the permanent id can vary </p>
  
  <h2>data example</h2>
  
  <p>(before)</p>
  
  <pre><code>cve_subproceso (PK) | num_subproceso (PK) | inidate (dd-mm-yyyy hh:MM:ss)
  --------------------+---------------------+---------------------
  A01                 | -27813578125        | 01-05-2011 09:00:00
  A02                 | -27813578125        | 15-05-2011 10:00:00
  A03                 | -27813578125        | 16-05-2011 07:30:00
  A07                 | -27813578125        | 21-05-2011 09:15:00
  A12                 | -27813578125        | 30-05-2011 10:00:00
  ...                 | ...                 | ...
  </code></pre>
  
  <p>(after)</p>
  
  <pre><code>cve_subproceso (PK) | num_subproceso (PK) | inidate (dd-mm-yyyy hh:MM:ss)
  --------------------+---------------------+---------------------
  A01                 | 157                 | 01-05-2011 09:00:00
  A02                 | 122                 | 15-05-2011 10:00:00
  A03                 | 15                  | 16-05-2011 07:30:00
  A07                 | 90                  | 21-05-2011 09:15:00
  A12                 | 140                 | 30-05-2011 10:00:00
  ...                 | ...                 | ...
  </code></pre>
  
  <h2>How is calculated [num_subproceso]</h2>
  
  <p>the number of the permanent id is based in how many [cve_subproceso] are in the table.</p>
  
  <p>related to this example ... exists or existed at given time 156 'A01' secuenced from 1 to 156 when i'm adding 157 to A01 in this case, is because i calculated MAX([num_subproceso]) and MAX() returned 156</p>
  
  <pre><code>cve_subproceso (PK) | num_subproceso (PK) | inidate (dd-mm-yyyy hh:MM:ss)
  --------------------+---------------------+---------------------
  A01                 | 156                 | 01-05-2011 09:00:00
  A01                 | 155                 | 15-04-2011 10:00:00
  A01                 | 154                 | 03-04-2011 07:30:00
  A01                 | 152                 | 11-03-2011 09:15:00
  A01                 | 151                 | 10-01-2011 10:00:00
  ...                 | ...                 | ...
  </code></pre>
  
  <p>i repeat the same operation for each value of [cve_subproceso] ...</p>
  
  <h2>SP code</h2>
  
  <pre><code>ALTER PROCEDURE [dbo].[ReprocesarEventos] 
  -- Add the parameters for the stored procedure here
  @numSubproceso int = 0
  AS 
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
      BEGIN TRANSACTION;
      -- Insert statements for procedure here
      DECLARE @nuevoID integer;
      DECLARE @cveSubProc varchar(4);
      DECLARE cUpd CURSOR SCROLL KEYSET SCROLL_LOCKS
          FOR SELECT [cve_subproceso] FROM [dbo].[calendario] WHERE [num_subproceso]=     @numSubproceso
      FOR UPDATE OF [num_subproceso];
  OPEN cUpd;
  FETCH NEXT FROM cUpd INTO @cveSubProc;
  WHILE (@@FETCH_STATUS &lt;&gt; -1)
  BEGIN
      IF (@@FETCH_STATUS &lt;&gt; -2)
      BEGIN
          SELECT @nuevoID=(ISNULL(MAX([num_subproceso]),0)+1) FROM [dbo].[calendario] WHERE [cve_subproceso]=RTRIM(LTRIM(@cveSubProc));
          IF(@nuevoID &lt; 0)
          BEGIN
              SET @nuevoID = 1;
          END;
          UPDATE [dbo].[calendario] SET [num_subproceso]=@nuevoID WHERE CURRENT OF cUpd;
      END;
      FETCH NEXT FROM cUpd INTO @cveSubProc;
  END;
  CLOSE cUpd;
  DEALLOCATE cUpd;
  
  IF(@@ERROR &lt;&gt; 0)
   BEGIN
      -- Rollback the transaction
      ROLLBACK TRANSACTION;
  
      -- Raise an error and return
      RAISERROR ('Oops! - something went wrong.', 16, 1);
      RETURN;
   END;
  COMMIT TRANSACTION;
  END;
  </code></pre>
  
  <p>what is wrong here?</p>