On 28/09/15 04:17, Doug Service wrote:
We use MonetDB extensively in an internal system we are developing. We initially had issues getting transactions working so we put it off to make progress in other areas. However we are at the point now were we need to have transactions working.
The mapi documentation (https://www.monetdb.org/Documentation/Manuals/SQLreference/Programming/MAPI)
says the following about auto commit.
MapiMsg mapi_setAutocommit(Mapi mid, int autocommit) Set the autocommit flag (default is on). This only has an effect when the language is SQL. In that case, the server commits after each statement sent to the server.
I infered from this statement that using transactions requires setting auto commit to false. However, when I did that I received the following error when I tried to use transactions.
MSERVER(25001!START TRANSACTION: cannot start a transaction within a transaction)
If I did not disable auto commit, I did not receive any errors when using transactions, but I did not know if the transaction were actually working. After using hg to enlist in the MonetDb code and searching for the error I found this.
str SQLtransaction2(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci) { mvc *sql = NULL; str msg;
(void) stk; (void) pci;
if ((msg = getSQLContext(cntxt, mb, &sql, NULL)) != NULL) return msg; if ((msg = checkSQLContext(cntxt)) != NULL) return msg; if (sql->session->auto_commit == 0) throw(SQL, "sql.trans", "25001!START TRANSACTION: cannot start a transaction within a transaction"); if (sql->session->active) { RECYCLEdrop(cntxt); mvc_rollback(sql, 0, NULL); } sql->session->auto_commit = 0; sql->session->ac_on_commit = 1; sql->session->level = 0; (void) mvc_trans(sql); return msg; }
which leads me to believe that one should not disable auto commit to use transaction as it appears to be auto disabled by the transaction code.
My questions are: 1) Is the correct method of using transactions in MonetDb to leave the auto commit flag alone and just issue START TRANSACTION;, COMMIT;, and ROLLBACK; as needed? 2) If the answer to (1) is yes, is disabling auto commit, issuing updates, and then re enabling auto commit like a transaction?
Doug Service
In autocommit mode, you can use START TRANSACTION and COMMIT/ROLLBACK to indicate transactions that are larger than a single query. If autocommit mode is off, the START TRANSACTION is implicit, and you should only use COMMIT/ROLLBACK.