[sqlite] Changing WAL mode for a transaction
On 04/29/2015 04:28 PM, Navaneeth K N wrote: > Hi Dan, > > >> On 27-Apr-2015, at 11:34 am, Dan Kennedy wrote: >> >> On 04/27/2015 12:55 PM, Navaneeth K N wrote: >>> Hi Dan, >>> >>> On 27-Apr-2015, at 10:52 am, Dan Kennedy wrote: On 04/26/2015 07:01 PM, Navaneeth K N wrote: > Hello, > > My application runs the following right after opening the connection to > the database. > >pragma journal_mode=wal; >pragma page_size=4096 Did executing the "PRAGMA journal_mode=wal" statement succeed? Or did it return SQLITE_BUSY or some other error code? >>> Yes. It succeeded. I can see -wal files after this. >> Sorry, it's early here. I meant the "PRAGMA journal_mode=delete" - did it >> succeed? > Yes. It succeeded. Running `pragma journal_mode;` again shows delete for that > connection. So does that mean, all the other connections uses WAL mode and > just this connection will use DELETE mode for all the transactions? If it succeeded, it should mean that the connection was able to gain exclusive access to the database file and switch the database back to rollback (non-WAL) mode. The *-wal and *-shm files should have been deleted when the command returns. Dan.
[sqlite] Changing WAL mode for a transaction
Hi Dan, > On 27-Apr-2015, at 11:34 am, Dan Kennedy wrote: > > On 04/27/2015 12:55 PM, Navaneeth K N wrote: >> Hi Dan, >> >> >>> On 27-Apr-2015, at 10:52 am, Dan Kennedy wrote: >>> >>> On 04/26/2015 07:01 PM, Navaneeth K N wrote: Hello, My application runs the following right after opening the connection to the database. pragma journal_mode=wal; pragma page_size=4096 >>> Did executing the "PRAGMA journal_mode=wal" statement succeed? Or did it >>> return SQLITE_BUSY or some other error code? >> Yes. It succeeded. I can see -wal files after this. > > Sorry, it's early here. I meant the "PRAGMA journal_mode=delete" - did it > succeed? Yes. It succeeded. Running `pragma journal_mode;` again shows delete for that connection. So does that mean, all the other connections uses WAL mode and just this connection will use DELETE mode for all the transactions? ? Thanks
[sqlite] Changing WAL mode for a transaction
On 04/27/2015 12:55 PM, Navaneeth K N wrote: > Hi Dan, > > >> On 27-Apr-2015, at 10:52 am, Dan Kennedy wrote: >> >> On 04/26/2015 07:01 PM, Navaneeth K N wrote: >>> Hello, >>> >>> My application runs the following right after opening the connection to the >>> database. >>> >>>pragma journal_mode=wal; >>>pragma page_size=4096 >> Did executing the "PRAGMA journal_mode=wal" statement succeed? Or did it >> return SQLITE_BUSY or some other error code? > Yes. It succeeded. I can see -wal files after this. Sorry, it's early here. I meant the "PRAGMA journal_mode=delete" - did it succeed? > > > >> >> >> >> >> >> >>> When the application wants to perform a bulk data load (loading around 21Gb >>> of data), it runs the following before starting the transaction. >>> >>> pragma journal_mode=delete >>> >>> This is done because I am under the assumption that WAL journal mode is not >>> suited for long running, big transactions (please correct me if I am wrong). >>> >>> But the journal mode change seems to be not affecting. Documentation for >>> WAL states the following: >>> >>> "Unlike the other journaling modes, PRAGMA journal_mode=WAL is persistent. >>> If a process sets WAL mode, then closes and reopens the database, the >>> database will come back in WAL mode. In contrast, if a process sets (for >>> example) PRAGMA journal_mode=TRUNCATE and then closes and reopens the >>> database will come back up in the default rollback mode of DELETE rather >>> than the previous TRUNCATE setting." >>> >>> Does this mean, in my case the journal mode change won't have any effect? >>> When I tested, I still see -wal & -shm files present. Will it use "DELETE" >>> journal mode for my huge transaction? >>> >>> Any help would be great! >>> ? >>> Navaneeth >>> >>> ___ >>> sqlite-users mailing list >>> sqlite-users at mailinglists.sqlite.org >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Changing WAL mode for a transaction
On 04/26/2015 07:01 PM, Navaneeth K N wrote: > Hello, > > My application runs the following right after opening the connection to the > database. > >pragma journal_mode=wal; >pragma page_size=4096 Did executing the "PRAGMA journal_mode=wal" statement succeed? Or did it return SQLITE_BUSY or some other error code? > > When the application wants to perform a bulk data load (loading around 21Gb > of data), it runs the following before starting the transaction. > > pragma journal_mode=delete > > This is done because I am under the assumption that WAL journal mode is not > suited for long running, big transactions (please correct me if I am wrong). > > But the journal mode change seems to be not affecting. Documentation for WAL > states the following: > > "Unlike the other journaling modes, PRAGMA journal_mode=WAL is persistent. If > a process sets WAL mode, then closes and reopens the database, the database > will come back in WAL mode. In contrast, if a process sets (for example) > PRAGMA journal_mode=TRUNCATE and then closes and reopens the database will > come back up in the default rollback mode of DELETE rather than the previous > TRUNCATE setting." > > Does this mean, in my case the journal mode change won't have any effect? > When I tested, I still see -wal & -shm files present. Will it use "DELETE" > journal mode for my huge transaction? > > Any help would be great! > ? > Navaneeth > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Changing WAL mode for a transaction
Hi Dan, > On 27-Apr-2015, at 10:52 am, Dan Kennedy wrote: > > On 04/26/2015 07:01 PM, Navaneeth K N wrote: >> Hello, >> >> My application runs the following right after opening the connection to the >> database. >> >> pragma journal_mode=wal; >> pragma page_size=4096 > > Did executing the "PRAGMA journal_mode=wal" statement succeed? Or did it > return SQLITE_BUSY or some other error code? Yes. It succeeded. I can see -wal files after this. > > > > > > > >> >> When the application wants to perform a bulk data load (loading around 21Gb >> of data), it runs the following before starting the transaction. >> >> pragma journal_mode=delete >> >> This is done because I am under the assumption that WAL journal mode is not >> suited for long running, big transactions (please correct me if I am wrong). >> >> But the journal mode change seems to be not affecting. Documentation for WAL >> states the following: >> >> "Unlike the other journaling modes, PRAGMA journal_mode=WAL is persistent. >> If a process sets WAL mode, then closes and reopens the database, the >> database will come back in WAL mode. In contrast, if a process sets (for >> example) PRAGMA journal_mode=TRUNCATE and then closes and reopens the >> database will come back up in the default rollback mode of DELETE rather >> than the previous TRUNCATE setting." >> >> Does this mean, in my case the journal mode change won't have any effect? >> When I tested, I still see -wal & -shm files present. Will it use "DELETE" >> journal mode for my huge transaction? >> >> Any help would be great! >> ? >> Navaneeth >> >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Changing WAL mode for a transaction
Hello, My application runs the following right after opening the connection to the database. pragma journal_mode=wal; pragma page_size=4096 When the application wants to perform a bulk data load (loading around 21Gb of data), it runs the following before starting the transaction. pragma journal_mode=delete This is done because I am under the assumption that WAL journal mode is not suited for long running, big transactions (please correct me if I am wrong). But the journal mode change seems to be not affecting. Documentation for WAL states the following: "Unlike the other journaling modes, PRAGMA journal_mode=WAL is persistent. If a process sets WAL mode, then closes and reopens the database, the database will come back in WAL mode. In contrast, if a process sets (for example) PRAGMA journal_mode=TRUNCATE and then closes and reopens the database will come back up in the default rollback mode of DELETE rather than the previous TRUNCATE setting." Does this mean, in my case the journal mode change won't have any effect? When I tested, I still see -wal & -shm files present. Will it use "DELETE" journal mode for my huge transaction? Any help would be great! ? Navaneeth
[sqlite] Changing WAL mode for a transaction
On 26 Apr 2015, at 1:01pm, Navaneeth K N wrote: > pragma page_size=4096 Just to remove this from your question, that PRAGMA has effect only before the first item is created in the database. Once the database file has something in it, it already has pages, so the page size is fixed. Further changes have no effect. Simon.
