database constraints and concurrent transactions


I am wondering how the dataphor's developers have managed to make constraints work so well.

For a constraint to play its role, its query should be run against a database that is build from :
The initial database plus
the user inserts/deletes/updates plus
All the modifications done by all the transactions that have commited after the user's transaction started (and of course before the constraints are evaluated).

The 2 first points does not cause any problem. But, unless I am wrong, the 3rd cannot always be insured.

Considering a RDBMS such as SQL-SERVER, there seems to exist no isolation level that guarantee that a transaction sees all commited changes done by others transaction. ReadCommited isolation level seems to do the job but often causes deadlocks.

This only solution I see (but I am not a SQLDBMS expert) is to run one transaction at a time.

How does dataphor handle that situation?

Thank you.


Jean-Christophe Mincke

Constraint Enforcement and Transactions

Hi Jean-Christophe,

I'm not sure I entirely understand the question, so forgive me if my answer is off base, but as far as Dataphor is concerned, the engine runs each transaction at the isolation level that most closely matches the Dataphor isolation level. It seems to me that the Isolated isolation level (Serializable in most SQL-DBMSs) will ensure that the third criteria you describe above is met. By default, Dataphor connections will run at Cursor-Stability (Repeatable-Read in most SQL-DBMSs), so you will need to specify Isolated as part of the SessionInfo if that is the behavior you want.

Bryn Rhodes
Database Consulting Group LLC