Infinity loop for cursor in gpglobal. gplog

The original code is as follows:

Begin

Declare

@ SQL _stmt nvarchar (300 ),

@ V_client_id int,

@ V_pgname varchar (1000 ),

@ V_logid int;

Declare C2 cursor for select pgname, client_id from gpglobal. gplog;

Open C2

Fetch next from C2 into @ v_pgname, @ v_client_id

While (@ fetch_status = 0)

Begin

Exec getnextsequencenumber @ v_logid out, 'gplog _ seq ';

Set @ SQL _stmt = 'Update gpglobal. gplog set log_id = '+ Cast (@ v_logid as varchar) + 'where pgname = ''' [email protected] _ pgname + ''' and

Client_id = '+ Cast (@ v_client_id as varchar );

Exec sp_exec_with_log 'db-1216 ', @ SQL _stmt;

Fetch next from C2 into @ v_pgname, @ v_client_id

End

Close C2

Deallocate C2

End

 

The above stored procedure will be executed infinitely, mainly because the cursor C2 is based on the table gpglobal. gplog, when the cursor C2 is looping, the code in the loop changes gpglobal. gplog data,

This will cause C2 to see a new record that has been changed. The record is continuously executed and cannot be stopped. You only need to define C2 as static. It will only see the record when the cursor is opened, which is equivalent to a snapshot at that time.

 

Declare C2 cursor static for select pgname, client_id from gpglobal. gplog;