Re: [sqlite] implicit transaction is commited without sqlite3_reset or sqlite3_finalize, differs with Documentation

[ni james]

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  on behalf of 
Keith Medcalf 
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



_

Re: [sqlite] implicit transaction is commited without sqlite3_reset or sqlite3_finalize, differs with Documentation

[Keith Medcalf]

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 Do

[sqlite] implicit transaction is commited without sqlite3_reset or sqlite3_finalize, differs with Documentation

[ni james]

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 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

Re: [sqlite] implicit transaction is commited without sqlite3_reset or sqlite3_finalize, differs with Documentation, which one is correct ?

[Keith Medcalf]

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

Re: [sqlite] implicit transaction is commited without sqlite3_reset or sqlite3_finalize, differs with Documentation, which one is correct ?

[Igor Tandetnik]

On 8/5/2017 5:44 AM, 倪磊 wrote:

I verified this through Linux Perf tools.


What does that mean? How do these tools convince you that a transaction was 
committed?

Examine the database before and after, using SQLite command line utility. Does 
"test" table contain one more row after than it did before? That's what matters.
--
Igor Tandetnik

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

[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