Thanks, got it.
So the stmt will be reset/finalized automatically if it reaches to NO-MORE-ROWS or SQLITE_DONE, OR it can be explicitly reset/finalized during the execution. One more, I signed in 5 days ago but didn't receive the notification email and today I changed email address and repost the same question 😊 Regards, James ________________________________ From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of Keith Medcalf <kmedc...@dessus.com> Sent: Friday, August 11, 2017 11:19 To: SQLite mailing list Subject: Re: [sqlite] implicit transaction is commited without sqlite3_reset or sqlite3_finalize, differs with Documentation 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users