Lock question - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Lock question

Author

Topic

liffey
Yak Posting Veteran

58 Posts

Posted - 2007-05-02 : 11:19:20

This is for SQL 2000

I use a job number formatted as: YYMXNNNN

where YY = 2 digit year
M = a letter that denotes the month
X = a type of job
NNNN = sequential from 1 to 9999

I use a table to store these values. Each time a new job is created the NNNN is incremented by 1. Checks are included to test for change of month and change of year to increment these as necessary.

This is all currently done in the application and a time stamp is used to ensure that the update is not beaten by another user.

I am considering creating a stored procedure to read, calculate the next number and update the table all within a transaction.

The ideal to prevent possible duplication of job numbers would be to use an auto incrementing field but this will not work in this case as the NNNN resets to 1 on change of month (M) or year and month.

How can I ensure that no other user can access the row between the time it is read and rewritten? Is the row I read locked if I use transactions to surround the read, calculate, write (update)?

Also

DECLARE @mm char(2)
DECLARE @yy char(4)
DECLARE @nn char(5)
DECLARE @TimeStamp timestamp

select @mm = (SELECT JobMonth FROM [dbo].[jobNextNumber] where JobNumberType = @jobtype)
select @nn = (SELECT JobNumber FROM [dbo].[jobNextNumber] where JobNumberType = @jobtype)
select @yy = (SELECT JobYear FROM [dbo].[jobNextNumber] where JobNumberType = @jobtype)
select @TimeStamp = (SELECT timestamp FROM [dbo].[jobNextNumber] where JobNumberType = @jobtype)

Is this 4 hits on the db. Is there a better way of filling the variables @mm, @yy, @nn

-dw

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-05-02 : 11:54:58

Does each JobType have its own sequence of numbers (NNNN). Or are the numbers used "globally" across all JobTypes?

If the numbers are global, then I would probably create a Sequence table and with an IDENTITY column. Then you could just insert a DEFAULT VALUE and get the SCOPE_IDENTITY(). That should avoid most conflicts. Then, when you need to reset it, you could just TUNCATE the table and that'll clear it out and resent the Identity.

Also, instead of selecting the JobMonth/Year can you use GETDATE() or CURRENT_TIMESTAMP or just select a date from a job table once and then use date fucntions to get the values you want?

-Ryan

liffey
Yak Posting Veteran

58 Posts

Posted - 2007-05-02 : 12:04:39

quote:
Originally posted by Lamprey

Does each JobType have its own sequence of numbers (NNNN). Or are the numbers used "globally" across all JobTypes?
-Ryan

JobType 0 uses 1 to 49999
JobType 1 uses 50000 to 99999

Therefore I could not use identity; unless I use two tables. Now there is an idea!

quote:

Also, instead of selecting the JobMonth/Year can you use GETDATE() or CURRENT_TIMESTAMP or just select a date from a job table once and then use date fucntions to get the values you want?

-Ryan

JobMonth and year are stored in the db and are used by comparing to date functions in my application to detect when the month and/or year changes.

If I port to a stored proc I plan using GETDATE for the comparison.

-dw

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources