Sql Server - counter column that increments even with transactions rollback

Question

I need to manage an incremental counter (protocol number) for every year of my product life. The value must be unique inside each year, and I need that once the counter has been incremented it cannot be rolled back.

If I make a stored proc that creates its inner transaction, when my backend calls it inside its own transaction and fails, the counter returns to the previous value.

Is there a method to increment the counter even if all transactions fail, like the identity columns do?

Thanks, David

P.S. I cannot use identity columns, as I don't know how many counters I'll have to manage. And I cannot use sequences either, I must use a SQL2008 database :-(

Create a table with an identity column just for that? And I believe that you can't do what you want (not sure though). — Jorge Campos, Feb 28 '18 at 12:08
Please see my updated question: cannot use identity columns nor sequences. Anyway, thanks for answers! — David, Feb 28 '18 at 13:37
No matter what you do here you have an issue because you have potential concurrency problems. You are putting way too much emphasis on an incremental value. You say you are still on sql 2008, when are you going to upgrade. 2008 is out of support for a few years now. Time to upgrade!!! — Sean Lange, Feb 28 '18 at 14:17
Sequences would be perfect for you, but unsupported in MSSQL 2008. My only idea is a CLR function that accesses some external storage (e.g. a file), protected against concurrent accesses by a global mutex object. As a alternative to a file, that CLR function could internally connect to your own database and access a table in separate transaction context. Just a thought... — KarloX, Feb 28 '18 at 14:34
Could drop the incremental requirement and use a GUID generated with `newid()` — Brian, Feb 28 '18 at 16:10
The incremental requirement cannot be dropped, sorry. But there can be gaps in the sequence. The protocol number is used to physically archive documents with their folder and the protocol printed on the folder, so it cannot be a guid, it must be an "easy" number. — David, Feb 28 '18 at 17:11

score 0 · Answer 1 · answered Feb 28 '18 at 16:56

Here's a solution that makes use of a loopback linked server connection. That allows to update a table outside of the current transaction context:

use TEST; -- assume the database is named TEST

create table SeqNo (
  SeqName nvarchar(10) not null primary key, 
  LastValue int default 0
);
insert into SeqNo (SeqName) values ('A'),('B');

create table Protocol (
  id int not null primary key identity(1, 1), 
  SeqName nvarchar(10) not null, 
  SeqNo int not null, 
  SomeText nvarchar(100)
);

GO

EXEC master.dbo.sp_addlinkedserver @server = N'MyLoopback',
                                   @srvproduct = N'', 
                                   @datasrc = @@SERVERNAME, -- use own server
                                   @provider = N'SQLOLEDB',
                                   @catalog=N'TEST';

GO

EXEC master.dbo.sp_serveroption @server=N'MyLoopback', @optname=N'remote proc transaction promotion', @optvalue=N'false';
EXEC master.dbo.sp_serveroption @server=N'MyLoopback', @optname=N'rpc', @optvalue=N'true';
EXEC master.dbo.sp_serveroption @server=N'MyLoopback', @optname=N'rpc out', @optvalue=N'true';

GO

/* We did the preparation work, now do a test... */

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION; 

declare @SeqName nvarchar(10);
set @SeqName = N'A';
declare @NewSeqNo int;

exec (N'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION; 
declare @tmp table (NewValue int);
update SeqNo set LastValue = LastValue + 1 output inserted.LastValue into @tmp where SeqName = ?;
select top 1 ?=NewValue from @tmp;
COMMIT TRANSACTION;', @SeqName, @NewSeqNo output) at MyLoopback;

insert into Protocol (SeqName, SeqNo, SomeText) values (@SeqName, @NewSeqNo, N'Whatever you want');

ROLLBACK TRANSACTION; -- alternatively "COMMIT TRANSACTION" to have the row in Protocol persistent

select * from Protocol;
select * from SeqNo; -- became updated, even if rolled back (outer) transaction above

Very smart, but the master database is managed by the hosting provider, I do not have the necessary permissions to create a linked server — David, Feb 28 '18 at 17:13
Hm, in that case I see no solution, sorry. I hope this answer may be useful for other people... — KarloX, Feb 28 '18 at 19:17

Sql Server - counter column that increments even with transactions rollback

1 Answers1