Transaction Concurrency Control and Locking (examples on Informix Dynamic Server)

1. Transaction Concurrency Control and Locking (examples on Informix Dynamic Server)

2. 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.

3. 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.

4. 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.

5. 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.

6. 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

7. 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.

8. 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).

9. 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.

10. 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.

11. 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.

12. 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.

13. 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.

14. 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.

15. 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.

16. 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 S-locks) can be placed on the same row anymore. Prevents others from reading or updating the data.

17. 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.

18. Lock Compatibility Matrix If the item already has a lock of type… Can I place a lock of type…?

19. 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.

20. 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.

21. 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 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

22. 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 Row locks 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.

23. Informix Lock Granularity (lock scopes) (4) • What is the type of data object (item) that is locked (secured)? More Concurrency Less Deadlocks Larger overhead to maintain locks More Difficult to implement Index key Row Page Table Database

24. 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.

25. 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.

26. 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.

27. 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.

28. 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 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.

29. 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.

30. 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.

31. 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.

32. 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.

33. 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.

34. 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).

35. 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.

36. 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.

37. 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.

38. 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.

39. Summary Informix and ANSI Isolation Levels

40. Problems that the different Isolation Levels prevent Less protection / isolation of the data read More Concurrency Less Deadlocks

41. 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;

42. 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.

43. Retain Update Locks • The update lock is retained until the end of the transaction. • This feature lets you avoid the overhead of the repeatable-read isolation level or workarounds, such as dummy updates on a row.

44. 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.

45. 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

46. 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

47. 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

48. 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