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
Advertisements

Author: lwpro2

Java J2EE professional

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s