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;
  }