Query Connection Isolation Levels

[Former Member]

I can do SA_CONN_LOCKS and get the information of all the connections.  I have all the connection numbers.

I then need a way to determine the ISOLATION LEVEL of each of the connections.    We use a different ISOLATION LEVEL and  we want to make sure that isolation level is proper for all connections to stop freezing.

I'm sure I can get the isolation level of the current connection (ITSELF) ---

1. How do I get the isolation level of my own connection?

2. How do I get the isolation levels of all the other connections?  (If I have to do it manually for each connection, that's not an issue for me)

Please help!

We are setting an EXECLUSIVE lock on a table which is causing all the other workstations to freeze, and I'm sure that we are setting the isolation level to stop this from happening when opening all the connections - so for some reason, something is getting set back or the user is creating other custom connections and locking records.

Thank you.

Sybase SQL Anywhere 12.01

[Former Member]

In terms of this issue, it makes no sense.

The query ran fine for 5+ years at hundreds of places, in 3-10 seconds.  Once upgrading, it takes more than 60 minutes to run and your staff told us it was that the system was doing so many query plans - etc. etc. etc.   They offered many suggestions and it did not resolve the issue.

I rewrote the query myself using a Temp Table, which reduced the one join from my query where I did:

and rooms.bedtype in (select roomtype from #TEMPTABLE) - and then it ran now in 3 seconds.

BOTTOM LINE IS THIS:

* The issue is resolved using a temp table

* No query (regardless of how poorly it is written) takes 60 minutes to run!!

* This query ran as originally written at hundreds of places every single night without issue until we upgraded to latest Sybase SQL Anywhere

It's not a production issue anymore because I rewrote it myself - and the suggestions given by your staff did not help.  But, more importantly, we see a considerable amount of performance issues now with our clients - where jobs that have been running for 10+ YEARS that took seconds are now taking 38 and 60 minutes.

There is obviously an issue - see other posting I have done.

We are looking at downgrading these people to the older SQL Anywhere to see if this resolves the issue. 

I don't believe that ANY query - no matter how poorly written should take 60 minutes to obtain a record set of a few records.  Yea, we have it now running using a temp table and then removing ONE JOIN --- but still, it doesn't make sense when this query, TODAY is running at other properties without the modification, without issue, within seconds.  We upgrade their Sybase SQL Anywhere to the latest build - and goes from seconds to an hour.  And also, if we run it on another computer, it runs without issue.

So, obviously it make be an issue with CPU, multi-core CPU's, parallel processing, or something that we have no control over - but needs to be resolved.

[former_member188493]

Situations like this happen from time to time when upgrading database software, regardless of the vendor.

If you want assistance in this forum, please post the offending query and the graphical plan with statistics that runs slowly.

[former_member188493]

You can see the isolation level for all connection number as follows:

SELECT Number, Value FROM sa_conn_properties() WHERE PropName = 'isolation_level' ORDER BY Number;

     Number Value
----------- -----------
          1 0
          2 0
          3 0
          4 0
          5 0
          6 0
          7 0

It sounds like you have a locking and blocking problem, which may or may not have anything at all to do with isolation levels. Foxhound may be able to help investigate a blocking problem...

[Former Member]

I will look at this - but the support we are getting through the SUPPORT channel is sending on on wild goose chases.  (EBF's, Parallelism, etc.)

We run this query at 10 different sites without issue

We run this query in another database (without active users!) and it runs in 3 seconds

We run this query when no other users are on the system and it runs in 3 seconds

We run this query for other dates, and it runs in 3 seconds.

It is just a certain date range (2 days!!!) and it stays there for an hour doing nothing. 

It's obvious it's a locking issue, right??

[former_member188493]

> but the support we are getting through the SUPPORT channel is

> sending on on wild goose chases.  (EBF's, Parallelism, etc.)

Just for the record, I have no connection to the SAP support channel.

It might be a locking issue... that should be easy to determine. Here are some queries that might help:

---------------------------------------------------------------------
-- Show connections and locks.

BEGIN
SELECT * FROM sa_conn_info();
SELECT * FROM sa_locks ( max_locks = -1 ) WHERE lock_name IS NOT NULL ORDER BY lock_name;
SELECT *
  FROM sa_conn_info() AS conn
          INNER JOIN sa_locks ( max_locks = -1 ) AS "lock"
             ON conn.lockname = "lock".lock_name;
END;

[Former Member]

Does this FOXHOUND work with Version 12.01 database?

We have the latest EBF and we are hoping that support is going to help us get to the to the bottom of it - so far, we've not had any luck.  The information that was sent to us about the PLAN really didn't offer a solution ... and as I said, it runs in 3 seconds and the view table has 11 records in it....

[former_member188493]

Foxhound requirements are shown here.

In particular: "Foxhound works with target databases using 5.5 to 16 of SQL Anywhere but the Foxhound engine itself needs the 32-bit or 64-bit non-authenticated version of SQL Anywhere 16.0.0.1915 or later to run."

[jeff_albion]

Hi Robert,

I don't believe that understanding the isolation levels of your connections will help solve your issue:

We are setting an EXECLUSIVE lock on a table which is causing all the other workstations to freeze, and I'm sure that we are setting the isolation level to stop this from happening when opening all the connections - so for some reason, something is getting set back or the user is creating other custom connections and locking records.

Exclusive table locks are only acquired by the LOCK TABLE ... IN EXCLUSIVE MODE or by the LOAD TABLE SQL statements - isolation levels do not affect connections acquiring these types of locks.

Exclusive row locks (write locks) are automatically acquired when inserting, updating, or deleting records and are released at the end of a transaction. These are also not affected by isolation levels.

Exclusive schema locks are acquired any time you want to issue DDL, such as ALTER TABLE and should be released once the operation completes. Again, these are acquired automatically and not affected by isolation levels.

There are also other locks that are acquired automatically when querying tables such as shared schema locks on the tables.

The only type of different locks you will see with isolation levels are related to shared read locks on rows (underneath the cursor or as a cursor moves over rows) at isolation levels 1, 2 and 3, and positional phantom/anti-insert locks on indexes/rows at isolation level 3.

---

How have you determined what type of locks are being held and that they are exclusive table locks? Can you post a sample of your sa_locks() table that you are seeing problems with?

Regards,

Jeff Albion

SAP Active Global Support

[Former Member]

Well, there seems to be a lot of issues here that I need to get to the bottom of.   First, we are setting the IsolationLevel on the Clarion driver level  - and no matter what number we put in there, it always returns zero on the SQL Anywhere level.  This seems to be a Clarion bug / issue which we are going to have to get them involved in.

I am doing this:

SELECT CONNECTION_PROPERTY('Isolation_Level',NUMBER) as 'iso', * FROM SA_CONN_INFO()

When I run that, it always returns zero - even if I set it to different numbers on our driver level - this is the driver for our programming language by Soft Velocity.  We open a connection and after the first read from the configuration table (CONFIG) - we set the IsolationLevel -- I put 0,1,2,3,4 there and ran this statement, and it always returns as 0 in the "ISO" column above.    If they were setting the isolation level properly, would not the above statement show the different numbers?

As for the Exclusive Lock - I run this:

CALL SA_LOCKS(0)

And when this was done at the site when all the other workstations were frozen showed:

user_id DBA

table_name FOLIOTRN

lock_type Exclusive

Everything else showed as "Shared"

There is nothing in our entire system that would do this, and the users have no access to the database, DBA password or anything.

There was a REORGANIZE TABLE FOLIOTRN done about six hours prior to this and the job, but that job would not have run if the following had any records returned:

select table_name from sa_locks(0) WHERE TABLE_NAME = 'FOLIOTRN'

Meaning, we run that first and if it returns anything, we don't REORGANIZE the table because we don't freeze the job.

Again, that ran more than six hours prior on this workstation and that user already signed off and signed back on - so nothing was held up there.

So, what other option would put EXCLUSIVE there as there was definitely no database maintenance being done - and the table isn't all that big of a table (less than 10,000 records)

[Former Member]

Hello,

We are now seeing the same thing on another customer.  Where we run a Stored Procedure, and it's completely locked up.   But, we run it on our computer and it's fine.

I am in the middle of opening a support incident with SAP - and hopefully someone on your level can help us, please.   It seems like we've gone from one compiler to another, and it seems like there is an issue with them (the driver that we are using) --- but getting support from them is not good, and we will need you guys to point us in the right direction to them.   I don't mind opening the support incident.   You'll be able to GotoAssist into our customer with us and see all the issues.  You simply select to run a stored procedure, and it's frozen.  But, if you open SCJVIEW and look at any locks - there are NONE at all.

[jeff_albion]

Hi Robert,

When I run that, it always returns zero - even if I set it to different numbers on our driver level - this is the driver for our programming language by Soft Velocity
...
This seems to be a Clarion bug / issue which we are going to have to get them involved in.

We do support standardized ODBC transaction calls with the ODBC driver via SQLSetConnectAttr (see: ODBC transaction isolation levels), otherwise you will need to use the SET OPTION SQL statement. Maybe this information will help diagnose the isolation level issue with SoftVelocity...?

You simply select to run a stored procedure, and it's frozen.  But, if you open SCJVIEW and look at any locks - there are NONE at all.

It sounds like something internal to the database engine may be holding up the processing of the queries / stored procedure: can you supply the stored procedure definition?

If you can still open Sybase Central and still query other data / run sa_locks(), this implies that the database server is still doing some processing, so it may not be completely locked up at this point.

I am in the middle of opening a support incident with SAP

This sounds like a good plan for a way forward. If it's required, we have also have specialized diagnostic tools available internally in technical support that can help diagnose issues like this, but technical support will need to work with you in order to collect the information from the database server and provide the specialized diagnostic tool to you directly.

Regards,

Jeff Albion

SAP Active Global Support

[Former Member]

Hello,

I have logged the incident with support.  We haven't move much towards resolution on that side, but we have on our side.

Here is the issue.

1. We have a stored procedure.

2. When we run the stored procedure, it's in a locked state (from SCJVIEW or DBISQL)

3.  It shows Fetching records - can run for hours - nothing

4. We remove the line:

JOIN VIEW_ROOMTYPES ON VIEW_ROOMTYPES = HISTHD.PREVROOM

As well as the one line we need it for:

AND VIEW_ROOMTYPES.MTGROOM = 0

The query runs just fine!   It runs in 3 seconds.

If we put those two lines back - the query freezes and can sit there forever.

They asked us to do logging (your SAP SUPPORT) and we did ..... we gave it to them and they came back and told us it could have to do with some known bug called "Issue with parallelism and we can test this by setting the max_query_tasks to 1"

[jeff_albion]

Hi Robert,

I have logged the incident with support.  We haven't move much towards resolution on that side, but we have on our side.

If you're ever finding that an incident needs more attention to it or that you are not receiving an urgent reply from SAP, there is a mechanism to have the SAP Customer Interaction Center involved and for the incident to be flagged at higher support levels, if the production situation warrants it - see: https://websmp106.sap-ag.de/call1sap

The query runs just fine!   It runs in 3 seconds.

If we put those two lines back - the query freezes and can sit there forever.

On the surface, this doesn't sound like a locking issue, but more of a performance issue with those views/tables. Especially if you are using isolation level 0.

They asked us to do logging (your SAP SUPPORT) and we did ..... we gave it to them and they came back and told us it could have to do with some known bug called "Issue with parallelism and we can test this by setting the max_query_tasks to 1"

Yes, after reviewing the incident I can see that we have requested and you have performed and provided us with some request logging, and after reviewing this information we had found that there were large table scans inside queries with intra-query parallelism involved (indicating a performance issue). To diagnose the performance issue further, we have requested graphical plans with statistics from these queries to see in more detail about why we are picking the query plans we are at that time. The optimizer uses statistical analysis to pick plans so therefore we need to understand the statistics behind its decisions.

The above suggestion was provided as an interim diagnostic test in order to determine if the behaviour is related to known fixed issues related to intra-query parallelism on earlier builds of 12.0.1. Providing the specific build number of 12.0.1 would avoid needing to run the diagnostic test.

---

However, if you are convinced that locking is truly the root issue of your problem, please provide the full output of sa_locks() at this time - there should be 11 columns, including the 'lock_type' information which would provide the type of object that is being locked:

conn_name,conn_id,user_id,table_type,creator,table_name,index_id,lock_class,lock_duration,lock_type,row_identifier

If you are over 1000 locks, you will need to increase the 'max_locks' argument to sa_locks().

I have also noted that you have also just provided a response to us in the incident shortly after posting here. Please continue to use the support incident to work with us directly for the diagnostic discussion and your resolution options.

Regards,

Jeff Albion

SAP Active Global Support

[Former Member]

Sorry, that should read:

We are SEEING an EXECLUSIVE lock ...

[JasonHinsperger]

The isolation_level connection property should give you what you want:

select connection_property('isolation_level') from dummy

You can also specify a connection id to get the value for a different connections:

select connection_property('isolation_level', <conn_id>) from dummy

--Jason