freetds AT lists.ibiblio.org
Subject: FreeTDS Development Group
List archive
- From: "James K. Lowden" <jklowden AT schemamania.org>
- To: TDS Development Group <freetds AT lists.ibiblio.org>
- Subject: [freetds] Fw: FreeTDS patch for MSSQL autocommit
- Date: Mon, 31 Mar 2008 18:52:41 -0400
Hi Freddy,
I received the attached AUTOCOMMIT patch by private mail. I don't know
how/if it would be merged with CVS HEAD and/or 0.82.
What do you think?
--jkl
Begin forwarded message:
[...]
I have tested the patch for over 6 months now in
production environment and to the best of my knowledge it helps to fix
the problem stated in the comment without breaking anything else.
[...]
diff -ruN freetds-0.64-orig/src/odbc/odbc.c freetds-0.64/src/odbc/odbc.c
--- freetds-0.64-orig/src/odbc/odbc.c 2006-06-29 14:38:13.000000000 -0700
+++ freetds-0.64/src/odbc/odbc.c 2007-08-29 16:35:08.000000000 -0700
@@ -187,6 +187,51 @@
* mssql: SET IMPLICIT_TRANSACTION ON
* sybase: SET CHAINED ON
*/
+ /*
+ * alex AT ingrian.com: on MS SQL you cannot get away with just one statement to change autocommit setting,
+ * as you have to commit all the outstanding transactions. The Sybase case won't work as expected for MS
+ * either, because the first commit after begin tran would return the session into the previous mode,
+ * so you HAVE to use IMPLICIT_TRANSACTIONS. Also to make a rollback behavior more consistent, it is
+ * recommended to have XACT_ABORT setting also ON. In this case the rollback would cause the entire
+ * transaction rolled back regardless of error severity, which otherwise may cause just one statement to be
+ * rolled back with the rest of the transaction being processed.
+ *
+ * To summarize actions needed for MS SQL:
+ * - autocommit OFF:
+ * SET IMPLICIT_TRANSACTIONS ON
+ * SET XACT_ABORT ON
+ * - autocommit ON:
+ * WHILE @@TRANCOUNT > 0 COMMIT
+ * SET IMPLICIT_TRANSACTIONS OFF
+ * (this also helps to address the issue with @@TRANCOUNT incremented twice after the first
+ * BEGIN TRAN in IMPLICIT_TRANSACTIONS mode).
+ */
+ if (TDS_IS_MSSQL(tds)) {
+ if ((state == SQL_AUTOCOMMIT_ON)) {
+ strcpy(query, "WHILE @@TRANCOUNT > 0 COMMIT");
+ tdsdump_log(TDS_DBG_INFO1, "change_autocommit: executing %s\n", query);
+ if (tds_submit_query(tds, query) != TDS_SUCCEED) {
+ odbc_errs_add(&dbc->errs, "HY000", "Could not change transaction status");
+ ODBC_RETURN(dbc, SQL_ERROR);
+ }
+ if (tds_process_simple_query(tds) != TDS_SUCCEED) {
+ odbc_errs_add(&dbc->errs, "HY000", "Could not change transaction status");
+ ODBC_RETURN(dbc, SQL_ERROR);
+ }
+ }
+ else {
+ strcpy(query, "SET XACT_ABORT ON");
+ tdsdump_log(TDS_DBG_INFO1, "change_autocommit: executing %s\n", query);
+ if (tds_submit_query(tds, query) != TDS_SUCCEED) {
+ odbc_errs_add(&dbc->errs, "HY000", "Could not change transaction status");
+ ODBC_RETURN(dbc, SQL_ERROR);
+ }
+ if (tds_process_simple_query(tds) != TDS_SUCCEED) {
+ odbc_errs_add(&dbc->errs, "HY000", "Could not change transaction status");
+ ODBC_RETURN(dbc, SQL_ERROR);
+ }
+ }
+ }
/* implicit transactions are on if autocommit is off :-| */
if (TDS_IS_MSSQL(tds))
-
[freetds] Fw: FreeTDS patch for MSSQL autocommit,
James K. Lowden, 03/31/2008
- Re: [freetds] Fw: FreeTDS patch for MSSQL autocommit, Peter Deacon, 03/31/2008
Archive powered by MHonArc 2.6.24.