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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

james ni
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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-
>[hidden email]] On Behalf Of ??
>Sent: Saturday, 5 August, 2017 03:44
>To: [hidden email]
>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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
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-
>[hidden email]] On Behalf Of ni james
>Sent: Thursday, 10 August, 2017 20:48
>To: [hidden email]
>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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

james ni
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 <[hidden email]> on behalf of Keith Medcalf <[hidden email]>
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-
>[hidden email]] On Behalf Of ??
>Sent: Saturday, 5 August, 2017 03:44
>To: [hidden email]
>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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
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-
>[hidden email]] On Behalf Of ni james
>Sent: Thursday, 10 August, 2017 20:48
>To: [hidden email]
>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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...