|
|
|
User-controlled locking is available through the set lockmode option of the set statement. This option provides the following types of locking parameters:
Important! You cannot issue the set lockmode statement in a transaction. You can issue it as the first statement in a session or after a commit statement.
For details on the syntax for the set lockmode statement, see the SQL Reference Guide.
|
|
|
There are several ways to specify the set lockmode statement:
set lockmode session where readlock = nolock;
For example, to specify readlock = nolock for your sessions with the set lockmode option using ING_SET, issue the following commands at the operating system prompt:
Windows:
set ING_SET="set lockmode session where readlock = nolock"
UNIX:
C shell:
setenv ING_SET "set lockmode session where readlock = nolock"
Bourne shell:
ING_SET = "set lockmode session where readlock = nolock"
export ING_SET
VMS:
define ing_set
"set lockmode session where readlock = nolock"
The set statements pointed to by the environment variables or logicals are executed whenever a user connects to the server. The environment variables or logicals can be set installation-wide or locally in each user's environment. For further details on setting these environment variables or logicals, see the System Administrator Guide.
|
|
|
With the set lockmode statement, you can:
set lockmode on emp where readlock = nolock;
set lockmode session where readlock = nolock;
If multiple set lockmode statements are issued for the same session or table, the most recent statement is the one in effect. Setting a locking parameter on a specific table has precedence over the session setting. Any lockmode settings issued during a session end when that session ends.
|
|
|
There are several situations where the page-level locking default is not appropriate:
Deadlock occurs when multiple transactions are waiting for each other to release locks, and none of them can complete. For a discussion on deadlock, see Deadlock.
If page locking causes unnecessary contention, row-level locking can be used.
|
|
|
To specify table-level locking, use the following statement:
set lockmode session where level = table;
To specify row-level locking, use the following statement:
set lockmode session where level = row;
|
|
|
By default, the locking system escalates to a table-level lock after locking 50 pages in a table during a query.
Note: Lock escalation can lead to deadlock.
By changing the value for maxlocks to a number greater than 50, you can reset the number of locks that are requested before escalation occurs.
Increasing this value requires more locking system resources, so the installation configuration for the maximum number of locks must be raised; but this can provide better concurrency in a table with unavoidable overflow chains.
|
|
|
The following statement changes the number of pages in the EMP table that can be locked during a transaction from 50 to 70:
set lockmode on emp where maxlocks = 70;
With the new maxlocks value, the locking system escalates to a table-level lock only after more than twenty pages have been locked in table EMP during a query.
|
|
|
By default, the locking system waits for a lock indefinitely. (The default is "0," that is, no timeout.) For example, if User1 is running a report and User2 issues an insert statement for the table used for the report, the insert appears to "hang" while waiting for a lock. User2's transaction waits for a lock on the table until User1's report has completed, no matter how long that takes.
If you are not certain how long users in your database wait for locks, you need to limit the period of time (expressed in seconds) a user waits for a lock. This can be done using the timeout option of the set lockmode statement.
If a lock is not used in the amount of time specified, the statement is rolled back (not the entire transaction) and an error is returned. This error must be trapped and handled in embedded SQL and 4GL programs.
To immediately return control to the application when a lock request is made that cannot be granted without incurring a wait, use timeout=nowait on the set lockmode statement.
|
|
|
To limit to thirty seconds the time that a lock request remains pending, issue the following statement:
set lockmode session where timeout = 30;
To immediately return control to the application when a lock request is made that cannot be granted without incurring a wait, issue the following statement:
set lockmode session where timeout = nowait
|
|
|
If you embed a set lockmode with timeout in an application, timeout must be carefully handled by the application. There are two cases, depending on whether cursors are used in the embedded application:
We recommend that the timeout error handler check on the transaction status so it can tell which case was used. This can be done with an inquire_sql statement that retrieves the transaction state. For example, in the following statement xstat has a value of 1 if the transaction is still open:
exec sql inquire_sql (:xstat = transaction);
For a detailed description of the inquire_sql statement, see the SQL Reference Guide.
|
|
|
The following program example, written in ESQL/C and using the Forms Runtime System, checks for timeout and retries the transaction.
The program assumes an interface using a form to enter the department name, manager name, and a list of employees. The program inserts a new row into the department table to reflect the new department and updates the employee table with the new department name. An ESQL error handler checks for timeout. If timeout is detected, the user is asked whether to try the operation again.
/* Global variable used by main and by error handler */
int timeout;
main()
{
int myerror();
exec sql begin declare section;
char deptname[25];
char mgrname[25];
char empname[25];
char response[2];
exec sql end declare section;
. . .
exec sql set lockmode session where timeout = 15;
exec sql set_ingres(errorhandler=myerror);
. . .
/* Assume this activate block starts a new transaction */
exec frs activate menuitem 'addemp';
exec frs begin;
while (1)
{
timeout=0;
exec frs getform empform (:deptname=dept, :mgrname=mgr);
exec sql insert into dept (dname, mgr)
values (:deptname, :mgrname);
if (!timeout)
{
exec frs unloadtable empform emptbl (:empname=name);
exec frs begin;
exec sql update emp set dept = :deptname
where ename = :empname;
if (timeout)
exec frs endloop;
/* Terminate unloadtable */
exec frs end;
}
if (!timeout)
{
exec sql commit;
break;
}
else
{
exec sql rollback;
exec frs prompt ('Timeout occurred. Try again? (Y/N)',
:response);
if (*response == 'N')
break;
}
}
exec frs end;
. . .
}
int
myerror()
{
#define TIMEOUT 4702
exec sql begin declare section;
int locerr;
exec sql end declare section;
exec sql inquire_sql (:locerr = dbmserror);
if (locerr == TIMEOUT)
timeout = 1;
}
|
|
|
Pages locked for reading are normally locked with a shared lock. A shared lock on a page does not prevent multiple users from reading that data concurrently.
However, a user trying to change data on the locked page must wait for all shared locks to be released, because changing data requires exclusive locks.
This can be a problem if one user is running a long report that accesses a table with a shared lock. No users can make changes to the locked table data until the report is complete.
|
|
|
Setting the lockmode to readlock = nolock on a table accessed by a user running a long report allows others users to modify the table data while the report is running. Using readlock = nolock does not affect any query that updates, deletes, or inserts rows in a table.
Note: If readlock is set to nolock, and rows are changed while a report is being run on the table, the report is not a consistent snapshot of the table. Before using this strategy, consider the importance of the consistency and accuracy of the data.
A table control lock is used to ensure that no reader of any type (including when readlock = nolock is set) can look at a table when:
Whereas shared locks prevent other users from obtaining write locks and slow down their performance, setting readlock = nolock can improve concurrent performance and reduce the possibility of deadlocks.
|
|
|
To set readlock to nolock, issue the following statement:
set lockmode session where readlock = nolock;
|
|
|
Setting readlock to nolock is beneficial when:
|
|
|
Setting readlock to nolock is undesirable when:
|
|
|
A locking option that is useful in special circumstances is setting readlock to an exclusive lock.
Here is an example where controlling the shared lock locking level is necessary. User1 submits a multiple query transaction that retrieves data into a table field that the user is allowed to change before writing changes back into the table. At the same time, User2 submits a multiple query transaction to retrieve the same set of data into his or her table field, makes changes to the data, and writes the changes back to the table.
Eventually, the two users deadlock. Each is waiting for the other to finish and release the shared lock, so that each one can get an exclusive lock to make changes.
If the retrievals and changes had not been done with a multiple query transaction, no deadlock has occurred, because the shared locks are released before the requests for exclusive locks are made. But the exclusive lock transaction is necessary to prevent data from changing between the times you read the data and write to it.
It is preferable to exclusively lock the data when reading it into the table field, so that no other user can also retrieve the same set of data until the first user is finished. This can be achieved by setting exclusive readlock.
If it is likely that User1 holds these locks for a long time after retrieving into the table field and before committing changes, set timeout. For this reason, changing data inside a multiple query transaction is discouraged.
|
|
|
To set readlock to exclusive, using the following statement:
set lockmode session where readlock = exclusive;
|
|
|
Isolation levels allow users to specify an appropriate compromise between consistency and concurrency. This feature makes it possible to increase concurrency when the absolute consistency and accuracy of the data is not essential.
Ingres supports four isolation levels defined by the ANSI/ISO SQL92 standard:
The highest degree of isolation is called "serializable" because the concurrent execution of serializable transactions is equivalent to a serial execution of the transactions. Serializable execution is the default behavior of Ingres transactions because it offers the highest degree of protection to the application programmer. This highest degree of isolation, however, is the lowest degree of concurrency.
At lower degrees of isolation, more transactions can run concurrently, but some inconsistencies can occur.
|
|
|
The ANSI/ISO specifies three inconsistencies that can occur during the execution of concurrent transactions:
|
|
|
The following table shows how the ANSI standard defines which inconsistencies are possible (Yes) and impossible (No) for a given isolation level:
Isolation Level |
Dirty Read |
Non-Repeatable Read |
Phantom Rows |
|---|---|---|---|
Read Uncommitted |
Yes |
Yes |
Yes |
Read Committed |
No |
Yes |
Yes |
Repeatable Read |
No |
No |
Yes |
Serializable |
No |
No |
No |
For programmers who are aware of possible inconsistencies, lower degrees of isolation can dramatically improve throughput.
The most commonly cited example of this is a cursor-based program that scans through a large table, examining many rows, but updating only a few rows. Under normal serializable execution, this transaction takes share locks on all rows or pages that it reads—typically, it takes a shared lock on the entire table—thus locking out all update activity on the table until the transaction commits or aborts.
|
|
|
The Read Uncommited (RU) isolation level provides greatly increased read and write concurrency, but it suffers from the "dirty read" anomaly. Greater concurrency is achieved because the RU transaction does not acquire locks on data being read and other transactions can immediately read or/and modify the same rows. RU is ideal for applications where the reading of uncommitted data is not a major concern.
|
|
|
The Read Committed (RC) isolation level is well suited to allowing increased concurrency that is more controlled than at the RU level. RC transactions do not perform dirty reads but rather hold a lock on data while reading the data. For "cursored" queries, a lock is held on the current data item (page or row) pointed to by the cursor. The lock is automatically released when the cursor is positioned to the next item or closed. However, if any data on the current item of the cursor is changed, the lock must be held until the transaction commits. Such locking strategy is called cursor stability, and it defines an isolation level slightly stronger than "classical" RC.
The reason for cursor stability at the RC isolation level is to prevent cursor lost updates that are possible if locks are released immediately after data is read. The problem occurs when a transaction T1 running at the "classical" RC isolation level reads a data item; transaction T2 updates the data item and commits; T1 updates the data based on its earlier read value and also commits. T2's update is lost! Because of cursor stability, this problem does not exist in Ingres at the RC and higher isolation levels. At the same time, the RC mode does not guarantee that a transaction sees the same data if it repeats the initial read.
Cursor stability assumes that whenever the user is accessing a row with a cursor, this row is locked. However, if the user issues a complex cursor declaration that involves a join, and the results of the join are placed into a temporary buffer to be sorted before being updated, the assumption can be wrong. The problem exists because, in this case, the fetch statement returns rows to the user, not from the base table, but from the temporary buffer. When the user attempts to update the "current" row of the cursor, the server locates the proper row of the base table by its TID taken from the temporary buffer. The user expects a lock to be held on the base table row until the row has been processed, but at the RC isolation level, the lock is released when the row is placed into the temporary buffer. Therefore, the row to be updated no longer exists or no longer meets the criteria in the where clause. To prevent this problem, the server automatically upgrades the isolation level from "RC" to "RR" when the query is initiated.
|
|
|
In Repeatable Read (RR) isolation mode, locks are automatically released from data opened for reading but never read. With this option, if the application process returns to the same page and reads the same row again, the data cannot have changed. At the same time, repeatable read does not prevent concurrent inserts: if the same select statement is issued twice (in the same transaction), "phantom rows"can occur.
|
|
|
The Serializable isolation mode requires that a selected set of data not change until transaction commit. The page locking protocols prevent phantoms because the page locks cover the pages that hold the phantom. Simple row-level locking can provide repeatable read, but preventing phantoms in the serializable mode requires extra locks. These locks include data page locks for the ISAM and heap tables, value locks for the hash table, and leaf page locks for the B-tree table.
An isolation level is automatically increased from RC and RR to serializable for any operation on system catalogs and during the checking of integrity constraints or the execution of actions associated with referential constraints. This is necessary to ensure data integrity. However, if an integrity constraint is implemented by a user-defined rule, it is the user's responsibility to provide the appropriate isolation level.