Another thing I forgot is about timestamps - that seems like a very good idea, would it work if I simply added a column to each table and I would put the timestamp there myself everytime a row gets modified? That way I'll only have to check that column, instead of all for changes. Right?
Dennis ----- Original Message ----- From: "Dennis Volodomanov" <[EMAIL PROTECTED]> To: "Greg Obleshchuk" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, October 29, 2003 5:36 PM Subject: Re: [sqlite] Checking the busy state > Thank you Greg for the detailed explanation. I'll try to implement this way, > but just one more question :-) How do I get the original values, or rather, > when? I mean I should read them in first, then check if the database is > locked and then use your routine below? > > Thanks! > > Dennis > > ----- Original Message ----- > From: "Greg Obleshchuk" <[EMAIL PROTECTED]> > To: "Dennis Volodomanov" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Wednesday, October 29, 2003 2:19 PM > Subject: Re: [sqlite] Checking the busy state > > > Hi , > The example that you have given highlight an the issue well, but that issue > is not about locking . That issue is about data retention and how to deal > with updated rows. > Your user B may have a form with the data displayed on their screen then > some time later after use A has delete the row user b might try to modify > it. The time between User B retrieving the row then user A deleting the row > and finally user B updating the row might be minutes or hours. In any case > the issue is the same an update command is issued on a row that doesn't > exist. > > A simple solution and one you should always do in a multi-user system is try > and retrieve the PK before issuing the delete. You would also wrap this in > a transactions. > So in your example The row in question has a PK column called ID and a > value of 2 > > the command from User B would be (lots of b's here) > > Begin transaction > select count(*) from tableName where ID = 2 > if count is equal to one then > update tablename set 333,. where ID = 2 > else > return some message back to user say the row has been deleted > end the if > commit transaction > > > There is also another issue you need to consider in a multi-user system. If > User A was updating the row not deleting then User B may over write User A > changes. In this situation other DB system have columns designated as > Timestamps (I know MS SQL has) . Every time a row is updated the system > updates the timestamp column automatically. Then the process is simply to > compare the timestamp and if they are the same then no changes have taken > place. If the differ then warn the users someone else has changed the row > and ask what they want to do. SQLite doesn't have this timestamp concept . > (it would be great to have it, hidden like the ROWID column) so you should > be comparing every column in the row to the original values the user has and > then if they are the same update otherwise ask a question. > so an update becomes > > Begin transaction > select count(*) from tableName where ID = 2 and col1 = 'orginalcol1data' > and col2='orginalcol2data',... and so on > if count is equal to one then > update tablename set 333,. where ID = 2 > else > return some message back to user say the row has been updated by someone > else > end the if > commit transaction > > The SQL_Busy issue is just one of when the command was issued the database > was locked (this needs to be handled as described in my first email) this > other issue is more important to you in a multi user system (and it more > generic it happens on all DB systems) > > regards > Greg O > > > ----- Original Message ----- > From: Dennis Volodomanov > To: [EMAIL PROTECTED] > Sent: Wednesday, October 29, 2003 1:53 PM > Subject: Re: [sqlite] Checking the busy state > > > Thank you Greg, > > But I think that you've implemented a different behavior than what I'm > looking for (or please correct me if I'm wrong!). > > What happens if the user modifies the row that a second user has placed a > query for? For example, user A access the db first and deletes one row, at > the same time user B tried to modify that row, but had to wait because A is > working with the db. After A deleted the row, the db is released and B's > query to modify a now-deleted row is sent. I would rather just give a > message to the user that the db is locked and ask him to come back later > than to wait and process that query. And I wish to update user B's db > display after A has finished working with the db (I'm thinking of using > mailslots for that). > > Dennis > > > ----- Original Message ----- > From: Greg Obleshchuk > To: Dennis Volodomanov > Sent: Wednesday, October 29, 2003 11:40 AM > Subject: Re: [sqlite] Checking the busy state > > > Hi Dennis, > I have just done some work on this. Take a look at this wiki page > http://www.sqlite.org/cvstrac/wiki?p=MultiThreading > > The lock is only help while updating. If you follow the instructions in > the above page > Updates in transactions > > and you add a loop like the one in the solution then actual SQLITE_BUSY > situation should be reduced to a bare minimum. > > You approach of using a secondary table to write to and see is not the > best approach because if the write works there is no guarantee that the > write to your primary table will work (someone might get in and still lock > it) > > Yet an update in a transaction if fails will rollback. So the code could > look something like > > begin transaction > > while not sqlite_busy and retries count not reached > update table > increase a retries counter > if sqlite_busy delay for some time (10ms) > loop > > if not sqlite_busy then commit transaction else rollback transaction > > regards > Greg O > ----- Original Message ----- > From: Dennis Volodomanov > To: [EMAIL PROTECTED] > Sent: Wednesday, October 29, 2003 11:07 AM > Subject: [sqlite] Checking the busy state > > > Hello, > > I would like to check for the state of the database before letting the > user change any values, because it's on a LAN and could be in use by another > machine, but how can I do that without issuing a sqlite_exec() that would > attempt to write something and then checking for the SQLITE_BUSY? Is there > any way except that? > > If I understand correctly, I can set up a one-row table for that > purpose and before each modification try to write to it and see what the > return is? But will I get a SQLITE_BUSY if another table is being used? Does > SQLite lock the entire database when it works with it or just one table? I > read the explanation of SQLITE_LOCKED, but it didn't answer my question - as > I understood it, it means if I screw up somehow then it's issued? > > Thank you in advance, > > Dennis > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
