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]
