GetNextGenerator thread-safe?

Is the GetNextGenerator method for making surrogate key values supposed to be usable in multi-user scenarios? In my database, when two different users are inserting lots of tuples into the same relvar, which has a key with a GetNextGenerator default, key constraint violations are fairly common. It seems the users are both getting the same value from GetNextGenerator.

Re: GetNextGenerator thread-safe?

On second thought, I suppose this is the expected behaviour given that Dataphor uses optimistic concurrency control instead of locking..?

But how to create a workaround? I want the ability for multiple users to insert lots of tuples into the same relvar concurrently. I could include the user ID as part of a composite key, but that kinda defeats the purpose of using an auto-increment surrogate...

GetNextGenerator

Hi John,

The GetNextGenerator function is definitely designed to be thread-safe. The Frontend is designed to allow this behavior to work by issuing the GetNextGenerator call on it's own process in a cursor stability or higher transaction, so even though the read cursors are running at browse isolation, the various update and proposable calls all run at the cursor stability or higher isolation by default. Are you using default isolation settings for your sources? Also, what database is the application using for storage. It may be that a different backend has different isolation behavior, the GetNextGenerator operators were tested mostly against an MSSQL server.

Bryn Rhodes
Database Consulting Group LLC

Re: GetNextGenerator

I'm using Oracle, and as I'm not doing anything about isolation levels, I assume I'm using the default setting.

The problem occurs when I use C# ScriptActions to loop through text files, for each line calling a D4 operator (using Host.Session.Execute(...)) that inserts a tuple into a relvar with a GetNextGenerator key default. When two of these run simultaneously, they often get the same GetNextGenerator value.

Jon

RE: GetNextGenerator

If you wrap the inserts in a transaction, does the behavior change? What about with specific isolation level:

BeginTransaction('Isolated');
try
...
CommitTransaction();
except
RollbackTransaction();
raise;
end;

Bryn Rhodes
Database Consulting Group LLC