Re: [sqlite] Write locking - subquery
On 16 Jul 2011, at 9:30pm, Kevin Martin wrote: > If I run an update query which has a subquery, will the database be > locked before the subquery is run. Yes. The transaction does the locking. The transaction encloses the entire UPDATE. If you want to do the SELECT yourself as a separate statement, and make sure it's valid, you can specify your own transaction using "BEGIN IMMEDIATE' before your SELECT. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Write locking - subquery
On 16 Jul 2011, at 21:30, Kevin Martin wrote:
> insert into x values ('abc', -1);
> update x set pos = 1+max(0,(select max(pos) from x));
Oops, deliberate mistake there. As I'm sure you all realise that
should be
update x set pos = 1+max(0,(select max(pos) from x)) where name='abc';
Kevin
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Write locking - subquery
Hello,
I'm just looking for some clarification of the documentation if that's
ok. I did a quick search of the mailing list but couldn't find
anything relevant.
If I run an update query which has a subquery, will the database be
locked before the subquery is run.
I'm thinking something like:
create table x(name text, pos int);
insert into x values ('abc', -1);
update x set pos = 1+max(0,(select max(pos) from x));
If two processes run this at once (with different values for name), am
I right in thinking the lock will occur before the subquery runs,
hence it is impossible for the subquery to return -1 in both processes.
Thanks,
Kevin Martin.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
