I work for a saas company that uses multi tenancy by a tenant_id, however we perform nightly jobs that dump mass amount of data from the last day of each tenant, sometimes we face deadlocks or simply locks that makes all operations run slow due to shared tables between tenants. Are there any alternatives to this problem besides using a DB per tenant to avoid DUI locks due to shared tables?
1 Answer
Without specific details on individual examples of locking and / or deadlocking issues you're facing, there's not much of a dedicated answer that can be provided. If you're aware of a specific case or two that is particularly troublesome, especially with the deadlocks, providing the queries, DDL of the entities involved, the deadlock graphs, and the execution plans, in a targeted question, would all be very useful to trying to improve those particular cases.
That being said, a general potential fix you could look into is changing the isolation level on the database to something more conducive to your locking tolerance. By default SQL Server uses the Read Committed isolation level, which employs pessimistic concurrency - essentially writers blocking readers and other writers during data changes. One particular isolation level you might be interested in is called Read Committed Snapshot Isolation aka RCSI.
RCSI helps reduce locks during concurrency of DELETES and UPDATES with SELECT statements by using row versioning to preserve the data at different points in time. This comes at the tradeoff of additional resource overhead on the server, generally with tempdb. It is considered optimistic concurrency by nature.
You may find these additional readings on RCSI informative:
-
As i said in the post, it is a scenario of multiple DUI(over millions) on the same tables from different tenants,even if a deadlock doesnt occur, the sql server still run these queries slowly due to the fact it is many bulk DUI at the same time, so I wonder If theres any alternatives besides one db per customer to deal with this problem. Each client of my saas depends upon these night jobs to update data from their last day– betoFeb 17, 2022 at 12:24
-
@beto Can you clarify what you mean by
DUI? Your post asks how to reduce / improve locking and deadlock issues, which my answer provides a global way that is sometimes recommended for doing that, without needing to break out your database into multiple databases. If you're having other performance issues occuring that are unrelated to locking / deadlocking then that's a separate question we'd need more details on, and using multiple databases wouldn't help you with anyway.– J.D.Feb 17, 2022 at 12:41 -
Deletes, Updates and Inserts. Im sure using multiple databases would help cause tenants wouldnt be competing with eachother for locks during these night jobs that our bussiness needs to perform, your solution might help– betoFeb 17, 2022 at 20:15
-
@beto Sorry I feel like you're not understanding what I'm saying: "If you're having other performance issues occuring that are unrelated to locking / deadlocking...using multiple databases wouldn't help you with anyway." It sounds like your issues with high DUI concurrency is locking related, in which case my answer provides you a global potential solution without having to split up your database. If anything is unclear about that, please let me know.– J.D.Feb 17, 2022 at 22:22
sql-serverplease also update them with which version and edition you're using as well.