How to query Synapse data and avoid the 'Operating system error code 12(The access code is invalid.).' locking of tables issue - help, please?

Anon101 51 Reputation points
2022-12-22T10:30:54.773+00:00

Hi,
I'm struggling to understand a method to query Synapse data without receiving the 'Operating system error code 12(The access code is invalid.).' error. This is now becoming a serious obstacle and I would like your help to understand how to bypass it for my needs.
I have Power BI (PBI) reports that call SQL Views that reside in a Serverless SQL pool in Synapse. These SQL Views query the External Tables held within Synapse's 'Lake database'. When performing a PBI data refresh, or developing a PBI report which triggers Power Query data evaluations, the refresh will fail due to the refresh taking place at the same time a Dynamics update is being pushed to Synapse. It seems any processes against Synapse will be stopped so Synapse can receive the Dynamics update.

Question 1 - Is the stopping of processes against Synapse, when Synapse receives a Dynamics update, expected behaviour? Data Export Services, which is what Synapse is replacing, didn't have this problem so I would be surprised if its replacement (Synapse) was to designed to have this issue.

Question 2 - I need to understand how I can query/retrieve Synapse data without it failing due to this Dynamics/Synapse locking issue. Is there a suitable method to the following needs?
I have seen the suggestion of using the _partitioned tables. I understand these _partitioned tables are updated hourly and therefore the occurrence of Synapse locking will be less. This is still not an option for me as I need to refresh/develop throughout the day and not keep facing this problem on the hour (or throughout each hour if the updates are staggered per Entity table).
I've also read the option of querying the Azure Data Lake Storage Gen2 CSV files. On this option I have two further questions:
a) When I view each CSV file, for each Entity, I find they have no header row. Without this header row I cannot query the columns via my SQL Views. How do I produce the header row in each CSV file?
b) The below link suggests that even when querying the CSV files, I will still face the locking issue. Is this true, would using the OPENROWSET command against the CSV files still incur the Dynamics/Synapse locking issue?
https://learn.microsoft.com/en-us/answers/questions/988935/cannot-bulk-load-file-error-code-12-azure-synapse.html

I've read the method of importing data from Dynamics to Synapse using 'Append only' mode, but this will bring in the historical changes which will greatly increase our data volume, which will incur greater Serverless SQL pool querying costs. This isn't viable as it will be costly.

Please can someone help me understand how to query Synapse data throughout working hours, when our Dynamics implementation is continuously updated, so I can refresh/build my PBI reports? I just do not understand how the standard approach to querying Synapse's table faces this locking issue.

Thanks in advance.

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,252 questions
0 comments No comments
{count} votes

Accepted answer
  1. ShaikMaheer-MSFT 37,566 Reputation points Microsoft Employee
    2023-01-16T05:02:51.52+00:00

    Hi @Anon101 ,

    Let's summarize the issue.

    1. Dataverse is exporting data in the lake in CSV format. CSV is plain file format that not ACID compliant and cannot have transactional/consistency guarantees. If a writer lease/lock a file or modifies it while a reader is reading it, the reader will get wrong results or lock/access error. That's the reality because the CSV files are not RDBMs.
    2. Users have two options to query files:
    • Querying snapshots of files which will give them delayed consistently and no errors.
    • Querying real-time version only if they are just appending data. There is still a risk of getting access error if a writer puts a lock on a file while the serverless is reading it, or if a writer updated content of file. INSCONSITENT reads should mitigated this problem in some cases, but users must be sure that their write process is not updating the files. In case of update this is not applicable.
    1. Soon, there are chances of releasing synapse link for Dataverse that will read exported files in Delta lake format. Delta is ACID compliant, and it will solve most of the problems on the read part. However it is still not available.

    Please consider filling a support ticket it's not working even with inconsistent reads.

    Hope this helps.


    Please consider hitting Accept Answer button. Accepted answers help community as well.


1 additional answer

Sort by: Most helpful
  1. ShaikMaheer-MSFT 37,566 Reputation points Microsoft Employee
    2023-01-12T08:06:52.0366667+00:00

    Hi @Anon101 ,

    Thak you for posting query in Microsoft Q&A Platform.

    I feel using SQL tables instead of serverless external tables here may helpful. Kindly try them if its feasible in your case.

    Also, I am trying to check with internal team they have any suggestions on this. Will get back to you with updates once I here back. Thank you.

    Please let me know how it goes.