Login
Register
Ingres.comNavigation
Learn About
Developing With
Ingres Talk
Information
Toolbox
Views
Ingres MVCC User Guide
From Ingres Community Wiki
Contents |
Introduction
This page describes Ingres MVCC from an end user's perspective. Specifically, it outlines how to enable Ingres so MVCC is used, what behavior to expect when MVCC is used, and how MVCC works with other Ingres features.
MVCC (i.e., Multi-Version Concurrency Control) provides an alternative to traditional Ingres locking. With traditional locking, provided you are not running with an isolation level of read uncommitted or a readlock setting of NOLOCK, any data read will acquire locks on pages and/or rows. This ensures that you are reading consistent, committed data. With MVCC, no locks are acquired on reads because the data read is based on some consistent point in time in the past. The resulting data read is consistent because the point in time always corresponds to a point in time when your data has been committed. By avoiding read locks in this way, readers are no longer blocked by writers holding exclusive locks, and conversely, writers are no longer blocked by shared locks held by readers.
Ingres uses a log-based approach to implement MVCC. Log records are used to dynamically reconstruct points in time from the past by reverting modifications that have been made to data pages. An alternative approach is to keep multiple versions of data records and/or pages when data is updated, and then to read the appropriate version based on the desired point in time. Because Ingres does log-based recovery, the log-based approach fits in better with the current Ingres architecture, and therefore, that's the approach that is used. For more details on the design and implementation of MVCC, see the MVCC Wiki page. From that page, you can access the detailed design specification, as well as slides that provide a high level overview of the feature.
Preconditions Required To Use MVCC
This section outlines the various pre-conditions required in order for MVCC to be used. Note that these conditions are the same ones that apply in order for row level locking to be used.
Database Requirements
Your database must be enabled for MVCC. All databases will be MVCC-enabled, by default. However, MVCC does not apply to the iidbdb database because it does not support fast commits.
Due to minor overhead incurred when your database is MVCC-enabled, if you determine that you will never want to use MVCC within a particular database, it is possible to disable MVCC for that database by executing:
ALTERDB -disable_mvcc <dbname>
To reenable MVCC, execute:
ALTERDB -enable_mvcc <dbname>
This will take affect for the next session that uses that database.
Table Requirements
MVCC only applies to tables that meet the following criteria:
- They were not created with a page size of 2k.
- They are permanent tables; i.e., they are not temporary tables.
- They are not using the rtree storage structure.
- They are not one of the core system catalogs (i.e., iirelation, iiattribute).
- They are not gateway tables.
- They do not contain data rows that are larger than the table's page size.
Other Requirements
- Logging has not been disabled, unless you are only issuing read-only queries.
- Fast commits have not been disabled.
- You are not running in a cluster environment.
An isolation level of READ_UNCOMMITTED will be silently changed to READ_COMMITTED to ensure that MVCC protocols are adhered to.
Similarly, when using MVCC with read-only queries, readlock=nolock is ignored; readlock=exclusive causes the Table to be opened with an intent update (IX) instead of intent shared (IS) lock.
Note that the READ_UNCOMMITTED and readlock=nolock behavior just described only applies starting in build 122.
You will be able to use MVCC with logging disabled, but only with read-only queries. Note that this behavior only applies starting in build 120.
Note that journalling can be disabled when using MVCC. But this can result in snaphot too old errors if journals that Ingres needs to reconstruct previous points in time are not available. Therefore, if you have long running transactions, it's best not to disable journalling.
Fast commits are enabled if any of the following system parameters are enabled:
- fast_commit = ON
- cache_sharing = ON
- sole_server = ON
Enabling MVCC
By default, MVCC will not be enabled. It can be enabled either at the system level or within individual user sessions.
Enabling It At The System Level
To enable it at the system level, you will need to modify the system_lock_level parameter in your server configuration, setting it to mvcc. The parameter setting will take affect on the next restart of the Ingres server.
If set at the system level, all sessions by default will use MVCC, unless overridden at the session level.
If MVCC is set at the system level and it is not overridden at the session level, a warning will be written to the server log when a user attempts to access a table that is not compatible with MVCC. In that case, the user's lock level will automatically switch to row level for that table.
Enabling It At The Session Level
Enabling MVCC at the session level can be done by enabling it for all tables the user acceses within their session:
SET LOCKMODE SESSION WHERE LEVEL = MVCC
Or only for specific tables:
SET LOCKMODE ON <table> WHERE LEVEL = MVCC
If you have set MVCC explicitly through SQL and your system wide lock level is not set to MVCC, then you will receive the following error when you try accessing tables that are not compatible with MVCC.
E_US120C Invalid lock level for table or database.
For example, you will get this error if you have set the session lockmode to MVCC and attempt to access a table created with a 2k page size. Note that this error only applies to tables for which the user can control the lock level. E.g., temporary tables are never locked because they are private to a user's session, so MVCC does not apply.
Note that if you set MVCC at the session level, then other users updating those same tables must also either be using MVCC or row level locking. Otherwise, MVCC will not take effect, and lock conflicts will occur. For example, if the system lock level is set to default, and user 1 sets his session to use MVCC, but user 2 does not, then if Ingres decides to use page level locking for user 2, then pages updated on a table by user 2 (that user 1 needs to read) will result in user 1 having to wait on user 2. Beware that even if you set your system lock level to use row locking, Ingres will automatically do lock escalation if the number of row locks you're holding exceeds a certain threshhold.
What To Expect When Using MVCC
When MVCC is used, data can be read based on either what was committed at the start of the user's current transaction, or what was committed at the start of the statement. Whether one or the other is used depends on the user's isolation level setting, as summarized in the table below. Transaction level read consistency ensures that your data is consistent from a transactional point of view, whereas statement level consistency only guarantees that your data is consistent per individual statement.
| Isolation Level | Read Consistency Level | Snapshot Corresponds To |
|---|---|---|
| Serializable | Transaction | Start of Transaction |
| Repeatable Read | ||
| Read Committed | Statement | Start of Statement |
| Read Uncommitted |
Note: to change your isolation level, either modify the system_isolation system parameter, or execute the following within your session:
SET SESSION ISOLATION LEVEL <isolation level>
To illustrate the difference between transaction and statement level read consistency, consider the following example.
For the purpose of this example, the select from x1 is there simply to establish a point in time corresponding to the start of user 1's read transaction. Whether user 1's read from x2 sees the newly inserted row by user 2 depends on user 1's isolation level setting.
If the isolation level is serializable, user 1's read from x2 corresponds to time T0, the start of its transaction. Therefore, in this case, user 1 does not see the newly inserted row.
On the other hand, if user 1's isolation level is read committed, then its read from x2 corresponds to data committed at the start of the statement. Therefore, because user 2 has committed its transaction by then, this means that in this case, user 1 will see the newly inserted row.
As a comparison, if user 1 is using traditional locking, then the read from x2 will also see the newly inserted row. But that's because user 1 has to wait until the update transaction commits in order to acquire a lock on x2.
Statement level read consistency generally results in better performance. As noted earlier, the basic approach Ingres uses to implement MVCC is by reverting modifications that have been made to the desired data page. Therefore, because the point in time corresponding to statement level read consistency is closer in time to the current point in time, less work is required to reconstruct statement-consistent data pages. However, there are subtle differences in behavior when using statement level read consistency. See the next two sections for details.
Application Consistency Issues When Using Statement Level Read Consistency
The example below illustrates a case where statement level read consistency can result in application consistency issues.
Conceptually, the example correponds to a scenario where you are transferring $100 from a row in one table to another row in a second table. Therefore, from a transactional standpoint, the total amounts prior to the updates should be the same as the total after the updates -- $6000.
If statement level read consistency is used, then the two selects on the right will end up reading based on two different points in time. Namely, the first select does not see the update that has been made to table X because at the start of the statement, the update transaction has not committed yet. So, it reads $1000. On the other hand, the second select does see the update to table Y because by then, the update transaction has committed. So, it reads $5100, which results in an inconsistent total dollar amount of $6100!
With transaction level read consistency, the two selects will read based on the same point in time, the start of its transaction. So, the selects will both read the pre-update values. With traditional locking, the timeline shown above does not apply, as the selects will have to wait until the update transaction completes. Therefore, both selects will read the post-update values. Subtle differences in behavior like this need to be taken account for users who are accustomed to the behavior of traditional locking.
Inserts and Selects From The Same Table Within The Same Transaction
If you are inserting data into a table and selecting it from within the same transaction, the data will be seen by the select when using MVCC.
Note that in early builds of the product, the data seen would depend on your isolation level. If your isolation level is repeatable read or serializable, then the select will see the newly inserted data. However, if your isolation level is read committed, you will not. This has since been changed.
Update Conflicts
Update conflicts are possible with MVCC. Conflicts are resolved so that the first updater wins. In other words, the first update succeeds, and the conflicting one either receives an error or retries the update.
If the conflicting updater is using transaction level read consistency, then the conflicting updater will receive the following error:
E_US125B Unable to serialize access to row.
Assuming transaction level read consistency, user 1 would receive an error on the UPDATE in the following example:
| Time | User 1 | User 2 |
|---|---|---|
| T0 | { Starts transaction } | |
| T1 | UPDATE t SET b = 1 WHERE a = 1; | |
| T2 | COMMIT; | |
| T3 | UPDATE t SET b = 2 WHERE a = 1; |
Whereas, in this example, user 1's UPDATE succeeds because the rollback done by user 2 results in there being no conflict.
| Time | User 1 | User 2 |
|---|---|---|
| T0 | { Starts transaction } | |
| T1 | UPDATE t SET b = 1 WHERE a = 1; | |
| T2 | ROLLBACK; | |
| T3 | UPDATE t SET b = 2 WHERE a = 1; |
If the conflicting updater is using statement level read consistency, and it encounters a conflict, it will undo the current update and retry it.
Note that what's described above only applies if at least one of the updaters is using MVCC.
When concurrent updates are running, it's still possible for users to have to wait on one another if both users need to acquire the same exclusive lock. If a lock timeout is set, then they only wait for the duration of the timeout and receive a timeout error if the lock cannot be acquired before the timeout elapses. To change the lock timeout setting, either modify the system_timeout system parameter, or execute:
SET LOCKMODE SESSION WHERE TIMEOUT = <timeout-value>
Update Cursors
Rows read by update cursors that are not explicitly declared FOR READONLY will exclusively lock rows, as they are fetched, when MVCC is used. Any attempt to update or delete the row that the cursor is currently positioned on, which results in an update conflict, will return the unable to serialize error described in the previous section. This occurs regardless of the user's isolation level setting and results in the transaction being aborted.
In the example below, user 1's update will result in an update conflict.
| Time | User 1 | User 2 |
|---|---|---|
| T0 | SET SESSION ISOLATION LEVEL READ COMMITTED; | |
| T1 | DECLARE c CURSOR FOR SELECT a, b FROM t FOR UPDATE OF b; | |
| T2 | OPEN c; | |
| T3 | UPDATE t SET b = 11 WHERE a = 1; | |
| T4 | COMMIT; | |
| T5 | FETCH c INTO :var1, :var2; | |
| T6 | UPDATE t SET b = 12 WHERE CURRENT OF c; |
Lock Escalation
Lock escalation does apply if you are using MVCC. If you set your lock level to MVCC, whether through the system parameter or at the session level, if your transaction exceeds the lock threshhold, e.g., due to a query updating a large number of rows, then Ingres will escalate to using table locking.
Referential Integrity
Referential constraints are enforced when using MVCC. The appropriate mechanisms will be in place to prevent dangling children from appearing if concurrent transactions are deleting parent records while children records are being inserted. The following are examples of scenarios where dangling children should not arise:
| Time | User 1 | User 2 | Notes |
|---|---|---|---|
| T0 | Insert foreign key X into child | Succeeds because parent key X exists | |
| T1 | Delete parent key X | Fails because of pending insert of child key X |
| Time | User 1 | User 2 | Notes |
|---|---|---|---|
| T0 | Delete parent key X | Succeeds because there are no children keys X | |
| T1 | Insert child key X | Fails because of pending delete of parent key X |
Large Objects
Large objects (i.e., datatypes like LONG VARCHAR) will work with MVCC.
--Zelaine 22:17, 12 February 2010 (CST) See the Current Limitations section.
DDL Statements
DDL statements like MODIFY (without the CONCURRENT_UPDATES option) and CREATE INDEX will still block readers, even if MVCC is enabled. This may be enhanced in the future.
Performance Tuning Considerations
TBD
MVCC-capable databases do not reclaim individual free pages from BTREE tables, whether or not an MVCC transaction is actually in progress. (The structural change involved in free page reclamation would confuse an MVCC transaction that started during the reclaim.) This restriction may or may not be permanent. In any case, to reclaim unused pages from BTREE tables in an MVCC-enabled database, use MODIFY.
Current Limitations
MVCC is still in the early alpha stages. The feature is available for early testing starting in community edition build 117 of Ingres 10. The product can be downloaded from the Ingres download page. Select the following in the dropdowns:
- Product - Community Projects
- Release - Ingres Database
- Platform - <Your desired platform>
Currently the product is only available on Linux and Windows. For information on how to install the Ingres product, see the Install Wiki page.
In build 117, the following features do not yet work:
- Using large objects with MVCC
- Enforcing referential constraints when concurrent inserts and deletes are taking place. I.e., you can end up with dangling children records.
- Update conflicts are not properly detected in the case where the winning updater is using MVCC locking, but the conflicting updater is using row locking.
In build 118, the large object limitation still exists, but the other two areas have been corrected. Support for large objects will be available in build 120.
Prior to build 122, if your isolation level is set to read uncommitted or you have a readlock setting of NOLOCK, then MVCC will not be used. This has been changed, starting in build 122.
