X++ Debugging Tips and Tricks #4 – Reading uncommitted data


 X   Debugging - tall 2

This next trick in the X++ Debugging Tip and Tricks series is invaluable when stuck deep in a debugging session, and want to verify that the SQL statements being executed has the intended behavior.

For illustrative purposes, I’ve created a small job. It updates the customer group to “40” for the customer with account number “4001”. This happens inside a transaction. I’ve also added a breakpoint statement to jump to the debugger inside the transaction.

When running the job the debugger opens:

image

Now, if we go the SQL Server Management Studio and execute a select statement to validate that the field was correctly updated, we will notice that it still has the old value:

image

This should not be a surprise, as the transaction has not been committed yet. However; that shouldn’t block our noble purpose. By adding the keyword “nolock” to the query, we instruct SQL to read the uncommitted data, and voila, we get the results we expect:

image

One additional piece of advice is related to more complicated SQL statements: You have to add the nolock keyword to every joined table.

This can be a true life saver when debugging. In fact I used this heavily the past few weeks where I’ve been working on the new Warehouse Management module in R3.

Comments (2)

  1. Bostjan says:

    If you need to use it on every table in the query, just put SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in front of everything. I use that instead of WITH (NOLOCK), less typing required.

  2. mfp says:

    Thanks Bostjan. You are right this is an alternative way to achieve the same. For sake of completeness the scope of "SET TRANSACTION ISOLATION" is the current connection, and you'll have to reset it in case you want the standard behavior back.