Are you facing slow database performance and frequent lock chains caused by the high concurrency of DML statements on a central table in your system? Do you find yourself dealing with Primary Key Violation errors and deadlocks due to multiple different processes accessing the same table? If yes, then check out this solution that can help you resolve these issues and significantly improve your system's performance.
The "Asynchronous Ledger" method is a great solution to the problem of long lock chains and primary key violations that can happen due to high concurrency. The solution requires minimal changes to the existing system, and by creating only 3 new database objects, the throughput of INSERTs, UPDATEs, and DELETEs can be improved significantly. These 3 database objects are:
An INSERT-only "Buffer Ledger" table.
A "Staging" table with an identical structure.
A "Buffer Synchronization" stored procedure that would be executed separately (i.e. by a scheduled SQL Agent job).
However, there is a delay in the visibility of the data in the final production table, which could be a problem in some cases.
Overall, this is a useful solution to consider for those facing performance problems with high-concurrency OLTP processes.
To learn more about the "Asynchronous Ledger" trick for SQL Server, I encourage you to read the full blog post on my website: