SAP SQL Anywhere

Question

I'm currently stuck as to why I cannot get 2 connections working. I have two separate connections in a single c++ program, but when I attempt to commit with the first connection it hangs. I am running SQL Anywhere 17 Development Server on Linux 64bit. The code I that hangs is as follows


#include <iostream>
#include <sqlapi.h>
int main() {
    SAConnection con;
    SAConnection con1;
    SACommand cmd(&con);
    SACommand cmd1(&con1);
try {    con.Connect("links=tcpip(host=10.11.12.17;port=49153);databasename=ftnode_sa;servername=ftnode_sa", "sadb", "sadb", SA_SQLAnywhere_Client);
  con1.Connect("links=tcpip(host=10.11.12.17;port=49153);databasename=ftnode_sa;servername=ftnode_sa", "sadb", "sadb", SA_SQLAnywhere_Client);
  con.setIsolationLevel(SA_ReadCommitted);
  con1.setIsolationLevel(SA_ReadCommitted);
  cmd.setCommandText("BEGIN TRANSACTION");
  cmd.Execute();

  std::cout << "After first BEGIN" << std::endl;

  cmd.setCommandText("insert into test1 (x,y) values (1,'red4')");
  cmd.Execute();

  cmd.setCommandText("insert into test1 (x,y) values (2,'Blue4')");
  cmd.Execute();

  cmd1.setCommandText("BEGIN TRANSACTION");
  cmd1.Execute();

  std::cout << "After 2nd BEGIN" << std::endl;

  cmd1.setCommandText("insert into test1 (x,y) values (1,'red4')");
  cmd1.Execute();
  std::cout << "After first INSERT" << std::endl;

  cmd1.setCommandText("insert into test1 (x,y) values (2,'Blue4')");
  cmd1.Execute();
  std::cout << "After second INSERT" << std::endl;

  cmd1.setCommandText("delete from test1 where x=1");
  cmd1.Execute();

  std::cout << "Before first Commit" << std::endl;
  con1.Commit();
  std::cout << "After first Commit" << std::endl;

  cmd.setCommandText("delete from test1 where x=1");
  cmd.Execute();

  std::cout << "Before 2nd Commit" << std::endl;

  con.Commit();
  std::cout << "After 2nd BEGIN" << std::endl;

  std::cout << "Transaction completed sucessfully.\n";
}
catch(SAException &x) {
  std::cout << "Error : " << (const char*)x.ErrText() << "\n";
}
return 0;



};

I tried increasing the number of max connections using:

set OPTION PUBLIC.max_connections=20

But it had no effect, still hangs on first commit.

I have however managed to successfully open over a dozen dbisql connections and run sql on each connection.

When I start the server the following appears:

$ ./sastart.sh SQL Anywhere Network Server Version 17.0.4.2053 Developer edition, not licensed for deployment. Copyright (c) 2016 SAP SE or an SAP affiliate company. All rights reserved. Use of this software is governed by the SAP Software Use Rights Agreement. Refer to http://global.sap.com/corporate-en/our-company/agreements/index.epx.

Connection limit (licensed seats): 3

So it states that there is a connection limit. Does that mean I the server is limited to 3 connections regardless what max connections is set to ? If so, why am I able to open more than 3 dbisql sessions ?

If the limit is indeed 3 for SQL Anywehere 17 Development Server, and modifying max connections has no effect, does the same apply to SQL Anywhere 16 Evaluation Server, for which a key is needed and an evaluation period of 60 days applies ?

Answer 1

1

What does dbisql reveal for sa_conn_info when the “hang“ appears?

Please show that, as well.

In my understanding, you will run into a deadlock, as both connections insert rows and then try to delete them (including the one inserted by the other connection), so both block each other.

This is somewhat identical to the following SQL script in DBISQL (with option auto_commit = 'Off':

create table if not exists test1 (
   x numeric(1,0),
   y varchar(20)
);

set temporary option isolation_level = 1; -- read committed
insert into test1 (x,y) values (1, 'red4');
insert into test1 (x,y) values (2, 'Blue4');
message 'After two inserts on connection ' || connection_property('Number') to console;
waitfor delay '00:00:10';  -- wait 10 seconds to make both script executions overlap
delete from test1 where x=1; -- omitting that statement will prevent deadlock
message 'Before Commit on connection ' || connection_property('Number') to console;
commit;
message 'After Commit on connection ' || connection_property('Number') to console;
commit;

When you run that script in 2 DBISQL windows in parallel, the DELETE will lead to a deadlock (SQLCODE -308), and you have to drop the according connection.

Note, the deadlock will not appear if you leave out the delete statement on one of the scripts or if both connections try to insert rows with different x values (1, 2) vs. (3, 4) and then try to delete x=1 vs. x=3.

In the sample, the isolation level does not really matter, as writers always block writers.

permanent link

answered 18 Aug '17, 07:47

Volker Barth
40.2k●361●550●822
accept rate: 34%

edited 18 Aug '17, 07:53

3

This is a blocking issue due to a write lock on row (1,'red4') inserted by connection 2 and uncommitted. With a read committed isolation, the write lock blocks delete from t where x=1.

If you monitor both sa_conn_info() and sa_locks(), you can observe the write locks for the newly inserted rows which for connection 1 are released at the commit. When the delete is executed, it blocks (not a hang). The BlockedOn and LockRowID is populated in the sa_conn_info() output for the affected connection (delete operation). The value of the LockRowID is mapped to the sa_locks() row_identifier column. You will see that the block row has a write lock_type. If you commit or rollback on connection 2, the blocking will be released and the delete will proceed.

(18 Aug '17, 11:08) Chris Keating

Replies hidden

Hi

I appreciate that this is somewhat of a late reply, but thought I'd give an update to clarify the confusion. I previously posted an example that results in a deadlock, which confused the issue.

The real problem appeared to be the fact that there are two versions of the 'libdbcapi' library in ASA17 installation and one of them is a re-entrant variant.

As I am performing concurrent work in several threads, the program kept hanging. So after emailing the SQLAPI++ support they came back to me and told me to explicitly select the thread safe (_r) library, as follows;

con->setOption(_TSA("SQLANY.LIBS")) = _TSA("/opt/sqlanywhere17/lib64/libdbcapi_r.so");

All works fine now. What confused me is that it is not necessary to specify the thread safe version of a library for both Oracle and PostgreSQL, but it seems one has to for SQL Anywhere.

(27 Aug '17, 14:40) bluefrog

Comment Text Removed

As I am performing concurrent work in several threads

Glad you got it working, but I guess without showing us the "real" case, it's difficult for us to help you find the "real" cause... (Yes, I'm aware that it's not necessarily easy to know the real problem when posting the problem, that's what such a forum is also all about:))

For threaded applications on Linux/Unix, see the general deployment information here.

(27 Aug '17, 16:21) Volker Barth

flat view

SAP SQL Anywhere

c++ hanging with 2 connections

Follow this question

Related questions