c++ hanging with 2 connections

[Former Member]

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 ?

[VolkerBarth]

What is your program's output?

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

Am I right that you have wrote your own wrappers like SACommand around SQL Anywhere' C API (via dbcapi.dll)?

[reimer_pods]

"Connection limit (licensed seats): 3" means, up to 3 different machines (seats) will be able to connect to the server. This doesn't limit the number of connections you can open simultaneously. That's why the number ob ISQL sessions may exceed 3.

[VolkerBarth]

set OPTION PUBLIC.max_connections=20

IMHO, that option has a different usage. It's available to limit the connection number for that particular database whereas the "licensed" limit restricts the number of connections to any database running on the according database server.

That being said, I don't think your issue has to do with connection numbers but possible with blocking or threading.

[Former Member]

I am using the SQLAPI++ library.

Both connections exist in the main thread. Each Command object (SACommand) is coupled with a unique connection.

Is it perhaps the "BEGIN TRANSACTION" ?

[VolkerBarth]

Please answer my first two questions.

Also, do you use Auto commit mode with the SQLAPI++ lib?

(Obviously, I'm not familiar with that lib...)

And what would be the expected outcame, as you possibly insert identical values from both connections - AFAIK, without knowing the schema of table test1?

[Former Member]

The output is:

$ ./sa_connect_test2
After first BEGIN
After 2nd BEGIN
After first INSERT
After second INSERT
# at this point not more output, and I have to use kill -9

The table has no keys or indexes:

$ /opt/sqlanywhere17/bin64s/dbisql -c "uid=sadb;pwd=sadb;eng=ftnode_sa;links=tcpip(host=10.11.12.17;port=49153);LOG=conn.log;dbn=ftnode_sa" "describe test1"
Column Type         Nullable Primary Key 

x      numeric(1,0)        1           0 
y      varchar(20)         1           0 
(2 rows)

I have modified the code to include con.setAutoCommit(SA_AutoCommitOff)
for all connection objects, as you suggested, but the same behaviour is oberved.

[VolkerBarth]

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.

[chris_keating]

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.

[Former Member]

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.

[VolkerBarth]

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.