Transcript Informix Concurrency and Locking

Transaction Concurrency Control and Locking
(examples on Informix Dynamic Server)
15 June 2011
© 2011 IBM Corporation
Objectives





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.
2
© 2011 IBM Corporation
Transactions – Review
 A transaction is a program (sequence of statements) that takes
a database from one consistent state to another.
 Transactions have the ACID properties:
– A – Atomicity
• The operation sequence is either executed completely or not at all.
– C – Consistency
• The operation sequence takes the database from any consistent state to
another consistent state (correct, integrity).
– I – Isolation
• Intermediate states of transactions are not visible to other transactions
(equivalence to each user feeling they are alone using the database – as in
single user mode).
– D – Durability
• Completed transactions are not lost due to hardware or software failures.
3
© 2011 IBM Corporation
Transactions – Isolation
 Isolation means that:
– Multiple transactions running at the same time do not impact each other’s
execution.
– Each user has the impression that he/she has exclusive access for the entire
transaction:
• All other transactions that happen at the same time should appear either as before or
after it.
• Like in a “Serial” schedule of transactions.
– Isolation level defines how deep transactions isolate from one another:
•
•
•
•
•
•
Dirty read / Read Uncommitted
Last committed read (Optimistic)
Committed read
Cursor stability
Serializable
Repeatable read
– If DBMS provides concurrency control support for transactions,
users/programmers do not need to worry that there are other transactions
running at the same time or not.
4
© 2011 IBM Corporation
Need for Concurrency Control
 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.
 Concurrency control:
– 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.
5
© 2011 IBM Corporation
Transaction Schedule – By Example
 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
6
© 2011 IBM Corporation
Serial Schedule
 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.
7
© 2011 IBM Corporation
Serial Schedule – Examples and Main Problem
 In the example on the right:
– Transactions T1 and T2 update totally different items of the database
(X,Y).
– Hence, T1 and T2 could have been executed concurrently (“in
parallel”).
 Serial schedules are always correct but do not use computer
resources on optimal way (for concurrency and performance).
8
© 2011 IBM Corporation
How To Improve Efficiency? Non-serial schedules
 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.
9
© 2011 IBM Corporation
Non-Serial Schedule – Example and New Problems
 If operations are not “meaningfully” ordered, we can get
unexpected results.
 Typical problems with schedules:
– Dirty read.
– Non-repeatable read.
– Phantom read.
10
© 2011 IBM Corporation
Dirty Read – Problem and Example
 A Dirty Read occurs because transaction T2 sees the uncommitted results of
transaction T1:
– Transaction T1 reads an item and updates it.
– Transaction T2 reads updated item.
– Transaction T1 might abort in the future (and its update would be annulled)
– In meantime, transaction T2 proceeds with the item that now has incorrect /
uncommitted value.
– Expected (good) behavior if the transactions were serialized: Once T1 is
aborted, T2 will still use the old (valid, non-updated) value of the item.
11
© 2011 IBM Corporation
Non-Repeatable Read – Problem and Example
 A Non-repeatable Read occurs if transaction T1 retrieves a different result
from the each read:
– Transaction T1 reads an item.
– Transaction T2 reads and updates the same item.
– Transaction T1 reads the same item again, but now it has a new, modified
value.
– Expected (good) behavior if the transactions were serialized: If a transaction
only reads (and does not modify) the item, each time the item is read, the same
value will be obtained.
12
© 2011 IBM Corporation
Phantom Read – Problem and Example
 A Phantom Read occurs if transaction T1 obtains a different result from each
Select for the same criteria:
– Transaction T1 executes search on certain criteria and retrieve m items from a
table.
– Transaction T2 inserts another item that would match the search criteria.
– Transaction T1 again executes search and now retrieves m+1 items from the
table.
– Expected (good) behavior if the transactions were serialized: The first and the
second search within the same transaction will give the same result.
13
© 2011 IBM Corporation
Introducing Locking
 Locking is very important in a multi-user DBMS.
 Locking allows one user to work with a data item without another
user changing the data item's value.
 Locking is necessary for maintaining data integrity while concurrent
users access database information.
14
© 2011 IBM Corporation
Locks
 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.
 Lock types
– Shared locks:
– Multiple users can read an item at the same time.
– Exclusive locks:
– 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).
– Intent lock:
– Placed at the table level, to indicate a cursor is working on the rows of the
table.
15
© 2011 IBM Corporation
Lock types (1)
 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 Slocks) can be placed on the same row anymore.
– Prevents others from reading or updating the data.
16
© 2011 IBM Corporation
Lock types (2)
 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.
17
© 2011 IBM Corporation
Lock Compatibility Matrix
If the item already has a lock of type…
Can I place a lock
of type…?
18
© 2011 IBM Corporation
Duration of a Lock
 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.
19
© 2011 IBM Corporation
Informix Lock Granularity (lock scopes) (1)
 With Informix, you can apply locks to:
– Entire databases
– Entire tables
– Disk pages
– Single rows, or
– Index-key values
 In general, the larger the scope of a lock, the more
