It's not clear what locks did you want to show with your diagram but in reality locks would be held as following:
*Process A*: Begin Transaction - no lock *Process B*: Select DB1 <-- shared lock *Process A*: Insert DB1 <-- if insert is small then only reserved lock *Process B*: Select DB1 <-- shared lock still can be obtained *Process A*: Insert DB1 <-- if insert is big then pending lock is obtained *Process B*: Select DB1 <-- cannot get shared lock, blocking *Process A*: <-- whatever it does here it still has pending lock *Process A*: Commit Transaction <-- exclusive lock then release all locks *Process B*: Can proceed with blocked Select and do anything else Pavel On Thu, Jan 28, 2010 at 7:00 AM, nyetngoh wong <nyetn...@gmail.com> wrote: > Hi, > Is the locking states shown below for DB1 correct ? > > *Process A* > *Process B* > Begin Transaction > Insert DB1 <-- Reserved lock > Select DB1 <-- Shared lock > Insert DB1 > : > after 10 Inserts and 10 Selects > > Select DB1 <-- Shared lock > : > Continue Inserts and Selects > : > > End Transaction <-- Pending to Exclusive Lock > > > On Thu, Jan 28, 2010 at 7:42 PM, Max Vlasov <max.vla...@gmail.com> wrote: > >> > >> > > SQLite allows multiple readers OR a single writer to access the >> database >> > simultaneously. >> > From the SQLite doc, as long as no transaction is pending, other process >> > can >> > read or obtain reserved lock for write. >> > >> >> the docs say: "Locks are not acquired until the first read or write >> operation. The first read operation against a database creates a SHARED >> lock >> and the first write operation creates a RESERVED lock.". >> >> So your only chance to have concurrent access from both processes only if >> the first one starts transaction and does something innocent like >> preparations with selects, temp tables and so on. Any UPDATE or INSERT will >> lead to RESERVED lock and process B suspension with db access. >> >> Max >> _______________________________________________ >> 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
