Ingest: SQL allow dirty reads

This is an issue with many ERPs that work through central transaction tables and expect other applications to work with read uncommitted isolation levels. Having this option is important.

It would also be great if reading FROM the ODX was not blocked during import. In many cases it could be possible to skip waiting and return the last/previous full dataset.

@Steven: Can you please check if your request has been resolved by installing the latest version?

I found this in the release notes:

Fixed in ODX 20.10.21

• 10782: Deadlock on executions from ODX
  Fixed potential deadlock issue when using an ODX SQL Storage and using the same database as the data warehouse database in the MDW.

Hi Alex, thanks. Do you know if we can update ODX without updating TX itself? We only have one ODX environment (production) and I'd love to update it but can't touch the TX server anytime soon.

NB: This is a general question, I don't believe that fix affects us since we are using ADLS gen 2 storage for the ODX. Here, there are no "dead"locks - just very long waiting periods if the ODX is doing an import. (Technically this would be a livelock)

@Steven: In general, the versions of ODX and TX should match, as there may be API version changes.

This fix will have no impact on the ADLS storage. I will pass your suggestion to R&D for review.

It would be good to have NOLOCK available as it can improve performance and latency issues for users who work in an application. Any news on this?

Ran into this last week. ERP system is in heavy usage during the day and the business wants up to date analytics data as well. Hence we we're using the NOLOCK/'Dirty reads’ option. This is not available in TDI, however, so we end up with failing executions because of deadlocks. Cost/benefit wise we're okay with small temporary differences in the data because of the dirty reads.