This chapter describes how the locking system works. Ingres is a concurrent database system, which means it allows multiple users to access the same data at the same time. The purpose of this chapter is to provide you with the knowledge you need to maximize concurrent performance and analyze associated problems.
The chapter describes modes and levels of locks, how long locks are held, locking examples, locking parameters and the set lockmode statement, how to avoid deadlock situations, how to monitor locks, and how to improve concurrency.
|
|
|
In any database management system with multiple users, there is a trade-off between concurrency and consistency. Ideally, you want all users to be able to access any data at virtually any time (concurrency) but you must ensure that changes to the database are done in an orderly sequence that maintains the underlying structure of the data (consistency).
The task of the locking system is to manage access to resources shared by user databases, tables, and pages to guarantee the consistency of the shared data. Various types of locks are used to ensure that the database does not become inconsistent through concurrent accesses.
|
|
|
The locking system works with the Ingres DBMS Server to coordinate access to databases.
The system administrator initially configures the locking system during installation by setting parameters. The locking parameters are installation-wide. They can be changed after installation only by the system administrator.
On UNIX systems, shared memory and semaphores are used as resources during lock control. The shared memory and semaphores used by your installation are configured in the operating system when the UNIX kernel is configured.
For more information, see the System Administrator Guide.
|
|
|
The locking system grants two types of locks:
Are held for the life of a transaction. The logical lock is held until you commit, roll back, or abort the transaction.
A transaction is a group of statements processed as a single database action and can consist of one or more statements.
Can be used and released in a transaction. The locking system uses them to synchronize access to resources.
|
|
|
A lock has a mode that determines its power—for example, whether it prevents other users from reading, or only from changing, the data.
The six lock modes are as follows:
Exclusive locks or write locks. Only one transaction can hold an exclusive lock on a resource at any given time. A user of this lock is called a writer.
Update locks. Only one transaction can hold an update lock on a resource at any given time. This lock mode is used for update cursors. Update lock protocols are used by Ingres to increase concurrency and reduce deadlocks, because update locks can be converted to shared locks for rows and pages that are not updated.
Shared locks or read locks. Multiple transactions can hold shared locks on the same resource at the same time. No transaction can update a resource with a shared lock. A user of this lock is called a reader.
Intended exclusive and intended shared locks. Whenever the locking system grants an exclusive (X) or shared (S) lock on a page in a table, it grants an intended exclusive (IX) or intended shared (IS) lock on the table. An intended lock on a table indicates finer locking granularity (that pages in the table are being accessed). An IX lock indicates that pages are being updated, while IS indicates that pages are being read.
Shared intended exclusive locks. These locks specify a resource as "shared with intent to update." They can be considered as combining the functionality of S (shared) locks and IX (intended exclusive) locks. SIX locks are used in table locking strategies, where possible, to minimize the extent of exclusive locking required. The Ingres DBMS Server's buffer manager uses these locks to modify pages in its cache.
Null locks. These are locks that do not block any action but preserve the number in the value block of the locks or preserve data structures for further use.
|
|
|
Locks can be of several levels. The level of a lock refers to the scope of the resource on which the lock is requested or used, for example, whether the lock affects:
The levels of locks subject to user control are row, page and table. Queries and commands use other lock levels that affect concurrency, such as database and table control locks.
Lock levels are as follows:
Manages access to the row. Use row-level locking in situations where page locking can cause unnecessary contention or where increased concurrency is desired.
Row-level locking is not supported for tables that have a page size of 2 KB. Maxlocks is ignored with row-level locking (it only refers to page-level locks), but the number of row locks cannot exceed the maximum number of locks allowed per transaction, as specified by the system administrator when the locking system was configured. If it does, the row locks are escalated to a table-level lock. For more information, see Escalation of Locks.
Manages access to the data page, except by readers with the lockmode set to readlock = nolock. For more information, see Readlock = Nolock Option.
Manages all access to a table, except by readers with the lockmode set to readlock = nolock. For more information, see Readlock = Nolock Option.
Affects the ability of all users to connect to that database. A user blocked by an exclusive database lock is not able to connect to the database and receives an error message indicating that an exclusive lock is held.
Manages a table while its schema is changed or loaded. This lock is always a physical lock.
For example, during the operation of data management utilities (create/drop table, create/drop index, create table as, modify and modify to relocate—or their equivalent operations in VDBA), an exclusive table control lock is used. This combination of mode and level of lock insures that no transaction can read a table while its schema is changed or loaded, even though the lockmode of the user is set to readlock = nolock. For more information, see Readlock = Nolock Option. Conversely, readers can block data management utilities during schema read operations.
When row locking, provides phantom protection for serializable users and serializes duplicate checking for unique indexes.
Note: We recommend that you be aware of what resources you lock during application development, database maintenance, and ad hoc queries.
For details on the set lockmode operation and user-controlled locking, see User-Controlled Locking. For details on the set lockmode statement syntax, see the SQL Reference Guide.