What are the rules for AutoCommit, CHAINED, procxmode in ASE MobiLink?

[Breck_Carter]

When using MobiLink 16 with ASE 15.5 or 15.7, what are the rules for preventing ASE from incorrectly auto-committing database changes made by MobiLink scripts that both (a) execute SQL INSERT, UPDATE and DELETE statements directly and (b) call ASE stored procedures to execute those statements?

In particular, how should the ODBC AutoCommit option, ASE SET CHAINED statement, and ASE sp_procxmode calls be used, or not used?

EVEN MORE IMPORTANT... How can one tell if something is wrong? Running WITH AutoCommit is like NOT wearing seatbelts, you really don't know anything is amiss until you hit something.

---

Research

---

1. Some due diligence was performed...

No results found for "set chained" mobilink site:sap.com
Your search - "sp_procxmode" mobilink site:sap.com - did not match any documents.
Your search - "procxmode" mobilink site:sap.com - did not match any documents.
4 irrelevant hits for "autocommit" "mobilink" "ase" site:sap.com

---

2. The following mlsrv16.exe error message appears when (I think) SET CHAINED OFF has been explicitly used, but everything else appears "to work":

I. 2015-07-07 15:53:18. <1> (,u1) System event on synchronization connection:
                        save tran  it1 
I. 2015-07-07 15:53:18. <1> (,u1) Translated SQL:
                        save tran  it1 
E. 2015-07-07 15:53:18. <1> (,u1) [-10002] Consolidated database server or ODBC error:  ODBC: [Sybase][ODBC Driver][Adaptive Server Enterprise]Attempt to issue 'SAVE TRANsaction' when there is no active transaction.
                         (ODBC State = ZZZZZ, Native error code = 628)

---

3. The following (very FRIGHTENING) mlsrv16.exe warning message appears no user-specified SET CHAINED statements have been executed. I assume this is happening on a connection that is different from the one(s) used to run synchronizations; is a similar thing displayed when a script exists for the begin_connection_autocommit connection event? http://dcx.sybase.com/index.html#sa160/en/mlserver/begin-connection-autocommit.html

I. 2015-07-09 19:20:10. SQL Anywhere MobiLink Server Version 16.0.0.2052
...
I. 2015-07-09 19:20:10. <main> System event on the notifier stage connection:
                        SELECT @@spid
I. 2015-07-09 19:20:10. <main> Translated SQL:
                        SELECT @@spid
I. 2015-07-09 19:20:10. <main> Locking/blocking detector connection with connection ID 'SPID 26' has been established
I. 2015-07-09 19:20:10. <main> Notifier stage connection with connection ID 'SPID 30' has been established
I. 2015-07-09 19:20:10. <main> ODBC isolation set to: Read Committed
I. 2015-07-09 19:20:10. <main> ODBC isolation set to: Read Committed
W. 2015-07-09 19:20:10. <main> [10050] ODBC: [Sybase][ODBC Driver]AutoCommit option has changed to true. All pending statements on this transaction (if any) are committed.  (ODBC State = 01000, Native error code = 32024)

---

4.The MobiLink 16 setup doesn't have any SET CHAINED statements (possibly a moot point), but it does have these sp_procxmode calls...

C:\\Program Files\\SQL Anywhere 16\\MobiLink\\Setup\\syncase.sql
exec sp_procxmode 'ml_model_drop_unused_schema', 'unchained'
exec sp_procxmode 'ml_model_drop', 'unchained'
exec sp_procxmode 'ml_[EVERYTHING ELSE]', 'anymode'

---

5.Interestingly, the MobiLink 12 setup contains this...

C:\\Program Files\\SQL Anywhere 12\\MobiLink\\Setup\\syncase.sql
exec sp_procxmode 'ml_[EVERYTHING]',       'anymode'

---

6.It may mean nothing OTHER THAN perhaps a bad example, but the MobiLink 12 wizard generates a "SET chained off" statement in the SQL script that loads MobiLink scripts:

1> /------------------------------------------------------------------------------
2> * ML Install Script generated 2015-05-13 12:16:34 for Adaptive Server Enterprise
3> *                     (Consolidated) by MobiLink 12 Plug-in
4> -----------------------------------------------------------------------------*/
5> 
6> COMMIT
7> SET chained off
8> SET quoted_identifier on

[ end ]

[Former Member · Accepted solution]

In an old email, I found the following description for ASE commit behavior:

1. CHAINED OFF + AUTOCOMMIT ON => commit every statement including statements inside stored procedure calls
2. CHAINED OFF + AUTOCOMMIT OFF => commit every statement including statements inside stored procedure calls
3. CHAINED ON + AUTOCOMMIT ON => commit every statement but only one commit per stored procedure calls
4. CHAINED ON + AUTOCOMMIT OFF => manual commits

#4 is used for MobiLink events (except begin_connection_autocommit) so that MobiLink can control the transactions. The begin_connection_autocommit event was added for ASE in case one needs to do unchained statements, such as DDL to create a temporary table, when a sync connection is created. For all other events, transaction mode is CHAINED, and any called stored procedures should be defined as "chained" or "anymode" by sp_procxmode.

FYI, the ASE OBDC driver sets CHAINED ON when the MobiLink server turns off autocommit.

[Breck_Carter · Accepted solution]

It goes without saying that should be in the docs... but not every instance of "Watcom does things the way they should be done" is documented... perhaps the thinking is that "even a cow knows" stuff like this? 🙂

[Former Member · Accepted solution]

It's not spelled out explicitly, but there is a caution to not do explicit or implicit transaction management, ruling out unchained transaction mode (since it implicitly commits each DML command).

I added a DCX comment to hopefully get the ASE specifics in the MobiLink documentation.

[VolkerBarth · Accepted solution]

I guess those users aware of "auto-commit" as such are not always aware that there are both server-side ("chained") vs. client-side auto-commit features, and that it takes both cited options set accordingly to get the desired behaviour... - so I highly second Breck's suggestion to add that to the docs.