Re: [sqlite] a question about muticonnection
Mario Frasca uttered: Christian Smith wrote: You can use the rowid to track the row version, and a unique constraint on your key to track conflicts. When updating a record, read the row, including the rowid. Update any columns. When writing the row out, delete the existing rowid, then insert the new updated row, all within a transaction. If noone has updated the row since you read it, the delete should delete one row and the insert should succeed. If someone else has updated the row using this protocol, the delete should delete no rows, and the insert should fail with a unique constraint violation on your key. maybe I'm missing something, but how does this behave if the row is updated a second time in the meanwhile (something like C acting as B between A-read and A-write)? I've the impression that the insert will not fail... If the row is subsequently updated, it doesn't matter, as the original row I based my record on has vanished due to the first update. My delete will delete no rows, and my insert will fail if the key clashes with an updated record. Basically, I'm using the rowid as a row version number, and assuming it is monotonically increasing (though not necessarily correctly.) The key identifies the unique record. It's a way of detecting conflicts, not preventing them. SQL UPDATE, OTOH, is atomic and thus prevents conflicts. just a doubt... regards, MF Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] a question about muticonnection
Christian Smith wrote: You can use the rowid to track the row version, and a unique constraint on your key to track conflicts. When updating a record, read the row, including the rowid. Update any columns. When writing the row out, delete the existing rowid, then insert the new updated row, all within a transaction. If noone has updated the row since you read it, the delete should delete one row and the insert should succeed. If someone else has updated the row using this protocol, the delete should delete no rows, and the insert should fail with a unique constraint violation on your key. maybe I'm missing something, but how does this behave if the row is updated a second time in the meanwhile (something like C acting as B between A-read and A-write)? I've the impression that the insert will not fail... just a doubt... regards, MF - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] a question about muticonnection
hongdong uttered: I just have a base question: assume user A and user B now both connection to a same database and both of them want to update a same record,but only one is allowed in this condition: A begin to browse the data in a client application,and load rowid into GUI and keep it in memory. B update the record which A want to update A now used the id stored in memory to update the same record now it's wrong. anyone can give me advice how to avoid this and keep the operation is effient You can use the rowid to track the row version, and a unique constraint on your key to track conflicts. When updating a record, read the row, including the rowid. Update any columns. When writing the row out, delete the existing rowid, then insert the new updated row, all within a transaction. If noone has updated the row since you read it, the delete should delete one row and the insert should succeed. If someone else has updated the row using this protocol, the delete should delete no rows, and the insert should fail with a unique constraint violation on your key. thanks! Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] a question about muticonnection
thank you all,all your ideas are good.btw,Stanton's logic transaction is a little hard to understand,can you show me a link where I can read the detail. 2006/12/7, Nicolas Williams <[EMAIL PROTECTED]>: On Wed, Dec 06, 2006 at 12:51:29PM -0600, John Stanton wrote: > To fully handle the situation you need to know how many users have a > transaction pending and are pondering ordering the item. That requires > some form of journal or "committed" total. If you have three items and > there are three people part way through an order then you really don't > have any stock until one of the three releases. > > It is not nice to mislead a customer that an item is available only to > fail to ultimately fulfil the order. What on Earth are you talking about? The original poster said nothing about ordering systems. Nico -- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] a question about muticonnection
On Wed, Dec 06, 2006 at 12:51:29PM -0600, John Stanton wrote: > To fully handle the situation you need to know how many users have a > transaction pending and are pondering ordering the item. That requires > some form of journal or "committed" total. If you have three items and > there are three people part way through an order then you really don't > have any stock until one of the three releases. > > It is not nice to mislead a customer that an item is available only to > fail to ultimately fulfil the order. What on Earth are you talking about? The original poster said nothing about ordering systems. Nico -- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] a question about muticonnection
Nicolas Williams wrote: On Wed, Dec 06, 2006 at 11:36:11AM -0600, John Stanton wrote: I fully understood. It is an age old problem that has puzzled generations of system designers. My first exposure was over thirty years ago. The approach we discovered worked was to treat it as a transaction in the logical sense. The decision then becomes one of granularity because obviously you cannot keep the whole DB locked to handle one item if you have multiple users. Logic using a committed journal and an application implemented rollback and commit can effectively implement the transaction in a multiple user environment. Another alternative is to run UPDATE ... SET ... WHERE column1 = AND column2 = ... and then check if the update happened -- if it didn't it's because the data read earlier was stale; recover by re-selecting the data, etc... Let the user see what SQL is executed, let them type in their own, and by and large you're there. - To unsubscribe, send email to [EMAIL PROTECTED] - To fully handle the situation you need to know how many users have a transaction pending and are pondering ordering the item. That requires some form of journal or "committed" total. If you have three items and there are three people part way through an order then you really don't have any stock until one of the three releases. It is not nice to mislead a customer that an item is available only to fail to ultimately fulfil the order. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] a question about muticonnection
On Wed, Dec 06, 2006 at 11:36:11AM -0600, John Stanton wrote: > I fully understood. It is an age old problem that has puzzled > generations of system designers. My first exposure was over thirty > years ago. The approach we discovered worked was to treat it as a > transaction in the logical sense. The decision then becomes one of > granularity because obviously you cannot keep the whole DB locked to > handle one item if you have multiple users. > > Logic using a committed journal and an application implemented rollback > and commit can effectively implement the transaction in a multiple user > environment. Another alternative is to run UPDATE ... SET ... WHERE column1 = AND column2 = ... and then check if the update happened -- if it didn't it's because the data read earlier was stale; recover by re-selecting the data, etc... Let the user see what SQL is executed, let them type in their own, and by and large you're there. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] a question about muticonnection
Nicolas Williams wrote: On Wed, Dec 06, 2006 at 10:04:42AM -0600, John Stanton wrote: hongdong wrote: I just have a base question: assume user A and user B now both connection to a same database and both of them want to update a same record,but only one is allowed in this condition: A begin to browse the data in a client application,and load rowid into GUI and keep it in memory. B update the record which A want to update A now used the id stored in memory to update the same record now it's wrong. anyone can give me advice how to avoid this and keep the operation is effient thanks! Use a transaction. That is why they are implemented. I think you misunderstood the poster. 'A' wants to read some rows, end its transaction and, some time later (minutes, hours, day), when the user manipulates the row through the GUI, update that row. 'A' can't keep the database locked for arbitrary periods of time, and 'B' needs to write independently of 'A'. Transactions don't help 'A' in this case. - To unsubscribe, send email to [EMAIL PROTECTED] - I fully understood. It is an age old problem that has puzzled generations of system designers. My first exposure was over thirty years ago. The approach we discovered worked was to treat it as a transaction in the logical sense. The decision then becomes one of granularity because obviously you cannot keep the whole DB locked to handle one item if you have multiple users. Logic using a committed journal and an application implemented rollback and commit can effectively implement the transaction in a multiple user environment. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] a question about muticonnection
On Wed, Dec 06, 2006 at 10:04:42AM -0600, John Stanton wrote: > hongdong wrote: > >I just have a base question: > >assume user A and user B now both connection to a same database and both of > >them want to update a same record,but only one is allowed > >in this condition: > >A begin to browse the data in a client application,and load rowid into GUI > >and keep it in memory. > >B update the record which A want to update > >A now used the id stored in memory to update the same record > >now it's wrong. > >anyone can give me advice how to avoid this and keep the operation is > >effient > >thanks! > > > Use a transaction. That is why they are implemented. I think you misunderstood the poster. 'A' wants to read some rows, end its transaction and, some time later (minutes, hours, day), when the user manipulates the row through the GUI, update that row. 'A' can't keep the database locked for arbitrary periods of time, and 'B' needs to write independently of 'A'. Transactions don't help 'A' in this case. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] a question about muticonnection
hongdong wrote: I just have a base question: assume user A and user B now both connection to a same database and both of them want to update a same record,but only one is allowed in this condition: A begin to browse the data in a client application,and load rowid into GUI and keep it in memory. B update the record which A want to update A now used the id stored in memory to update the same record now it's wrong. anyone can give me advice how to avoid this and keep the operation is effient thanks! Use a transaction. That is why they are implemented. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] a question about muticonnection
-Original Message- From: Nikki Locke [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 06, 2006 6:06 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] a question about muticonnection Hongdong wrote: > I just have a base question: > assume user A and user B now both connection to a same database and > both of them want to update a same record,but only one is allowed in > this condition: > A begin to browse the data in a client application,and load rowid into > GUI and keep it in memory. > B update the record which A want to update A now used the id stored in > memory to update the same record now it's wrong. > anyone can give me advice how to avoid this and keep the operation is Regarding: ... you can do it more easily with a timestamp on each record, which is updated every time the record is written - all you need to do then is to compare the timestamp. Since sqlite does not (inherently) have a central server, the clock of each connecting machine would be used for the timestamps. So I wonder if, even with fractional seconds, one might have duplicate timestamps. Even so, as long as one looks for *equality*, and doesn't depend on the newest timestamp as identifying the freshest update, then depending on your traffic, the timestamp might be sufficient. If I remember correctly, in a previous posting, I think Dr. H might have mentioned use of a random number for this purpose -- one with sufficient width to make collisions highly unlikely until long after our grandchildren cash in their retirement IRA's. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] a question about muticonnection
Thanks a lot,Nikki's solution is what I think about,but this may lost some efficiency,maybe this is the only way. Mario's solution is more easy,but there something hard to control,somebody will just open the window,displaying the data,but he will never modify the data,that will resist other client to view the same data. thanks again 2006/12/6, Mario Frasca <[EMAIL PROTECTED]>: hongdong wrote: > I just have a base question: > assume user A and user B now both connection to a same database and > both of > them want to update a same record,but only one is allowed > in this condition: > A begin to browse the data in a client application,and load rowid into > GUI > and keep it in memory. > B update the record which A want to update > A now used the id stored in memory to update the same record > now it's wrong. > anyone can give me advice how to avoid this and keep the operation is > effient > thanks! > this sounds like shared and exclusive locks. A wants a shared lock and possibly upgrade it. B wants an exclusive lock. when A starts first: once A gets its shared lock, B cannot receive an exclusive lock and goes in standby. A upgrades its lock to an exclusive lock (there are reasons not to start asking for a shared lock if you know from the beginning that you're going to need an exclusive lock). A releases the lock. B finally gets in. notice that if both A and B start asking a shared lock, you'll get in trouble... someone must be allowed to break locks and oblige clients to rollback. can't point you to english literature, but I'm sure Wikipedia contains useful hints. ciao, MF - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] a question about muticonnection
hongdong wrote: I just have a base question: assume user A and user B now both connection to a same database and both of them want to update a same record,but only one is allowed in this condition: A begin to browse the data in a client application,and load rowid into GUI and keep it in memory. B update the record which A want to update A now used the id stored in memory to update the same record now it's wrong. anyone can give me advice how to avoid this and keep the operation is effient thanks! this sounds like shared and exclusive locks. A wants a shared lock and possibly upgrade it. B wants an exclusive lock. when A starts first: once A gets its shared lock, B cannot receive an exclusive lock and goes in standby. A upgrades its lock to an exclusive lock (there are reasons not to start asking for a shared lock if you know from the beginning that you're going to need an exclusive lock). A releases the lock. B finally gets in. notice that if both A and B start asking a shared lock, you'll get in trouble... someone must be allowed to break locks and oblige clients to rollback. can't point you to english literature, but I'm sure Wikipedia contains useful hints. ciao, MF - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] a question about muticonnection
Hongdong wrote:
> I just have a base question:
> assume user A and user B now both connection to a same database and both of
> them want to update a same record,but only one is allowed
> in this condition:
> A begin to browse the data in a client application,and load rowid into GUI
> and keep it in memory.
> B update the record which A want to update
> A now used the id stored in memory to update the same record
> now it's wrong.
> anyone can give me advice how to avoid this and keep the operation is
> effient
You need to be able to detect if the record has changed since you read it. If
it has, then you can choose what to do - overwrite, cancel, or perhaps you
could program some kind of merge to offer a combination of the two changes (if
the users changed different columns).
One way to do this is to keep a complete copy of the record when read, then
inside the update transaction, re-read the record and compare all the data
with the original.
Provided you don't need the merge option, you can do it more easily with a
timestamp on each record, which is updated every time the record is written -
all you need to do then is to compare the timestamp.
You could update the timestamp automatically in a trigger if you like -
something like...
CREATE TRIGGER insert_notes_timeStamp AFTER INSERT ON notes
BEGIN
UPDATE notes SET timeStamp = strftime('%Y-%m-%d %H:%M:%f', 'now')
WHERE rowid = new.rowid;
END;
CREATE TRIGGER update_notes_timeStamp AFTER UPDATE ON notes
BEGIN
UPDATE notes SET timeStamp = strftime('%Y-%m-%d %H:%M:%f', 'now')
WHERE rowid = new.rowid;
END;
Notice I used strftime to create the timestamp - TIME('now') does not return
the fractional seconds, so you could conceivably get two updates with the same
timestamp.
--
Nikki Locke, Trumphurst Ltd. PC & Unix consultancy & programming
http://www.trumphurst.com/
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] a question about muticonnection
I just have a base question: assume user A and user B now both connection to a same database and both of them want to update a same record,but only one is allowed in this condition: A begin to browse the data in a client application,and load rowid into GUI and keep it in memory. B update the record which A want to update A now used the id stored in memory to update the same record now it's wrong. anyone can give me advice how to avoid this and keep the operation is effient thanks!
