How to lock table while issuing reference number
-
Using SQL 2000 SP3
Table tblnumberingsystem holds reference numbers for different types of objects (e.g. receipts, transactions etc). It does not have any identity fields, and I don't see creatng them as an option. We use the following stored procedure to generate new numbers. How can I ensure that both the select and the update in the transaction are carried out before the next user's transaction starts i.e. if two people execute the same transaction simultaneously with the same parameters, how can I ensure they receive different numbers? I've had a look in BOL but either didn't see what I wanted or saw it and didn't understand it.
CREATE PROCEDURE up_GetAccountNumber
@TypeFlag as varchar(3),
@CostCentre as varchar(5),
@AccountNumber as int OUTPUT AS
SET NOCOUNT ON
BEGIN TRANSACTION
SET @AccountNumber=(SELECT nmb_latest FROM tblnumberingsystem WHERE nmb_type=@AccountType AND nmb_costcentre=@CostCentre)+1
UPDATE tblnumberingsystem SET nmb_latest=@AccountNumber WHERE nmb_type=@AccountType AND nmb_costcentre=@CostCentre
COMMIT
Thanks
--
Scott -
CREATE PROCEDURE up_GetAccountNumber
@TypeFlag as varchar(3),
@CostCentre as varchar(5),
@AccountNumber as int OUTPUT AS
begin
SET NOCOUNT ON
UPDATE tblnumberingsystem SET @AccountNumber = nmb_latest= nmb_latest +1 WHERE nmb_type=@AccountType AND nmb_costcentre=@CostCentre
end
* Noel -
That works great, thanks. Do you know of a topic in BOL or an online link which explains exactly how it works? I should be able to use this elsewhere, but would rather understand something fully than blindly copying and pasting.
--
Scott -
I do something like this with all tables to be allocated compound ID's listed in one table giving the "identity" number to allocate
Table controlling ID's is
CREATE TABLE [SYS_SEQGENERATOR] (
[TABLENAME] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[FIRMID] [int] NOT NULL ,
[CURRENTVAL] [int] NOT NULL ,
CONSTRAINT [PK_SYS_SEQGENERATOR] PRIMARY KEY CLUSTERED
(
[TABLENAME],
[FIRMID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
And the sproc to get next value is
CREATE PROCEDURE SYS_GETSEQVAL
@TABLENAME varchar(50), @FIRMID int
AS
/* Get a pseudo identity value for a particular table and firm */
DECLARE @CurVal int
BEGIN TRANSACTION
UPDATE SYS_SEQGENERATOR WITH (HOLDLOCK TABLOCKX) SET CURRENTVAL = CURRENTVAL + 1 WHERE FIRMID=@FIRMID AND TABLENAME=@TABLENAME
SELECT @CurVal=CURRENTVAL FROM SYS_SEQGENERATOR WHERE FIRMID=@FIRMID AND TABLENAME=@TABLENAME
COMMIT TRANSACTION
return(@CurVal)
GO
The HOLDLOCK hint makes sure the table is locked while the numbers are given
James Horsley
Workflow Consulting Limited
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply