Database consistency in NonStop SQL/MX

Database consistency in NonStop SQL/MX

• 1.
  Database consistency inNonStop SQL/MX Frans Jongma, Hewlett Packard Enterprise Advanced Technology Center NonStop Enterprise Division Version 1.5 Date: August 2018 Change history: SQL/MX Release 3.6 has added SELECT FOR UPDATE support. The section “How to make SQL/MX behave more like Oracle” has been replaced by “SELECT for UPDATE in SQL/MX”. A new paper, describing this statement in detail is also available. Introduction Databases need to handle concurrent access by multiple transactions to the same data without compromising data integrity. They must make sure that data is protected from being updated by multiple users at the same time by locking rows against multiple modifications. However, locking data may reduce concurrency and throughput because transactions may have to wait for locks to be released. NonStop SQL/MX uses row locks to block certain read operations from accessing uncommitted data. Other database implementations use Multi-Version Concurrency Control (MVCC) to allow reading and updating the database without having readers to wait for writers and vice versa. The Oracle® RDBMS is one of those implementations and so are PostgreSQL and others who have implemented MVCC in various forms. (DB2 has support, and Microsoft SQL Server has an option to turn on MVCC). MVCC provides point-in-time consistent views. Read transactions under MVCC typically use a timestamp or transaction ID to determine what state of the DB to read, and read these versions of the data. Read and write transactions are thus isolated from each other without any need for locking. Writes create a newer version, while concurrent reads access the older version.1 SQL/MX provides current-time consistent views waiting for write locks to be released. In addition, SQL/MX is able to provide optimistic current-time views by allowing reading through locks if so desired. 1 From Wikipedia: https://en.wikipedia.org/wiki/Multiversion_concurrency_control
• 2.
  NonStop Advanced TechnologyCenter Database consistency in SQL/MX -2- These different approaches to database consistency become important when applications migrate to SQL/MX from an MVCC database. This paper describes the difference between Oracle and SQL/MX using a few examples. It shows how waiting for locks in SQL/MX can be avoided by using ANSI isolation levels that are not implemented by Oracle and it includes recommendations to simplify a migration. Examples used in this document Business transactions comprise multiple SQL statements that together should either complete or fail. The examples used in this document show multiple SQL statements that together form one business transaction. These business transactions use SQL Autocommit set to ’OFF’ and therefore transactions need to be explicitly committed. How MVCC works As the name implies with MVCC, there will be multiple versions of the same row available to allow point-in-time consistent reads. If a row is updated by a transaction, the old version may remain present in the database for reference by transactions that need a transaction consistent view of the data. Some form of cleanup of this stale data needs to be performed at some time. Another approach is that the DBMS reconstructs a consistent copy of the data using the before image of the row. Oracle uses the before images (UNDO records) to reconstruct the data to the time that a statement or a transaction started. When the before-images are no longer needed, they are automatically purged. PostgreSQL for example, uses the former method where older copies of data remain available in the table until removed by a “Vacuum Cleaner” process that garbage-collects expired or aborted versions of data. It is important to notice that MVCC systems will return stale data for rows that have been updated and not yet committed. Applications that want to update data must re-read during the update to ensure that it has not been changed. Alternatively, rows can be locked prior to being updated, in which case other transactions will see stale data. With the increasing need for real-time, up-to-date information, one might prefer reading data that will be valid in the next blink of an eye (since transactions will typically commit) than to read data that was correct before, but may have changed already. While returning committed data is a good principle, it is too restrictive for a DBMS to deny an application the choice of the right isolation level for the business. Isolation levels ANSI/ISO SQL defines four isolation levels to control concurrency between transactions.
