Tag Info

New answers tagged

9

I posted this to pgsql-bugs and the reply there from Tom Lane indicates this is a lock escalation issue, disguised by the mechanics of the way SQL language functions are processed. Essentially, the lock generated by the insert is obtained before the exclusive lock on the table: I believe the issue with this is that a SQL function will do parsing (and ...


3

Assuming you run another statements before calling new_customer, and those acquire a lock that conflicts with EXCLUSIVE (basically, any data modification in the customer table), the explanation is very simple. One can reproduce the problem with a simple example (not even including a function): CREATE TABLE test(id INTEGER); 1st session: BEGIN; INSERT ...


1

Your assumption is correct. Nevertheless, you need to pay attention to what is being locked. Both transactions are locking space id 10787 page no 139 n bits 176 index PERMISSION_ID of table IDENTITY.ROLE The DB Connections 12287 and 12279 ran one INSERT each Here is what I notice: In each INSERT, you are running SELECT DOMAIN_ID FROM DOMAIN WHERE ...


0

I suppose this graph is coming out of SQL Profiler. Profiler behaves this way. it cannot display multi-victim deadlocks correctly. If you pick the very same deadlock from the system_health extended events session (no extra trace necessary as this one is running by default), you can get the correct visual within Extended Events Viewer. Also you can use this ...


4

The is a supplement to the answers already posted by DBAFromTheCold and Aaron Bertrand. Microsoft has still left %%lockres%% as undocumented feature. Below is the script that will help you : declare @databaseName varchar(100) = 'yourdatabaseName' --CHANGE HERE ! declare @keyValue varchar(100) = 'KEY: 9:72057632651542528 (543066506c7c)' --Output from ...


5

Sorry, was already working on this answer and about to post when the other appeared. Adding as community wiki only because it's a slightly different approach and adds a bit of other info. The 543066506c7c is essentially a hash of the primary key, and you can retrieve that row (and potentially any rows with a hash collision) using this dynamic SQL: -- ...


8

You have the hobt_id so the following query will identify the table:- SELECT o.name FROM sys.partitions p INNER JOIN sys.objects o ON p.object_id = o.object_id WHERE p.hobt_id = 72057632651542528 From that you can then run the following statement to identify the row in the table (if it still exists):- SELECT %%LOCKRES%%, * FROM [TABLE NAME] ...


7

Using OPTION (LOOP JOIN) isn`t suitable since it costs almost 15% more than MERGE JOIN The cost percentages displayed in showplan output are always optimizer model estimates, even in a post-execution (actual) plan. These costs likely do not reflect actual runtime performance on your particular hardware. The only way to be sure is to test the ...


1

Deadlock happens when one query acquires a lock on a object (rows, data pages, extent, tables etc) and other resource tries to access it. Smallest unit in SQL Server is data pages and SQL holds a lock on page while working on it. So, yes it is possible that two select statement can create deadlock. Solution: WITH(NOLOCK) - If your data is fine to be read ...


0

Here is a quick definition of a Deadlock Query 1 has locked Table 1 and wants access to Table 2 Query 2 has locked Table 2 and wants access to Table 1 This creates a situation that can never be resolved. Each query is going to wait until it can get access to the other table until either the query is canceled, the connection killed or the instance shut ...


0

I know someone who wrote a Perl program that woke up every few minutes to read the new lines of the alert log and send an email if there are errors. If you have grid you can probably configure that to notify if there are deadlocks.


0

That could be caused by unindexed foreign keys. Tom Kyte has a query that you can use to find them. It could be as simple as adding a few indexes on the child tables. Unindexed Foreign Keys



Top 50 recent answers are included