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)
 How to manage a "last assigned value"

Author  Topic 

PaulieD
Starting Member

9 Posts

Posted - 2008-11-21 : 16:26:02
Hello all,

Using T-SQL on a SQL2K5 server, I am trying to determine the correct way to handle the following situation ... which, in my humble opinion, I consider to be a common task. Yet, when I asked a few SQL programmers, they were uncertain.

I have a Stored Proc that engages a transaction and performs a few tasks; one of which is to place a returned item into inventory. This is a temporary measure, as the item will eventually be reshipped. When an item is placed into the returns inventory, it needs to be assigned a unique, sequential value based on *two* separate values. To clarify, here's how I built the table that will hold the returned items:

(PK) RecID : int -- identity
     PackageBarcode : varchar(30) -- the barcode from the orig shipment
(IX) PackageType : smallint -- size of the pkg, used for storage location
(IX) InventoryYear : smallint -- YYYY of current date
     InventorySeq : int


For a (PackageType PLUS InventoryYear) combination, the Stored Proc needs to determine the last inventory sequence assigned, increment it and then insert the row into this table. To clarify, the InventorySeq must be a UNIQUE value PER each (PackageType PLUS InventoryYear) combination.

For example:

PackageType -- InventoryYear -- InventorySeq
1 -- 2007 -- 172
1 -- 2008 -- 5
2 -- 2008 -- 28
3 -- 2008 -- 5

My concern is this: I need to ensure that other apps calling this stored procedure create rows with a unique value for the InventorySeq column. Just because this proc utilizes a transaction with commit does not promise that another calling app won't get the same value when trying to ascertain the last inventory sequence assigned for the 2-column index.

I considered having a separate "control table" to manage the last inventory sequence assigned but the same issue / concern applies. Additionally, having a separate "control table" may introduce another issue, in the event of a rollback; I would not want this table rolled back.

It seems that I need to LOCK the row at the Select step, holding the lock while I increment the InventorySeq value and then perform the insert while releasing the lock. If I were to take the "control table" approach, the logic would be similar in that I would LOCK the row at the Select step, holding the lock while I increment the InventorySeq value and then perform an update while releasing the lock. Conceptually, it would be ideal to isolate the logic of allocating an InventorySeq value in a separate stored proc that only allows one concurrent execution at any given time.

I am hopeful someone has a answer to this seemingly commonplace approach of managing a "last value assigned".

Thanks in advance for your feedback.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-21 : 17:23:19
Doesn't putting an exclusive lock on the resources help your cause ?
An exclusive lock won't allow reads to the table , making sure that there isn't a case where other applications would get the same value when trying to ascertain the last inventory sequence assigned since your sequence is calculated basis columns in the same table.
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-11-21 : 17:34:42
quote:
Originally posted by sakets_2000

Doesn't putting an exclusive lock on the resources help your cause ?
No, that probably won't work for this case. That only prevents reading values that have changed but not yet committed.

I assume you cannot use or repurpse the RecID? Havig that Indentity would solve your problem (I think). But, fudimentally, you are faced with a design flaw. I'm not thinking too clearly cuz it's Friday and I want to get out of here, but one hack that comes to mind is to create another "dummy" table with an Indentity and do an insert into that "dummy" table to get your next ID. <shrug> EDIT: Oh, I thought of another hack. You could replace the sequence with a ROWVERSION(TIMESTAMP) column. That'd get you an ever increasing number.
Go to Top of Page

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-21 : 17:58:17
quote:
Originally posted by Lamprey

quote:
Originally posted by sakets_2000

Doesn't putting an exclusive lock on the resources help your cause ?
No, that probably won't work for this case. That only prevents reading values that have changed but not yet committed.



Not sure on this, But doesn't TABLOCKX lock all the rows and not just the uncommited ones ?
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-11-21 : 18:03:06
quote:
Originally posted by sakets_2000

Not sure on this, But doesn't TABLOCKX lock all the rows and not just the uncommited ones ?

Ahh, yes that should work, I had my head stuck thinking about TRANSACTION ISOLATION LEVELs. Also if you include the HOLDLOCK hint or use the SERIALIZABLE isolation level, that should keep the table locked until the end of the transaction.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-11-21 : 18:22:26
quote:
Originally posted by Lamprey

quote:
Originally posted by sakets_2000

Doesn't putting an exclusive lock on the resources help your cause ?
No, that probably won't work for this case. That only prevents reading values that have changed but not yet committed.

I assume you cannot use or repurpse the RecID? Havig that Indentity would solve your problem (I think). But, fudimentally, you are faced with a design flaw. I'm not thinking too clearly cuz it's Friday and I want to get out of here, but one hack that comes to mind is to create another "dummy" table with an Indentity and do an insert into that "dummy" table to get your next ID. <shrug> EDIT: Oh, I thought of another hack. You could replace the sequence with a ROWVERSION(TIMESTAMP) column. That'd get you an ever increasing number.



Rowversion changes every time you update the row, so it is not a good candidate for generating a primary key.



CODO ERGO SUM
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-11-21 : 18:27:37
quote:
Originally posted by Lamprey

quote:
Originally posted by sakets_2000

Not sure on this, But doesn't TABLOCKX lock all the rows and not just the uncommited ones ?

Ahh, yes that should work, I had my head stuck thinking about TRANSACTION ISOLATION LEVELs. Also if you include the HOLDLOCK hint or use the SERIALIZABLE isolation level, that should keep the table locked until the end of the transaction.



You can do that, but you will drastically limit the scalability and concurrent usage of your application.

Why not just use an identity column to generate the InvSeq? You didn’t state any requirement that would prevent it from being used. An identity value is never reused, and it is generated by the system with minimum overhead. It is much more suitable for high scalability and concurrency.



CODO ERGO SUM
Go to Top of Page

PaulieD
Starting Member

9 Posts

Posted - 2008-11-21 : 20:55:35
Hello all and thanks for the replies.

Please correct me if I am wrong but it's my understanding that SQL cannot support an Identity value that is UNIQUE per a key / index. Since my project requires a sequential value to be assigned PER the aforementioned 2 columns, using an Identity will not support my cause.

To clarify, the InventorySeq value assigned must be a UNIQUE value PER each (PackageType AND InventoryYear) combination.

For example:

PackageType : InventoryYear : InventorySeq
1 : 2007 : 172
1 : 2008 : 11
2 : 2007 : 69
2 : 2008 : 28
3 : 2008 : 5

...where an insert for PackageType=1, InventoryYear=2008 would result in 12 for the new row's InventorySeq value.

The overall stored proc / transaction where this logic needs to happen is small and simple. Therefore, using a row lock approach would be acceptable. I would rather not lock the table, unless it was the only available option.

Please provide a code example for employing row lock.

Lamprey: If there's a better design to support my project, I am open to restructing it. As I mentioned, my original thought was to have a "control table" that would hold the last InventorySeq value assigned PER the key combination as such:

(IX) PackageType : smallint -- size of the pkg, used for storage location
(IX) InventoryYear : smallint -- YYYY of current date
     LastInventorySeq : int


...where an update for PackageType=1, InventoryYear=2008 would result in 11 being incremented +1 and updating the row's InventorySeq value = 12.

This approach would involve selecting the row by the (IX) values WITH ROW LOCK (?), capturing the LastInventorySeq into a variable, incrementing the variable and updating the row with the new value thereby releasing the lock and continuing with the remainder of my stored proc.

Please be reminded that I would not want a rollback condition to undo the update to the control table, as that would really mess things up. I am not concerned if there's a "skipped" inventory seq value for this project ... however, I do not anticipate a rollback ever occurring, given the simplistic nature of the overall proc.

Thanks, again, for everyone's continued suggestions.
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-11-21 : 21:27:14
I think you are getting a bit obsessed with this whole "Unique within ..." thing.
As MVJ says, an identity satisfies all your criteria and is quick and scalable unlike every other option proposed.
Maybe you need to explain why this would not do the job - it might clarify what you are trying to do a bit better.

