0

Using Sql Server I need to have concurrent transactions update a counter, when they commit - but they must not block each other from updating the counter.

Let's say the first started transaction takes a while, while the second started finishes faster. Both must increase the counter, but the second transaction must not wait on the first.

The best way I can think of, is if it possible to update the counter eventually, in a queued manner. No need to wait for the update, if I'm just sure it will be updated at some point after - and only if - the transaction has committed (the nature of the counter allows for some eventual consistency).

But if this cannot be done, I seek a way to increment a counter on a single deterministicly selected row, but only take a lock in the smallest amount of time when the update is committed? The transaction will not use the the value for anything but the update, the applied function is always x = x + 1, no matter what the existing value is. And a rollback is the opposite.

Can anything like this be done with Sql Server?

1
  • Is it a single counter or multiple?
    – Zikato
    Sep 7, 2022 at 8:15

2 Answers 2

2

This can be done in several ways, depending on your version of SQL Server, and how you want to use/store the current value of the counter.

From SQL Server 2012 onwards, you can create a SEQUENCE for each counter. A single increment is achieved with NEXT VALUE FOR. More than one increment is handled using sp_sequence_get_range. To avoid losing values on a server restart, specify the NOCACHE option for the sequence.

Prior to that, people sometimes used a separate table per counter with an identity column. Properly controlled, the current value of the identity (retrievable from metadata) gives the current value of the counter. Less efficiently, a new row can be inserted and rows counted to derive the current counter value.

Neither of those solutions make it easy to handle rollbacks easily though.

Adding a row to a table and counting them (like a banking transaction record) can handle rollbacks easily enough (by deleting the row), but counting may not scale and concurrency needs to be handled correctly.

It is also possible to create your own sequence table to hold multiple counters. Care is needed over the implementation, and either a loopback linked server with the remote proc transaction promotion option set to off (2008 and later) or a CLR procedure is needed. A READCOMMITTEDLOCK hint is recommended when updating the shared state table to avoid problems under snapshot isolation. See my article Sequence Tables for details.

But if you're only updating the counter right at the end of the outermost transaction, none of this may be necessary. The lock will only be held for a very short period of time. A READCOMMITTEDLOCK hint may be needed on the update as previously noted.

Without the hint, you might read an old version of the row. It's a defensive addition really because the simple update in my demo already takes an update lock under RCSI and SI raises an error if it detects overlapping writes.

0
-3

There is no need to have any explicit transaction or a higher isoaltion level to get a counter value without any concurrency problem if you do it into one single SQL command...

Le me talk about an example I give in my Transct SQL courses...

CREATE TABLE T_COUNTER_CTR
(CTR_NAME   VARCHAR(128) NOT NULL PRIMARY KEY,
 CTR_VALUE  BIGINT NOT NULL DEFAULT 1);
GO

CREATE PROCEDURE P_GET_NEW_COUNTER_VALUE 
   @CTR_NAME VARCHAR(128),
   @CTR_VALUE BIGINT OUTPUT
AS
UPDATE T_COUNTER_CTR
SET    CTR_VALUE = CTR_VALUE + 1,
       @CTR_VALUE = CTR_VALUE
WHERE  CTR_NAME = @CTR_NAME;
GO

As you see UPDATE as two functions :

  • incrementing the counter value
  • reading the previous valu and assigning it to the output parameter CTR_VALUE

To use these counter, you must write a Transact SQL script like :

DECLARE @CTR_VALUE BIGINT;
EXEC P_GET_NEW_COUNTER_VALUE 'oneTable', @CTR_VALUE OUTPUT;
SELECT @CTR_VALUE;

When this code is integrated into a more complex explicit transaction, the T_COUNTER_CTR table will be blocked until the transaction is finalized. But there is a workaround, which is to use an Oracle-style "autonomous" transaction using a linked server...

In this case, create a linked server on the present server and point the execution of this procedure from the linked server... and you're done!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.