Using Adaptive Server Anywhere 9.0.2.3668.

I have a system that has multiple connections to the
database that all update records in the database.

When a record is updated, a trigger is fired which then
retrieves a sequence number from a Sequence number table.
After retrieving the sequence number the Sequence Number
Table is updated and it increments to the next one. (Also
performs min/max checks on the sequence number).

The issue that I am running into is that every so often I
have two transactions that are picking up the same sequence
number.

I can see in the transaction log that the first connection
grabs the current value, and then increments it. In the
second connection I see that the same value was retrieved as
the first value, but the increment never occurs. I am
thinking that I do not see it in the log because when it
went to update, the value was already incremented by the
first connection.

What can I do to prevent a situation like this from
occuring? I am a bit nervous to use a lock of some sort
because these transactions are from a system that is
real-time and any kind of long delay could have really bad
results.

I am familiar with mutexing in code, but is there a way to
mutex this record somehow?

Thanks in advance.

We came across this with key pool implementations
sometimes. My trick there was to not fetch the value
first but do an update first ... and do that within a nested
transaction level (see savepoint/release savepoint).
A user defined function may be best for this.

Alternatively, one could utilize the get_identity( )
system function if an autoincrement is being used
for this.


hth

"Paul" wrote in message news:4b2915c4.34bb.1681692777 (AT) sybase (DOT) com...

Thanks for the response. I already have a function that is
being used. This is below:


CREATE FUNCTION "accusort"."fGetNextNSequenceID"(szHost
varchar(64))
RETURNS bigint
DETERMINISTIC
BEGIN


DECLARE "nReturnSequenceNumber" bigint;
DECLARE nReturnValue bigint;
DECLARE err_notfound EXCEPTION FOR SQLSTATE '02000';
DECLARE nContinue smallint;
DECLARE nSequenceNumber bigint;
DECLARE nMinSequenceNumber bigint;
DECLARE nMaxSequenceNumber bigint;

SELECT CurrentSequenceNumber,
MinSequenceNumber,
MaxSequenceNumber
into nSequenceNumber,
nMinSequenceNumber,
nMaxSequenceNumber
FROM accusort.HostInformation
WHERE HostName = szHost;

If SQLState = err_notfound Then
set nContinue = 0;
set nReturnValue = 0;
ELSE
set nContinue = 1;
set nReturnValue = 0;
end if;

IF nContinue = 1 THEN
set nReturnSequenceNumber = nSequenceNumber;

If nSequenceNumber >= nMaxSequenceNumber then
set nSequenceNumber = nMinSequenceNumber;
else
set nSequenceNumber = nSequenceNumber + 1;
end if;


update accusort.HostInformation
set CurrentSequenceNumber = nSequenceNumber
WHERE HostName = szHost;

END IF;

RETURN "nReturnSequenceNumber";

END;


My table structure is as follows:

CREATE TABLE "accusort"."HostInformation" (
"HostName" varchar(64) NOT NULL,
"CurrentState" varchar(64) NOT NULL,
"CurrentSequenceNumber" bigint NOT NULL,
"MinSequenceNumber" bigint NULL,
"MaxSequenceNumber" bigint NULL,
PRIMARY KEY ( "HostName" )
);


If I wanted to use the get_identity call, how do I need to
setup my table then so that the autoincrementing field would
only go as high as my max value, and then reset to my min?

Thanks in advance.

Given that is your code, simply adding more of the original values
to the where clause might suffice ... specifically ....

Instead of just doing this
. . .
DECLARE nSequenceNumber bigint;
. . .
SELECT CurrentSequenceNumber, . . .
into nSequenceNumber, . . .
. . .
set nSequenceNumber = nSequenceNumber + 1;
. . .
update accusort.HostInformation
set CurrentSequenceNumber = nSequenceNumber
WHERE HostName = szHost;

you could try something like this instead
. . .
DECLARE nSequenceNumber bigint;
DECLARE nOrigSequenceNumber bigint;
. . .
SELECT CurrentSequenceNumber, . . .
into nOrigSequenceNumber, . .
--<<=== !important to note!
. . .
set nSequenceNumber = nOrigSequenceNumber + 1;
. . .
update accusort.HostInformation
set CurrentSequenceNumber = nSequenceNumber
WHERE HostName = szHost
AND CurrentSequenceNumber = nOrigSequenceNumber --<<===
!!critical point here!!;

and catch any collisions at that point.


By looping around that construct refetching and reupdating
until it succeeds could be a solution.


Altenatively you could reverse the updates and select and
build the equivalent of a mutex around them as follows

Create function . . .
. . .
Begin
. . . .

SAVEPOINT GetNextsequenceID;
update accusort.HostInformation
set CurrentSequenceNumber = CurrentSequenceNumber +1
WHERE HostName = szHost;

select CurrentSequenceNumber INTO nSequenceNumber;

RELEASE SAVEPOINT GetNextsequenceID;
. . .

return nSequenceNumber;
end


In this way if two callers are trying to update the same row,
the second one should block until you've fetched the
new/next value. The other way you are exposed to dirty
reads, race conditions and parallel execution.

Autoincrement is a pretty basic facility in the product. You do have
functions like get_identity( ) and the system procedure sa_reset_identity( )
do their job but adding business requirements on top of that may not
be a trivial as you may be expecting.

"Paul" wrote in message news:4b29394c.3879.1681692777 (AT) sybase (DOT) com...

Perhaps you should also think about setting the Function as

From the Help File
NOT DETERMINISTIC clause A function specified as NOT DETERMINISTIC
is re-evaluated each time it is called in a query. The results of
functions not specified in this manner may be cached for better
performance, and re-used each time the function is called with the
same parameters during query evaluation.

Functions that have side effects such as modifying the underlying data
should be declared as NOT DETERMINISTIC. For example, a function that
generates primary key values and is used in an INSERT ... SELECT
statement should be declared NOT DETERMINISTIC:



Kind Regards
Thomas

There are several problems here. Certainly one is that this is a
DETERMINISTIC function, and it should not be. With DETERMINISTIC
functions the server is free to cache the result of previous
invocations, which in your case has the consequence of the function
returning the same key that was generated by previous calls. Note that
this can occur only if the function is called multiple times within the
same statement.

The second problem is that you (likely) have a race condition in the
code, but it's difficult to assess exactly what the exposure is because
you don't stipulate what isolation level you are using. The problem is
that the first SELECT:

will not acquire a lock on accusort.HostInformation if you are running
at an isolation level less than 2. Consequently it is possible for two
simultaneous transactions to select the same set of 3 sequence number
values before either function gets to the following UPDATE statement to
update the values.

To lock the row on a SELECT, you can use the table hint WITH( XLOCK )
which will acquire an WRITE row lock on that row, and hence prevent
other connections from either reading or updating that row until the
first transaction COMMITs.

A different and perhaps more effective way of doing this is to use an
UPDATE statement to update the values immediately. With SQL Anywhere,
one can save the existing values by using a SET clause in the update
statement to copy the existing row values into variables. This approach
is documented in a whitepaper on capacity planning that I authored in 2008:

http://www.sybase.com/detail?id=1056535

Finally, your approach to key generation will not scale; generating keys
in this way serializes every INSERT because of the lock contention on
the key generation table. For an explanation, see

http://iablog.sybase.com/paulley/200...r-scalability/

As Nick suggested, a key pool would work better because the contention
can be minimized as long as the SQL statements that select and update
the values (rows) in the key pool are crafted carefully.

Glenn


Paul wrote:
--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at
http://www.sybase.com/products/datab...chnicalsupport