Raffle application - how to do an unique select

myibuki · New User Joined: 14 Sep 2007 Posts: 2 Location: brazil

Hi Fellows,

I got the following problem.

Our application in cobol will run in CICS. It makes a raffle, getting
a field from a record in a DB2 database and making a little calculation.

Basically it does the following :

1. selects an "access counter" from a field in a DB2 table
2. adds +1 to the "access counter" obtained before
3. updates this updated value in the "access counter"

This is done for each person who access the application.

This application will have some peak times of access when thousands of people will be using the same program.

Our doubt is if for example from 10.000 people per second suppose 10 people get the same number 29 from the select operation - this would be bad.

Would there be any way to make the application to always select exactly 1 unit for each person accessing the application ? Some people say we should find a way to lock the record in the DB2 but this would not avoid the select to get the same number 29 for the 10 people - it would
only avoid concurrent updates.

Would anybody help me about this?

Thanks in advance.

UmeySan · Posted: Wed Oct 31, 2007 1:46 pm

Hi !

I think, best would be to do your calculation on a matchless, unique content of a field. I would use a TimeStamp. It's unique.

Regards, UmeySan

enrico-sorichetti · Posted: Wed Oct 31, 2007 1:53 pm

TG Murphy · Posted: Wed Oct 31, 2007 7:28 pm

Rework your logic so that it does it like this:

1. Update table SET counter = counter + 1. This will lock the table.
2. Select from table the row that you just updated.

Update will lock the table and ensure only 1 process gets 29..

However, these types of counter schemes can lead to performance problems. They can lead to lock-wait bottlenecks. Where I work, we have several processes with chronic performance problems caused by this approach.

Of course, we also use the same technique in many transactions where it works just fine. So it all depends. Depends on how long your transactions execute for? How many of them execute? Do they all chase the same rows or pages? Are there really 10,000 hits per second? Consider using ROW level locking.

Umeysan has a good suggestion; consider using timestamps.

One problem with timestamps is that the key values generated cluster together in the same range and can cause hotspots. For that reason, we often use a "scambled" timestamp. Basically - the digits are reversed and this causes the key values to be evenly distributed throughout the range.

Of course if you need to ORDER BY timestamp, then a "scrambled" timestamp will address this requirement.

TG Murphy · Posted: Wed Oct 31, 2007 7:30 pm

Sorry mistake... my last words should have been...

...will NOT address this requirement.

I don't know how to edit posts yet...

myibuki · New User Joined: 14 Sep 2007 Posts: 2 Location: brazil

Hi fellows,

Thanks for the replies, we will be using a mixed solution we got from the written above. Until now it will be a cursor for select for update of, like the following :

DECLARE XXXX CURSOR
FOR SELECT FIELD_P FROM TABLE_Y
WHERE FIELD_M = NNN
FOR UPDATE OF FIELD_P.

UPDATE FIELD_P FROM TABLE_Y
WHERE CURRENT OF XXXX.

Seems that it will work ....