VistaDB 6 Introduces Faster Single Application Locks
For more information on what’s new in VistaDB 6 see What’s Coming in VistaDB 6
VistaDB has always offered several different access modes to help applications improve performance depending on how they intend to use the database. The traditional modes let you pick between having a single connection (Exclusive access) or multiple connections and read-only or read-write access.
When Exclusive access is used, VistaDB didn’t need to do any locking since there couldn’t be any contention. This is by far the fastest way to not just change data but query it. This is why we’ve always recommended the Exclusive modes for best performance when doing bulk inserts or updates.
When a Shared access mode is used, VistaDB has to make sure that no two connections conflict with each other when doing updates. This requires both readers and writers to get locks - readers to indicate what they’re depending on and writers to make sure they can respect transactions and make their changes only available to other connections once they’re complete. Since VistaDB is an embedded database, it has to guard against multiple processes accessing the database potentially on multiple computers. Ultimately, the only thing every VistaDB engine shares is the database file itself.
File System Locks
VistaDB uses a combination of lock files and range locks within the database file itself to enforce access. This lets all of the engines working with a particular database file coordinate safely, even if the database is located on a network share and accessed simultaneously from multiple processes on multiple computers. Unfortunately, there are two performance issues this creates:
- Lack of Shared Range Locks - Despite the .NET API allowing shared range locks to be specified, the actual OS-level call is always exclusive. This makes it difficult to implement shared reader locks.
- File Operations are Slow - Even on an SSD it takes a long time to create or destroy, lock or unlock a file. Couple that with no shared lock support and the engine has to repeatedly lock and unlock items.
Still, it’s a time-honored approach used by many databases all the way back to COBOL and it works on every platform and in a wide range of deployment scenarios - at worst just getting slower.
A Faster Approach for Single Applications
What if your application never shares a database file between multiple processes? This is particularly common with mobile applications where data is rarely shared between applications locally thanks to extensive security and sandboxing. We still need to ensure multiple connections can read and write to the database at the same time but we no longer need to worry about multiple processes - local or remote - accessing it.
For VistaDB 6 we’ve created two new locking modes - Single Process Read-Only and Single Process Read-Write. When you select these modes the VistaDB engine does all of its locking in memory, using .NET’s synchronization capabilities used to make multi-threaded applications. It doesn’t need to push locks down to the file system as it knows that only one process can have the file open at a time.
This implementation is dramatically faster - at least twice as fast as file system locks, even compared to high performance file systems. The results get even better under contention - not only do in-memory locks acquire and release faster but the new lock manager is designed to hand locks off directly to waiting connections which significantly speeds up acquisition of common locks. The final result? This single change alone yields a 20% to 50% in query times. Even better, the biggest benefit is to queries that can really use it: Ones that touch multiple tables and indexes as they have to acquire and release more locks.
So Good It’s the Default
Since we strongly suspect that most applications only ever have a single process accessing a VistaDB database at a time (either on mobile or a desktop application) we’ve changed the default access mode from Shared Read-Write to Single Process Read-Write. This means many applications will get the performance bump from this just by upgrading to VistaDB 6. If you share a database between multiple processes (local or remote) you need to explicitly specify the new Multi-Process Read-Write mode. The old Shared Read-Write access mode has been mapped to Multi-Process Read-Write so if you specified it explicitly in your connection string you’ll get the same behavior as before with file system locks.