• 3.
  NonStop Advanced TechnologyCenter Database consistency in SQL/MX -3- Serializable. This is the highest isolation level. It may require read and write locks on the selected data which are released at the end of a transaction. In addition, range locks may need to be set also to prevent phantom reads. Phantom reads are rows that are retrieved a second time a query runs within the same transaction due to an insert and commit by another transaction. Repeatable Reads. The DBMS may keep read and write locks until the end of the transaction. Phantom reads may occur. Read committed. Write locks will be acquired on updated data and will be kept until the end of the transaction. Any read locks will be released when a SELECT finishes. This isolation level prohibits a transaction from seeing uncommitted data. Read Uncommitted. This is the lowest isolation level. It provides the highest concurrency, but a transaction may see not-yet-committed data. NonStop SQL/MX implements three of these four levels; SERIALIZABLE and REPEATABLE READS are treated the same. The isolation levels can be set using the SET TRANSACTION statement as well as an option on the SELECT, UPDATE, and DELETE statements. The default isolation level for SQL/MX is READ COMMITTED. SQL/MX supports two additional access options: STABLE ACCESS and SKIP CONFLICT. The former places a temporary read lock on the current row of an updatable cursor2 , while the latter allows a query to skip over uncommitted data. Oracle only implements the SERIALIZABLE and READ COMMITTED isolation modes and does not allow uncommitted reads. It always enforces statement level read consistency, and optionally transaction level read consistency. The default isolation level in Oracle is READ COMMITTED just like it is in SQL/MX. Oracle will set a lock on a row when reading it with the SELECT …. ‘FOR UPDATE’ clause. Oracle SERIALIZABLE mode differs from the NonStop implementation in that it takes an optimistic approach assuming that data read in this mode will not be modified by another transaction. If that should happen, Oracle will reject the SERIALIZABLE transaction with error “ORA-08177 – can’t serialize access for this transaction”. The application then needs to decide whether to rollback or to commit the transaction. SQL/MX places locks while reading data to prevent other transactions from modifying any data that would change the result set of the query. Oracle will not prevent others from performing such updates. 2 SQL/MX Reference manual p. 1-10, Page 30 in Release 3.4
• 4.
  NonStop Advanced TechnologyCenter Database consistency in SQL/MX -4- Implications of locking data Locking data ensures transactional integrity but at the same time it may reduce concurrency because while a row is being updated by one transaction, the DBMS must block other transactions from doing the same. Row locks may also cause writers to block readers from accessing these locked rows even if they simply want to retrieve committed data. SQL/MX write transactions will block READ COMMITTED readers from reading, Oracle writes will not block readers; instead, it will return a previously committed (stale) version to the reader. Oracle read committed isolation level Oracle uses MVCC to avoid writers from blocking readers. The minimum isolation level in Oracle is READ COMMITTED, therefore, when a row is locked as the result of an update, other transactions will get a stale copy of the data as it existed before the update had occurred. For long running read transactions, the data returned will even be consistent with the time the read operation began, which may be a while back. When an Oracle application has found rows that it needs to update it needs to read them again requesting a lock either explicitly using the SELECT … FOR UPDATE clause or implicitly with the update or delete statement. Care must be taken by the application to prevent updates from earlier transactions from being lost as is shown in Table 1. In this example, transaction TX1 and TX2 both update the same row after they have read committed data. This is known as the “lost update” problem because TX1’s update of the SAL column to 900 at time T2 is lost. An additional effect is that TX1 and TX2 will see different results for the same row at time T6 while both are reading committed data. Table 1: Oracle read consistency Time Transaction TX1 Transaction TX2 T1 Select sal from emp where empno=7900; Result = 950 Select sal from emp where empno=7900; Result = 950 T2 Update emp set sal=900 where empno = 7900; Result = 900 T3 Select sal from emp where empno 7900; Result = 950
