12/10/2023 0 Comments Sql deadlock enterprise vault![]() ![]() The user & login is dropped as the session ends and this is a secure way of running app instances.īut as we have implemented this in large scale, we are seeing issues with the magnitude of grants being generated.Ĭlearly, we can't create indexes around system tables to help alleviate this. The vault app is designed to create credentials on the fly. Unfortunately, there is not a whole lot of documentation on the specifics around 'METADATA' locks. ![]() I want to prove the theory that subresource 'PERMISSIONS' is incompatible with 'SECURITY CACHE' and hence the deadlock. ![]() Extended events 'lock_acquried' gives information about the resource 'METADATA' but not subresource. The data from sys.dm_tran_locks is very transient in nature and not able to capture the data that I need. I am trying to see if there is a way to check the locks that are held on the 'PERMISSIONS' subresource. I would be more inclined to gain control over that process that spits out all those Sommarskog I am not sure that it is worth the effort, though. Looking at the procedure, I think it is possible to rewrite it to not use dynamic SQL, but instead use a static query with OPTION (RECOMPILE). I don't know the details about the security cache, but it makes sense that permissions are cached in some structure that is faster to read than the system tables. Obviously, since permissions are stored in the database, SQL Server needs to read them just like any other data. And that exception does not apply here, because the dynamic SQL is a stored procedure of its own without any owner, so ownership chaining does not apply. However, so much is clear, for every query there has to be a permission check, with one exception: the query is inside a stored procedure, and the table accessed have the same owner as the stored procedure. I will have to admit that I don't know the internals of these operations. GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON SCHEMA :: sharedproxy TO There is no documentation around xml nodes transaction name any where.Ĭould someone please throw some light on why sp_executesql is accessing PERMISSIONS for a simple insert into #temp table. This is interfering with SECURITY CACHE from process 1. Now from the deadlock graph we can see that sp_executesql holds a SCH_M lock on METADATA: database_id = 39 PERMISSIONS(class = 0, major_id = 0) under the transactionname 'read permissions' Process 2 runs a stored proc mar_MarriageView_Search which has dynamic sql that uses sp_executesql to insert into a #temp table. Process 1 holds a SCH_M lock on the resource 'SECURITY CACHE' under the transaction name 'SEC Cache Coherency' Here the process1 runs the following GRANT statements We are seeing a high volume of deadlocks since we introduced Vault process that gives permissions on schema. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |