CODEWITHSUNDEEP
sqlsql-serverdatabasetransactionslocking
Maksymilian Majer
Maksymilian Majer

Reputation: 2956

Can SQL Transaction lock rows only from specified view

In a multi-tenant application I have a table with TenantId column. This table is only accessed using a view which selects rows belonging to a single tenant using TenantId stored in CONTEXT_INFO. Is there a way to have transactions only locking data that this view returns for particular Tenant (ie. lock only rows with the same TenantId)?

It will be a performance loss if I lock the entire table when I'm sure the data being inserted/updated/deleted is never going to collide with those that have a different value in the TenantId column.

That same pattern would apply for any other View which limits some table rows using WHERE clause.

Upvotes: 0

Views: 1048

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294277

In a multi-tenant deployment the TenantId column must be the leftmost column in every single index, including the clustered index. Corollary is that you cannot have a a heap in multi-tenant applications.

If you follow this simple rule then, given that every single query must add the TenantId=@currentTenantId predicate, no query will ever 'wonder' outside the tenant data slice in any table. This will ensure that there is never contention between two tenants, since each one is interested in only his portion of the index.

A somehow similar solution can be achieved by using partitioning by TenantId, but given the current 1000 partitions limit and performance degradation inherent in partitioned tables, I would much stronger favor the index key solution.

There are thing that breaks this 'status-quo' like lock escalation (which can be explicitly disabled with ALTER TABLE SET LOCK_ESCALATION) or bulk load table lock (disabled with with sp_tabeloption).

So, with this knowledge, can we revisit your question and reformulate it as 'Can we enforce isolation that a tenant can never lock resources owned by a different tenant?'. The answer is yes, as long as you ensure that there are proper tenant specific access paths in every rowset. Which translates to exactly what I say in my first paragraph: add the TenantId as the leftmost key to every index, including all clustered ones, and don't have any heaps. Small print would follow that solution will work most times, but there will always be specific scenarios in which a tenant can lock resources owned by a different tenant. But to isolate better you'd have to have one db per tenant, which is a nightmare for deployment/maintenance/upgrade.

Upvotes: 4

Related Questions