> I see. Thanks for your explaination. > May I ask if SQLite can explicitly start a write lock transaction ?
Yes. It's BEGIN IMMEDIATE or BEGIN EXCLUSIVE. The difference is that IMMEDIATE still allows readers to read until actual writing to the database file occurs (before that everything is collected in memory buffers) and EXCLUSIVE doesn't allow even readers to step in. Pavel On Mon, Sep 21, 2009 at 9:19 AM, Wenbo Zhao <zha...@gmail.com> wrote: > 2009/9/21 Pavel Ivanov <paiva...@gmail.com> > >> > What I want to say is in this example, there should be only one step, >> > because the transaction knows it will do 'write'. >> > Then the txn should start a write lock before the select. >> > >> > And this is not a good example to explain dead lock, I think. >> >> And again, you're talking about application using SQLite. Maybe it >> knows that it will write (also not necessarily so - depends on the >> structure of application) and so it should take write lock right away. >> But maybe it wants to improve contention and in majority of cases it >> doesn't need to write after select... In any way SQLite doesn't know >> anything about application's intentions, so it can't take write lock >> before the select and dead lock is still possible. >> > I see. Thanks for your explaination. > May I ask if SQLite can explicitly start a write lock transaction ? > Sorry to bother you with such a basic question. I'm still new in SQLite. > > >> >> Pavel >> >> On Sun, Sep 20, 2009 at 2:04 AM, Wenbo Zhao <zha...@gmail.com> wrote: >> > I was talking about this example by >> > 2009/9/19 Igor Tandetnik <itandet...@mvps.org> >> > "Imagine the >> > classic example, where a transaction first verifies that the balance in >> > a bank account is sufficient, then performs a withdrawal. If it >> > relinquishes all locks between these two steps, then somebody else may >> > record a withdrawal from that account, so that the write operation would >> > then make the balance negative, thus violating an invariant." >> > >> > What I want to say is in this example, there should be only one step, >> > because the transaction knows it will do 'write'. >> > Then the txn should start a write lock before the select. >> > >> > And this is not a good example to explain dead lock, I think. >> > >> > >> > 2009/9/19 Pavel Ivanov <paiva...@gmail.com> >> > >> >> Wenbo, are you talking about what do you want to see in DBMS or are >> >> you trying to explain how SQLite works? >> >> If the latter then you're wrong. In SQLite 'read lock' is designed for >> >> transaction that _made_ any reads, 'write lock' - for transaction that >> >> _made_ any writes. >> >> >> >> Pavel >> >> >> >> On Sat, Sep 19, 2009 at 12:18 AM, Wenbo Zhao <zha...@gmail.com> wrote: >> >> > This is not a good example i think. >> >> > If a transaction is intent to update after the select, it should start >> >> > a write lock before the select. >> >> > And as described in previous 'dead lock' example, the update in this >> >> > example could fail due to 'dead lock' >> >> > I believe the 'read lock' is designed for a 'read only' transaction, >> >> > and the 'write lock' is for a transaction that 'may write something'. >> >> > >> >> > 2009/9/19 Igor Tandetnik <itandet...@mvps.org> >> >> > >> >> >> Angus March <an...@uducat.com> wrote: >> >> >> > Yes, I see. So what is key to the problem is that someone tries >> to >> >> >> > change their read lock to a write lock. I guess I just thought that >> >> >> > the kernel that manages fcntl() would have a way of dealing with >> >> >> > this. Can this situation not be averted if at step 3, transaction A >> >> >> > releases its read lock before requesting a write lock? >> >> >> >> >> >> Then it wouldn't be much of a transaction, now would it? Imagine the >> >> >> classic example, where a transaction first verifies that the balance >> in >> >> >> a bank account is sufficient, then performs a withdrawal. If it >> >> >> relinquishes all locks between these two steps, then somebody else >> may >> >> >> record a withdrawal from that account, so that the write operation >> would >> >> >> then make the balance negative, thus violating an invariant. >> >> >> >> >> >> Of course, if that's what the application wants, it can simply >> perform >> >> >> the read and the write operations in two separate transactions. >> >> >> >> >> >> Igor Tandetnik >> >> >> >> >> >> >> >> >> >> >> >> _______________________________________________ >> >> >> sqlite-users mailing list >> >> >> sqlite-users@sqlite.org >> >> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> >> >> >> > >> >> > >> >> > >> >> > -- >> >> > >> >> > Best Regards, >> >> > ZHAO, Wenbo >> >> > >> >> > ======================= >> >> > _______________________________________________ >> >> > 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 >> >> >> > >> > >> > >> > -- >> > >> > Best Regards, >> > ZHAO, Wenbo >> > >> > ======================= >> > _______________________________________________ >> > 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 >> > > > > -- > > Best Regards, > ZHAO, Wenbo > > ======================= > _______________________________________________ > 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
