Inside Dynamics AX 4.0: Transaction Semantics

[Blog bot]

Источник: http://feeds.feedburner.com/~r/Insid...semantics.html
==============

The X++ language includes the statements ttsbegin, ttscommit, and ttsabort for marking the beginning and ending of database transactions. It is important to understand how the execution of X++ code outside and inside a transaction scope affects the data that is retrieved from the database because of the difference in isolation levels, and also how the transaction scope affects exception handling. This section describes tts-prefixed statements, isolations levels, and exception handling, as well as the two concurrency models that Dynamics AX supports.

This section includes examples of how these X++ statements affect interaction with Microsoft SQL Server 2000. The X++ statements executed in the application are written in lowercase letters (select, for example), and SQL statements parsed to and executed in the database are written in uppercase letters (SELECT, for example). This chapter also includes the use of specific SQL hints and functions. These are not described completely, so you are advised to consult the SQL Server Reference documentation for a detailed description.
An instance of a Dynamics AX table type is both a record object and a cursor object. The remainder of this chapter refers to this combined object as a record buffer.

Transaction Statements

A transaction in X++ starts with ttsbegin and ends with either ttscommit or ttsabort. The use of these statements does not necessarily result in the following equivalent statements being sent to SQL Server 2000: BEGIN TRANSACTION, COMMIT TRANSACTION, and ROLLBACK TRANSACTION. Instead, when a transaction is initiated with a ttsbegin statement, implicit transactions are turned on. The transaction will not start until an SQL Data Manipulation Language (DML) statement is executed, so it will start when SELECT, UPDATE, INSERT, or DELETE is executed. When ttscommit or ttsabort is executed, the equivalent statements COMMIT TRANSACTION and ROLLBACK TRANSACTION execute only if a transaction has been initiated. This is illustrated in the following X++ code, in which the comments show the SQL statements that will be sent and executed by the database. The remaining code samples in this chapter contain the same notation, with the SQL statement shown as comments.

You can, however, have nested levels of transaction blocks to accommodate encapsulation and allow for reuse of business logic. This involves the notion of transaction level, also known as ttslevel, and nested transaction scopes involving inner and outer transaction scopes.

Note
Consider a class developed to update a single customer record within a transaction. This class would contain a ttsbegin/ttscommit block, which states the transaction scope for the update of the single instance of the customer. This class can be consumed by another class, which selects multiple customer records and updates them individually by calling the first class. If the entire update of all the customers were executed as a single transaction, the consuming class would also contain a ttsbegin/ttscommit block, stating the outer transaction scope.


When X++ code is executed outside a transaction scope, the transaction level is 0. However, when a ttsbegin statement is executed, the transaction level is increased by one, and when a ttscommit statement is executed, the transaction level is decreased by one. Not until the first DML statement is executed after the transaction level has changed from 0 to a higher level will the SET IMPLICIT_TRANSACTIONS ON statement be sent to the database. And only when the transaction level is decreased from 1 to 0 and a transaction has begun will the COMMIT TRANSACTION statement be sent. Assuming that a transaction has begun, the execution of ttsabort causes a ROLLBACK TRANSACTION statement to be sent to the database and the transaction level to be reset to 0.

The following example illustrates the use of nested transactions and TRANSACTION statements sent to the database, as well as the changes in the transaction level.

Note
The current transaction level can always be queried by calling appl.ttslevel(). The returned value is the current transaction level.

It is important that the number of ttsbegin statements balance the number of ttscommit statements. If the Dynamics AX application runtime discovers that the ttsbegin and ttscommit statements are not balanced, an error dialog box (shown in below image) is presented to the user, or an error with the following text is written to the Infolog: "Error executing code: Call to TTSCOMMIT without first calling TTSBEGIN."

An unbalanced transaction level error

Note
It might be necessary, in the event of an unbalanced TTS error, to log out of the Dynamics AX client to reset the transaction level. This would also roll back the started transaction in the database.

Isolation Levels

When Dynamics AX is installed running on a SQL Server 2000 database, two different isolation levels are used. The first isolation level is READ UNCOMMITTED, which is used when the Dynamics AX application runtime executes outside a transaction scope. The second is the READ COMMITTED isolation level, which is used when a transaction scope is entered. The default isolation level for every database process opened by the application runtime is READ UNCOMMITTED; when the first ttsbegin statement executes, the isolation level in the process changes to READ COMMITTED, and when a ttsabort statement or the final ttscommit statement executes, the isolation level in the process switches back to READ UNCOMMITTED. The change of isolation levels is accomplished by executing the following statements in SQL Server 2000: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED, SET TRANSACTION ISOLATION LEVEL READ COMMITTED.

