SAS/ACCESS Software for Relational Databases: Reference

Controlling Locks

Locks are completely under the control of CA-OpenIngres. The interface view engine, ACCESS procedure, and DBLOAD procedure all obtain locks of varying types on CA-OpenIngres tables and views.

Read Only Locks

When the interface view engine reads data from a CA-OpenIngres table or view, an SQL SELECT statement is issued to place a shared lock on the table. (A shared lock enables more than one user to access the data.) This lock is active for the entire execution of the procedure, regardless of the number of passes through the data the procedure requires. For example, the PRINT procedure places a shared lock on the table, and the lock remains active until all the specified data have been written to the Output window (that is, displayed).

UPDATE Locks

When you use the DATA step or certain procedures (such as FSEDIT or SQL) to update CA-OpenIngres data, an exclusive lock is placed on the CA-OpenIngres data. (An exclusive lock enables only a single user to access the data.) Except in the case of the DBLOAD procedure, these locks remain active only for the duration of a specific update and not for the entire procedure.

For example, in the FSEDIT procedure, if you update, delete, or add an observation, the interface starts an exclusive lock. That lock remains active until you complete your update, delete, or add.

Note: At every update, it is possible that a particular update process (for example, a PROC FSEDIT DATA=... statement) is not executed immediately. This indicates that another user has that CA-OpenIngres table locked. Your process remains in a "wait" state until the lock is released. If the "wait" is too long and times out, an error message is written to the SAS log. [cautionend]

When a table is to be loaded, the DBLOAD procedure issues a CREATE table statement, which causes an exclusive lock to be placed on the table. This lock is active until a commit is issued. (If the table already exists, an error message is written to the SAS log and the new table is not created.)

When you use a PROC DBLOAD SQL statement or the Query Entry window to submit SQL statements to CA-OpenIngres, locks are obtained in the same way as if you had issued the SQL command from the CA-OpenIngres Interactive SQL utility (called the CA-OpenIngres terminal monitor).

The following table shows examples of the locks CA-OpenIngres issues for some of the SAS/FSP interactive windowing procedures.

CA-OpenIngres Locks for Interactive SAS/FSP Procedures
Procedure Lock Level Lock

FSEDIT Table IX (intended exclusive)

Page lock(s) on pages in table X (exclusive or write)

FSBROWSE Table IS (intended shared)

Page lock(s) on pages in table S (shared or read)

FSVIEW

for browsing

Table IS (intended shared)

Page lock(s) on pages in table S (shared or read)

for editing

Table IX (intended exclusive)

Page lock(s) on pages in table X (exclusive or write)

***CA-OpenIngres Locks for Interactive SAS/FSP Procedures***
Procedure	Lock Level	Lock
FSEDIT	Table	IX (intended exclusive)
Page lock(s) on pages in table	X (exclusive or write)
FSBROWSE	Table	IS (intended shared)
Page lock(s) on pages in table	S (shared or read)
FSVIEW
for browsing	Table	IS (intended shared)
Page lock(s) on pages in table	S (shared or read)
for editing	Table	IX (intended exclusive)
	Page lock(s) on pages in table	X (exclusive or write)

To prevent users from being locked out of the data, you should encourage SAS/FSP software users to browse CA-OpenIngres data if they do not intend to change the data. You might also want to restrict who can use these procedures to access CA-OpenIngres data. For more information about locks, refer to your CA-OpenIngres database administrator's guide.

Chapter Contents
Previous
Next
Top of Page