on 2020 Aug 10 7:56 AM
As more people are working with one of our applications we seem to have phantom locks occurring more and more.
I think it has something to do with 2 processes that are part of the application. One is fetching changes from another database on Tanks/Orders. The other one is assigning a Tank to an Order.
The application is running on the MMP.db database. All the data in that database is copied from the TransFusion.db database. Every night the base data is copied from the TransFusion.db to the MMP.db. Regarding the Tank/Order data it is pulled from the transfusion.db by the users.
In the transfusion database we register when changes on a Tank/Order have occurred. In the application when we assign a Tank to an Order we check whether we still have the correct data by checking if a change occurred on the Tank/Order in the transfusion database. If a change has occurred for either the Tank or the Order the assignment can not be done because the data is no longer up to date and the criteria might have changed for assigning the given Tank to the given Order.
Applying the changes can only be done by 1 user at the time and it will delete the Tank/Order in the MMP database and will fetch the new data from the transfusion database. However assigning a Tank to an Order is not prohibited during this process. So I guess when assigning a Tank to an Order and the given Tank and/or Order is removed and added again during this applying the changes might cause these phantom locks.
I'm not sure if this is causing the phantom locks, but the process of applying the changes is hanging when we have a phantom lock. I then kill the connection causing the phantom lock and then everything seems to be working fine again for a given time. It can happen multiple times per day but it also doesn't happen at all.
My question is how can I determine when a phantom lock occurs what statement is causing the phantom lock. I'd rather solve the issue then killing the connection every now and then.
I know that with profiling enabled you can see deadlocks and what statements created the deadlock. Can something similar being achieved with phantom locks? Is it als possible to do this without profiling enabled with some queries?
Request clarification before answering.
Here's a general-purpose query you can run when there's a problem...
-- Display the blocked connection(s). SELECT victim.Number AS blocked_Number, victim.Name AS blocked_Name, CONNECTION_PROPERTY ( 'isolation_level', blocked_Number ) AS blocked_isolation_level, victim.BlockedOn AS blocked_by_Number, culprit.Name AS blocked_by_Name, CONNECTION_PROPERTY ( 'isolation_level', blocked_by_Number ) AS blocked_by_isolation_level, victim.LockRowID, victim.LockIndexID, victim.LockTable, victim.LockObject, victim.LockObjectType, CONNECTION_PROPERTY ( 'LastStatement', blocked_Number ) AS blocked_statement FROM sa_conn_info() AS victim INNER JOIN sa_conn_info() AS culprit ON victim.BlockedOn = culprit.Number WHERE victim.BlockedOn <> 0; blocked_Number,blocked_Name,blocked_isolation_level, blocked_by_Number,blocked_by_Name,blocked_by_isolation_level, LockRowID,LockIndexID,LockTable,LockObject,LockObjectType, blocked_statement 2,'SalesManager','0', 3,'Accountant','3', 0,,'GROUPO.Departments','GROUPO.Departments','TABLE', 'insert into "GROUPO"."Departments" ( "DepartmentID","DepartmentName","DepartmentHeadID" ) values ( 700,''Major Account Sales'',902 ) '
The first query tells you the culprit is the "Accountant" connection, and the blocked statement is an INSERT INTO Departments being run by the "SalesManager" connection.
You can use that information to tailor the second query to show you all the candidate locks that may be responsible; change the table and blocking connection names.
This tailoring may be necessary because in a production environment, sa_locks() may return thousands of rows.
-- Display the lock(s) held by the blocking connection(s). SELECT sa_locks.conn_name, sa_locks.table_name, sa_locks.lock_class, sa_locks.lock_duration, sa_locks.lock_type, IF sa_locks.row_identifier IS NULL THEN '[no row_identifier]' ELSE STRING ( sa_locks.row_identifier ) END IF AS row_identifier FROM sa_locks() AS sa_locks WHERE sa_locks.conn_name = 'Accountant' AND sa_locks.table_name = 'Departments' AND sa_locks.lock_class <> 'Schema' ORDER BY sa_locks.conn_name, sa_locks.lock_type, sa_locks.row_identifier; conn_name,table_name,lock_class,lock_duration,lock_type,row_identifier 'Accountant','Departments','Position','Transaction','Phantom','[no row_identifier]' 'Accountant','Departments','Row','Transaction','Read','42205184' 'Accountant','Departments','Row','Transaction','Read','42205185' 'Accountant','Departments','Row','Transaction','Read','42205186' 'Accountant','Departments','Row','Transaction','Read','42205187' 'Accountant','Departments','Row','Transaction','Read','42205188' 'Accountant','Departments','Row','Transaction','Read','42205189'
The Phantom lock must be the culprit because its the only lock with no row identifier, which matches the first query which returned zero in LockRowID.
Possible solutions are (a) add a COMMIT to the Accountant connection or (b) switch to snapshot isolation.
The Read locks are a result of Accountant running with isolation level 3... in other situations, these may cause row blocks.
You can use the magic ROWID() function to find all the rows affected by the Read locks; the second query then becomes this monstrosity :)...
-- Display the rows locked by the blocking connection(s). SELECT sa_locks.conn_name, sa_locks.table_name, sa_locks.lock_class, sa_locks.lock_duration, sa_locks.lock_type, IF sa_locks.row_identifier IS NULL THEN '[no row_identifier]' ELSE STRING ( sa_locks.row_identifier ) END IF AS row_identifier, IF row_lock_ident.DepartmentID IS NULL THEN '[no row]' ELSE STRING ( row_lock_ident.DepartmentID ) END IF AS DepartmentID, IF row_lock_ident.DepartmentName IS NULL THEN '[no row]' ELSE DepartmentName END IF AS DepartmentName FROM sa_locks() AS sa_locks LEFT OUTER JOIN ( SELECT * FROM sa_locks() AS row_lock, LATERAL ( SELECT * FROM GROUPO.Departments WHERE ROWID ( Departments ) = row_lock.row_identifier ) AS row_ident ) AS row_lock_ident ON row_lock_ident.row_identifier = sa_locks.row_identifier WHERE sa_locks.conn_name = 'Accountant' AND sa_locks.table_name = 'Departments' AND sa_locks.lock_class <> 'Schema' ORDER BY sa_locks.conn_name, sa_locks.lock_type, sa_locks.row_identifier; conn_name,table_name,lock_class,lock_duration,lock_type,row_identifier,DepartmentID,DepartmentName 'Accountant','Departments','Position','Transaction','Phantom','[no row_identifier]','[no row]','[no row]' 'Accountant','Departments','Row','Transaction','Read','42205184','100','R & D' 'Accountant','Departments','Row','Transaction','Read','42205185','200','Sales' 'Accountant','Departments','Row','Transaction','Read','42205186','300','Finance' 'Accountant','Departments','Row','Transaction','Read','42205187','400','Marketing' 'Accountant','Departments','Row','Transaction','Read','42205188','500','Shipping' 'Accountant','Departments','Row','Transaction','Read','42205189','600','Foreign Sales'
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Still having issues with phantom locks.
The first query mentioned has no result.
The second query after adjustment has this result:
conn_name;table_name;lock_class;lock_duration;lock_type;row_identifier 'SQL_DBC_af5ffb0';'Tank';'Position';'Position';'Phantom';'2762604549' 'SQL_DBC_af5ffb0';'Tank';'Row';'Position';'Read';'2762604549'
So the Phantom lock and the read lock occur on the same row_identifier. But what does that mean?
I have several examples every day of users having these same 2 records with a phantom lock and a read lock on the same row_identifier.
BTW Update 1 for Foxhound 5 fixes how phantom locks are displayed; see EBF 1: Bug Fix: The Block Reason field now correctly shows "Position Transaction Phantom lock" for p...
Open this in a new tab to see the result...
User | Count |
---|---|
52 | |
8 | |
5 | |
5 | |
5 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.