Locking Strategies
The default locking strategy varies across DBMSs and should be considered when accessing other DBMSs with Enterprise Access.
The default Ingres locking mechanism takes shared read locks-a setting of “serializable” for the system_isolation level-which means that once a select has been issued, repeated selects on the same data will show the same results until the session (transaction) commits. If another session attempts to write to the same table or tables, the writing session waits for the reading session to commit or roll back.
Conversely, if a session starts to write to a table and then another session attempts a read, the second session will “stall” until the writing session issues a commit or rollback. The default locking behavior is configurable in Ingres using CBF or VCBF for graphical user interfaces. (Ingres 6.4 users typically control default locking using ING_SET or an equivalent variable.) This locking behavior is the default to choose when starting a database session; the default locking mode may be overridden on a session-by-session basis (that is, within the application).
Other database systems have different default locking strategies. This has an impact when accessing other DBMSs using Enterprise Access. For example, compare the default locking strategies between Enterprise Access to Oracle (the Oracle gateway), and Ingres in the following sequences.
Assume a table that contains a single row and was previously created and populated as shown here:
drop table gateway_lock_test;
create table gateway_lock_test (col1 varchar(30));
insert into gateway_lock_test (col1) values ('row 1');
commit;
Sequence 1: Oracle runs with default Oracle locking setting
Session A:
select * from gateway_lock_test; /* Returns one row */
Session B:
insert into gateway_lock_test (col1) values ('row 2');
select * from gateway_lock_test; /* Returns two rows */
Session A:
select * from gateway_lock_test; /* Still returns one row */
Session B:
commit;
Session A:
select * from gateway_lock_test; /* Now returns two rows */
Sequence 2: Ingres runs with Ingres backend, and default Ingres 2.0 locking setting
Session A:
select * from gateway_lock_test; /* Returns one row */
Session B:
insert into gateway_lock_test (col1) values ('row 2'); /* This never completes and stalls until session A issues a "commit" (or "rollback") */
Different locking strategies (Ingres or Oracle in this example) produce different results. Enterprise Access does not impose any lock settings for database connections; therefore, applications accessing databases using Enterprise Access should not assume the default locking strategy is the same as for a default Ingres database.
Furthermore, for an application accessing an Ingres database, do not assume the default Ingres locking strategy is “serializable,” as the DBA of the Ingres installation may have altered the system_isolation level. Therefore, it is critical that an Ingres application take this into account when making transactions against a database (regardless of the type of DBMS). It is recommended that all user applications explicitly set the expected or required locking mode for the session before issuing transactions. (Preferably, issue lock settings immediately upon database connection.)
Note: Currently, OpenSQL does NOT specify any control over locking other than the usual commit and rollback statements with the option to enable (or disable) autocommit using the SET command. If the lock mode is set using Enterprise Access, it should be issued using the DIRECT EXECUTE IMMEDIATE SQL command enclosed within a string.