|
I want to access one database from another, but they both are on the same database server. So when I try do it by Proxy tables I can't create remote server: "[Sybase][ODBC Driver][SQL Anywhere]Unable to connect, server definition is circular". Please help. |
|
Here's an sample that works... "%SQLANY12%\Bin32\dbinit.exe" ddd1.db "%SQLANY12%\Bin32\dbinit.exe" ddd2.db "%SQLANY12%\Bin32\dbspawn.exe" -f "%SQLANY12%\Bin32\dbeng12.exe" -n ddd ddd1.db ddd2.db "%SQLANY12%\Bin32\dbisql.com" -c "ENG=ddd;DBN=ddd1;UID=dba;PWD=sql" "%SQLANY12%\Bin32\dbisql.com" -c "ENG=ddd;DBN=ddd2;UID=dba;PWD=sql" --------------------------------------------------------------------- -- On ddd2 CREATE TABLE t ( pkey INTEGER NOT NULL PRIMARY KEY, data INTEGER NOT NULL ); INSERT t VALUES ( 1, 2 ); INSERT t VALUES ( 2, 2 ); COMMIT; --------------------------------------------------------------------- -- On ddd1 CREATE SERVER ddd2_server CLASS 'SAODBC' USING 'DRIVER=SQL Anywhere 11;ENG=ddd;DBN=ddd2'; CREATE EXTERNLOGIN DBA -- optional for SQL Anywhere TO ddd2_server REMOTE LOGIN "DBA" IDENTIFIED BY 'sql'; CREATE EXISTING TABLE proxy_t AT 'ddd2_server...t'; SELECT * FROM proxy_t ORDER BY proxy_t.pkey; pkey,data 1,2 2,2 Thanks! That worked ;D
(08 Mar '12, 09:29)
nemo
|
|
Trying to access the same database using proxy tables that resides on the same server can cause undetectable deadlocks and could cause your connection (or database server) to hang/wait forever and therefore doing this is not recommended.... and is why the server detects this case and disallows it. [correction] If a proxy table refers to the same database from which the request was made then this is a circular connection and the database server will disallow the request. A deadlock can occur in this scenario because the originating connection could have a lock on a row or table which blocks the transaction on the proxy connection and hence the proxy request would never succeed. If multiple connections were to do the same or similar operations then eventually all of the server's worker threads would be consumed and the entire server would hang. Therefore circular proxy connections are disallowed. Since you are getting a circular connection error your proxy table definition must be incorrect - it must be pointing back to the same database from which it came. Check your SQL to make sure that you are connecting to the correct (second) database. 1
Huh??????? I think you're talking about web servers and web clients, a bogus limitation that nonetheless actually exists :)
(08 Mar '12, 09:21)
Breck Carter
Replies hidden
Comment Text Removed
Thanks Breck... I have corrected my answer.
(08 Mar '12, 09:36)
Mark Culp
What prevents the deadlock from being detected in this case? There are two connections involved, the originating connection, and the circular proxy connection... what makes this pair of deadlocking connections any different from some other pair of deadlocking connections? The whole argument for "no circular proxy connections" depends on such a deadlock not being detected... has anyone ever TESTED that to see if it is true?
(08 Mar '12, 09:47)
Breck Carter
Replies hidden
...(and can :)
(08 Mar '12, 09:48)
Breck Carter
Why would anyone want a circular proxy connection? Because some general-purpose code has been written to handle a process involving two databases, and sometimes it is needed for the situation where the "two databases" are actually one and the same. In particular, it is QUITE ANNOYING that Foxhound cannot monitor itself... I have to futz with another copy, running on different db and http ports... all because some Nanny has decided that I need to be protected from non-existent deadlocks.
(08 Mar '12, 09:52)
Breck Carter
1
Breck: As you know there are two types of deadlocks which are detected: (1) thread deadlock which occurs when all of the server worker threads are blocked waiting for a lock or other resource which is held by another worker/connection and (2) transaction or cycle deadlock which happens when a cycle of locks are being held between connections - e.g. conn1 gets lockA, conn2 gets lockB, conn1 then attempts to get lockB but blocks because conn2 has the lock, and then conn2 attempts to get lockA but will block because it is held by conn1 and therefore fails with a deadlock error. In the proxy table scenario the thread deadlock condition can be easily detected but the second type (transaction cycle) cannot - e.g. conn1 gets a lock on resourceA and then does a remote/proxy operation, conn2 attempts to get a lock on resourceA but blocks because conn1 is hold the resource... and we're deadlocked because conn1 will never relinquish the lock on resourceA because it is waiting for the remote/proxy operation to complete which it won't ever do because it is actually conn2 which is blocked. Note the "resource" does not have to be a table/row lock, it could be blocked on waiting for a checkpoint or commit operation to complete or other nefarious operation deep in the depths of the server.
(08 Mar '12, 15:10)
Mark Culp
|