On Tuesday, 18 June, 2019 07:12, Thomas Kurz <sqlite.2...@t-net.ruhr> wrote:
>This has been a very informative and helpful discussion. Thank you. >So have I understood correctly, that in an application, this kind of >SQLITE_BUSY handling is sufficient: >BEGIN >UPDATE #1 >SELECT #2 >UPDATE #3 >COMMIT <----- check for busy here and retry only the commit on failure UPDATE #1 could fail with SQLITE_BUSY -- it may be unable to obtain a RESERVED lock. Assuming that the UPDATE #1 completes then the transaction holds a RESERVED lock and the rest of the statements will not get an SQLITE_BUSY. The COMMIT needs to obtain an EXCLUSIVE lock to write the changes, so it may return SQLITE_BUSY if there are other connections having SHARED locks which might need to clear before the transaction can commit. Since you know that the transaction will require a RESERVED lock to complete, you should use BEGIN IMMEDIATE to acquire that lock immediately. Then if the BEGIN IMMEDIATE succeeds you only need to worry about lock escalation again when you commit (and not on the interim statements which do not need to acquire any additional locks). >And second, what is the best place for busy-handling when only >reading? Examle: >BEGIN <---- sufficient here? >SELECT #1 >SELECT #2 >SELECT #3 >ROLLBACK > >Can I assume that if the "begin" succeeds, I have the right to >perform all following selects without having to fear getting an >SQLITE_BUSY? Or do I have to repeat the whole block? Again, you need to check for SQLITE_BUSY on SELECT #1 since that is where the SHARED lock is obtained. Once SELECT #1 has completed the transaction holds a shared lock and the rest of the statements will proceed without requiring additional locks. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
