Running part of stored procedure outside of transaction?

[Former Member]

Hello,

we have a stored procedure which generates primary keys by base36 incrementing a field/row. This normaly works fine, but sometimes (during high load) we sometimes get duplicate primary keys.

This is related to the fact that we don't lock the counter field/row, because sometimes we have long running transactions which would block the whole system.

Out code currently looks like this:

FUNCTION "DBA"."getNewID"(in tableName char(20))
select value,IncrementProcedure into oValue,incProc from gd where keyname = tableName and RemoteName = current_publisher;
.....calculate new id based on value......
update gd set value = rNewID where keyname = tableName and RemoteName = current_publisher
return rNewID;
end;

This code is running in the transaction context of the application and somtimes we get a race condition when the getNewID is executed concurrently. Is there a way to prevent this from inside sql anywhere ? In java we would use synchronized on the function....

André

[VolkerBarth]

Is there a chance to use the builtin PK generators like DEFAULT AUTOINCREMENT, GET_IDENTITY() or SEQUENCEs?

Besides that, the following (unanswered) question asks for the same kind of race condition (in case that is of any help to you...):

[Breck_Carter]

Isn't synchronized in Java the same as blocking others and making them wait?

[Former Member]

Yes, a java synchronized blocks all others from entering the same procedure/function at the same time.

[Former Member]

@volker: - Sequences are a no go since we have systems running Sql Anywhere 9-11 - Default autoincrements (or global increments won't work, since the keys are 6 or 10 character length 0-9 and A-Z, the application is unfortunally looking for exact this lenght of the key, and a integer from 100000-999999 is not large enough, since we have a few tables with 2-3 mio rows - Get_identity has the same numeric-only problem

In new systems/tables we use global autoincrements, but this one is grown up since about 8 years now....

[VolkerBarth]

Just as an idea: AFAIK, DEFAULT AUTOINCREMENT can be used with BIGINT types. Therefore you might use a bigint column for the key generator column and could use these unique values to calculate smaller values in the desired char format (via some kind of mapping). - Besides that, Glenn's suggestion is obviously much more straight forward:)

[Former Member]

Yea, a bigint would be enough, but I don't realy look forward to unload/reload the largest db with currently ~45GB in space and having two sql remote replicas ;) So Glenn's suggestion will be the first step, we will then see if more is needed.

Thanks

[Former Member · Accepted solution]

If keyname is the primary key of the gd table, then you can use SELECT FOR UPDATE to cause an intent row lock to be acquired during the SELECT. The intent lock will prevent the scenario you are experiencing now, which is that two connections simultaneously execute the SELECT, generate the same key value, and then one of the connections performs a COMMIT - and then the other connection tries to use the same generated key.

However, while this will prevent duplicates (intent locks block other intent locks, but NOT read locks) it still has the behaviour that all insertions are effectively serialized via the updates to the gd table. This can be prevented, as per Volker's comment above, by using a SEQUENCE or an AUTOINCREMENT primary key. Alternatively, one could develop a key pool and manage the keys in the pool using a second connection (one implementation would be to use an EVENT) and a row-level update trigger.

[Former Member · Accepted solution]

I think I will look first at the select for update work arround.

I know that all solutions with a self-managed primary key are a potential bottleneck when high insert concurrency exists.

Do you have an idea if changing primary keys from CHAR(10) to integer will have a large positive effect on query execution ? (Or doe this not matter)