concurrency is reduced, but the simpler programming
becomes.
20
© 2011 IBM Corporation
Informix Lock Granularity (lock scopes) (2)
 When the different lock granularities are useful / optimal?
– Database-level locks
• Useful for some administrative activities, such as imports and exports:
– Ex: DATABASE database_name EXCLUSIVE
– Table-level locks
• Useful and more efficient when an entire table or most of the tables rows are
being updated
–
–
–
–
21
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):
> ALTER FRAGMENT
> ALTER INDEX
> ALTER TABLE
> CREATE INDEX (if not using ONLINE keyword)
> DROP INDEX (if not using ONLINE keyword)
> RENAME COLUMN
> RENAME TABLE
© 2011 IBM Corporation
Informix Lock Granularity (lock scopes) (3)
 When the different lock granularities occur?
– Page locking
• 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:
– DEF_TABLE_LOCKMODE
– Row locks
ROW
• 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);
– Key locking
• Is automatic in conjunction with row-level locking to ensure the same optimal
level of concurrency during index updates.
22
© 2011 IBM Corporation
Informix Lock Granularity (lock scopes) (4)
 What is the type of data object (item) that is locked (secured)?
Page
Table
Database
23
More Difficult to implement
More Concurrency
Less Deadlocks
Row
Larger overhead to maintain locks
Index key
© 2011 IBM Corporation
Which Granularity Level is Optimal?
 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.
24
© 2011 IBM Corporation
Which Granularity Level is Optimal?
 For massive updates in many tables or the whole database,
lock the database in exclusive mode.
 Informix’s default lock granularity (lock mode) on tables is
Page:
– ALTER TABLE statement can change the locking mode from PAGE to
ROW
• ALTER TABLE table_name LOCK MODE (ROW);
– New default can be set with ONCONFIG parameter
DEF_TABLE_LOCKMODE
– oncheck –pt dbname:tablename can be used to see table’s lock
mode.
25
© 2011 IBM Corporation
Isolation Levels of Transactions / Sessions
 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.
26
© 2011 IBM Corporation
Informix isolation levels: Dirty Read (1)
 ANSI:
– SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
 Informix:
– SET ISOLATION TO DIRTY READ;
 With this isolation level used at the session/transaction, the
database server does not place any locks or check for existing
locks when resolving your query.
27
© 2011 IBM Corporation
Informix isolation levels: Dirty Read (2)
 Dirty-read isolation makes it possible for your query to retrieve
phantom rows.
 Dirty-read isolation is the only isolation level available for nonlogging 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.
28
© 2011 IBM Corporation
Informix isolation levels: Committed Read (3)
 ANSI:
– SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
 Informix:
– SET ISOLATION TO COMMITTED READ;
 Default Isolation Mode in logged non-ANSI databases.
 Ensures that all rows read are committed to the database:
– You will not see any phantom rows or dirty data.
– You know the current row was committed, at least when it is read.
– After a process reads the row, however, other processes can change it.
29
© 2011 IBM Corporation
Informix isolation levels: Committed Read (4)
 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:
– Lookups
– Queries
– Reports that yield general information.
30
© 2011 IBM Corporation
Informix isolation levels: Cursor Stability (5)
 Not available in ANSI databases.
 Informix:
– SET ISOLATION TO CURSOR STABILITY;
 With CURSOR STABILITY, a shared lock is acquired on each
row as it is read by a cursor:
– This shared lock is held until the next row is retrieved.
– If data is retrieved by using a cursor, the shared lock is held until the
next FETCH is executed.
31
© 2011 IBM Corporation
Informix isolation levels: Cursor Stability (6)
 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:
– Lookups.
– Queries.
– Reports yielding operational data.
32
© 2011 IBM Corporation
Informix isolation levels: Repeatable Read (7)
 ANSI:
– SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
– SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 Informix:
– SET ISOLATION TO REPEATABLE READ;
 Default isolation level in ANSI databases.
 The database server places a shared lock on all the rows that
the database server examines for a query:
– All these locks are held until the transaction is committed.
– Other users can read the data, but cannot modify it in any way.
33
© 2011 IBM Corporation
Informix isolation levels: Repeatable Read (8)
 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).
34
© 2011 IBM Corporation
Informix isolation levels: Last Committed Read (9)
 The Problem with Committed Read: Avoiding Locked Rows:
– Updated rows cannot be read until the change is committed unless they
use dirty reads.
– Applications may perform poorly if they wait on updated rows to
commit.
– Applications can use dirty reads but may get unexpected results.
– Deadlocks may occur which waste a significant amount of time.
35
© 2011 IBM Corporation
Informix isolation levels: Last Committed Read (10)
 The Solution: Last Committed Read (Optimistic Locking).
 In Committed Read isolation level, exclusive row-level locks
held by other sessions can cause SQL operations to fail when
attempting to read data in the locked rows.
 The LAST COMMITTED keyword option to the SET ISOLATION
COMMITTED READ statement reduces the risk of locking
conflicts when attempting to read a table.
36
© 2011 IBM Corporation
Informix isolation levels: Last Committed Read (11)
 Also known as Optimistic Locking, common in web-based
