What are peoples thoughts on implementing optimistic concurrency control in
SQLite?
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/RWVERSIN 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/RWVERSIN 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?

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.

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/RWVERSIN 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/RWVERSIN 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

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). 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.


>>
>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/RWVERSIN
>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/RWVERSIN 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

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). 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. K, 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

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). 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).