Used SA 12 (12.0.1 EBF 3311 on Linux; EBF 3324 on Windows). We have seen lock tables (using sa_locks() stored procedure). Than we try to drop some connections using DROP CONNECTION 'CONNID' command or using 'Disconnect' option in Sybase Central Connections folder. In both case it seems that connection is dropped, but when we check again connections exist and 'Last Request Type' is Fetch. Moreover we have restart application (java server based on JBOSS), connection still appears in connection list and in sa_locks() result. In Sybase Central 'Overview' folder message 'The number of requests waiting to be fullfilled: 6' appears. We will not stop/kill database server, want understand what is caused to this problem. Size of database is around 25G, running on Linux machine. Query/reports is running on tables having 173M and 63M records. Please help, explain drop connection problem

asked 05 Oct '11, 13:54

HBrener's gravatar image

HBrener
426212333
accept rate: 0%

1

What information does sa_conn_info give you about the connection that has the locks?

From the sa_conn_info information, can you determine where the connection is coming from? Is is from your application? What API is it using (ODBC/DBLIB/SQL Anywhere JDBC/JConnect/etc)?

After you drop the connection does the same connection number still appear in sa_conn_info? What about a minute or two after the drop? Does the same connection still appear in the sa_conn_info list a couple minutes after the application that creates the connection is restarted?

(06 Oct '11, 08:40) Ian McHardy

We are using JDBC connection (Our server application based on JBOSS java server). After drop connection in sa_conn_info() result appears this dropped connection and related children internal connection. May be these internal connections make problem to close parental connection. It is something new in SA12 that connection with request type 'Fetch' make many internal connections?!

(06 Oct '11, 14:30) HBrener
Replies hidden

Are you saying after the drop connection, the connection no longer appears in sa_conn_info() or that the connection does still appear in sa_conn_info()? In SA 10 and up, the server may use a parallel plan to execute a query, in which case there will be multiple internal connections with connection_property( 'Name' ) of 'INT:Exchange'.

FYI, you have only answered my question about the API and the application. You haven't answered my other questions in my original comment.

(07 Oct '11, 09:37) Ian McHardy

After drop connection and waitind 1-2 minutes we can see this dropped connection in sa_conn_info() result,and as mention in my previous comment this connection have children connections, appears in result of sa_conn_info() too

(09 Oct '11, 07:41) HBrener

There is a known issue (CR #702733 - 11.0.1.2786, 12.0.1.3713) which allows connections to be 'undroppable' in rare circumstances (usually involving a parallelized query that makes use of a HashFilter). Setting the connection option 'MAX_QUERY_TASKS' to '1' or restarting the database server is a workaround for the issue.

For more information about this issue, see my response to this question here: http://sqlanywhere-forum.sap.com/questions/6164/connections-that-cannot-be-dropped/6197

permanent link

answered 30 Mar '12, 14:20

Jeff%20Albion's gravatar image

Jeff Albion
10.7k171174
accept rate: 24%

I would just like to add we are running SQL Anywhere 12.0.1.3759 and are still experiencing the same problem with connections that cannot be dropped. We've had a Sybase trouble ticket opened since April 2012 on the matter. If I get a solution I will post and let people know.

(02 Oct '12, 19:03) ScottN
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:

×412
×7

question asked: 05 Oct '11, 13:54

question was seen: 2,070 times

last updated: 02 Oct '12, 19:03