• 5.
  NonStop Advanced TechnologyCenter Database consistency in SQL/MX -5- T4 Update emp set sal = 1000 where empno = 7900; Transaction waits for row to be unlocked T5 Commit Update now finishes T6 Select sal from emp where empno=7900; Result = 900 Select sal from emp where empno=7900; Result is 1000 T7 Commit T8 Select sal from emp where empno=7900; Result = 1000 Select sal from emp where empno=7900; Result is 1000 NonStop SQL/MX read committed and uncommitted isolation level SQL/MX uses locks to protect readers that require committed data from retrieving data that has not been committed. However, it does allow applications to “read through” locks using the READ UNCOMMITTED isolation level. The next table shows the two transactions from the previous example executing the same sequence of SQL statements in SQL/MX. The table includes two additional points in time, T3a and T5a, where instead of waiting for a lock release, the transaction retrieves the current value of modified, but uncommitted data with READ UNCOMMITTED access. Table 2: NonStop SQL/MX waiting for locks or reading uncommitted data Time Transaction TX1 Transaction TX2 T1 Select sal from emp where empno=7900; Result = 950 Select sal from emp where empno=7900; Result = 950 T2 Update emp set sal=900 where empno = 7900; Result = 900
• 6.
  NonStop Advanced TechnologyCenter Database consistency in SQL/MX -6- T3 Select sal from emp where empno=7900; Transaction waits for lock to be released (or timeout 60 seconds) T3a Select sal from emp where empno 7900 READ UNCOMMITTED ACCESS; Transaction reads through the lock: Result = 900 T4 Update emp set sal = 1000 where empno = 7900; Transaction waits for row to be unlocked (or timeout) T5 Commit Update now finishes T6 Select sal from emp where empno=7900; Waits for lock of TX2 (or timeout) Select sal from emp where empno=7900; Result is 1000 T6a Select sal from emp where empno = 7900 READ UNCOMMITTED ACCESS; 1000 T7 Commit T8 Select sal from emp where empno=7900; Result = 1000 Select sal from emp where empno=7900; Result is 1000 The effect of READ UNCOMMITTED access is that current (but uncommitted) information is returned to the application and this can be important in time-critical situations. SQL/MX allows an application to take an optimistic approach when it expects that transactions will typically commit in this type of environment. The alternative is to wait for the lock to be released. An application configurable timeout can be used to limit the time to wait for a release. An
• 7.
  NonStop Advanced TechnologyCenter Database consistency in SQL/MX -7- application that uses the default isolation level will not see wrong data by accident: either it waits for committed data, or it explicitly allows uncommitted data. Note that the “lost update” problem can occur in Oracle as well as in SQL/MX if two transactions update the same row right after another without taking precautions such as verifying data to be unchanged when updating or by locking the target row explicitly. Looking back in time or looking forward SQL/MX allows reading through the locks which allows an application to see the data as it will be soon (that is, after it is committed), because in well written applications, transactions will be committed instead of rolled back. Rolling back transactions is the exception in most commercial applications3 . Oracle can only show the data as it was at the time a transaction or the statement was started. And while this gives a consistent state of the data at a moment in time, the application might be behind reality. The choice is for the application designer to make, not the DBMS. SQL/MX STABLE access As mentioned under Isolation Levels, SQL/MX has implemented an additional level: STABLE ACCESS. This is useful with updatable cursors. It is used to lock the row that is the “current of cursor” to prevent other sessions from updating it. The lock is removed when the next row is fetched from the cursor which then becomes the “current of cursor”. STABLE ACCESS for cursors prevents “lost updates”, it requires the FOR UPDATE clause to be present in the DML to define the cursor as updatable. When the READ COMMITTED isolation level is used a SQL warning is issued when the current of cursor row is updated using a WHERE CURRENT OF CURSOR clause, when it has been changed or deleted by another transaction. SQL/MX will issue a warning “WARNING[8106] The last row fetched by this cursor was updated or deleted between the FETCH and UPDATE/DELETE...WHERE CURRENT... of statements”. (See P 4-25 of the SQL/MP to SQL/MX migration guide). This warning might avoid the lost-update problem, but only if applications check for warnings and take appropriate action. Skipping lock conflicts The throughput can be increased when locks are ignored by skipping locked rows. This way a transaction will not wait for locks, and updates will not be lost, because they did not occur. Oracle as well as SQL/MX provide this option. SQL/MX calls this SKIP CONFLICT ACCESS; Oracle calls it SKIP LOCKED. The next example is from SQL/MX. 3 High-frequency trading at stock exchanges where orders may be canceled at a regular basis may be the exception.
