How to: Read uncommitted records using Dynamics 365 for Operations
Recently we had a challenge to read uncommitted data from the Dynamics 365 for Operations database. Usually this is not required as you may retrieve dirty data. When using a business application, you should rely on correct and committed data as otherwise reporting might provide you incorrect data. However, if you find a very good reason to change the isolation level temporary, this post will guide you how to achieve this.
What is database isolation level?
First of all, I’m not a technical database specialist. Thanks to several troubleshooting on performance and finding gains in Microsoft Dynamics AX (and in my previous life, also NAV), I learned something about locking and data isolation. A recommended read would be the documentation from Microsoft: Isolation Levels in the Database Engine and Understanding Isolation Levels.
It is measured that the Microsoft Dynamics AX database performs the best using the option READ_COMMITTED_SNAPSHOT. You can read more about this on the next page: Configuring the Microsoft Dynamics AX business database.
Reasons to read uncommitted data
When you want to run a report while other users are attempting to insert and update records, you must ensure that data will be consistent. E.g. when updating a packing slip, first the sales order lines are being updated; then the sales order header. In opposite when you run a report, the header is retrieved first; then the lines. If you first print the header as still being an open order, you don’t want to have the lines already updated with the status and remaining quantities. For this reason, data which will be updated (header and lines) will be isolated until the transaction is complete.
Sometimes, you want to read data which has not been committed yet. A reason would be having a look at intermediate data when performing larger insert or update sets. As mentioned before, the downside is that there are risks of reading dirty data.
Challenge with Dynamics 365 for Operations
When we had an on-premise SQL server, it is possible to use statements to set connection properties in X++ or C# coding. As we have to deal with an Azure SQL server database, we cannot use this. Also, I understood that select statements containing the keyword WITH NOLOCK is not supported on Azure SQL databases. The only option to read uncommitted data seems to be using a statement like:
set transaction isolation level READ UNCOMMITTED select * from myTable
But then the 64-million-dollar question: How can we do this using X++ statements? And moreover: How to translate an advanced query object containing multiple data sources and ranges with wildcards?
Continue reading for your answer…
Execute simple SQL statement
To execute a simple SQL statement, you can use the next coding for reading uncommitted data:
str sqlStatement = 'set transaction isolation level READ UNCOMMITTED\n' + 'SELECT * FROM MYTABLE'; UserConnection connection = new UserConnection(); Statement statement = connection.createStatement(); ResultSet resSet; new SqlStatementExecutePermission(sqlStatement).assert(); resSet = statement.executeQuery(sqlStatement); CodeAccessPermission::revertAssert();
The result set (resSet) contains the data retrieved from the database. You have to extend the coding to read the data from the result set. An example is provided below. Note that you are executing the command directly. When you need to retrieve data from a single company, you have to specify the filter with a WHERE clause yourself.
Execute advanced query object
Suppose you have a (packed) query object which contains multiple data sources and some more advanced queries. For example, a query with customers from several regions and a date range between January 1 and April 30. On forehand, you don’t know the number of data sources and query ranges. Good to know, there is one x++ statement getSQLStatement() which will do the correct translation for you. This method has been introduced in Microsoft Dynamics AX 2012 R2. This method is used by the Data Import Export Framework which also needs to get the statement and alter it for some reasons.
If you use this statement, it will convert a query to an SQL statement, initially using parameters for the range values such as the DataAreaId. For this reason, also the parameters needs to be converted to literal range values. Now, given some complexity, use the next X++ statements to perform the SQL statement with reading uncommitted data:
str sqlStatement; str sqlQuery; str sqlUncommitted = 'set transaction isolation level READ UNCOMMITTED'; UserConnection connection = new UserConnection(); Statement statement = connection.createStatement(); ResultSet resSet; // Assume the _queryRun variable was received as method parameter // First set the query to send literal values instead of parameters _queryRun.literals(1); sqlQuery = _queryRun.query().getSQLStatement(); sqlStatement = strFmt('%1\n%2', sqlUncommitted, sqlQuery); new SqlStatementExecutePermission(sqlStatement).assert(); resSet = statement.executeQuery(sqlStatement); CodeAccessPermission::revertAssert(); // Loop and read the result set while (resSet.next()) { // If the indexes in the resSet variable match the column indexes, use the next statements RecId recId = resSet.getInt64(1); GroupId groupId = resultSet.getString(2); Description description = resultSet.getString(3); ... ... }
This is one example how to read the data. It is also possible to make this more flexible by using the ResultSetMetaData class. Using this class, you can find out the number of columns and find column names to convert the values in your own variables.
Warnings/considerations
I like to conclude my post with some warnings and notes for consideration.
- Reading uncommitted data results in dirty reads and data might be inconsistent.
- Only use it when really required and standard execution of queries needs te altered to read not committed data.
- When all query range values are translated to literals, SQL server will have more query plan compilations, rather than re-using query plans form cache (stolen pages).
- Executing direct SQL statements will bypass all user related security done by the application, such as table permissions and eXtensible Data Security (XDS).
That’s all for now. Till next time!
Leave a Reply
Want to join the discussion?Feel free to contribute!