Re: [sqlite] PRAGMA locking_mode could stand a rewrite

[Pavel Ivanov]

>   1. When is a lock released during a SELECT? Is it after the first
>  call to step()? The last call to step()? The call to finalize()?
>  Yes, I'm using the C API.

Lock is released during the call to finalize() or reset().

>   2. If while in the step() process of a SELECT, there is a change made
>  to the db, will those changes affect SELECT's result set? In MySQL
>  I'm used to the result set of a SELECT reflecting the state of a
>  db at an instant, and not subject to change by any subsequent
>  INSERTs and UPDATEs, but I don't know if it's the same w/SQLite.

There cannot be any changes in database while you're in process of
stepping through select statement. Of course unless you're doing
changes through the same SQLite connection.

>   3. If I have a permanent write lock, and I go to
>  "locking_mode=NORMAL" will a SELECT remove the permanent write lock?

Yes. Documentation seems to clearly state that.

> Will what I'm trying to do work?

Probably not like you intended. There cannot be any INSERT made while
you're doing your d) step, so with high probability e) step will
return no rows. It will return something only if somebody will quick
enough to lock the database for insertion before you began to step
through e).


Pavel

On Wed, Aug 19, 2009 at 9:07 AM, Angus March wrote:
> For one thing, they shouldn't be using the word "exclusive" to mean two
> different things. There's "locking_mode=EXCLUSIVE" meaning "permanent"
> and "exclusive lock" meaning "write lock". At least I think that's what
> they mean.
>    But my problem is understanding exactly when a lock is released
> during a SELECT and if a SELECT will release a write lock after going
> "locking_mode=NORMAL":
>
>   1. When is a lock released during a SELECT? Is it after the first
>      call to step()? The last call to step()? The call to finalize()?
>      Yes, I'm using the C API.
>   2. If while in the step() process of a SELECT, there is a change made
>      to the db, will those changes affect SELECT's result set? In MySQL
>      I'm used to the result set of a SELECT reflecting the state of a
>      db at an instant, and not subject to change by any subsequent
>      INSERTs and UPDATEs, but I don't know if it's the same w/SQLite.
>   3. If I have a permanent write lock, and I go to
>      "locking_mode=NORMAL" will a SELECT remove the permanent write lock?
>
> If the minutia of the above is confusing, here's what I want to do. I
> have a column defined with
>    IsNew INTEGER NOT NULL DEFAULT 1
> INSERTs made to this table do not mention IsNew so the column indicates
> which rows have recently been added. I want to process all the rows in
> the db, then go back and process any rows that were added during the
> first phase of processing. Here's what I imagined doing:
>
> a) PRAGMA locking_mode=EXCLUSIVE;//this is to cause atomicity between b)
> and d)
> b) UPDATE the_table SET IsNew=0;
> c) PRAGMA locking_mode=NORMAL;
> d) SELECT * FROM the_table;//at this point I would want all rows, and
> only those rows, affected in b) to be returned here, INSERTs to
> the_table to be allowed, and those INSERTs *not* to affect the result
> set returned
> e) SELECT * FROM the_table WHERE IsNew=1;//don't worry about locking
> here, I'm going to be performing some extra-SQLite locking to ensure
> that no other operations are performed on the db at this point
>
> Will what I'm trying to do work?
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] PRAGMA locking_mode could stand a rewrite

[Angus March]

For one thing, they shouldn't be using the word "exclusive" to mean two
different things. There's "locking_mode=EXCLUSIVE" meaning "permanent"
and "exclusive lock" meaning "write lock". At least I think that's what
they mean.
But my problem is understanding exactly when a lock is released
during a SELECT and if a SELECT will release a write lock after going
"locking_mode=NORMAL":

   1. When is a lock released during a SELECT? Is it after the first
  call to step()? The last call to step()? The call to finalize()?
  Yes, I'm using the C API.
   2. If while in the step() process of a SELECT, there is a change made
  to the db, will those changes affect SELECT's result set? In MySQL
  I'm used to the result set of a SELECT reflecting the state of a
  db at an instant, and not subject to change by any subsequent
  INSERTs and UPDATEs, but I don't know if it's the same w/SQLite.
   3. If I have a permanent write lock, and I go to
  "locking_mode=NORMAL" will a SELECT remove the permanent write lock?

If the minutia of the above is confusing, here's what I want to do. I
have a column defined with
IsNew INTEGER NOT NULL DEFAULT 1
INSERTs made to this table do not mention IsNew so the column indicates
which rows have recently been added. I want to process all the rows in
the db, then go back and process any rows that were added during the
first phase of processing. Here's what I imagined doing:

a) PRAGMA locking_mode=EXCLUSIVE;//this is to cause atomicity between b)
and d)
b) UPDATE the_table SET IsNew=0;
c) PRAGMA locking_mode=NORMAL;
d) SELECT * FROM the_table;//at this point I would want all rows, and
only those rows, affected in b) to be returned here, INSERTs to
the_table to be allowed, and those INSERTs *not* to affect the result
set returned
e) SELECT * FROM the_table WHERE IsNew=1;//don't worry about locking
here, I'm going to be performing some extra-SQLite locking to ensure
that no other operations are performed on the db at this point

Will what I'm trying to do work?

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users