portable sequence

[Drazen Gemic]

Hi !

I need to have a portable global sequence generator that works
regardless of database engine used. For example, Postgres have
sequence, mssql doesn't, but I need sequence for both. I tried to
create one, uzing SERIALIZABLE transactions, but under the increased
load it started to generate deadlocks.

The sequence table, 'uniseq' in this example, contains just one row
with one column ('next_id'), and that column is a sequence value, a
globaly unique number (BIGINT).

If the table is empty, new row is inserted, with sequence number = 1.
Otherwise sequence number is increased and stored.

What should I do to avoid deadlocks ?

Here is the code:

public static Long getUniqId(AdbWrapper dbx)
{
boolean flag=true;
dbx.startTran("SERIALIZABLE"); // transaction start
Long num=(Long) dbx.getField("select next_id from uniseq");
if(num == null)
{
num=new Long(1);
flag=(dbx.update("insert into uniseq values (1)") > 0);
}
else
{
num=new Long(1+num.longValue());
flag=(dbx.update("update uniseq set next_id="+num) > 0);
}
dbx.endTran(flag); // transaction end
//
return num;
}

 

[Arne Vajhøj]

I need to have a portable global sequence generator that works
regardless of database engine used. For example, Postgres have
sequence, mssql doesn't, but I need sequence for both. I tried to
create one, uzing SERIALIZABLE transactions, but under the increased
load it started to generate deadlocks.

The sequence table, 'uniseq' in this example, contains just one row
with one column ('next_id'), and that column is a sequence value, a
globaly unique number (BIGINT).

If the table is empty, new row is inserted, with sequence number = 1.
Otherwise sequence number is increased and stored.

What should I do to avoid deadlocks ?

Here is the code:

public static Long getUniqId(AdbWrapper dbx)
{
boolean flag=true;
dbx.startTran("SERIALIZABLE"); // transaction start
Long num=(Long) dbx.getField("select next_id from uniseq");
if(num == null)
{
num=new Long(1);
flag=(dbx.update("insert into uniseq values (1)") > 0);
}
else
{
num=new Long(1+num.longValue());
flag=(dbx.update("update uniseq set next_id="+num) > 0);
}
dbx.endTran(flag); // transaction end
//
return num;
}

With a high transaction isolation level you need to be prepared to
retry in case of deadlocks/timeouts.

If you switch to Scott Ambler high-low approach, then:
- performance will be much better
- deadlocks/timeouts will me rare
(but you can get holes in your sequence though)

Arne

 

[Lew]

With a high transaction isolation level you need to be prepared to
retry in case of deadlocks/timeouts.

If you switch to Scott Ambler high-low approach, then:
- performance will be much better
- deadlocks/timeouts will me rare
(but you can get holes in your sequence though)

I prefer to use the built-in sequence mechanism, and just translate to the
appropriate dialect if I need to port to a new RDBMS.

 

[Drazen Gemic]

I prefer to use the built-in sequence mechanism, and just translate to the
appropriate dialect if I need to port to a new RDBMS.

If there is one. MS SQL 2000 does not have it.

I am thinking about asking my clients to standardize on Postgres.
Majority of them don't have true DBA anyway, and there are problems
because
of that

DG

 

[Lew]

I am thinking about asking my clients to standardize on Postgres.
Majority of them don't have true DBA anyway, and there are problems
because of that

Those problems will not go away by using Postgres.

You can't cure lack of a DBA by switching DBMSes.

 

[Drazen Gemic]

Those problems will not go away by using Postgres.

You can't cure lack of a DBA by switching DBMSes.

Yes, but I can support Postgres.

DG

 

[Lew]

Yes, but I can support Postgres.

A key fact. In other words, you have a PG DBA. That makes all the difference.

PG is a great product.

 

[Arne Vajhøj]

I prefer to use the built-in sequence mechanism, and just translate to
the appropriate dialect if I need to port to a new RDBMS.

You will be more portable and get better performance by going the
other route.

You will also need to write some code and have to live with holes
in the sequence.

Arne

 

[Drazen Gemic]

A key fact. In other words, you have a PG DBA. That makes all the difference.

Exactly.

PG is a great product.

Exactly, again.

DG

 

[Drazen Gemic]

You will be more portable and get better performance by going the

other route.

You will also need to write some code and have to live with holes
in the sequence.

I haven't been able to find an article or example you were referring
to.


What do you mean by holes ? Do you mean increment greater than 1 or
possibility
to get duplicate ID ?

DG

 

[Arne Vajhøj]

I haven't been able to find an article or example you were referring
to.
http://fox.wikis.com/wc.dll?Wiki~HighLowKeyGeneration~SoftwareEng
http://www.theserverside.com/patterns/thread.tss?thread_id=4228
http://www.agiledata.org/essays/keys.html
etc.

What do you mean by holes ? Do you mean increment greater than 1 or
possibility
to get duplicate ID ?

Increments larger than 1.

Arne

 

[Lew]

I haven't been able to find an article or example you were referring
to.

Ten minutes or so of searching for "Scott Ambler database sequence" and
following links through Mr. Ambler's site found me
<http://www.agiledata.org/essays/keys.html#SurrogateKeyImplementation>
along with a ton of other useful information.

 

[Drazen Gemic]

I've taken a look, it is elegant, but I don't think I am going to
use it, because there is something more simple. I know how many
keys I need per session, so I coud request a continuous block of 10 or
20 keys
in single operation.

That will significantly decrease the load, and I can still check for
deadlocks.

ID is used to match response to requests sent to another, remote
application. Looking at the logs I suspect that load is generated
when impatient users repeatedly click while waiting for the response
from remote host, so I am going to add Thread.sleep() call to the
operation.

DG