proposal for write-lock on "commit" rather than "begin transaction"

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

proposal for write-lock on "commit" rather than "begin transaction"

Brannon King
This is a request for a small change to the handling of multiple
connections. I think it would significantly enhance the usefulness there
via allowing multiple "views" of the data.

Consider that I have two simultaneous connections to one file, named Con1
and Con2. They could be in one process or one thread -- that's irrelevant.
Either one may write to the DB; we don't know yet. For starters, assume
that their journal mode is MEMORY.

Both connections begin with "begin transaction". Already I'm dead in the
water; one of those will fail presently with "database is locked". But it
doesn't need to be that way! Each connection can have its own journal file,
especially if it's in memory. Once one connection commits, the other
connection will no longer be allowed to commit. It will be forced to
rollback (or perhaps rebase if there are no conflicts).

Multiple WAL files could be supported in a similar fashion; they just need
some kind of unique naming scheme. For recovery, the user would be prompted
to select one or none. It doesn't seem that far from Sqlite's current
behavior. Thoughts?

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

Re: proposal for write-lock on "commit" rather than "begin transaction"

Simon Slavin-3
On 25 Oct 2019, at 5:07pm, Brannon King <[hidden email]> wrote:

> Once one connection commits, the other connection will no longer be allowed to commit. It will be forced to rollback (or perhaps rebase if there are no conflicts).

While lots of software supports rollback, in that it issues an error message rather than crashing, imagine what this would do in real life.

Two users – members of staff – enter data.  Each user enters a new invoice.  One of these entries gets rolled back.  What should their software do ?  Or should it just return an error message to the user ?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: proposal for write-lock on "commit" rather than "begin transaction"

Brannon King
>
> Two users – members of staff – enter data.  Each user enters a new
> invoice.  One of these entries gets rolled back.  What should their
> software do ?  Or should it just return an error message to the user ?
>

Multi-user data entry is not a part of my intended use case. I think other
database engines are better suited for this need. It could be done, though,
if you had an easy way to check for conflicts on commit. This change would
get us much closer to this than we are now.

My particular use case is for data verification. I have one part of the
system that does some computation. I then have another part of the system
that verifies that computation. I don't want the "verifier" to write to the
database; that data will be written by the true data owner. However, the
verifier to redo some of the inserts/deletes in order for the computation
to come out correctly.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: proposal for write-lock on "commit" rather than "begin transaction"

Rowan Worth-2
In reply to this post by Brannon King
On Sat, 26 Oct 2019 at 00:07, Brannon King <[hidden email]> wrote:

> This is a request for a small change to the handling of multiple
> connections. I think it would significantly enhance the usefulness there
> via allowing multiple "views" of the data.
>
> Consider that I have two simultaneous connections to one file, named Con1
> and Con2. They could be in one process or one thread -- that's irrelevant.
> Either one may write to the DB; we don't know yet. For starters, assume
> that their journal mode is MEMORY.
>
> Both connections begin with "begin transaction". Already I'm dead in the
> water; one of those will fail presently with "database is locked".


This is not true, unless you're using BEGIN IMMEDIATE or BEGIN EXCLUSIVE
whose express purpose is to obtain a lock. BEGIN TRANSACTION's default mode
is DEFERRED, which does not obtain any locks until the DB is actually
queried (causing it to obtain a read-lock) or modified (causing it to
obtain a write-lock).

Read-locks and write-locks can coexist, except during the window when the
DB file is actually being modified. This period is protected by an
exclusive lock, and is generally brief unless you have a transaction which
modifies lots of pages and spills sqlite's memory cache before COMMIT is
reached.

The only time you get "database is locked" is (1) if a connection requests
the write-lock (ie. tries to modify the DB) when another connection already
owns it, or (2) if a connection requests a read-lock while a writing
connection is updating/ready to update the DB _and_ said update takes
longer than the busy timeout configured for the connection.


> But it
> doesn't need to be that way! Each connection can have its own journal file,
> especially if it's in memory. Once one connection commits, the other
> connection will no longer be allowed to commit. It will be forced to
> rollback (or perhaps rebase if there are no conflicts).
>

