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?

  • I think one database per tenant is a good design pattern for data management and more accurate data statistics, but can be more work from a schema management perspective. That being said, knowing what database system, version, and edition (when applicable) would be good for us to try to help solve your problem.
    – J.D.
    Feb 17, 2022 at 1:39
  • We use SQL server right now, but im considering any approach to resolve this problem since I really want to know which are my alternatives
    – beto
    Feb 17, 2022 at 1:41
  • 1
    I've updated your tags to include sql-server please also update them with which version and edition you're using as well.
    – J.D.
    Feb 17, 2022 at 1:46

1 Answer 1


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:

  1. An answer by David Browne regarding RCSI
  2. Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide - By Kendra Little on Brent Ozar
  3. How to Choose Between RCSI and Snapshot Isolation Levels - By Kendra Little
  • 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
    – beto
    Feb 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
    – beto
    Feb 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.