Just a few things to consider though:
- Sequence numbers without gaps can only be generated by completely serialising all your transactions, AKA grinding your database to a halt.
- Sequence numbers are rarely something you want a human to see.
- It never matters that there are gaps (and you have acknowledged that thank god!)
- You can always generate a regular gap-free sequence when you query the data using row_number() over(PackageType, InventoryYear)
Go to Top of Page

PaulieD
Starting Member

9 Posts

Posted - 2008-11-21 : 22:32:55
quote:
Originally posted by LoztInSpace

I think you are getting a bit obsessed with this whole "Unique within ..." thing.



LOL!

I can't speak for everyone but I tend to receive additional work from my clients when I follow their requirements ... of course, considering there isn't a flaw in their logic. This project is no exception - they want a sequential number assigned per (PackageType AND InventoryYear).

Since you asked for a more detailed explanation:

1. A package is received; returned by the customer.
2. The package is inventoried; checked in by a receiving clerk who merely scans the package's barcode and chooses a Package Type (a code that identifies where the package will be physically stored for later retrieval, based on its size).
3. The front-end app that performs this task at the behest of the receiving clerk calls a Stored Proc to handle the actual task -- updates order status, affects A/R for taxation purposes, etc.
4. That very same SP needs to be enhanced whereby it will also assign an inventory number (yes, a boring ol' number).
5. The front-end app will display the generated inventory number to the clerk who will then take a marker (preferably permanent) and scribe the value on the package before routing it for safe-keeping.

The receiving clerk wants the assigned inventory number ("InventorySeq" or "LastInventorySeq") to be unique AND sequential WITHIN the scope of the chosen PackageType and the current (calendar) year.

In short, I need to come up with a solution that fits their requirements .. especially since their request does not contain an inherent flaw.

Thanks for your continued insight.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-11-22 : 17:03:57
The following code shows how to do what you want with a control table. You don’t have to worry about locking the table, since the update is a single transaction and the updated value is returned in the same statement.

You should be aware of the following things to watch out for that are part of the disadvantages of scalability and concurrency.
1. You should avoid running the update inside a transaction, since all users are accessing the same table and a transaction could block every user. In fact, you should include code to error out in case it is in a transaction to avoid locking.
2. It is a challenge to insert a large number of rows. If you need to do that, you have to run the inserts one at a time.

There are a plenty of other disadvantages that you will run into, so make sure you and your users are really prepared for dealing with the disadvantages of this approach compared to my suggestion of using an identity column.


print 'Create table [dbo].[PackageTypeInventoryYearSeq]'
if OBJECT_ID('[dbo].[PackageTypeInventoryYearSeq]','U') is not null
drop table [dbo].[PackageTypeInventoryYearSeq]

CREATE TABLE [dbo].[PackageTypeInventoryYearSeq](
[PackageType] [smallint] NOT NULL,
[InventoryYear] [smallint] NOT NULL,
[InventorySeq] [int] NOT NULL,
CONSTRAINT [PK_PackageTypeInventoryYearSeq] PRIMARY KEY CLUSTERED
([PackageType] ASC,[InventoryYear] ASC))
go
print '*** Initial load of [PackageTypeInventoryYearSeq]'
insert into [dbo].[PackageTypeInventoryYearSeq]
select
a.PackageType,
b.InventoryYear,
0
from
(
select 1 PackageType union all
select 2 union all
select 3 union all
select 4 union all
select 5
) a
cross join
(
select 2007 InventoryYear union all
select 2008 union all
select 2009 union all
select 2010 union all
select 2011
) b
order by
a.PackageType,
b.InventoryYear
go

--
declare @out table (NewInventorySeq int not null)

print '*** Update table [PackageTypeInventoryYearSeq] to get next value'
update [dbo].[PackageTypeInventoryYearSeq]
set
[InventorySeq] = [InventorySeq] +1
output
inserted.[InventorySeq]
into
@out
where
[PackageType] = 2 and
[InventoryYear] = 2008

print '*** Show updated row in table [PackageTypeInventoryYearSeq]'
select *
from
[dbo].[PackageTypeInventoryYearSeq]
where
[PackageType] = 2 and
[InventoryYear] = 2008


print '*** Show updated value to return to client'
select * from @out


Results:
Create table [dbo].[PackageTypeInventoryYearSeq]
*** Initial load of [PackageTypeInventoryYearSeq]

(25 row(s) affected)
*** Update table [PackageTypeInventoryYearSeq] to get next value

(1 row(s) affected)
*** Show updated row in table [PackageTypeInventoryYearSeq]
PackageType InventoryYear InventorySeq
----------- ------------- ------------
2 2008 1

(1 row(s) affected)

*** Show updated value to return to client
NewInventorySeq
---------------
1

(1 row(s) affected)


CODO ERGO SUM
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-11-23 : 08:38:15
Well I would disagree that their requirement does not have an inherent flaw. The requirement for it to be unique within other data is not required to identify something. The cost of having a unique number for a group of other data elements is that - and this is the really important bit you and your client have to understand- you have to ensure every other transaction on those tables is committed or rolled back before you can determine the number.
This is a) very degrading to your overall throughput for obvious reasons and b) quite difficult to do (because it's a bogus requirement) as I hope you are discovering as you read this thread.
The fact that MVJ has given you some code that you can't run in a transaction (or otherwise, so why would you use it - sorry MVJ) and will return errors when you hit simultaneous updates and the fact that nobody has been able to agree with your assertion that it is easy or a common thing should make you think that maybe there is a flaw here.
To me, the requirement appears to be "generate a unique number so I can write it on a box".
Go to Top of Page

PaulieD
Starting Member

9 Posts

Posted - 2008-11-23 : 16:25:32
Thanks for the constructive feedback. With thanks and respect to MVJ for his time and contribution, I am also confused as to where he was going - especially since his suggestion contained hard-coded values rather than adaptive logic for continued use into the future.

While I am not entirely against using an identity value to drive the LastInventorySeq value, it would not be useful to have an ever-growing sequence number serving this purpose.

I assume you are proposing this schema, in tandem with my requirements:

(IX) PackageType : smallint -- size of the pkg, used for storage location
(IX) InventoryYear : smallint -- YYYY of current date
     LastInventorySeq : int *IDENTITY*

That would result in the following data example, considering we started with a value of 10, seeded by 1 and only had 5 returns logged between 2007 and 2008:

PackageType : InventoryYear : InventorySeq
1 : 2007 : 10
1 : 2008 : 12
2 : 2007 : 11
2 : 2008 : 14
3 : 2008 : 13

By the way, for the above dataset, the value written on returned packages would be:

2007-1-10
2008-1-12
2007-2-11
2008-2-14
2008-3-13

Anticipating your inquiry, the format "2007-1-10" serves multiple purposes: "2007" identifies the year it was inventoried; more so for purging aged inventory, "1" dictates where the package should be stored and "10" is nothing more than the unique identifier that completes a unique key.

Again, I am not against that value being an Identity. However, five years from now, I am trying to avoid them having to write a value such as "2013-2-1341869". Yet, if that's the simplest and smartest approach, than I will sell the idea to them.

Considering we're now in sync, how would you accomplish this goal? One file ... or using a control table and recording the physical returns in another table? Please include schema and suggested T-SQL logic, if possible.

Thanks!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-23 : 17:30:09
quote:
Originally posted by PaulieD

For example:

PackageType : InventoryYear : InventorySeq
1 : 2007 : 172
1 : 2008 : 11
2 : 2007 : 69
2 : 2008 : 28
3 : 2008 : 5

...where an insert for PackageType=1, InventoryYear=2008 would result in 12 for the new row's InventorySeq value.

Why exactly 12? A value of 173 is exactly as unique, right?
And with an identity, you also can make sure no other packagetype nor year can have exact same inventoryseq.



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-11-23 : 17:37:10
quote:
Originally posted by PaulieD

Thanks for the constructive feedback. With thanks and respect to MVJ for his time and contribution, I am also confused as to where he was going - especially since his suggestion contained hard-coded values rather than adaptive logic for continued use into the future...



I was showing how to use the UPDATE to return the updated value in the OUTPUT clause without having to follow with a SELECT statement.

I hard-coded the values in the UPDATE statement for simplicity with the assumption that you would be able to understand and adapt this for your needs.





CODO ERGO SUM
Go to Top of Page

PaulieD
Starting Member

9 Posts

Posted - 2008-11-23 : 17:56:11
quote:
Originally posted by Michael Valentine Jones: I hard-coded the values in the UPDATE statement for simplicity with the assumption that you would be able to understand and adapt this for your needs.
Michael, taking a second (admittedly closer) inspection of your suggestion, I now see that the first portion was merely a method to populate a test dataset, to support the remaining two code suggestions; namely update and then a proofing fetch. Therefore, I apologize for initially misunderstanding your apparent brilliance. I also did not realize that it was possible to set an Output variable based on an inserted value during an Update -- that, by far, was the coolest part of your post.

For other readers, here's more info on Mike's suggestion (look at bullet 'C'): http://msdn.microsoft.com/en-us/library/ms188927.aspx
in which the MSDN example creates a table variable to store the values specified in the OUTPUT clause of the UPDATE statement.

Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-11-23 : 17:56:37
quote:
Originally posted by PaulieD
Anticipating your inquiry, the format "2007-1-10" serves multiple purposes: "2007" identifies the year it was inventoried; more so for purging aged inventory, "1" dictates where the package should be stored and "10" is nothing more than the unique identifier that completes a unique key.



I would just use the IDENTITY by itself as the PRIMARY KEY. The year 2007 and 1 for package type are really just attributes of the transaction, not part of the PK. This is especially important if someone makes an error and you need to correct the year or package type. There is never a need to update the IDENTITY value, so that is never an issue.

If they have a need to display the year and location, write it on the package, etc. there is no reason why they can't.

I think another way to look at this is to ask the question: What information does the InventorySeq hold? Really, it is nothing but a number to make the row unique, so it is just very inconvenient to use IDENTITY value.


CODO ERGO SUM
Go to Top of Page

PaulieD
Starting Member

9 Posts

Posted - 2008-11-23 : 18:12:14
quote:
Originally posted by Michael Valentine Jones: (They) are really just attributes of the transaction, not part of the PK
Michael, my use of the word "key" in that reply was not intended to infer the PK. In fact, I typically have a RecID (int) Identity column to serve as the PK, for the exact reason you mentioned - allow updates to all columns - assuming no Foreign Key constraints would conflict.

quote:
Originally posted by Michael Valentine Jones: What information does the InventorySeq hold? Really, it is nothing but a number to make the row unique, so it is just very inconvenient to use IDENTITY value.
I agree completely, except that I am thinking of the future where that number could potentially grow and become cumbersome to write. Nothing more behind that particular motive.

Go to Top of Page

PaulieD
Starting Member

9 Posts

Posted - 2008-11-23 : 18:15:37
quote:
Originally posted by Peso: And with an identity, you also can make sure no other packagetype nor year can have exact same inventoryseq.
I assume that you were not suggesting I could have a unique InventorySeq value PER packagetype and year. Rather, you are merely joining the other posters in their suggestion to employ the identity value for my purpose.

Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-11-23 : 18:42:21
If you can establish that you will never go back a year then you might be able to reset the identity or subtract some kind of milestone number to reduce the number of digits. Of course you have to be absolutely certain otherwise you will get a duplicate.
Go to Top of Page

PaulieD
Starting Member

9 Posts

Posted - 2008-11-23 : 20:23:07
I am likely going to use the method that Michael suggested (update with output returned) thereby retaining my original intent to manage a sequential number for the "InventorySeq" column per PackageType and InventoryYear (index). Moreover, I will not be using a "control table", since it's not required by this method.

My choice, for the most part, is based on the simplistic nature of the overall SP, as it will only manage one returned item at a time at the request of the front-end application. Additionally, it will allow the InventorySeq value to "reset" on its own, with each new calendar year and without programmer intervention. Moreover, having to scribe a short number on each returned package will reduce the possibility for human error, in comparison to writing an inevitably long number after the proposed identity value continued to grow.

I thank everyone for the valued time and feedback. I will continue to monitor this thread, should anyone post an afterthought.
Go to Top of Page
    Next Page

- Advertisement -