Custom sequence and Concurrency

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Custom sequence and Concurrency

Author

Topic

hrishy
Starting Member

47 Posts

Posted - 2008-05-25 : 16:45:57

Hi

I need to generate a custom sequence the sequence logic is based on a datepart and a 6 digit unique number.

The existing solution uses a table where it stores the last generated sequence and when a new sequence is to be generated the table row is locked so nobody gets to update it until work is done and then the locked row is incremented by one.

I have serious doubts about the scalability of that approach is my guess right ?

I am thinking of using a identity column but the problem is everyday the sequence should start from 0000001.

so for example today it should start from
080525000001
and tomorrow onwards it should start from
080526000001 etc..

I have coded a stored proc which depends upon a helper table and generates that and if it is a new day then it truncates that table so the identity column is reset.

is there a way to call the stored proc like this

insert into my_tables
SELECT stored_proc,other_values ...........

regards
Hrishy

nr
SQLTeam MVY

12543 Posts

Posted - 2008-05-25 : 18:25:27

Nothing to stop you resettng the identity seed every day - problem might be that the values might not be consecutive.
see
http://www.simple-talk.com/sql/t-sql-programming/identity-columns/

The alternative is to go with the table solution you have at the moment but reduce the locking.
Have a scheduled task to reset the sequence number at the start of the day then

declare @id bigint
update seqtbl
set seq = seq+1, @id = @id+1

that will give you the next sequence number and update the table in one statement without needing explicit locking and should be done outside a transaction.

You could also encapsulate with an insert inside a transaction but as you say that would cause blocking.

Another option is to use the identity and clear up gaps at the end of the day.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

hrishy
Starting Member

47 Posts

Posted - 2008-05-26 : 03:09:32

Hi Nr

Thanks for looking into my problem.
I am okay with the gaps and the numbers not being in a sequence just need a robust and a scalable solution.

I will post my code tomorrow .

For resetting the identity column i would truncate the table in the stored proc.

is there a way to call the stored proc like this

insert into my_tables
SELECT stored_proc,other_values ...........

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-26 : 03:32:21

quote:
Originally posted by hrishy

Hi Nr

Thanks for looking into my problem.
I am okay with the gaps and the numbers not being in a sequence just need a robust and a scalable solution.

I will post my code tomorrow .

For resetting the identity column i would truncate the table in the stored proc.

is there a way to call the stored proc like this

insert into my_tables
SELECT stored_proc,other_values ...........

You cant. you should use
INSERT into my_tables
EXEC stored_proc

and if you want to get other values as well put the result of EXEC stored_proc into temporary table and SELECT from there along with other values to insert to your table.

hrishy
Starting Member

47 Posts

Posted - 2008-05-26 : 04:16:36

Hi Visakh

Thanks for replying .I appreciate.

What happens when two concurrent users execute my stored proc at the same time.
Will they get different sequence numbers.

Using a stored proc the way i am thinking is it more scalable then use a table lock it do whatever you want to do with the sequence and then update the table with the next sequence value and release the lock.

regards
Hrishy

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-26 : 04:23:57

See http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

E 12°55'05.25"
N 56°04'39.16"

hrishy
Starting Member

47 Posts

Posted - 2008-05-26 : 05:04:23

Hi Peso

Thanks for the article link.
I had a look and i cannot use a function as when a new day starts i need to truncate the table and start the sequence from the beginning.
and a function cannot do DML or DDL.

So i am just wundering from a concurrency perspective what happens when somebody calls my stored_proc at the same time which has a DDL in it to truncate the table containing the identity column

regards
Hrishy

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-05-26 : 23:59:47

You realise you can't guarantee that you'll actually start at 1 if the first transaction rolls back?
If you're ok with gaps & stuff then why start at 1 each day at all?

If I were you I'd use a normal identity, store the date the row was created (you probably already have this) and sort out the human readable 'key' on the odd occasion you need to when you select it out using the transaction date and ROW_NUMBER().

I'd be interested if there really is a reason why you have to do all this. More often than not there isn't when you dig a bit deeper.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources