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;

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....


asked 20 Dec '11, 10:54

ASchild's gravatar image

accept rate: 14%

edited 20 Dec '11, 16:42

Volker%20Barth's gravatar image

Volker Barth

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...):

(20 Dec '11, 11:09) Volker Barth
Comment Text Removed

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

(20 Dec '11, 13:24) Breck Carter

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

(20 Dec '11, 16:13) ASchild

@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....

(20 Dec '11, 16:19) ASchild
Replies hidden

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:)

(20 Dec '11, 16:41) Volker Barth

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.


(20 Dec '11, 17:37) ASchild
showing 4 of 6 show all flat view

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.

permanent link

answered 20 Dec '11, 13:53

Glenn%20Paulley's gravatar image

Glenn Paulley
accept rate: 43%

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)

(20 Dec '11, 16:28) ASchild
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 20 Dec '11, 10:54

question was seen: 2,585 times

last updated: 20 Dec '11, 17:37