Re: [sqlite] Commit and rollback behaviour during power loss
There is one degenerate case, which has been discussed a few times on this list. With PRAGMA journal_mode=DELETE (the default), the atomic signal that marks a transaction being committed is the deletion of the rollback journal. Deleting a file is a directory level operation, which means there are three files being changed: 1. The main DB file 2. The rollback journal 3. The directory By default sqlite fsyncs (1) and (2) as part of COMMIT, but not (3). This makes it possible for your program's control flow to get an SQLITE_OK result from "conn.commit()" but then lose power before the OS syncs the directory change to disk. As a result the transaction will be rolled back when the DB is next opened. It's a pretty extreme scenario, but you can avoid the risk via PRAGMA synchronous=EXTRA (to also sync the directory), or PRAGMA journal_mode=truncate (avoiding the need to modify the directory). I don't think there is any scenario in which power loss could result in multiple transactions being rolled back (unless fsync lies of course, in which case all bets are off). -Rowan On 24 November 2017 at 16:47, Blagovest Buyukliev < blagovest.buyukl...@gmail.com> wrote: > Let's say we have the following Python code: > > import sqlite3 > conn = sqlite3.connect('mydb.db') > c = conn.cursor() > c.execute("INSERT INTO ...") > conn.commit() > c.execute("INSERT INTO ...") > conn.commit() > > Can it be assumed that after conn.commit() has returned, fsync() has > been called on the file and (assuming that fsync doesn't lie and > doesn't just act as a barrier), the last inserted row has made its way > to disk? > > Also, say that power is lost during the *second* conn.commit(). Is > there a possibility that the database will be rolled back to the state > before the first INSERT, or is it guaranteed that it will be rolled > back to the state before the second INSERT? > ___ > 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] Commit and rollback behaviour during power loss
> If Commit returns with SQLITE_OK, then YES, it is handed off to the disk > with some caveats, namely: > > A - We are assuming the Python sqlite3 wrapper you use doesn't do > obfuscation of any sort and directly calls the sqlite3 API and returns the > direct result from those calls. If you haven't yet, consider using (or at > least trying) the APSW wrapper. Some inspection of the Python wrapper would reveal that. The important thing you note is that this condition can be distinguished from the C API. > B - When SQLite reports back SQLITE_OK after a commit, it means that SQLite > has handed off the fsync() to the OS. Some OSes may lie about this having > been effected yet (in the interest of speed) and also, perhaps even > unbeknown to the OS, the hardware itself may lie about having actually > committed those bits to platters/NVRam/etc. Implementing a good database > system is as much about making the hardware comply as making the software > work. That said, data failures/corruption due to this B caveat is extremely > improbable and only ever happens in the weirdest of circumstances. The hardware and the OS are pretty much a fixed combination, since this will be used in an in-house hosted solution with Linux and some chosen enterprise-level storage. > [1] What I am not seeing in your code is conn.startTransaction() (or however > that will be named for you). How does the connection know you have meant for > a transaction to start so as to commit it later? I'm also not seeing you > testing any return value from those conn.commit() calls, but that might just > be for brevity of the post. They can however return a failure code, in which > case the commit didn't happen. Yes, the code is purely illustrative and lacks any error handling for brevity. > Again, if the first Commit returned with SQLITE_OK (with noting the above > caveats again), then no, that state is forever captured and part of the DB > now, and any new transaction will have its own atomicity. That's a very good thing which renders our potential solution viable. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Commit and rollback behaviour during power loss
On 2017/11/24 10:47 AM, Blagovest Buyukliev wrote: Let's say we have the following Python code: import sqlite3 conn = sqlite3.connect('mydb.db') c = conn.cursor() c.execute("INSERT INTO ...") conn.commit() c.execute("INSERT INTO ...") conn.commit() Can it be assumed that after conn.commit() has returned, fsync() has been called on the file and (assuming that fsync doesn't lie and doesn't just act as a barrier), the last inserted row has made its way to disk? Probably not[1], but lets assume for a moment a transaction was started successfully, then: If Commit returns with SQLITE_OK, then YES, it is handed off to the disk with some caveats, namely: A - We are assuming the Python sqlite3 wrapper you use doesn't do obfuscation of any sort and directly calls the sqlite3 API and returns the direct result from those calls. If you haven't yet, consider using (or at least trying) the APSW wrapper. B - When SQLite reports back SQLITE_OK after a commit, it means that SQLite has handed off the fsync() to the OS. Some OSes may lie about this having been effected yet (in the interest of speed) and also, perhaps even unbeknown to the OS, the hardware itself may lie about having actually committed those bits to platters/NVRam/etc. Implementing a good database system is as much about making the hardware comply as making the software work. That said, data failures/corruption due to this B caveat is extremely improbable and only ever happens in the weirdest of circumstances. Also, say that power is lost during the *second* conn.commit(). Is there a possibility that the database will be rolled back to the state before the first INSERT, or is it guaranteed that it will be rolled back to the state before the second INSERT? Again, if the first Commit returned with SQLITE_OK (with noting the above caveats again), then no, that state is forever captured and part of the DB now, and any new transaction will have its own atomicity. [1] What I am not seeing in your code is conn.startTransaction() (or however that will be named for you). How does the connection know you have meant for a transaction to start so as to commit it later? I'm also not seeing you testing any return value from those conn.commit() calls, but that might just be for brevity of the post. They can however return a failure code, in which case the commit didn't happen. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Commit and rollback behaviour during power loss
Let's say we have the following Python code: import sqlite3 conn = sqlite3.connect('mydb.db') c = conn.cursor() c.execute("INSERT INTO ...") conn.commit() c.execute("INSERT INTO ...") conn.commit() Can it be assumed that after conn.commit() has returned, fsync() has been called on the file and (assuming that fsync doesn't lie and doesn't just act as a barrier), the last inserted row has made its way to disk? Also, say that power is lost during the *second* conn.commit(). Is there a possibility that the database will be rolled back to the state before the first INSERT, or is it guaranteed that it will be rolled back to the state before the second INSERT? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] commit and rollback
You can use the SQLITE_BUSY returned by sqlite3_step to synchronize. Just pause and resubmit the call when you get it. arbalest06 wrote: about this synchronization of multiple writers, can you please explain on how to make this possible? or your just saying that i need to make a daemon that will eventually synchronize the writers? John Stanton-3 wrote: Multiple writers merely have to be synchronized. arbalest06 wrote: so there is really no way that multiple processes can write into the database?..but multiple processes can read at the same time right?.. Igor Tandetnik wrote: arbalest06 <[EMAIL PROTECTED]> wrote: q#1: is it possible that multiple users can write into the database at the same time? No. q#2: if users A, B, C are writing to the database at the same time, They can't. q#3: if users A, B, C are writing to the database at the same time, They can't. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] commit and rollback
about this synchronization of multiple writers, can you please explain on how to make this possible? or your just saying that i need to make a daemon that will eventually synchronize the writers? John Stanton-3 wrote: > > Multiple writers merely have to be synchronized. > > arbalest06 wrote: >> so there is really no way that multiple processes can write into the >> database?..but multiple processes can read at the same time right?.. >> >> >> Igor Tandetnik wrote: >>> arbalest06 <[EMAIL PROTECTED]> wrote: q#1: is it possible that multiple users can write into the database at the same time? >>> No. >>> q#2: if users A, B, C are writing to the database at the same time, >>> They can't. >>> q#3: if users A, B, C are writing to the database at the same time, >>> They can't. >>> >>> Igor Tandetnik >>> >>> - >>> To unsubscribe, send email to [EMAIL PROTECTED] >>> - >>> >>> >>> >> > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > -- View this message in context: http://www.nabble.com/commit-and-rollback-tf4804976.html#a14018116 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] commit and rollback
Multiple writers merely have to be synchronized. arbalest06 wrote: so there is really no way that multiple processes can write into the database?..but multiple processes can read at the same time right?.. Igor Tandetnik wrote: arbalest06 <[EMAIL PROTECTED]> wrote: q#1: is it possible that multiple users can write into the database at the same time? No. q#2: if users A, B, C are writing to the database at the same time, They can't. q#3: if users A, B, C are writing to the database at the same time, They can't. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] commit and rollback
On 11/28/07, arbalest06 <[EMAIL PROTECTED]> wrote: > ok..thanx for that..now if process A is writing into the database, and > process B attempts to write, does sqlite take note of B's attempt and gives > the permission to B when A is done? like would it be a queue that the first > process that attempted to write should be given priority to write? or is it > that when process A is done, then process C attempts to write, C is given > the permission to write even though process B attempted to write first than > C? There's no queue. When process B attempts to write, it will be refused, and must try again later itself. It controls if/when it does so; SQLite doesn't force any particular behavior. If process C tries before B tries again, then C will get to write and B will have to wait again. In API terms, an sqlite function will return SQLITE_BUSY if it can't do what it needs to do right now. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] commit and rollback
ok..thanx for that..now if process A is writing into the database, and process B attempts to write, does sqlite take note of B's attempt and gives the permission to B when A is done? like would it be a queue that the first process that attempted to write should be given priority to write? or is it that when process A is done, then process C attempts to write, C is given the permission to write even though process B attempted to write first than C? Sreedhar.a wrote: > >>so there is really no way that multiple processes can write into the > database?..but multiple processes can read at the >>same time right?.. > > --Yes > > -Sreedhar > > > Igor Tandetnik wrote: >> >> arbalest06 <[EMAIL PROTECTED]> wrote: >>> q#1: is it possible that multiple users can write into the database >>> at the same time? >> >> No. >> >>> q#2: if users A, B, C are writing to the database at the same time, >> >> They can't. >> >>> q#3: if users A, B, C are writing to the database at the same time, >> >> They can't. >> >> Igor Tandetnik >> >> -- >> --- To unsubscribe, send email to >> [EMAIL PROTECTED] >> -- >> --- >> >> >> > > -- > View this message in context: > http://www.nabble.com/commit-and-rollback-tf4804976.html#a13991789 > Sent from the SQLite mailing list archive at Nabble.com. > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > -- View this message in context: http://www.nabble.com/commit-and-rollback-tf4804976.html#a13992544 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] commit and rollback
>so there is really no way that multiple processes can write into the database?..but multiple processes can read at the >>same time right?.. --Yes -Sreedhar Igor Tandetnik wrote: > > arbalest06 <[EMAIL PROTECTED]> wrote: >> q#1: is it possible that multiple users can write into the database >> at the same time? > > No. > >> q#2: if users A, B, C are writing to the database at the same time, > > They can't. > >> q#3: if users A, B, C are writing to the database at the same time, > > They can't. > > Igor Tandetnik > > -- > --- To unsubscribe, send email to > [EMAIL PROTECTED] > -- > --- > > > -- View this message in context: http://www.nabble.com/commit-and-rollback-tf4804976.html#a13991789 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] commit and rollback
so there is really no way that multiple processes can write into the database?..but multiple processes can read at the same time right?.. Igor Tandetnik wrote: > > arbalest06 <[EMAIL PROTECTED]> wrote: >> q#1: is it possible that multiple users can write into the database >> at the same time? > > No. > >> q#2: if users A, B, C are writing to the database at the same time, > > They can't. > >> q#3: if users A, B, C are writing to the database at the same time, > > They can't. > > Igor Tandetnik > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > -- View this message in context: http://www.nabble.com/commit-and-rollback-tf4804976.html#a13991789 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] commit and rollback
good day! thanx guys for helping me out..i got it working already..i just misunderstood your solutions, that's why it took me a while to get it right.. =^D now i have another question, still related to this topic, but more on theoretical.. q#1: is it possible that multiple users can write into the database at the same time? q#1.1: if yes, what can i do to make this possible (e.g. compiling the sqlite source code with a macro for this scenario )? q#2: if users A, B, C are writing to the database at the same time, then user A commits first, followed by B, does the changes imposed by user A is ignored and is overwritten by the changes imposed by user B? q#3: if users A, B, C are writing to the database at the same time, then if user A commits first, followed by B, and if user C rolls back, from what point does the database rolls back? explanations and C sample codes are very much appreciated.. thanx and God bless! :working: -- View this message in context: http://www.nabble.com/commit-and-rollback-tf4804976.html#a13991028 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] commit and rollback
Why not just test the status returned by sqlite3_step? Use sqlite3_prepare_v2 to make the returned status give more information. When you get an error just finalize your COMMIT statement and move your pointer from the prepared COMMIT statement to a ROLLBACK and shortcut the transaction. Instead of committing you will rollback. arbalest06 wrote: good day! i think its really a good solution to this problem. However, im required to implement the c apis of sqlite..so i need to use the sqlite3_commit_hook and sqlite3_rollback_hook..im doing some prototyping to see their functionalities but i really cant make it work..and i also cant find some C sample source code using these apis..if it is possible, can someone post some c code using these apis?.. thank you and more power! God bless!.. John Stanton-3 wrote: All you need to do is to test the returned status of your sqlite3_step calls and if you get an error launch an SQL statement "ROLLBACK" and bail out of the transaction. If there are no errors you complete your transaction with an SQL "COMMIT". sqlite_prepare_v2 SQL statements exec BEGIN count = 0; while ((count < MAX) && !error) { rc = sqlite3_step SQL statement[count++]; if (rc != SQLITE_OK) { error = TRUE; } } if (error) exec ROLLBACK else exec COMMIT d_maniger06 wrote: good day!.. i have a list of records that i want to insert in my database..if ever an error occurred ( e.g. insert was not successful ), i want to undo all the previous inserts that i have done..to do this, i have read that i would need to use sqlite3_commit_hook and sqlite3_rollback_hook..i have read their descriptions in the sqlite site, but i really can't understand it..im just a newbie in sqlite by the way..i also have tried searching sample c codes that does these things but i really cant find any.. i would just like to request a simple sample code demonstrating on how to use these two apis..if it is ok also, please leave a very brief explanation on what happened in the code.. thank you and God bless!.. :working: - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] commit and rollback
On 11/26/07, arbalest06 <[EMAIL PROTECTED]> wrote: > John Stanton-3 wrote: > > All you need to do is to test the returned status of your sqlite3_step > > calls and if you get an error launch an SQL statement "ROLLBACK" and > > bail out of the transaction. If there are no errors you complete your > > transaction with an SQL "COMMIT". > i think its really a good solution to this problem. However, im required to > implement the c apis of sqlite..so i need to use the sqlite3_commit_hook and > sqlite3_rollback_hook..im doing some prototyping to see their > functionalities but i really cant make it work..and i also cant find some C > sample source code using these apis..if it is possible, can someone post > some c code using these apis?.. SQL *is* part of the API of SQLite. There is no separate C API to implement transactions; they are part of SQL. The commit and rollback hooks are called when someone uses the relevant SQL statements. They cannot be used to do what you have described, a transactional batch insert. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] commit and rollback
good day! i think its really a good solution to this problem. However, im required to implement the c apis of sqlite..so i need to use the sqlite3_commit_hook and sqlite3_rollback_hook..im doing some prototyping to see their functionalities but i really cant make it work..and i also cant find some C sample source code using these apis..if it is possible, can someone post some c code using these apis?.. thank you and more power! God bless!.. John Stanton-3 wrote: > > All you need to do is to test the returned status of your sqlite3_step > calls and if you get an error launch an SQL statement "ROLLBACK" and > bail out of the transaction. If there are no errors you complete your > transaction with an SQL "COMMIT". > > sqlite_prepare_v2 SQL statements > > exec BEGIN > > count = 0; > while ((count < MAX) && !error) { > rc = sqlite3_step SQL statement[count++]; > if (rc != SQLITE_OK) { > error = TRUE; > } > } > if (error) exec ROLLBACK > else exec COMMIT > > d_maniger06 wrote: >> good day!.. >> >> i have a list of records that i want to insert in my database..if ever an >> error occurred ( e.g. insert was not successful ), i want to undo all the >> previous inserts that i have done..to do this, i have read that i would >> need >> to use sqlite3_commit_hook and sqlite3_rollback_hook..i have read their >> descriptions in the sqlite site, but i really can't understand it..im >> just a >> newbie in sqlite by the way..i also have tried searching sample c codes >> that >> does these things but i really cant find any.. >> >> i would just like to request a simple sample code demonstrating on how to >> use these two apis..if it is ok also, please leave a very brief >> explanation >> on what happened in the code.. >> >> thank you and God bless!.. :working: > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > -- View this message in context: http://www.nabble.com/commit-and-rollback-tf4804976.html#a13963043 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] commit and rollback
All you need to do is to test the returned status of your sqlite3_step calls and if you get an error launch an SQL statement "ROLLBACK" and bail out of the transaction. If there are no errors you complete your transaction with an SQL "COMMIT". sqlite_prepare_v2 SQL statements exec BEGIN count = 0; while ((count < MAX) && !error) { rc = sqlite3_step SQL statement[count++]; if (rc != SQLITE_OK) { error = TRUE; } } if (error) exec ROLLBACK else exec COMMIT d_maniger06 wrote: good day!.. i have a list of records that i want to insert in my database..if ever an error occurred ( e.g. insert was not successful ), i want to undo all the previous inserts that i have done..to do this, i have read that i would need to use sqlite3_commit_hook and sqlite3_rollback_hook..i have read their descriptions in the sqlite site, but i really can't understand it..im just a newbie in sqlite by the way..i also have tried searching sample c codes that does these things but i really cant find any.. i would just like to request a simple sample code demonstrating on how to use these two apis..if it is ok also, please leave a very brief explanation on what happened in the code.. thank you and God bless!.. :working: - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] commit and rollback
good day!.. i have a list of records that i want to insert in my database..if ever an error occurred ( e.g. insert was not successful ), i want to undo all the previous inserts that i have done..to do this, i have read that i would need to use sqlite3_commit_hook and sqlite3_rollback_hook..i have read their descriptions in the sqlite site, but i really can't understand it..im just a newbie in sqlite by the way..i also have tried searching sample c codes that does these things but i really cant find any.. i would just like to request a simple sample code demonstrating on how to use these two apis..if it is ok also, please leave a very brief explanation on what happened in the code.. thank you and God bless!.. :working: -- View this message in context: http://www.nabble.com/commit-and-rollback-tf4804976.html#a13746534 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Commit and Rollback
On Jul 28, 2005, at 4:29 AM, Edwin Knoppert wrote: And i wish for a test if the transaction is already set or not, without using a callback. You already can: " int sqlite3_get_autocommit(sqlite3*); Test to see whether or not the database connection is in autocommit mode. Return TRUE if it is and FALSE if not. Autocommit mode is on by default. Autocommit is disabled by a BEGIN statement and reenabled by the next COMMIT or ROLLBACK. "
Re: [sqlite] Commit and Rollback
And i wish for a test if the transaction is already set or not, without using a callback. Even better, nested tranactions would be the best. :) - Original Message - From: "Marco Bambini" <[EMAIL PROTECTED]> To: Sent: Thursday, July 28, 2005 9:24 AM Subject: [sqlite] Commit and Rollback I need to know when a commit or a rollback is executed, I know that I can use the sqlite_commit_hook routine to be notified of a commit command, but what about rollback? Is there a way to know (or to be notified) when a rollback happens? Thanks a lot for your help, Marco Bambini
[sqlite] Commit and Rollback
I need to know when a commit or a rollback is executed, I know that I can use the sqlite_commit_hook routine to be notified of a commit command, but what about rollback? Is there a way to know (or to be notified) when a rollback happens? Thanks a lot for your help, Marco Bambini