I am using a Scala application with Ucanaccess in an environment were occasionally the MS Access Application is accessing the Database while the Scala application is accessing it as well. I have a few questions about this topic since we had problems with data corruption. I have read the following blog post (https://ucanaccess.blogspot.de/2017/07/multiuser-concurrent-write-access.html https://ucanaccess.blogspot.de/2017/07/multiuser-concurrent-write-access.html).
Is it only problematic if the MS Access Application is writing to the same table as Ucanaccess? Or is it generally problematic writing to the same database file? In our case the Scala App is only writing to one table and reading from the others, while the MS Access Application is used to manage the other tables.
At the end of the blog post you are writing: "only one process writes to the database while all other processes just read it". How would it work to use this approach? Is it a simple configuration in the Access DB? Would the codebase need changes?
Are there possibilities to fix concurrency problems via some kind of locking? If yes, do you have examples of how to achieve this?
We are generally really happy how Ucanaccess is working for us and would love to stick with the current solution, yet we feel that some data corruption issues we have are linked to the multi user environment in which our database lies in. We are willing to have some sort of negative consequence for using Ucanaccess in a multi user environment, let it be performance or something else...
Is it only problematic if the MS Access Application is writing to the same table as Ucanaccess? Or is it generally problematic writing to the same database file?
Concurrent writes to the same file can be problematic. For example, assume that both the UCanAccess app and the MS_Access app are connected to the database file. The UCanAccess app can perform an INSERT on [table1] that requires a new data page to be allocated. Then, the MS_Access app can perform an INSERT on [table2] that also requires a new data page. The ACE/Jet database engine is unaware that the UCanAccess app has allocated the new data page, so ACE/Jet may try to allocate the same page.
In the case where the UCanAccess app writes to [table1] and reads others, while the MS_Access app writes to the other tables but never writes to [table1], then the write contention could be mitigated by storing [table1] in a separate file and using linked tables to see all of the tables from one place. However, that still would still leave the problem of the MS_Access app not being able to see the [table1] changes performed by the UCanAccess app unless the MS_Access app closed and re-opened its connection.
Managing which apps read and/or write to a given file is just a function of how the apps have been coded. MS_Access itself has an option to open a database read-only but that applies to the database as a whole, not individual tables within that database.
And unfortunately, no, table locking cannot be used to manage the concurrency because the two processes are still completely independent of each other.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi guys,
I am using a Scala application with Ucanaccess in an environment were occasionally the MS Access Application is accessing the Database while the Scala application is accessing it as well. I have a few questions about this topic since we had problems with data corruption. I have read the following blog post (https://ucanaccess.blogspot.de/2017/07/multiuser-concurrent-write-access.html https://ucanaccess.blogspot.de/2017/07/multiuser-concurrent-write-access.html).
Is it only problematic if the MS Access Application is writing to the same table as Ucanaccess? Or is it generally problematic writing to the same database file? In our case the Scala App is only writing to one table and reading from the others, while the MS Access Application is used to manage the other tables.
At the end of the blog post you are writing: "only one process writes to the database while all other processes just read it". How would it work to use this approach? Is it a simple configuration in the Access DB? Would the codebase need changes?
Are there possibilities to fix concurrency problems via some kind of locking? If yes, do you have examples of how to achieve this?
We are generally really happy how Ucanaccess is working for us and would love to stick with the current solution, yet we feel that some data corruption issues we have are linked to the multi user environment in which our database lies in. We are willing to have some sort of negative consequence for using Ucanaccess in a multi user environment, let it be performance or something else...
I am looking forward for your answers,
Thanks!
Concurrent writes to the same file can be problematic. For example, assume that both the UCanAccess app and the MS_Access app are connected to the database file. The UCanAccess app can perform an INSERT on [table1] that requires a new data page to be allocated. Then, the MS_Access app can perform an INSERT on [table2] that also requires a new data page. The ACE/Jet database engine is unaware that the UCanAccess app has allocated the new data page, so ACE/Jet may try to allocate the same page.
In the case where the UCanAccess app writes to [table1] and reads others, while the MS_Access app writes to the other tables but never writes to [table1], then the write contention could be mitigated by storing [table1] in a separate file and using linked tables to see all of the tables from one place. However, that still would still leave the problem of the MS_Access app not being able to see the [table1] changes performed by the UCanAccess app unless the MS_Access app closed and re-opened its connection.
Managing which apps read and/or write to a given file is just a function of how the apps have been coded. MS_Access itself has an option to open a database read-only but that applies to the database as a whole, not individual tables within that database.
And unfortunately, no, table locking cannot be used to manage the concurrency because the two processes are still completely independent of each other.