0

I have almost 10 token machines where customers are getting token numbers from table Token. I am using a stored procedure for accessing this table and updating it

Id    Name       TokenFrom   TokenTo   LastUsedToken
----------------------------------------------------
1     Token         551        999         562

I have notices that during rush hours a lot of customers are getting duplicate numbers. Maybe this problem is showing up because 10 customers are getting tokens at the same time/hour/second.

Here is issuedToken table

Id  PosId   Barcode        Date                      Status  Number isTaken 
1   2       6282019214317  2016-10-20 09:41:45.020    1      366        1
2   2       6282019215918  2016-10-20 09:42:15.020    1         367     1
3   2       6282019225016  2016-10-20 09:42:45.020    1         368     1
4   3       6282019230812  2016-10-20 09:42:55.020    1         369     1

Even sometimes same number is coming on two cashier machines also. I am getting and updating Next Token Number on POS using this Update statement

UPDATE POS 
SET tNo = (SELECT TOP 1 NUMBER 
           FROM Tickets 
           WHERE STATUS = 1 
             AND isTaken = 1 
             AND PosId = (SELECT CGROUP 
                          FROM POS 
                          WHERE NAME='ABC')) 
WHERE NAME = 'ABC'

I have 3-3 POS in one group that's why selecting cGroup and in table it's PosId.

I have asked question related to this question before in this question, where someone help me to write a stored procedure for accessing Token Number easily.

But still I am having duplication issue. Can anyone tell me what the best way to avoid duplication ?

What is Best Approach for Auto Increament

Improve this question
13
  • @RyanVincent, customers are entering 10 digits National ID in token system and getting token numbers Commented Oct 21, 2016 at 0:21
  • 3
    Is a token just an incrementing number? (please explain). Use a sequence or an identity (insert a record then use scope_identity). Any other convoluted MAX or manual locking method will just give you problems. Commented Oct 21, 2016 at 0:34
  • @RyanVincent, by the way i am generating a 13 digit unique barcode as well when issuing ticket Commented Oct 21, 2016 at 0:35
  • @Nick.McDermaid, yes it's incrementing number only. nothing else Commented Oct 21, 2016 at 0:36
  • 2
    I suggest you use an identity or sequence but first I must convince that it's a bad idea to have a special range of ticket number for special purposes. Why do ladies need to be 1-50? what happens if you get more than 50 ladies in a day. Anyway did you link to your other question because that is the way you implemented it? That'll definitely give you duplicate tokens Commented Oct 21, 2016 at 0:38

3 Answers 3

Reset to default
2

This assumes you have SQL Server 2012 (please clarify)

Not a complete answer but I can expand if you wish.

First create a sequence (just run this once):

create sequence CustomerCare 
    as integer
    start with 51
    increment by 1
    minvalue 51
    maxvalue 350
    cycle;

now get the next sequence from it (run this as often as you like):

select next value for CustomerCare

This method can't hand out the same number to two different requests so you won't get duplicates. It will automatically wrap around when it gets to 350. You can create and use sequences for your other groupings. Much simpler than the other solution and 100% reliable.

Again I need to advise against creating magic number ranges for specific groups.

Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

I have Sql Server 2008 R2
So even 20 Token Machines are requesting number at the same time it will not be duplicate ??
I am using Sql Server 2008 R2 Express Edition. When i am trying to run this query. here is error. Unknown object type 'sequence' used in a CREATE, DROP, or ALTER statement.
sequences only work on SQL Server 2012. I'll update your post and think of another solution
Yeah, tell me something that can work on SQL Server 2008 R2
|
1

Here is something that works in SQL 2008 but does not take into account groupings, does not reset, and has a different formula for barcode

This is the token issued table. Inserting a record in here 'reserves' the token number:

CREATE TABLE [dbo].[issuedToken2](
    [Token] [int] IDENTITY(1,1) NOT NULL,
    [Barcode]  AS (((6820000000.)+[Token])*(100)+[PosID]),
    [GenerationDate] [smalldatetime] NOT NULL,
    [PosID] [int] NOT NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[issuedToken2] 
    ADD  CONSTRAINT [DF_issuedToken_GenerationDate]  
    DEFAULT (getdate()) FOR [GenerationDate]
GO

This is a stored procedure that you can use to get a token number. You can have 100 systems calling this simultaneously and they'll all get a different number:

CREATE PROC [dbo].[pGetToken]
@PosID INT
AS
BEGIN
SET NOCOUNT ON

insert into issuedToken2 (PosID) 
VALUES(@PosID)

RETURN scope_identity()
END
GO

This is how you use it all: call the stored proc with a posid (in this example 7) to reserve the token number, then use it to get the barcode:

DECLARE @Token INT

EXEC @Token = pGetToken 7

SELECT @Token, [Barcode]
FROM issuedToken2
WHERE Token=@Token

Basically this works by using an identity - an incrementing number. I know your existing system doesn't work like this but you haven't explained why it needs to.

Improve this answer

Comments

0

I got solution by changing my Stored Procedure that is accessing Next Number. Now logic is to lock Token table and getting next number. Now i am getting only unique numbers.

Thanks everyone for your kind responses.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.