Does journal_mode=DELETE writes uncommitted queries into DB?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Does journal_mode=DELETE writes uncommitted queries into DB?

林自均
Hi folks,

When I was learning about rollback journal, I did the following tests:

(in shell 1)
$ sqlite3 /tmp/db.sqlite
SQLite version 3.21.0 2017-10-24 18:55:49
Enter ".help" for usage hints.
sqlite> PRAGMA journal_mode;
delete
sqlite> CREATE TABLE bank (name STR, money INT);
sqlite> INSERT INTO bank VALUES ("john", 5566);
sqlite> BEGIN;
sqlite> UPDATE bank SET money = money + 100 WHERE name = "john";
sqlite>

(then in shell 2)
$ kill -kill $(pidof sqlite3) # kills the sqlite3 process in shell 1
$ rm -f /tmp/db.sqlite-journal
$ sqlite3 /tmp/db.sqlite .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE bank (name STR, money INT);
INSERT INTO bank VALUES('john',5566);
COMMIT;

I was expecting that deleting the rollback journal would commit the
uncommitted transaction (i.e. increase money from 5566 to 5666). However,
it didn't.

I also noticed that the md5sum of db.sqlite are the same before the UPDATE
query and after it, which means that the UPDATE query doesn't really write
into db.sqlite. Does it only write into memory?

Thanks for answering my questions.

John Lin
_______________________________________________
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: Does journal_mode=DELETE writes uncommitted queries into DB?

J. King-3
The rollback journal is used to return the database to its previous state in the case of a partial write, not complete writes which are interrupted. As you didn't commit the transaction, no write occurred, never mind a partial one, so the database remained in its initial state.

Deleting a rollback journal is a bad idea. In this case it was harmless, but normally it's an excellent way to corrupt your database.

As to where uncommitted transactions live, I am not sure. I'll let someone else answer that.

On November 23, 2017 9:13:20 PM EST, "林自均" <[hidden email]> wrote:

>Hi folks,
>
>When I was learning about rollback journal, I did the following tests:
>
>(in shell 1)
>$ sqlite3 /tmp/db.sqlite
>SQLite version 3.21.0 2017-10-24 18:55:49
>Enter ".help" for usage hints.
>sqlite> PRAGMA journal_mode;
>delete
>sqlite> CREATE TABLE bank (name STR, money INT);
>sqlite> INSERT INTO bank VALUES ("john", 5566);
>sqlite> BEGIN;
>sqlite> UPDATE bank SET money = money + 100 WHERE name = "john";
>sqlite>
>
>(then in shell 2)
>$ kill -kill $(pidof sqlite3) # kills the sqlite3 process in shell 1
>$ rm -f /tmp/db.sqlite-journal
>$ sqlite3 /tmp/db.sqlite .dump
>PRAGMA foreign_keys=OFF;
>BEGIN TRANSACTION;
>CREATE TABLE bank (name STR, money INT);
>INSERT INTO bank VALUES('john',5566);
>COMMIT;
>
>I was expecting that deleting the rollback journal would commit the
>uncommitted transaction (i.e. increase money from 5566 to 5666).
>However,
>it didn't.
>
>I also noticed that the md5sum of db.sqlite are the same before the
>UPDATE
>query and after it, which means that the UPDATE query doesn't really
>write
>into db.sqlite. Does it only write into memory?
>
>Thanks for answering my questions.
>
>John Lin
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
_______________________________________________
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: Does journal_mode=DELETE writes uncommitted queries into DB?

林自均
Hi King,

Thank you for the detailed explanation! Now I understand rollback journal a
bit more.

John Lin

J. King <[hidden email]> 於 2017年11月24日 週五 上午10:28寫道:

> The rollback journal is used to return the database to its previous state
> in the case of a partial write, not complete writes which are interrupted.
> As you didn't commit the transaction, no write occurred, never mind a
> partial one, so the database remained in its initial state.
>
> Deleting a rollback journal is a bad idea. In this case it was harmless,
> but normally it's an excellent way to corrupt your database.
>
> As to where uncommitted transactions live, I am not sure. I'll let someone
> else answer that.
>
> On November 23, 2017 9:13:20 PM EST, "林自均" <[hidden email]> wrote:
> >Hi folks,
> >
> >When I was learning about rollback journal, I did the following tests:
> >
> >(in shell 1)
> >$ sqlite3 /tmp/db.sqlite
> >SQLite version 3.21.0 2017-10-24 18:55:49
> >Enter ".help" for usage hints.
> >sqlite> PRAGMA journal_mode;
> >delete
> >sqlite> CREATE TABLE bank (name STR, money INT);
> >sqlite> INSERT INTO bank VALUES ("john", 5566);
> >sqlite> BEGIN;
> >sqlite> UPDATE bank SET money = money + 100 WHERE name = "john";
> >sqlite>
> >
> >(then in shell 2)
> >$ kill -kill $(pidof sqlite3) # kills the sqlite3 process in shell 1
> >$ rm -f /tmp/db.sqlite-journal
> >$ sqlite3 /tmp/db.sqlite .dump
> >PRAGMA foreign_keys=OFF;
> >BEGIN TRANSACTION;
> >CREATE TABLE bank (name STR, money INT);
> >INSERT INTO bank VALUES('john',5566);
> >COMMIT;
> >
> >I was expecting that deleting the rollback journal would commit the
> >uncommitted transaction (i.e. increase money from 5566 to 5666).
> >However,
> >it didn't.
> >
> >I also noticed that the md5sum of db.sqlite are the same before the
> >UPDATE
> >query and after it, which means that the UPDATE query doesn't really
> >write
> >into db.sqlite. Does it only write into memory?
> >
> >Thanks for answering my questions.
> >
> >John Lin
> >_______________________________________________
> >sqlite-users mailing list
> >[hidden email]
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> --
> Sent from my Android device with K-9 Mail. Please excuse my brevity.
> _______________________________________________
> 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
|

Re: Does journal_mode=DELETE writes uncommitted queries into DB?

Rowan Worth-2
In reply to this post by J. King-3
On 24 November 2017 at 10:27, J. King <[hidden email]> wrote:

> The rollback journal is used to return the database to its previous state
> in the case of a partial write, not complete writes which are interrupted.
> As you didn't commit the transaction, no write occurred, never mind a
> partial one, so the database remained in its initial state.
>
> Deleting a rollback journal is a bad idea. In this case it was harmless,
> but normally it's an excellent way to corrupt your database.
>
> As to where uncommitted transactions live, I am not sure. I'll let someone
> else answer that.
>

When a database page is modified during a transaction, the initial data is
written to the rollback journal and the modified data is held in memory
until COMMIT or the transaction memory cache is exceeded (see PRAGMA
cache_size).

If either of those happen, sqlite obtains an EXCLUSIVE lock on the database
and starts writing the modified data (after making sure the rollback
journal is properly synced to disk). At this point you have a partially
committed transaction and the DB is not necessarily consistent (which is
why it's done under an exclusive lock, and why deleting the rollback
journal is a terrible idea as you said).

In the case of COMMIT this is just a matter of I/O (syncing the DB and
deleting the rollback journal) and should be fairly quick. But in the case
of a cache spill, this partially committed state can be observed for much
longer, depending on the size of the transaction.

-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: Does journal_mode=DELETE writes uncommitted queries into DB?

J. King-3
Thanks for the thorough explanation, Rowan. I must say I learned something, myself!

On November 23, 2017 11:23:11 PM EST, Rowan Worth <[hidden email]> wrote:

>On 24 November 2017 at 10:27, J. King <[hidden email]> wrote:
>
>> The rollback journal is used to return the database to its previous
>state
>> in the case of a partial write, not complete writes which are
>interrupted.
>> As you didn't commit the transaction, no write occurred, never mind a
>> partial one, so the database remained in its initial state.
>>
>> Deleting a rollback journal is a bad idea. In this case it was
>harmless,
>> but normally it's an excellent way to corrupt your database.
>>
>> As to where uncommitted transactions live, I am not sure. I'll let
>someone
>> else answer that.
>>
>
>When a database page is modified during a transaction, the initial data
>is
>written to the rollback journal and the modified data is held in memory
>until COMMIT or the transaction memory cache is exceeded (see PRAGMA
>cache_size).
>
>If either of those happen, sqlite obtains an EXCLUSIVE lock on the
>database
>and starts writing the modified data (after making sure the rollback
>journal is properly synced to disk). At this point you have a partially
>committed transaction and the DB is not necessarily consistent (which
>is
>why it's done under an exclusive lock, and why deleting the rollback
>journal is a terrible idea as you said).
>
>In the case of COMMIT this is just a matter of I/O (syncing the DB and
>deleting the rollback journal) and should be fairly quick. But in the
>case
>of a cache spill, this partially committed state can be observed for
>much
>longer, depending on the size of the transaction.
>
>-Rowan
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users