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:
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:
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:
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.