As Novulo applications are running on databases, typical issues with database locking can occur.
In real world situations, people can experience waiting times when users (or processes) read and write in the same tables at the same time.
Database locking exists to prevent so-called ‘dirty reads’.
- When one user is writing data in a table, other users need to wait reading until the writing is done
- When one user is reading data in the table, other users that want to write data, need to wait until reading is done
- Multiple reads at the same time are allowed
See also:
- Isolation (database systems) - Wikipedia for a comprehensive view
- Lock record, exclusive lock, database lock and model lock
Table locks: users wait before reading data
When you are the only user, this will practically never occur. When the application is used by many users, it can happen often, especially in this case.
- User 1 performs a long read transaction, like exporting data which takes 2 minutes;
- User 2 comes after 1, and wants to write data in this same table. User needs to wait 2 minutes until user 1 has finished it’s process;
- User 3 comes after 2, and wants to read the date in that same able. User 3 now needs to wait until user 2 has finished writing, so will also wait for 2 minutes before data is being displayed at all.
This gives a slow experience for user 2.
Possible solutions
By default, SQL Server is run in Read Committed mode. This prevents ‘dirty reads’ but thereby creates these kinds of locks.
To resolve this, there are various options:
- Make your slow queries faster. It sounds easy, but optimizing queries by optimizing Novulo Expressions, applying caches or avoiding unnecessary queries will always help your applications performance and reduce infrastructure costs.
Besides that, there are a few options within and outside Novulo
- Run the SQL database in Read Committed Snapshot Isolation. This is the default on Azure. It does require more server hardware, so needs testing on your application before switching, but will for a large part eliminate locking.
- For the Export engine, you are able to set individual Export Datasets to Isolation level: Read uncommitted. This is an indicator which will tell the database server, that as long as this query is running, write actions do not need to wait. This can lead to inconsistent data in your export, for example a half-processed update. For cases like exporting hourly feeds with all product data for e-commerce, this is typically a trade-of you want to make. It can be found at Export Dataset level (so for each table) > Show more > Advanced:
- For all grids in the application, you can set that they must run readuncommitted through a web.config key:
key: gridpanel.selectwithreaduncommitted
value: true