Transaction Concurrency Control and Locking (examples on Informix Dynamic Server). Objectives. Understand the need for Concurrency Control mechanisms. Understand locks and types of locks. Understand how locking affects performance Understand isolation levels.
Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.
(examples on Informix Dynamic Server)
Understand the need for Concurrency Control mechanisms.
Understand locks and types of locks.
Understand how locking affects performance
Understand isolation levels.
Understand the criteria to tune the locking:
the different concurrency controls.
the Lock granularity/mode on a table.
the Lock Wait Mode/Time of a session/transaction.
the Isolation Level of a session/transaction.
Identify Informix configuration parameters that affect locking.
Identify potential locking issues, including dirty reads, phantom reads, non-repeatable reads, and deadlocks.
Isolation (+ Consistency) => Concurrency Control
Multiple transactions may want to access and modify the same resources.
Whenever multiple processes share resources there is need to schedule the access.
Takes care that transactions access database items (database, table, page, row, index key) such that the meaningful results are produced.
Produces a schedule of database operations from transactions running concurrently so the order of operations for each particular transaction is preserved.
Assume that Transaction T1 has operations
O1 O2 O3
Assume that Transaction T2 has operations
P1 P2 P3
O1O2P1O3P2P3 is a schedule.
O1P1O3P2P3O2 is not a schedule
Order is not preserved operation O3 must be executed after O2 within T2
Schedule is serial if all operations from one transaction are completed prior to beginning of another transaction.
Each serial schedule is considered correct since one transaction is independent of the other transactions:
There is no overlapping of transactions.
Allow transactions to occur at the same time (concurrently).
Operations of one transaction can be executed before another transaction is committed.
Schedules where transactions occur concurrently are called non-serial or concurrent schedules.
A lock is implemented as a variable associated to a data item.
Can be placed explicitly by the program, or implicitly by the DBMS.
Lock describes status of an item with respect to operations that can be performed on the item.
Multiple users can read an item at the same time.
Only one user can read an item at the same time.
Promotable (Update) lock:
A lock can upgrade (from shared to exclusive) or downgrade (vice versa).
Placed at the table level, to indicate a cursor is working on the rows of the table.
Share lock (lock-S):
Share locks can be placed on objects that do not have an exclusive lock already placed on them.
Prevents others from updating the data.
But still, others can read the data (others can place S-locks on it).
More than one share lock can be placed on the same object at the same time.
Exclusive lock (lock-X):
Exclusive locks can only be placed on rows that do not have any other kind of lock (not even S-lock) on it.
Once an exclusive lock is placed on a row, no other locks (not even S-locks) can be placed on the same row anymore.
Prevents others from reading or updating the data.
Update lock (lock-U):
Used in Update Cursors.
Update locks are created by cursors that have the ‘for update’ extension specified and can only be placed on a row that doesn’t already have an exclusive or update lock on it.
The update lock is converted to an exclusive lock as soon as the row is actually updated.
Intent lock (lock-IX or IS):
Intent locks are automatically set by Informix.
If a row in a table is updated, an exclusive lock is placed on the row and an intent-exclusive lock is placed on the table.
This ensures that no other session could place a share or exclusive lock on the table as long as an individual row is locked exclusively.
If the item already has a lock of type…
Can I place a lock of type…?
The program controls the duration of a database lock:
A database lock is released when the database closes.
Depending on whether the database uses transactions, table lock durations vary:
If the database does not use transactions (no transaction log exists and you do not use a COMMIT WORK statement), an explicit table lock remains until it is removed by the execution of the UNLOCK TABLE statement.
The duration of table, row, and index locks depends on the SQL statements used and whether transactions are in use.
When you use transactions, the end of a transaction releases all table, row, page, and index locks:
When a transaction ends (commits, rollbacks), all locks are released.
When the different lock granularities are useful / optimal?
Useful for some administrative activities, such as imports and exports:
Ex: DATABASE database_name EXCLUSIVE
Useful and more efficient when an entire table or most of the tables rows are being updated
LOCK TABLE tab1 IN EXCLUSIVE MODE
LOCK TABLE tab2 IN SHARE MODE
To unlock: UNLOCK TABLE tab1;
Implicitly during operations like these (Completion of the statement (or end of the transaction) releases the lock):
CREATE INDEX (if not using ONLINE keyword)
DROP INDEX (if not using ONLINE keyword)
When the different lock granularities occur?
Provides the optimum in lock efficiency when rows are being accessed and modified in physical order.
If you want to move the lock mode of a table from ROW to PAGE:
ALTER TABLE tab1 LOCK MODE PAGE;
Default locking mode for Informix tables.
Default lock mode for all new tables can be set in Informix configuration: (ONCONFIG) file, with parameter DEF_TABLE_LOCKMODE. Ex:
Deliver the highest degree of concurrent access and are most useful for OLTP activity.
OLTP Tables should and must have lock mode ROW:
CREATE TABLE tab1 (col1...) LOCK MODE ROW;
ALTER TABLE tab1 LOCK MODE (ROW);
Is automatic in conjunction with row-level locking to ensure the same optimal level of concurrency during index updates.
Larger overhead to maintain locks
More Difficult to implement
Depends on the character of transactions.
Row and key locks generally provide the best performance overall when you update a relatively small number of rows because they increase concurrency:
However, the database server has some overhead in obtaining a lock.
If a typical transaction accesses a small number of records, use granularity on row (record) level.
If transactions frequently access the whole table (e.g., update all salaries, etc), set coarse granularity (on the page or the table level):
For massive updates on a table, lock the table in exclusive mode.
In SQL we may specify what properties a transaction or a session should satisfy:
What kind of data would like to read? Dirty, committed, last committed, etc.
Setting the Isolation Level of a session/transaction, we may prevent some (or all) the problems we saw here, to occur.
It is left on DBMS to ensure that the specified “level of isolation” is actually accomplished.
Dirty-read isolation makes it possible for your query to retrieve phantom rows.
Dirty-read isolation is the only isolation level available for non-logging databases.
Dirty-read isolation can be useful when:
The table is static (no updates, read-only tables).
100% is not as important as speed and freedom from contention.
You cannot wait for locks to be released.
To perform a committed read, the DB server attempts to acquire a shared lock on a row before trying to read it:
It does not place the lock without checking whether it can acquire the lock or not.
If it can, it is guaranteed that the row exists and is not being updated by another process while it is being read.
Remember, a shared lock cannot be acquired on a row that is locked exclusively, which is always the case when a row is being updated.
Committed reads can be useful for:
Reports that yield general information.
Not only can you look at committed rows, but you are assured the row will continue to exist while you are looking at it:
No other process (UPDATE or DELETE) can change that row while you are looking at it.
Once you move to the next row, the lock is released and the value can change.
You can use SELECT statements that uses an isolation level of CURSOR STABILITY for:
Reports yielding operational data.
You are assured the row will continue to exist not only while you are looking at it, but also when you reread it later within the same transaction.
Repeatable reads are useful when you must treat all rows read as a unit or you need to guarantee that a value does not change. For example:
Critical, aggregate arithmetic (as in account balancing).
Coordinated lookups from several tables (as in reservation systems).
Also known as Optimistic Locking, common in web-based applications
Typically, websites allow you to add items into your shopping-cart using the status of the item at the time you added it, even though later on, when you are checking out or updating it, it can alert you that the price or the availability status has changed.
Not available in ANSI databases.
SET ISOLATION TO COMMITTED READ LAST COMMITTED
Provides concurrency and throughput improvement over Committed Read.
Less protection / isolation of the data read
Do not wait for lock to be released (default)
If the database item is locked, it will immediately return an error code:
-244: Could not do a physical-order read to fetch the next row
107: ISAM error: record is locked
SET LOCK MODE TO NOT WAIT;
Wait forever for lock to be released:
A transaction can hang and deadlocks can occur, waiting on a resource (e.g. row, page) to be released.
SET LOCK MODE TO WAIT;
Wait n seconds for the lock to be released:
If the lock has not been released during that time, it will return an error saying the object is locked.
Ex: SET LOCK MODE TO WAIT 20;
SET ISOLATION TO DIRTY READ RETAIN UPDATE LOCKS;
SET ISOLATION TO COMMITTED READ RETAIN UPDATE LOCKS;
SET ISOLATION TO CURSOR STABILITY RETAIN UPDATE LOCKS;
It only affects SELECT...FOR UPDATE statements with dirty read, committed read and cursor stability isolation levels.
When the update lock is in place on a row during a FETCH of a SELECT... FOR UPDATE statement with one of the isolation levels above, it is not released at the subsequent FETCH or when the cursor is closed.
LOCKS parameter in ONCONFIG:
Specifies the initial size of the lock table that is allocated in resident memory, or the number of locks in this internal table.
The lock table holds an entry for each lock.
Max. configurable size of Informix lock table is:
Monitoring isolation levels the sessions use:
Use: onstat –g sql and/or onstat –g ses
To see details of a session of id sid: onstat –g sql sid
Monitoring the status of the user threads (waiting on locks?):
Use: onstat –u
Monitoring the locks being held and waited for:
Use: onstat -k
Monitoring the transactions and their status:
Use: onstat -x
IBM Informix Guide to SQL: Tutorial - Programming for a Multiuser Environment
IBM Informix Dynamic Server Administration Guide: Locking
Informix Dynamic Server locking, Part 1: Understand locking behavior and analyze locking conflicts in Informix
Informix DBA: Informix Performance Locking and Concurrency
(old but good) Informix Unleashed book – Ch 15: Managing Data with Locking