Re: [sqlite] Handling ROLLBACK
On Monday, 4 March, 2019 20:23, Rowan Worth wrote: >On Sun, 3 Mar 2019 at 20:53, Keith Medcalf wrote: >> Statements which were in progress that were permitted to proceed >> (ie, where the next step did not return an abort error) continue >> with a read lock in place (ie, as if they were part of an implicit >> transaction on the connection) and once all those statements are >> completed, the read locks are released. You can BEGIN another >> transaction on the same connection (or another connection) >> and the locks will be escalated as you requested in the >> same fashion as would normally be expected for an in-progress >> implicit transaction. > Wait what? If I've understood correctly you're describing a > situation where statements outlive their transaction context? >Something like: > > >sqlite3 *db; // initialised elsewhere > >sqlite3_stmt *stmt; >int rc; > >sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, NULL); >stmt = sqlite3_prepare_v2(db, "SELECT * FROM some_table", -1, >, 0); >rc = sqlite3_step(stmt); // advance to first row >sqlite3_exec(db, "COMMIT", NULL, NULL, NULL); > >rc = sqlite3_step(stmt); // advance to second row? >... >sqlite3_finalize(stmt); > >And the sqlite3_step() following the transaction acquires a new read- >lock? >Or it prevents the COMMIT from dropping the read-lock? >It seems bizarre that this is even possible, so I may have >misunderstood! It appears that changes are committed (assuming that the COMMIT was successful) however the read lock is not released (that is, after the commit the changes are visible to other connections). However, there is no longer a transaction in progress on the original connection (the one the commit was issued against) and it is in autocommit mode and still holding a read lock (and is still repeatable read with respect to changes committed on another connection). I believe this is consistent with the documentation and operates appropriately (that is, as would be expected) for the journaling mode (delete or wal). --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Handling ROLLBACK
On Sun, 3 Mar 2019 at 20:53, Keith Medcalf wrote: > My observation (on the current tip version 3.28.0) of Schrodingers > Transactions is that if there is (for example) a transaction in progress > and that is COMMIT or ROLLBACK, then the changes are either committed or > rolled back and the explicit transaction is ended (that is, autocommit > becomes True). > You kind of covered this in a previous email where you talked about "COMMIT or ROLLBACK command completing successfully", but sqlite has a special case around COMMIT which I think is worth mentioning in detail: If COMMIT fails with SQLITE_BUSY, it means the EXCLUSIVE lock could not be obtained within the configured timeout, because of other concurrent activity on the DB. In this case, the transaction's changes are not committed or rolled back -- it _remains open_. It is then up to the programmer to decide whether to ROLLBACK and give up, or try to COMMIT again at a later date. > Statements which were in progress that were permitted to proceed (ie, > where the next step did not return an abort error) continue with a read > lock in place (ie, as if they were part of an implicit transaction on the > connection) and once all those statements are completed, the read locks are > released. You can BEGIN another transaction on the same connection (or > another connection) and the locks will be escalated as you requested in the > same fashion as would normally be expected for an in-progress implicit > transaction. > Wait what? If I've understood correctly you're describing a situation where statements outlive their transaction context? Something like: sqlite3 *db; // initialised elsewhere sqlite3_stmt *stmt; int rc; sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, NULL); stmt = sqlite3_prepare_v2(db, "SELECT * FROM some_table", -1, , 0); rc = sqlite3_step(stmt); // advance to first row sqlite3_exec(db, "COMMIT", NULL, NULL, NULL); rc = sqlite3_step(stmt); // advance to second row? ... sqlite3_finalize(stmt); And the sqlite3_step() following the transaction acquires a new read-lock? Or it prevents the COMMIT from dropping the read-lock? It seems bizarre that this is even possible, so I may have misunderstood! -Rowan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Equiv stmts, different explain plans
In the first query the subselect that creates the list is independent. In the second query the subselect that creates the list is correlated. In the first query you have requested that the subquery be executed to create the list for use by the IN operator. After this has been done the main (outer) query is executed and a result generated when the where condition (which includes the IN operator) are satisfied. Since it is possible that the list may not need to be generated because the condition c==1 in the outer query may never be satisfied, the subquery is only executed ONCE the first time its results are required. In the second query you have requested that the outer query be executed AND FOR EACH ROW that passes the WHERE c=1 constraint, execute the subquery and then check if d in the outer query is in the set of the results obtained by running the correlated subquery. --- 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 Kyle >Sent: Monday, 4 March, 2019 18:05 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] Equiv stmts, different explain plans > >On another DB I came across 2 stmts, that I think are equivalent, but >generated different explain plans. I request a second opinion - are >these 2 stmts equivalent? If so, why do they generate different >explain >plans even on sqlite? >TIA >-- >create table t1(c,d); >create table t2(c,d); >explain select * from t1 > where c=1 and d in (select d from t2 where c=1); >explain select * from t1 > where c=1 and d in (select d from t2 where t2.c=t1.c); >___ >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] Deserialize a WAL database file
On Fri, 1 Mar 2019 at 18:26, Lloyd wrote:
> I have two database files. One in Rollback mode and the other in WAL mode.
> I am able to serialize, deserialize and prepare a SQL query against the
> rollback database. When I do the same against the WAL database file, the
> 'prepare' statement fails with code '1'. Is it not possible to do this on
> WAL based database file? A sample code fragment is given below-
>
> sqlite3 *dbHandle=nullptr;
> if (sqlite3_open_v2("db_filename", , SQLITE_OPEN_READONLY, NULL)
> != SQLITE_OK){//error}
>
> sqlite3_int64 sz=0;
> unsigned char* mem=sqlite3_serialize(dbHandle,"main",,0);
>
> if(sqlite3_deserialize(dbHandle, "main", mem, sz,
> sz,SQLITE_DESERIALIZE_READONLY) != SQLITE_OK){//error}
>
> char* Query = "select * from test";
> sqlite3_stmt *statement = nullptr;
> int res=sqlite3_prepare_v2(dbHandle, Query, strlen(Query), , 0);
> //res is 1 for WAL
>
I can't see any obvious reason for this. The WAL database definitely has a
table called test? Is sqlite3_serialize returning non-NULL in the WAL case?
Check what sqlite3_errmsg(dbHandle) has to say.
-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Equiv stmts, different explain plans
On 05/03/2019 01:33, Richard Hipp wrote: On 3/4/19, Kyle wrote: On another DB I came across 2 stmts, that I think are equivalent, but generated different explain plans. I request a second opinion - are these 2 stmts equivalent? If so, why do they generate different explain plans even on sqlite? The two SELECT statements below may well compute the same output (unless I'm missing something) but they are not the same. The WHERE clause in the subquery is different. So why do you expect them to generate the same query plan? create table t1(c,d); create table t2(c,d); explain select * from t1 where c=1 and d in (select d from t2 where c=1); explain select * from t1 where c=1 and d in (select d from t2 where t2.c=t1.c); DRH, many thanks for your reply, I was expecting same output because I believe stmts to be equivalent, so was not sure why query plan was different. I see the explain plans are very similar. But I believe original stmts mentioned are still equivalent? Do you agree? And in SQLite what is best way to write such stmt (or in other terms, what is difference)? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Equiv stmts, different explain plans
On 3/4/19, Kyle wrote: > On another DB I came across 2 stmts, that I think are equivalent, but > generated different explain plans. I request a second opinion - are > these 2 stmts equivalent? If so, why do they generate different explain > plans even on sqlite? The two SELECT statements below may well compute the same output (unless I'm missing something) but they are not the same. The WHERE clause in the subquery is different. So why do you expect them to generate the same query plan? > create table t1(c,d); > create table t2(c,d); > explain select * from t1 >where c=1 and d in (select d from t2 where c=1); > explain select * from t1 >where c=1 and d in (select d from t2 where t2.c=t1.c); -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Equiv stmts, different explain plans
On another DB I came across 2 stmts, that I think are equivalent, but generated different explain plans. I request a second opinion - are these 2 stmts equivalent? If so, why do they generate different explain plans even on sqlite? TIA -- create table t1(c,d); create table t2(c,d); explain select * from t1 where c=1 and d in (select d from t2 where c=1); explain select * from t1 where c=1 and d in (select d from t2 where t2.c=t1.c); ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a tool to convert `where`s to equivalent `join`s?
On Fri, 1 Mar 2019 13:44:57 +0530 Rocky Ji wrote: > So for learning sake, is there a tool that converts a query using > `WHERE` to a query (that yields identical results) using JOINs? Like > a English -> side-by-side translator. SQL-92 was introduced a long time ago. At that time, I don't remember any vendor offering any kind of automatic conversion tool. Nor do I know of one now. It's not that it couldn't be done. It's that it wouldn't help. Given a database schema and a query, it's no problem to separate join criteria from (relational) select criteria. But equivalent translations could be expressed several ways, and no machine-generated conversion would necessarily communicate the intention of the query any better. Put another way: how likely is a machine to better pose a query than the human being did in first place? If automatic translation adds functionality, that's different. For example, Oracle had a tool to convert T-SQL to PL/SQL. While the output was no one's concept of beauty, it did have the property of running on Oracle. The most important contribution of SQL-92 to the SELECT statement, as I think Keith mentioned, wasn't separating JOIN from WHERE, but a clear, standardized outer-join syntax. --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
