Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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?

asked 10 Aug '20, 06:56

Frank%20Vestjens's gravatar image

Frank Vestjens
1.3k354765
accept rate: 21%

What is your definition of the term "phantom lock"? Is it related to term "phantom row" described in the SA17 Help here?

Note that the Help describes "phantom lock" as "a shared lock that is placed on an indexed scan position to prevent phantom rows" which is a solution rather than a problem.

(10 Aug '20, 15:35) Breck Carter

It is not a definition of me. It is what Sybase central shows in the Locks tab in the Lock Type column. Normally it will only show Shared, Insert, Intent, Write, WriteNoPK, Read.

When people are complaining that applying changes is hanging we see Phantom locks. Killing the connections with the Phantom locks solves the issue

(11 Aug '20, 01:46) Frank Vestjens
Replies hidden
1

Those locks should only appear at isolation level 3 (aka "serializable"). Do you need to use that high isolation level? - If so, snapshot isolation might be a solution - or shortening the time the according reading connection has the transaction open.

(11 Aug '20, 03:31) Volker Barth

Isolation level is at 0

(11 Aug '20, 03:56) Frank Vestjens
Replies hidden

For all connections? I would not know how reading connections at isolation level 0 could block any insert... (Of course any writer can block any other writer...)

(11 Aug '20, 04:22) Volker Barth
1

See here - according to that

Phantom locks are acquired only by transactions operating at isolation level 3.

(11 Aug '20, 07:28) Volker Barth
1

The victim (blocked connection) may be running at isolation level 0, but checked the culprit (blocking connection); that one is probably running at isolation level 3.

The victim's BlockedOn value contains he culprit connection number.

It's the culprit that has the offending phantom lock.

Your application may need to run the culprit at isolation level 3.

(11 Aug '20, 11:16) Breck Carter
1

The Help topic Position Locks might be helpful.

Also try calling sa_locks(). If it returns zillions of rows, try filtering:

SELECT * from sa_locks() WHERE lock_type = 'Phantom';

SELECT * from sa_locks() WHERE lock_class = 'Position';

(11 Aug '20, 14:13) Breck Carter
1

Having said all that... chances are good that the application running at isolation level 3 is missing a COMMIT. That can happen if the application gives control to the user (keyboard, mouse) while holding locks, something which should never happen in a multi-user environment... because that user can take a lunch break while blocking other folks.

(11 Aug '20, 14:21) Breck Carter
1

Please note also that isolation_levels can be set temporarily to different values, i.e. even when your app defaults to isolation level 0, certain code blocks (via set temporary option isolation_level) or queries (via table hints or the OPTION clause) might use a higher level.

(12 Aug '20, 03:31) Volker Barth
Comment Text Removed
1

Thanks for all the info.

I think we somehow first need to figure out where these phantom locks are occurring. The description I gave is a wild guess. We'll focus on all the input you guys gave to figure out what causes the phantom locks. Then hopefully we'll be able to fix the problem

(13 Aug '20, 01:47) Frank Vestjens
More comments hidden
showing 5 of 11 show all flat view

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'
permanent link

answered 13 Aug '20, 14:16

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

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.

(08 Dec '20, 13:47) Frank Vestjens
Replies hidden

Can you tell us what isolation level the according connections are using (temporarily)? I know you are generally using level 0 but in my understanding that would not create such locks...

(08 Dec '20, 15:41) Volker Barth

If the first query displays no rows WHERE victim.BlockedOn <> 0 then you don't have any blocked connections.

If you don't have any blocked connections, what is the problem?

(the mere existence of a lock is not necessarily a problem)

(08 Dec '20, 16:11) Breck Carter
(08 Dec '20, 16:18) Breck Carter

Please show us "The second query after adjustment".

(08 Dec '20, 16:33) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×29
×21

question asked: 10 Aug '20, 06:56

question was seen: 1,561 times

last updated: 08 Dec '20, 16:52