• 8.
  NonStop Advanced TechnologyCenter Database consistency in SQL/MX -8- Table 3: NonStop SQL/MX skipping locks Time Transaction TX1 Transaction TX2 T1 Select sal from emp where empno=7900; Result = 950 Select sal from emp where empno=7900; Result = 950 T2 Update emp set sal=900 where empno = 7900; Result = 900 T4 Update emp set sal = 1000 where empno = 7900 SKIP CONFLICT ACCESS; 0 rows will be updated T5 Commit T6 Select sal from emp where empno=7900; Result = 900 Select sal from emp where empno=7900; Result is 900 T7 Commit T8 Select sal from emp where empno=7900; Result = 900 Select sal from emp where empno=7900; Result is 900 A SQL UPDATE or DELETE statement returns the number of affected rows to the application. This can be used to check if an update has actually happened. In this example, the application should expect one row to be updated, but due to access mode, 0 rows were updated. The application can determine whether to accept this as valid or raise an exception. Oracle has a similar method to skip over locked rows. It is a part of the FOR UPDATE clause (which locks rows or waits for locks to be released). The SELECT … FOR UPDATE incudes a SKIP LOCKED clause to jump over locked rows. Summary Oracle will not let writers block readers; in SQL/MX writers do block readers. In the event that a row is locked by a write, Oracle will return committed, stale data to the reader, but the reader has no way of telling that this data is subject to change. SQL/MX will wait, for a limited time, for
• 9.
  NonStop Advanced TechnologyCenter Database consistency in SQL/MX -9- a lock to be released, however, an application has the option to retrieve uncommitted data if it chooses to. There may be valid reasons to read uncommitted data in time-critical situations. Both products allow to skip over locked rows. Locking rows for update Business transactions will often contain multiple SQL statements. The result of one statement may lead to the execution of another. The previous examples showed that updates can be lost so it becomes important that the DBMS prevents other transactions from changing the rows that are part of another transaction. Oracle and SQL/MX have different ways to achieve this transaction consistency. Oracle The previous section showed how updates in Oracle can be lost because SELECT statements do not reveal that rows have been updated by other transactions. Oracle normally returns committed data from the time the SELECT or transaction was started. This is not good if one or more rows need updating. To prevent updates from being lost, rows must be locked before the update occurs. In Oracle, this is achieved by the FOR UPDATE [OF <col>, <col> …] clause of the SELECT statement. The FOR UPDATE clause of a SELECT tries to lock the resulting rows of a query so that they can be modified at a later moment in time by the same transaction. If the row is locked by another session, the lock cannot be set and Oracle will wait until the lock is released. FOR UPDATE is also used in cases where a parent row is locked to serialize the insertion of child rows, for example when resources such as meeting-rooms are to be scheduled: the resource is locked such that time-slots (child rows) can be reserved. The FOR UPDATE clause includes an option to return immediately when the row is locked (NOWAIT option) or to skip locked rows (SKIP LOCKED option). SQL/MX In SQL/MX, the lost update problem can be prevented by locking the rows to be updated for exclusive access. In addition, if updates or deletes are done using cursors, STABLE ACCESS is sufficient and this mode will lock only the current row of the cursor. The SELECT clause supports the ANSI isolation levels READ (UN) COMMITTED and REPEATABLE / SERIALIZABLE plus the SHARED and EXCLUSIVE modes. The use of EXCLUSIVE MODE combined with SERIALIZABLE ACCESS places an exclusive lock on rows. SQL/MX has a default lock wait time of 60 seconds before it returns a “row locked” error. This wait time can be altered by the application using a CONTROL TABLE statement per table or by using a Control Query Default (CQD). The CONTROL TABLE statement includes an