applications
– E-Commerce example:
• 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.
 Informix:
– SET ISOLATION TO COMMITTED READ LAST COMMITTED
 Provides concurrency and throughput improvement over
Committed Read.
37
© 2011 IBM Corporation
Informix isolation levels: Last Committed Read (12)
 Returns the most recently committed version of the rows, even if
another concurrent session holds an exclusive lock:
– It ensures that writers don’t block readers.
– You are trying to read the row, not update it or delete it.
 Can be set as default isolation level using ONCONFIG parameter:
– USELASTCOMMITTED.
 The table has been configured for row-level, NOT page-level
Locking.
38
© 2011 IBM Corporation
Summary Informix and ANSI Isolation Levels
Informix SQL
ANSI SQL
Remarks
Dirty Read
Read Uncommitted
No locks are placed during reading
data and no locks from other
sessions will block this reader.
set isolation to dirty read [retain
update locks]
set transaction isolation level read
uncommitted
Last Committed Read
Not Available
Returns the most recently committed
version of the rows, even if in another
concurrent session holds an
exclusive lock.
Committed Read
Read Committed
Checks for locks being held by other
sessions but does not place a lock
itself.
set isolation to committed read [retain
update locks]
set transaction isolation level read
committed
Cursor Stability
Not Available
set isolation to committed read last
committed [retain update locks]
An update lock is placed on the
current fetched row, it will be
promoted to an exclusive lock as
soon as an update is executed.
set isolation to cursor stability [retain
update locks]
39
© 2011 IBM Corporation
Problems that the different Isolation Levels prevent
Dirty read
Can occur?
Nonrepeatable
read
Can occur?
Phantom
read
Can occur?
Dirty read
(ANSI: Read
uncommitted)
Yes
Yes
Yes
Last Committed Read
(ANSI: Not supported)
No
Yes
Yes
Committed read
(ANSI: Read Committed)
No
Yes
Yes
Cursor Stability
(ANSI: Not supported)
No
No
Yes
Less protection / isolation of the data read
More Concurrency
Less Deadlocks
40
Isolation level
© 2011 IBM Corporation
Setting the Lock Mode of a Transaction/Session
 Do not wait for lock to be released (default)
– If the database item is locked, it will immediately return an error code:
• Ex:
• -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;
41
© 2011 IBM Corporation
Retain Update Locks
 Syntax:
– 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.
42
© 2011 IBM Corporation
Retain Update Locks
 The update lock is retained until the end of the transaction.
 This feature lets you avoid the overhead of the repeatableread isolation level or workarounds, such as dummy updates
on a row.
43
© 2011 IBM Corporation
Deadlock
 A deadlock occurs if two sessions hold a lock and each session wants to acquire a
lock that the other sessions already owns
– Example:
•
•
•
•
Process A waits for process B to release resources.
Process B waits for process A to release some other resources.
Process A waits for B which waits for A which waits for B…
So this infinite loop need be interrupted.
 A deadlock is automatically solved by Informix:
– Before granting a new lock, Informix scans the internal lock table and delivers ISAM error
code 143 to the application if it detects a possible deadlock situation.
– For distributed transaction the maximum lock wait time is specified thru the ONCONFIG
parameter DEADLOCK_TIMEOUT.
– This is also the time it will pass before Informix declares a deadlock has occurred.
44
© 2011 IBM Corporation
Informix – Total Number of Locks in the system (1)
 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:
– Informix 32-Bit
• 8.000.000 locks
– Informix 64-Bit
• 500.000.000 locks
45
© 2011 IBM Corporation
Informix – Total Number of Locks in the system (2)
 Automatic dynamic lock allocation:
– If while Informix is operating, the number of locks allocated exceeds
the value of LOCKS, the database server increases the size of the
lock table.
– Informix increases the size of the lock table by attempting to double
the lock table on each increase.
 Informix automatically doubles the size of the lock table up to 15
times if the lock table becomes full
– Each lock table increase is limited to 100.000 locks:
• 32-Bit: 8.000.000 + (15 x 100.000) = 9.500.000
• 64-Bit: 500.000.0000 + (15 x 100.000) = 501.500.000
46
© 2011 IBM Corporation
Informix – Monitoring the Locks
 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
47
© 2011 IBM Corporation
References
 IBM Informix Guide to SQL: Tutorial - Programming for a Multiuser Environment
 http://publib.boulder.ibm.com/infocenter/idshelp/v117/index.jsp
 IBM Informix Dynamic Server Administration Guide: Locking
 http://publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.perf.doc/ids_prf_412.htm
 Informix Dynamic Server locking, Part 1: Understand locking behavior and analyze
locking conflicts in Informix
 http://www.ibm.com/developerworks/data/library/techarticle/dm-0609herber/index.html
 Informix DBA: Informix Performance Locking and Concurrency
 http://www.ibmdatabasemag.com/showArticle.jhtml?articleID=216300349
 (old but good) Informix Unleashed book – Ch 15: Managing Data with Locking
 http://www.freebookzone.com/goto.php?bkcls=db_other&bkidx=8&lkidx=1
48
© 2011 IBM Corporation