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
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.
Thanks for the clarification. I believing adding this explanation to the transaction documentation page, https://www.monetdb.org/Documentation/Manuals/SQLreference/Transactionssacti..., or the explanation of mapi_setAutocommit() on the mapi documentation page, https://www.monetdb.org/Documentation/Manuals/SQLreference/Programming/MAPI, would potentially be useful to many users using mapi.
Doug Service
On Mon, Sep 28, 2015 at 10:50 AM, Sjoerd Mullender sjoerd@acm.org wrote:
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
On Sep 28, 2015, at 15:31, Doug Service n848dg@gmail.com wrote:
Thanks for the clarification. I believing adding this explanation to the transaction documentation page, https://www.monetdb.org/Documentation/Manuals/SQLreference/Transactionssacti..., or the explanation of mapi_setAutocommit() on the mapi documentation page, https://www.monetdb.org/Documentation/Manuals/SQLreference/Programming/MAPI, would potentially be useful to many users using mapi.
Done. Most info went to the Transaction page. Added a link to the Transaction page in the MAPI page.
Doug Service
On Mon, Sep 28, 2015 at 10:50 AM, Sjoerd Mullender sjoerd@acm.org wrote: 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
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
participants (3)
-
Doug Service -
Sjoerd Mullender -
Ying Zhang