• 10.
  NonStop Advanced TechnologyCenter Database consistency in SQL/MX -10- IF_LOCKED ‘RETURN’ option. There is a DML option to skip over locked rows (SKIP CONFLICT ACCESS option). The next table shows how a row in the EMP table can be locked by transaction TX1 using the FOR UPDATE statement in Oracle and by the SERIALIZABLE ACCESS in EXCLUSIVE MODE in SQL/MX. SQL/MX Exclusive Mode prevents all reads except READ UNCOMMITTED access. In Oracle, the FOR UPDATE clause will place an update lock but regular SELECTS will return data as it existed prior to the update and any change will not be noticed by the reader. Table 4: Oracle and SQL/MX locking for update Time Oracle TX1 Oracle TX2 SQL/MX TX1 SQL/MX TX2 Notes T1 Select * from emp where empno = 7782 for update; Select * from emp where empno = 7782 serializable access in exclusive mode; EXCLUSIVE MODE required to prevent TX2 from reading the row in serializable/share mode. T2 (value of sal=2450) Select * from emp where empno = 7782 for update; Select * from emp where empno = 7782 serializable access in exclusive mode; T3 Row is owned by TX1 Waits for release of lock Row is owned by TX1 Waits for release of lock (or timeout) T4 Update emp set sal=2600 where empno = 7782; Update emp set sal=2600 where empno = 7782;
• 11.
  NonStop Advanced TechnologyCenter Database consistency in SQL/MX -11- T5 Commit; Sees value=2600 Commit Sees value =2600 When queries are simple, single table and access using the primary key, the method to block multiple transactions from accessing the same row is similar between Oracle and SQL/MX. The example uses an EMP table that has a primary key on EMPNO. Both SELECTS should only lock the row with EMPNO equal to 7782. SQL/MX will lock only this one row if the access uses a unique read on the primary key. More complex queries than the one used here may involve multiple rows to be read and as a result, more rows will get an exclusive lock. Locking for update in complex queries When queries are not as simple, there is a difference between the two approaches. The SELECT for UPDATE clause in Oracle places locks on the resulting rows, where the SERIALIZABLE ACCESS mode in SQL/MX may lock more rows because of the serializability of the query. Consider the following transactions. They want to update the salaries of some employees that are in departments based in one location. SELECT for UPDATE causes the EMP rows that are selected to be locked for update. In other words: only rows from the EMP table will be updated, but those rows are found by joining the EMP table with the DEPT table. The UPDATE statement can rely on no other transactions modifying these rows. The example shows the intent to update one column (e.sal) from the EMP table, therefore only rows from this one table will be locked in this example. Blocking data As described in the previous section, records must be locked before they can be updated. However, a lock on a resource will block another session requesting it. The next quote comes from Tom Kyte, a well-known expert and the author of various books on the Oracle database select empno, sal, job from emp e, dept d where e.deptno = d.deptno and loc = 'NEW YORK' and job = 'MANAGER' for update of e.sal; Followed by: update emp set sal = <new_salary> where empno = <empno retrieved from previous select>;
