2

I have an application for display token queues in hospitals/similar institutes. It issues department wise token numbers for the visitors. For this we have the below table which has the last issued token no.

HospId  Int Not Null
DeptId  Int Not Null
TokenId Int Not Null /* current token number */

While issuing the token I execute the following update statement 

Update <table> Set TokenId = (Select Max(TokenId) + 1 From <table>)
Where HospId = @HospId And DeptId = @DeptID

This is working fine, but when there are more concurrent users (more than 200 users) updating the token it gets hang. Is there a better way to update?

Shah
  • 106
  • 6
  • Have you ever had this thing issue two of the same tokens? I'd be suprised if it's never done that. A more reliable solution is to either use a table with an identity or a sequence. – Nick.Mc May 08 '17 at 12:50
  • @Nick.McDermaid `sequence` is introduced in sql server 2012. – SqlZim May 08 '17 at 12:52
  • My mistake I didn't read the tags.... and I actually don't remember when it was introduced anyway! – Nick.Mc May 08 '17 at 12:53
  • 1
    @Nick.McDermaid No worries. I actually wrote an answer using sequences before I reread the question. – SqlZim May 08 '17 at 12:55
  • Also the Identity approach will not give you a sequential number unique to Hospital and Department. But think carefully - do you _really_ need that? – Nick.Mc May 08 '17 at 12:55
  • Here's an example of using OUTPUT to capture unique values: http://stackoverflow.com/questions/42648/best-way-to-get-identity-of-inserted-row There are many examples online – Nick.Mc May 08 '17 at 12:56
  • @Nick.McDermaid: I cannot use identity because I'm not inserting into the table – Shah May 09 '17 at 09:39
  • I'm talking about changing the way you generate your id's altogether. The output is the same: a unique number. But you don't have locking problems. You wouldn't be using the same table. – Nick.Mc May 09 '17 at 11:11

1 Answers1

0

If you can't use auto incremental numbers, maybe having your token generation code in a stored procedure that uses SET TRANSACTION ISOLATION LEVEL SERIALIZABLE within would be a viable option.

It would slow things down a bit but should prevent concurrency issues.

The reason for the stored procedure would be that by disabling direct write rights to the table to your application users and allowing it only to the stored procedure via EXECUTE AS OWNER (msdn) you guarantee there will be no funny stuff going on and rogue code poking holes in your token generation.

Vedran
  • 10,369
  • 5
  • 50
  • 57