Singleton pattern in a stored procedure

Question

How can you implement the Singleton pattern in a SQL Server 2005/2008 stored procedure?

We want the stored procedure to return the next value from a table to a caller, and then update the value, so the next caller gets a different value ...

BUT there will be time when there are lots of callers!

AND we don't want blocking/time-out issues

PS. maybe singleton isn't the answer ... if not, how could you handle this?

If I understand you correctly you could use something like this `update T set ID += 1 output inserted.ID` — Mikael Eriksson, Dec 06 '13 at 07:37
Or if it was the previous value you wanted then `update T set ID += 1 output deleted.ID` — Mikael Eriksson, Dec 06 '13 at 07:38
@AlexanderTorstling ... nice idea, but sorry, it's a SQL Server 2005 and 2008 environment, and I believe SEQUENCE is a 2012 feature — SteveC, Dec 06 '13 at 07:42
@SteveC: If a sequence-like feature is what you seek, check out this question: http://stackoverflow.com/questions/282943/how-would-you-implement-sequences-in-microsoft-sql-server — Alexander Torstling, Dec 06 '13 at 07:46

score 1 · Answer 1 · edited Mar 24 '16 at 11:29

By definition SINGLETON IS A LOCKING Pattern.

Talking about databases, there are so many DB professionals that get afraid when you mention the word "Lock", but locks per se are not a problem, they are a fundamental mechanism for the Relational Databases.

You must learn how locks works, what kind of locks exists, and treat them with respect. Always work with short transactions, lock the minimum rows as you can, work with sets not individual rows. Locks become a problem when they are massive, and when they last too much, and of course when you build a DEADLOCK.

So, the golden rule, when you must change data into a transaction, first put an exclusive Lock (UPDATE), never a Shared Lock (SELECT), it means sometimes you have to start doing a fake LOCK as in :

BEGIN TRAN
   UPDATE table
     set col1 = col1
   Where Key = @Key
   .......
COMMIT TRAN

Prior to SQL Server 2012, when I needed a serial I've done it in two ways:

Create an IDENTITY column, so after inserting you can get the value with the built in function SCOPE_IDENTITY() there is also @@IDENTITY, but if someone create a trigger that inserts into another table with an identity column starts the nightmare.

CREATE TABLE [table]
(
   Id int IDENTITY(1,1) NOT NULL,
   col2 ....
   col3 ....
)

The second option is to add an serial column usually in the parent table or a table made for it plus a procedure (you can use client code) to get the serial:

--IF YOU CREATE A SERIAL HERE YOU'LL SPENT SOME SPACE,
--BUT IT WILL KEEP YOUR BLOCKINGS VERY LOW
CREATE TABLE Parent
(
  Id,
  ChildSerial  int  not null,
  col2 ...
  col3 ...
    CONSTRAINT PK_Parent PRIMARY KEY (Id)
)
GO

--NAMED CONSTRAINT Auto names are random (avoid them)
ALTER TABLE Parent
  ADD CONSTRAINT Parent_DF_ChildSerial DEFAULT(0) FOR ChildSerial;
GO

CREATE TABLE Child
(
   Id int not null
   col2..
   colN..
     --PLUS PRIMARY KEY... INDEXES, etc.
)

CREATE PROC GetChildId
(
  @PatentId       int
  @ChildSerial    int output  --To use the proc from another proc
)
As
Begin

   BEGIN TRAN
      --YOU START WITH A LOCK, SO YOU'LL NEVER GET A DEADLOCK
      --NOR A FAKE SERIAL (Two clients with the same number)
      UPDATE Parent
        SET ChildSerial = ChildSerial + 1
      WHERE Id = @PatentId

      If @@error != 0
          Begin
          SELECT @ChildSerial = -1
          SELECT @ChildSerial
          ROLLBACK TRAN
          RETURN
      End

      SELECT @ChildSerial = ChildSerial
      FROM Parent
      WHERE Id = @PatentId

   COMMIT TRAN

   SELECT @ChildSerial   --To Use the proc easily from a program

End
Go

Singleton pattern in a stored procedure

1 Answers1