Re: [sqlite] Optimistic concurrency control
Mikey C wrote: > Maybe I didn't make the question clear. I'm not talking about locking and > multiple writers. I'm talking about optimistic concurrency control in a > disconnected environment. > > Two processes (say a webserver). One reads some data and presents it to a > user (open - read - close). The other reads the same same data and presents > it to another user (open - read - close). The first user updates the data > (open - write - close). Several seconds/minutes later the second user > updates the same data (open - read - close). Result is the first users > changes are lost. I usually use a timestamp for this. I believe recent versions of SQLite support auto-updating timestamps (which record the time the record was last updated). -- Nikki Locke, Trumphurst Ltd. PC & Unix consultancy & programming http://www.trumphurst.com/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Optimistic concurrency control
Mikey C uttered: Hi, Maybe I didn't make the question clear. I'm not talking about locking and multiple writers. I'm talking about optimistic concurrency control in a disconnected environment. Two processes (say a webserver). One reads some data and presents it to a user (open - read - close). The other reads the same same data and presents it to another user (open - read - close). The first user updates the data (open - write - close). Several seconds/minutes later the second user updates the same data (open - read - close). Result is the first users changes are lost. Aha. OK, I see where you're coming from. ALL I am asking is could SQLite give each table a special column that increases it's value for each row whenever data changes? I could implement is with a trigger on each table BUT it would be nice if SQLite supported this natively. Anyhow, I get from the tone of the answers that this is not likely to happen, so I'll code it up myself. Sorry if I sounded arsy. I wasn't trying to, I was just trying to be succinct. Cheers, Mike Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Optimistic concurrency control
Hello On Tue, 19 Sep 2006 11:24:02 -0700 (PDT), you wrote: >Maybe I didn't make the question clear. I'm not talking about locking and >multiple writers. I'm talking about optimistic concurrency control in a >disconnected environment. > >IF anyone has changed the data since you last read it, the UPDATE affects no >rows and you know your update failed due to optimistic concurrency failure. I solved this problem by doing a logical Recordlocking. I connect to a SQL-Server DB always in optimistic Mode... and I open my SQLite-DB as if it was also (and any other DB too). The solution is a logical Recordlocking.That is what I do, before I try to alter a record. I check, if this attempt to write is permitted, to avoid that one User overwrite the changes from another User. Therefore I store (after I fetched the wanted Record) a Timestamp to a specific Table in a external specific DB (a specially Key-Collection). The TimeStamp contains: - the Users ID (Users Domain-ID) - the Workstations-ID (Network-Computername) - the Operating-Systems Process-ID - a initial Timestamp with Date and exhausted Seconds this Day - a Heartbeat-Timestamp (Always after 10 minutes every process updates his own locks) - a Class or Data-ID and - the Records ID (such as a Personnel number, not the RecID). It's needed, to lock also Records in several Childtables with same Personnel number, or so. If this full Timestamp successful written to the Recordlocking-Table, I perform a search to another Record with same tokens. If I found anyone and the founded initial Timestamp is earlier than my, only read is allowed to me. If my own Timestamp is the earliest, I can write and any other user only can read. If this "Edit-Class" is closed, or Programm is ended, the Lock becomes removed. If moved to another Record, the Timestamps and the Records-ID will be updated. If change to another class, the Class-ID will be updated in addition.. Sometimes I search dead or lost Locks and remove them. Dead or Lost Locks are Locks, which Heartbeat is older than 2 hours. In this way I lock also whole Tables or the full DB. That works really successful in a Network-Environment with up to 1500 concurrent users. The real advantage is in that way, that permitted Jobs (started by a Admin) can get and hold full rights. Best Regards Thomas www.thlu.de - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Optimistic concurrency control
Hi, Maybe I didn't make the question clear. I'm not talking about locking and multiple writers. I'm talking about optimistic concurrency control in a disconnected environment. Two processes (say a webserver). One reads some data and presents it to a user (open - read - close). The other reads the same same data and presents it to another user (open - read - close). The first user updates the data (open - write - close). Several seconds/minutes later the second user updates the same data (open - read - close). Result is the first users changes are lost. You can of course create a complex WHERE clause in all your SQL UPDATE statements so that an update only succeeds in changing a row if the all the column values match the original values. e.g. UPDATE ... SET col1 = new_value_1 col2 = new_value_2 WHERE col1 = old_value_1 AND col2 = old_value_2 etc. BUT (and here's what I'm asking) many databases (e.g. MS SQL Server) have a special data type or column in each table for each row which is an incrementing value. Whenever a row is written to, this value changes/increases. Hence your where clause needs only include: WHERE row_version_column = old_row_version_value IF anyone has changed the data since you last read it, the UPDATE affects no rows and you know your update failed due to optimistic concurrency failure. ALL I am asking is could SQLite give each table a special column that increases it's value for each row whenever data changes? I could implement is with a trigger on each table BUT it would be nice if SQLite supported this natively. Anyhow, I get from the tone of the answers that this is not likely to happen, so I'll code it up myself. Cheers, Mike Christian Smith-4 wrote: > > Mikey C uttered: > >> >> What are peoples thoughts on implementing optimistic concurrency control >> in >> SQLite? > > > Not an option. SQLite has a single writer database locking protocol which > can't handle multiple writers, so the issue of concurrency control is > moot. > > >> >> One way is modify the where clause to compare every column being updated, >> old value to new value. This makes the SQL cumbersome. >> >> Microsoft SQL Server has a column data type named TIMESTAMP/ROWVERSION >> which >> is database-wide unique number that gets updated every time a row gets >> updated. Effectively it is a hash of all the current values in every row >> of >> the table and is updated automtically. >> >> Hence to see if any row has been updated by another person you just have >> to >> compare the TIMESTAMP/ROWVERSION value you read with the one currently >> in >> the table in the UPDATE where clause. >> >> >> >> Q. Does SQlite has such a capability? Can we have one please? If not, is >> it >> easy to simulate one? If not, how do people manage concurrency in >> applications such as web sites? >> > > A. No. Probably not. Probably not. Use a client/server DB such as > PostgreSQL which already has multiple version concurrency control. > > Right tool for the job. If it's multiple concurrent writers, SQLite isn't > it. > > > Christian > > > > -- > /"\ > \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL > X - AGAINST MS ATTACHMENTS > / \ > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > -- View this message in context: http://www.nabble.com/Optimistic-concurrency-control-tf2299903.html#a6394076 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Optimistic concurrency control
Mikey C uttered: What are peoples thoughts on implementing optimistic concurrency control in SQLite? Not an option. SQLite has a single writer database locking protocol which can't handle multiple writers, so the issue of concurrency control is moot. One way is modify the where clause to compare every column being updated, old value to new value. This makes the SQL cumbersome. Microsoft SQL Server has a column data type named TIMESTAMP/ROWVERSION which is database-wide unique number that gets updated every time a row gets updated. Effectively it is a hash of all the current values in every row of the table and is updated automtically. Hence to see if any row has been updated by another person you just have to compare the TIMESTAMP/ROWVERSION value you read with the one currently in the table in the UPDATE where clause. Q. Does SQlite has such a capability? Can we have one please? If not, is it easy to simulate one? If not, how do people manage concurrency in applications such as web sites? A. No. Probably not. Probably not. Use a client/server DB such as PostgreSQL which already has multiple version concurrency control. Right tool for the job. If it's multiple concurrent writers, SQLite isn't it. Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Optimistic concurrency control
What are peoples thoughts on implementing optimistic concurrency control in SQLite? One way is modify the where clause to compare every column being updated, old value to new value. This makes the SQL cumbersome. Microsoft SQL Server has a column data type named TIMESTAMP/ROWVERSION which is database-wide unique number that gets updated every time a row gets updated. Effectively it is a hash of all the current values in every row of the table and is updated automtically. Hence to see if any row has been updated by another person you just have to compare the TIMESTAMP/ROWVERSION value you read with the one currently in the table in the UPDATE where clause. Q. Does SQlite has such a capability? Can we have one please? If not, is it easy to simulate one? If not, how do people manage concurrency in applications such as web sites? -- View this message in context: http://www.nabble.com/Optimistic-concurrency-control-tf2299903.html#a6391291 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