If the other connection isn't allowed to commit, how is this materially
different from the semantics currently provided? Why would sqlite wait
until a client tries to COMMIT before raising an error when it already
knows that another write transaction is in progress?

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

Re: proposal for write-lock on "commit" rather than "begin transaction"

Dan Kennedy-4
In reply to this post by Brannon King

On 25/10/62 23:07, Brannon King wrote:

> This is a request for a small change to the handling of multiple
> connections. I think it would significantly enhance the usefulness there
> via allowing multiple "views" of the data.
>
> Consider that I have two simultaneous connections to one file, named Con1
> and Con2. They could be in one process or one thread -- that's irrelevant.
> Either one may write to the DB; we don't know yet. For starters, assume
> that their journal mode is MEMORY.
>
> Both connections begin with "begin transaction". Already I'm dead in the
> water; one of those will fail presently with "database is locked". But it
> doesn't need to be that way! Each connection can have its own journal file,
> especially if it's in memory. Once one connection commits, the other
> connection will no longer be allowed to commit. It will be forced to
> rollback (or perhaps rebase if there are no conflicts).
>
> Multiple WAL files could be supported in a similar fashion; they just need
> some kind of unique naming scheme. For recovery, the user would be prompted
> to select one or none. It doesn't seem that far from Sqlite's current
> behavior. Thoughts?

This branch might interest you:

   https://www.sqlite.org/src/timeline?r=begin-concurrent-pnu-wal2

The "BEGIN CONCURRENT" idea is that two connections may concurrently
have independent write transactions based on optimistic read/write page
locking.

Dan.



>
> ~Brannon
> _______________________________________________
> 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
|

WAL2 mode

Simon Slavin-3
On 12 Nov 2019, at 10:06am, Dan Kennedy <[hidden email]> wrote:

> This branch might interest you:
>
>   https://www.sqlite.org/src/timeline?r=begin-concurrent-pnu-wal2

" In wal2 mode, the system uses two wal files instead of one. The files are named "<database>-wal" and "<database>-wal2" "

Could this be changed to -wal1 and -wal2 ?  Or any other suffixes that aren't used by a different mode ?

This is to make crash/corruption diagnostics simpler.  At the moment, if the database file is so corrupt it can't be opened by SQLite, just by looking at the files in the directory I can tell a lot about what journal mode the database was in and what was being done, and what the user did to try to restore a backup.

But users do all sorts of weird things to try to recover from crashes, including restoring a database, sometimes in a different journal mode, but leaving journal files in place.  Seeing whether there's a -wal file and/or a -wal1 file, and comparing the changedates on the files, will give me better clues about what was done.  It means I can get further in figuring out what was going on before hexdumping the files concerned.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: WAL2 mode

Dan Kennedy-4

On 12/11/62 19:00, Simon Slavin wrote:

> On 12 Nov 2019, at 10:06am, Dan Kennedy <[hidden email]> wrote:
>
>> This branch might interest you:
>>
>>    https://www.sqlite.org/src/timeline?r=begin-concurrent-pnu-wal2
> " In wal2 mode, the system uses two wal files instead of one. The files are named "<database>-wal" and "<database>-wal2""
>
> Could this be changed to -wal1 and -wal2 ?  Or any other suffixes that aren't used by a different mode ?
>
> This is to make crash/corruption diagnostics simpler.  At the moment, if the database file is so corrupt it can't be opened by SQLite, just by looking at the files in the directory I can tell a lot about what journal mode the database was in and what was being done, and what the user did to try to restore a backup.
>
> But users do all sorts of weird things to try to recover from crashes, including restoring a database, sometimes in a different journal mode, but leaving journal files in place.  Seeing whether there's a -wal file and/or a -wal1 file, and comparing the changedates on the files, will give me better clues about what was done.  It means I can get further in figuring out what was going on before hexdumping the files concerned.

Fair point.

I think it reuse *-wal in order to avoid an extra call to access() when
opening a read-transaction in rollback mode. There might be other
reasons too. It's only a branch for now - this is something to consider
if it ever gets rolled into the main version though.

Dan.



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