Asked and answered 5 days ago:

Both.

You are stepping a "statement" to the "end" (that is, completion, naught more 
to do).  Thus the statement is automatically reset and any implicit transaction 
is committed.  

If however you executed a statement like:

SELECT * FROM ReallyBigTable;

and ReallyBigTable has 1 million rows in it, it would not be reset (and the 
implicit transaction committed) until either (a) you finished reading all 1 
million rows and got the appropriate NO MORE ROWS or DONE response (ie, ran the 
statement to completion), or you called sqlite3_reset or sqlite3_finalize on 
the statement after reading some lesser number of rows.

And of course, the terminology "last active statement finishes" means 
statements executed concurrently on the same connection (where the multiple 
statements are not in the DONE status at the same time).  Transaction status is 
an attribute of the connection, not of the statement.  So if you prepare 10 
statements against the same connection, the implicit transaction is started 
when you step the first one (any one of them), and committed when the last 
statement of all the statements you have step'd concurrently return DONE or NO 
MORE ROWS or are otherwise explicitly reset or finalized.  

Explicit transactions (wrapped in BEGIN / [COMMIT | ROLLBACK]) work exactly the 
same way.  Except that the BEGIN is the first statement step'd and COMMIT | 
ROLLBACK is the last.  BEGIN does not really do much of anything except to turn 
the autocommit mode off until the COMMIT | ROLLBACK statement is step'd at 
which point all open (have been step'd) statements on the connection are 
automatically reset (I believe), and an error is returned if a write statement 
(INSERT/UPDATE etc) is not already reset (run to completion).

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of ??
>Sent: Saturday, 5 August, 2017 03:44
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] implicit transaction is commited without
>sqlite3_reset or sqlite3_finalize, differs with Documentation, which
>one is correct ?
>
>Question for the paragraph in
>http://www.sqlite.org/lang_transaction.html:
>
>"An implicit transaction (a transaction that is started
>automatically, not a transaction started by BEGIN) is committed
>automatically when the last active statement finishes. A statement
>finishes when its prepared statement is reset or finalized. An open
>sqlite3_blob used for incremental BLOB I/O counts as an unfinished
>statement. The sqlite3_blob finishes when it is closed."
>
>This means without sqlite3_reset or sqlite3_finalize, the transaction
>is not commited. But my code shows it does commit immediately after
>sqlite3_step():
>
>void
>test_insert()
>{
>        int ite = 0;
>        int rc = 0;
>        sqlite3_stmt *stmt = NULL;
>        char sql[] = "insert into test values (?1, ?2);";
>
>        rc = sqlite3_prepare_v2(db, sql, strlen(sql) + 1, &stmt,
>NULL);
>        if (rc) {
>                perror("sqlite3_prepare_v2");
>                return;
>        }
>
>        for (ite = 0; ite < 1; ite++) {
>                rc = sqlite3_reset(stmt);
>                assert(rc == SQLITE_OK);
>
>                sqlite3_bind_int(stmt, 1, ite);
>                sqlite3_bind_int(stmt, 2, ite + 100);
>
>                rc = sqlite3_step(stmt);
>                if (rc != SQLITE_DONE) {
>                        printf("sqlite3_step ite:%d %s", ite,
>sqlite3_errmsg(db));
>                        return;
>                }
>    }
>}
>
>
>I verified this through Linux Perf tools.
>
>So, the result of the code is different with the Documentation, which
>one is correct ??
>
>
>
>
>
>
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of ni james
>Sent: Thursday, 10 August, 2017 20:48
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] implicit transaction is commited without
>sqlite3_reset or sqlite3_finalize, differs with Documentation
>
>Question for the paragraph in
>http://www.sqlite.org/lang_transaction.html:
>
>"An implicit transaction (a transaction that is started
>automatically, not a transaction started by BEGIN) is committed
>automatically when the last active statement finishes. A statement
>finishes when its prepared statement is
>reset<http://www.sqlite.org/c3ref/reset.html> or
>finalized<http://www.sqlite.org/c3ref/finalize.html>. An open
>sqlite3_blob<http://www.sqlite.org/c3ref/blob.html> used for
>incremental BLOB I/O counts as an unfinished statement. The
>sqlite3_blob<http://www.sqlite.org/c3ref/blob.html> finishes when it
>is closed<http://www.sqlite.org/c3ref/blob_close.html>."
>
>This means without sqlite3_reset or sqlite3_finalize, the transaction
>is not commited. But my code shows it does commit immediately after
>sqlite3_step():
>
>void
>test_insert()
>{
>        int ite = 0;
>        int rc = 0;
>        sqlite3_stmt *stmt = NULL;
>        char sql[] = "insert into test values (?1, ?2);";
>
>        rc = sqlite3_prepare_v2(db, sql, strlen(sql) + 1, &stmt,
>NULL);
>        if (rc) {
>                perror("sqlite3_prepare_v2");
>                return;
>        }
>
>        for (ite = 0; ite < 1; ite++) {
>                rc = sqlite3_reset(stmt);
>                assert(rc == SQLITE_OK);
>
>                sqlite3_bind_int(stmt, 1, ite);
>                sqlite3_bind_int(stmt, 2, ite + 100);
>
>                rc = sqlite3_step(stmt);
>                if (rc != SQLITE_DONE) {
>                        printf("sqlite3_step ite:%d %s", ite,
>sqlite3_errmsg(db));
>                        return;
>                }
>    }
>}
>
>I also verified this through Linux Perf tools by tracing syscalls.
>
>So, the result of the code is different with the Documentation, which
>one is correct ??
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to