CODEWITHSUNDEEP
sqldb2
the.unknowing
the.unknowing

Reputation: 117

How to create a concurrency-safe and gap-free counter in Db2

I understand that identity columns and sequences cannot guarantee a gap-free counter as generated numbers are lost during a rollback. So I'm trying to maintain my own counter.
Is the following sufficient to avoid that any other thread or system (e.g. data sharing) can potentially interfere with this update/select sequence:

SELECT counter FROM FINAL TABLE ( UPDATE cnt_table SET counter=counter+1 WHERE counter_id=? )

Or is there a better way to create such a counter ?

Formerly, I thought a sequence of

SELECT counter FROM cnt_table WHERE counter_id=? FOR UPDATE OF counter WITH RS
UPDATE cnt_table SET counter=counter+1 WHERE couner_id=?

is safely incrementing the counter while avoiding that concurrent transactions are even able to query the counter while the first transaction is not finished. Then I read that the SELECT FROM FINAL TABLE combination with UPDATE in a single statement is a better way. So will it also create appropriate locks so that concurrent transactions will wait ?

Note: I'm using dynamic SQL inside a stored procedure. The code needs to work on Db2 LUW and Db2 on Z. Distributed unit-of-work support is not required.

Upvotes: 0

Views: 539

Answers (2)

Rexford2
Rexford2

Reputation: 11

This answers the request for concurrency and gap-free unless rollback, which I don't think will ever be necessary assuming the row in the table holding the counter exists. In this example, XASYS6 is the table, COMPANY_NUMBER_ID is the inique key column and NEXT_AVAIL_SOLD_TO_CUSTOMER_NO is the counter.

This is Db2 for i SQL PL, but it should be 100% compatible.

IN @PARMORIG_COMPANY_NUMBER_ID DECIMAL(3,0),       
OUT @NEXTAVAILABLESOLDTOCUSTID DECIMAL(10,0)

DECLARE @NEXTAVAILWORKVAR10 DECIMAL ( 10 , 0 ) ; 
  
SELECT NEXT_AVAIL_SOLD_TO_CUSTOMER_NO INTO @NEXTAVAILWORKVAR10 
FROM XASYS6 WHERE ( 
( @PARMORIG_COMPANY_NUMBER_ID = COMPANY_NUMBER_ID ) ) 
WITH RS ; 
  
UPDATE XASYS6 SET 
    NEXT_AVAIL_SOLD_TO_CUSTOMER_NO = @NEXTAVAILWORKVAR10 + 1 
WHERE ( 
( @PARMORIG_COMPANY_NUMBER_ID = COMPANY_NUMBER_ID ) ) ; 
  
SET @NEXTAVAILABLESOLDTOCUSTID = @NEXTAVAILWORKVAR10 ;

Upvotes: 1

Clockwork-Muse
Clockwork-Muse

Reputation: 13096

(I've previously posted an answer for SQL Server with some additional bits, which should be translatable)

Eh, it's not going to matter too much which version you choose, because the primary guarantor of the system is going to be the isolation level of the transaction, not whether you have one or two statements. In some ways, I wouldn't be surprised if the underlying mechanism was the same for both. And you're going to want to be explicit about your transactions, here, to make it obvious what's going on. (Note that you'll need at least Read Stability if you've pre-populated the ids, probably Repeatable Read if you haven't)

Keep in mind that, in most cases, getting the answer back from the SELECT isn't the end - you're usually inserting that value in at least one table, or logging it to send it somewhere (eg, to a receipt-mailing program - although failure in this case is more problematic), any of which might fail. In those cases, you most likely need to ROLLBACK.

Personally, I think I'd use the change-table-reference, just because it makes it a little cleaner.

Upvotes: 0

Related Questions