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] -----------------------------------------------------------------------------
