Understanding set chained command not allowed within multi-statement transaction.

In actual Sybase ASE applications, especially in ASE + J2EE applications, set chained command not allowed within multi-statement transaction is easy to appear. (although ASE does not support the exception mechanism until version 15.0.1, This article uses the word "exception" for convenience ). Some developers think this is a problem with the Sybase Database; some think it is a problem to call the setautocommit () method multiple times; some developers think it is a problem with jconnect, or even from jconnect Code Directly block this exception.  
However, how is a set chained exception generated?  
I. Database Layer  
First, let's look at set chained. The following text snippet is taken from ASE 12.5.2 Reference Manual: commands and page 430:  

Chained  
Begins a transaction just before the first data retrieval or data modification  
Statement at the beginning of a session and after a transaction ends. In  
Chained mode, Adaptive Server implicitly executes a begin transaction  
Command before the following statements: delete, fetch, insert, lock table,  
Open, select, and update. You cannot execute set Chained within a transaction.  

From this paragraph, we can know that after set chained on, the delete, fetch, insert, lock table, open, select, and update statements will automatically start a transaction, and explicitly complete the transaction, that is, explicitly call commit/rollback. At the same time, the chained mode cannot be set in the transaction.  
The following SQL code snippet describes how the set chained error message is generated at the database layer.  

1> set chained on  
2> go  
1> set chained on  
2> go  
1> begin tran  
2> go  
1>  

It seems that multiple call to set chained does not produce exceptions. Next,  

1> set chained on  
2> go  
MSG 226, level 16, state 1:  
Server 'flybean', line 1:  
Set chained command not allowed within multi-statement transaction.  
1> set chained off  
2> go  
MSG 226, level 16, state 1:  
Server 'flybean', line 1:  
Set chained command not allowed within multi-statement transaction.  
1>  

Obviously, in the transaction environment, calling set chained will cause exceptions, which is also clearly stated in the manual. But why does the two consecutive call of set chained in the previous segment not produce exceptions? Note that the Adaptive Server implicitly executes a begin transaction command before the following statements :.  
Rebuild a database connection and start from scratch:  

1> set chained on  
2> go  
1> select 1  
2> go  

-----------  
1 

(1 row affected)  
1> set chained on  
2> go  
MSG 226, level 16, state 1:  
Server 'flybean', line 1:  
Set chained command not allowed within multi-statement transaction. 
1> set chained off  
2> go  
MSG 226, level 16, state 1:  
Server 'flybean', line 1:  
Set chained command not allowed within multi-statement transaction.  
1>  

Before executing select 1, the database automatically starts a transaction and therefore cannot execute set chained. Next, complete the implicit start transaction:  

1> rollback  
2> go  
1> set chained off  
2> go  
1>  

Ii. J2EE Layer  
In J2EE applications, some lightweight data access layers use the setautocommit (false) + commit ()/rollback () method of connection to manage transactions. Through the decompilation of jconnect and analysis of spt_mda data, we can find that setautocommit (true) = set chained off; setautocommit (false) = set chained on, and display the call to setautocommit () in sequence () method.  

On the other hand, most J2EE applications use connection pools. When the application calls the connection. Close () method, it does not actually close the connection, but recycles the connection to the pool. Assume that the initial state of the connection is chained off. If the application calls the setautocommit (false) method of the connection after the connection is obtained to start the transaction, after the transaction is completed, the close () method is used to return to the pool, if the connection fails to be restored to the initial state (that is, chained off), an exception may occur after the application obtains the connection multiple times and performs some operations. See:  

Through the above analysis, we can understand the cause of this exception, so it is easy to avoid this exception. That is, after the transaction is completed, we explicitly call setautocommit (true) before closing the connection ). Maybe some Program Personnel will think it is troublesome, but do not forget that "returning to Zhao" is the obligation of the resource borrower.

[Transfer]