Note that changes in isolation levels always occur when the first ttsbegin statement, the first ttsabort statement, and the final ttscommit statement execute. The following code is identical to the previous example, except that the comments now track the changing isolation levels.

The main reason for using the READ UNCOMMITTED isolation level outside a transaction scope is to prevent readers from getting blocked behind writers. If the Dynamics AX application runtime used only the READ COMMITTED isolation level inside and outside transactions, any selection of records from a table that is currently being updated by another process and not yet committed would be blocked because the other process would have an exclusive lock on the record. Note that this is the default behavior when using SQL Server 2000. To understand how Dynamics AX uses other databases, "Source Code Changes Required for Upgrade."

However, using the READ UNCOMMITTED isolation level implies that uncommitted changes made by other processes are not isolated from the process that executes an uncommitted read from the database. You should avoid manipulating the database with information that is read uncommitted because the read information could potentially be rolled back or be in an inconsistent state. If you want to manipulate the database, the information used should be a committed version; it is important to re-read data inside the transaction scope or use other means to ensure that inconsistent or rolled back data is not used. This subject is discussed in greater detail in the section titled "Concurrency Models," later in this chapter.

Note
Transactions are generally defined as having four properties, known as ACID (atomicity, consistency, isolation, durability) properties:
Atomicity: Every operation in the transaction is either committed or rolled back.
Consistency: When committed, the transaction should leave the database in a consistent state.
Isolation: Uncommitted changes are not visible to other transactions.
Durability: After a transaction is committed, the changes are permanent, even in the event of system failure.

Any scenario in Dynamics AX may use more than one database process when executing. The Dynamics AX application runtime may use both a process with a READ UNCOMMITTED isolation level and another process with a READ COMMITTED isolation level within the same scenario. This generally occurs when a READ UNCOMMITTED process is used where an open cursor still exists and the application runtime needs to start a transaction. In such a situation, the application runtime uses an additional READ COMMITTED process for the execution of statements within the transaction block.

This is illustrated in the following example, in which it is assumed that all records in custTable are not fetched immediately when executing the select statement because of the number of records in the table. This means that the cursor for selecting the custTable records is not closed when the ttsbegin statement executes. The application runtime, therefore, uses an additional process to update the vendTable record.





Enforcing Uncommitted Reads in Transactions

As stated at the beginning of this section, readers can get blocked behind writers when using the READ COMMITTED isolation level in SQL Server 2000. You can, however, select uncommitted data within a transaction scope even though the isolation level is set to READ COMMITTED. You accomplish this by executing selectLocked(false) on a record buffer before selecting any rows with it. This adds the NOLOCK hint to the SELECT statement, which is parsed to the database; consequently, uncommitted records are read, and the reader is not blocked.

The previous code example can be changed so that all changes to the vendTable records are committed on a set basis instead of row by row, while still reading uncommitted custTable records, as shown here.



As explained earlier, enforcing uncommitted reads should be done with great care and consideration.

Note
The use of selectLocked(false) has no impact when used outside a transaction scope. It will not enforce a committed read within a READ UNCOMMITTED isolation level.
Implicit Transactions
As explained earlier, implicit transactions are turned on when the first DML statement executes within a transaction. Similarly, implicit transactions are turned off when the first DML statement executes after the database process is used again outside the transaction scope. This is done by issuing the SET IMPLICIT_TRANSACTIONS OFF statement in the database. This causes any insert, update, or delete statement sent to the database in this mode to be automatically committed. Although possible, it is generally not advisable or a best practice to execute these statements outside a transaction scope because these statements are committed instantly to the database. This prevents you from rolling back the database later in the event of an error.

Transaction IDs

Each transaction in Dynamics AX may be given a unique transaction ID by the Application Object Server (AOS). However, the AOS supplies such an ID only if one of the following circumstances is true:

• A record is inserted into a table in which the CreatedTransactionId property is set to Yes.
• A record is updated on a table in which the ModifiedTransactionId property is set to Yes.
• The X++ code explicitly requests a transaction by calling appl.curTransactionsId(true).

The AOS Process Pool

The AOS does not open a new process in the database every time a process is needed. An open process that is no longer needed is placed in a pool of processes from which the AOS selects when it needs an additional process. The processes in the pool use a READ UNCOMMITTED isolation level and have implicit transactions turned off after they are used, unless they will be used to start a transaction. If the intention is to start a transaction, the isolation level is immediately changed and reverted before it is released to the pool.
http://insidedynamicsax.blogspot.com/
</img> </img> </img> </img> </img>


Источник: http://feeds.feedburner.com/~r/Insid...semantics.html