• 12.
  NonStop Advanced TechnologyCenter Database consistency in SQL/MX -12- and the host of the Oracle AskTom blog, describing Oracle locking. “As a result, the requesting session will be blocked – it will hang until the holding session gives up the resource. […….] The five common DML statements that will block in the database are INSERT, UPDATE, DELETE, MERGE and SELECT FOR UPDATE. The solution to a blocked SELECT FOR UPDATE is trivial: simply add the NOWAIT clause and it will no longer block. Instead, your application will report back to the end user that the row is already locked.4 ” The next sections compare Oracle and SQL/MX on how these DML statements deal with blocked data. Blocked SELECTs A SELECT for UPDATE in Oracle will wait for a row if it is locked, and then place an exclusive lock on it. The NOWAIT option will cause the statement to return immediately if a committed row exists and is already locked. SQL/MX has multiple ways to deal with blocked rows:  Use a CONTROL TABLE statement to let a statement immediately return if a row in the specified table is locked.  Use a CONTROL TABLE statement to specify a timeout value in hundreds of seconds. This allows for lock waits but limit the time they take. The default time is 6000; one minute.  Use a CQD to define the timeout value in hundredths of seconds for all tables.  Use a CQD to let the statement return immediately if a row is locked. Refer to section Control directives in SQL/MX for information on how they can be used. The CQDs and CONTROL TABLES operate on all DML statements, not only on SELECTs. Blocked INSERTs INSERTs in Oracle will block if two sessions attempt to insert a row with the same value of the primary or unique key. One of the sessions will block until the other either commits or rolls back the transaction. With inserts, there is no existing row to select and lock; there is no way to prevent others from inserting a row with the same value, thus blocking one session and causing a wait. The reason “there is no way to prevent” lies in the way Oracle retrieves committed data from the UNDO log. When one session has inserted a row, a SELECT FOR UPDATE with the same key value will return 0 rows, while a subsequent INSERT will wait for the other session to finish. 4 Expert Oracle Architecture, Chapter 6, locking and latching.
• 13.
  NonStop Advanced TechnologyCenter Database consistency in SQL/MX -13- (Note: A way around this in Oracle is to use a before trigger that sets a user-lock using the DBMS_LOCK_REQUEST function, however, this is not a recommended practice). Blocked MERGES, UPDATEs and DELETEs The Oracle recommendation for interactive programs is to issue a SELECT FOR UPDATE NOWAIT before UPDATEs and DELETEs are performed. This locks the row when possible or returns 0 rows and ensures for a following UPDATE or DELETE that no-one has updated the row since it was queried. This prevents the lost-update bug, and it prevents a subsequent DELETE or UPDATE statement from waiting possibly forever. Starting with Release 3.6, SQL/MX supports SELECT FOR UPDATE, and the same locking technique as used in Oracle can be applied. Deadlocks Deadlocks occur when there are two or more sessions: each holding a resource that another wants. The typical example is when there are two tables A and B, where session 1 holds a row in table A requesting a lock in table B that is held by session 2 and at the same time, session two requests the row that is held by session 1. Oracle detects deadlocks when they occur and resolves the deadlock by aborting one of the statements involved. That transaction then has to decide what to do: commit or abort the transaction. In SQL/MX, deadlocks are not specifically detected, however they will be resolved by a ROW_LOCKED exception after the timeout value has expired. Summary Applications written for both products need to lock rows before updating them. Locking however, may cause waiting by other transactions. Both products allow a statement to return if a row is locked, to prevent delays. However, Oracle only allows this for SELECTs, so waits may happen elsewhere. SQL/MX can return “if-locked” or timeout on any DML statement and it has some more options, such as setting a limit on how long to wait. SELECT for UPDATE in SQL/MX In releases prior to release 3.6 applications that wanted to mimic SELECT FOR UPDATE needed to place exclusive locks using normal SELECT statements on the rows to be updated. With release 3.6, SQL/MX supports the SELECT FOR UPDATE and when this is used, typically, only the rows that match the selection criteria will obtain an exclusive lock. There are however situations in which more than just the qualifying rows of a table are locked. These situations might occur in complex multi-table joins that are also joined using hash- or merge joins. Please see the paper “SELECT for UPDATE feature in NonStop SQL/MX 3.6” for a detailed description.
