sybase concurrent process/multi-threading

Encountered a multi-threading issue on this stored procedure

CREATE PROCEDURE dbo.spc_get_next_sequence_no
 		(@codedesc			varchar(30),
 		 @next_id			integer OUTPUT)
AS

		select @next_id = next_id from next_number where code_desc = @codedesc

        update next_number set next_id = next_id + 1 where code_desc = @codedesc
        

RETURN 0

table next_number is a record of sequence number for different purpose

code_desc	next_id
receipting_number	732215
receipting_batch_id	2521
conso_tax_extract	230
conso_dist_extract	79
Manual Journal	8933
receipt_number	101891
complaint_num	7
complaint_record_no	128

(I know we should use Sybase sequence, or create table with identity. But this is a legacy database, which I have no control.)

the issue was, there are more than one thread comes in and both threads get the same next_id,

the solutions are two parts:
1. make the SP atomic (begin transaction.. commit transaction)
2. lock the SP (to make it synchronized; update statement first plus the begin and commit transaction)

refer to:
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sqlug/html/sqlug/sqlug843.htm

CREATE PROCEDURE dbo.spc_get_next_sequence_no
 		(@codedesc			varchar(30),
 		 @next_id			integer OUTPUT)
AS
BEGIN TRANSACTION
        update next_number set next_id = next_id + 1 where code_desc = @codedesc
        
		select @next_id = next_id from next_number where code_desc = @codedesc
COMMIT TRANSACTION

RETURN 0

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s