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.


--
Sjoerd Mullender


_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list