• 14.
  NonStop Advanced TechnologyCenter Database consistency in SQL/MX -14- Because SELECT for UPDATE can also be used by embedded SQL programs that want to lock the “current of cursor” row, the new behavior must be enabled by setting the control directive DC_SELECT_FOR_UPDATE to ‘1’. NonStop SQL will skip over any locked rows when the SKIP LOCKED option is used. The NOWAIT option however currently requires a statement optimizer “WITH CQD” hint as shown below. select empno, sal, job from emp e, dept d where e.deptno = d.deptno and loc = 'NEW YORK' and job = 'MANAGER' for update of e.sal WITH CQD IF_LOCED ‘RETURN’; Control directives in SQL/MX As mentioned in the previous sections, the behavior of queries can be influenced by CONTROL directives. SQL/MX has two types of controls: CONTROL TABLE and CONTROL QUERY DEFINE or CQD in short. They have similar functionality, however there are some differences. CONTROL TABLE directives are –as the name implies- table oriented, and typically contain a table name or the all (*) wildcard symbol. CONTROL TABLE is useful if such a setting is required at a high level, such as for all, or a defined set of tables in an application. The CONTROL TABLE directives would be used at the data source level. For example: When a CONTROL TABLE * IF_LOCKED ‘RETURN’ directive is active, a program will never wait on a lock but return a locked status immediately if a row lock is encountered on any table in the database. Alternatively, CONTROL TABLE EMPLOYEE TIMEOUT ‘100’ will change the default lock-wait-time from 60 seconds to one second for the employee table. CONTROL directives remain active until they are reset. CQDs are more fine-grained and are useful to use as hints within SQL statements. CQDs do not refer to specific table names. When used in SQL statements as hints (using the WITH keyword), they apply only to that statement and need not be reset. For example: SELECT * FROM EMPLOYEE WITH CQD IF_LOCKED ‘RETURN’; will return the locked status immediately if SQL/MX encounters a locked row, and this is true only for this statement. Alternatively, SELECT * FROM EMPLOYEE WITH CQD TIMEOUT ‘100’; will wait for a lock to be released for about one second instead of the default 60 seconds.
• 15.
  NonStop Advanced TechnologyCenter Database consistency in SQL/MX -15- Conclusion Database consistency can be achieved by using MVCC as well as with SQL/MX locking methods. There is not one “superior” method, but application developers must know which method is used by the DBMS otherwise they will create applications that fail to keep the database consistent. The MVCC method of returning read-consistent, committed data as it exists at one point in time may appear to be an advantage because writers do not block readers. The amount of locks that are placed is limited, as only writers need to lock rows. Even serializable reads do not require locks because data from rows being modified are retrieved from the UNDO logs. The down side however, is that an application developer needs to be aware that data retrieved may be outdated and therefore should not be used to make important decisions. The STABLE ACCESS for updatable cursors mode in SQL/MX provides a short-lived row lock to ensure that a row is not updated by another transaction between a FETCH of the row and a following update of that row. With the release of SQL/MX 3.6, porting applications that use SELECT FOR UPDATE has become much simpler, since the same method can be used in SQL/MX. A specific CQD, DC_LOCK_FOR_UPDATE must be set to ‘1’ to enable this. The support in SQL/MX to read uncommitted data makes it possible to avoid waiting for locks and at the same time retrieve information that may become committed very shortly afterward. The isolation level can be defined per query or subquery in a transaction, which makes it possible to pick the desired level at all times. In Oracle one must set the isolation level on a per transaction basis. In summary, the key difference is that SQL/MX shows current data as it almost certainly will be (unless the transaction aborts) which makes it ideal for applications where having up-to-date information is essential, and Oracle MVCC shows data that is stale and may be committed to new values soon. References https://en.wikipedia.org/wiki/Isolation_%28database_systems%29#Default_isolation_level Oracle® Database Concepts 11g Release 2 manual NonStop SQL/MX Reference Manual “Expert Oracle Database Architecture” by Thomas Kyte. “SELECT for UPDATE feature in NonStop SQL/MX 3.6”, by Frans Jongma.
• 16.
  NonStop Advanced TechnologyCenter Database consistency in SQL/MX -16-