Committing changes to the database without releasing a writer lock

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

Committing changes to the database without releasing a writer lock

Theodore Dubois
I'd like to essentially commit changes to disk in the middle of the transaction, resulting in a transaction that is atomic with respect to other database connections but is two atomic transactions with respect to the filesystem.

When I first found the locking_mode pragma, my understanding was that it disabled the releasing of locks on transaction commit or rollback. So I came up with this:

pragma locking_mode=exclusive;
begin;
-- transaction part 1
commit;
pragma locking_mode=normal; -- exclusive lock will be released when next transaction completes
begin;
-- transaction part 2
commit;

The problem is that this doesn't work in WAL mode. My understanding according to pragma lock_status is that readers in WAL mode never release their shared locks, so acquiring an exclusive lock like this would require all readers to be closed. It seems like any connection in WAL mode acquires a shared lock, and there's some other lock being used to synchronize writers that pragma lock_status isn't showing me. So in order to do this I'd need some way to control that other write synchronization lock.

Is there a sane way to do this?

~Theodore

_______________________________________________
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: Committing changes to the database without releasing a writer lock

Simon Slavin-3
On 7 Feb 2019, at 9:53pm, Theodore Dubois <[hidden email]> wrote:

> I'd like to essentially commit changes to disk in the middle of the transaction, resulting in a transaction that is atomic with respect to other database connections but is two atomic transactions with respect to the filesystem.

Would SAVEPOINT work for you ?

<https://sqlite.org/lang_savepoint.html>

The thing you're actually asking for doesn't happen in SQL.  The state of the database on the disk (including the journal file as well as the database file) is meant to reflect what other connections are seeing.  I don't think we can do exactly what you asked for.

Simon.
_______________________________________________
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: Committing changes to the database without releasing a writer lock

Rowan Worth-2
In reply to this post by Theodore Dubois
On Fri, 8 Feb 2019 at 20:03, Theodore Dubois <[hidden email]> wrote:

> I'd like to essentially commit changes to disk in the middle of the
> transaction, resulting in a transaction that is atomic with respect to
> other database connections but is two atomic transactions with respect to
> the filesystem.
>

"atomic transaction with respect to the filesystem" doesn't really make
sense to me - from a filesystem perspective all you have is a bunch of
changes to various data blocks, and a sync(). Why does it matter to you
that changes are on-disk mid-transaction? What are the actual semantics
you're hoping to implement?

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