Hi, Is it possible for any SQLLite developer to explain the locking mechanism in case of the shared connections, specifically table level locking, how I can debug this and find out who is holding the lock. ?
Srikanth Bemineni On Thu, Jul 3, 2014 at 12:47 PM, Srikanth Bemineni < bemineni.srika...@gmail.com> wrote: > Hi, > > But in shared cache mode. I assume this is going to be a table level lock, > instead of a lock on the whole database. This will really block other > threads which are dealing with other tables. > > > http://www.sqlite.org/sharedcache.html > > 2.1 Transaction Level Locking > > SQLite connections can open two kinds of transactions, read and write > transactions. This is not done explicitly, a transaction is implicitly a > read-transaction until it first writes to a database table, at which point > it becomes a write-transaction. > > At most one connection to a single shared cache may open a write > transaction at any one time. This may co-exist with any number of read > transactions. > 2.2 Table Level Locking > > When two or more connections use a shared-cache, locks are used to > serialize concurrent access attempts on a per-table basis. Tables support > two types of locks, "read-locks" and "write-locks". Locks are granted to > connections - at any one time, each database connection has either a > read-lock, write-lock or no lock on each database table. > > At any one time, a single table may have any number of active read-locks > or a single active write lock. To read data a table, a connection must > first obtain a read-lock. To write to a table, a connection must obtain a > write-lock on that table. If a required table lock cannot be obtained, the > query fails and SQLITE_LOCKED is returned to the caller. > > Once a connection obtains a table lock, it is not released until the > current transaction (read or write) is concluded. > > > As per the above documentation > "Once a connection obtains a table lock, it is not released until the > current transaction (read or write) is concluded." > > This means once the statement is finalized or the whole transaction > is committed. Currently I am getting an error on table level locks > > Thread 1 SQLITE_LOCKED(6) Error <Table1> is locked > Thread 2 SQLITE_LOCKED(6) Error database table is locked > > Srikanth Bemineni > > > On Thu, Jul 3, 2014 at 12:35 PM, Simon Slavin <slav...@bigfraud.org> > wrote: > >> >> On 3 Jul 2014, at 6:11pm, Srikanth Bemineni <bemineni.srika...@gmail.com> >> wrote: >> > >> > As per Igor >> > BEGIN IMMEDIATE should get a write lock on the table 1 when first select >> > call is initiated >> > >> > 10:00.234 Thread 1 BEGIN >> > 10:00.235 Thread 1 select * from <table1> >> > 10:00.234 Thread 1 select * from <table x> >> > 10:00.456 Thread 1 delete from <table1> >> > 10:00.500 Thread 1 COMMIT >> > >> > Igor >> > >> > 1. If there is no second thread , then the above transaction works fine. >> > Here also I am doing the select operation first . So the same thread can >> > update a read lock to write lock ? >> > >> > 2. Will BEGIN IMMEDIATE get a write lock on the table for the first >> select >> > statement as per the thread sequence above. >> >> You're referring to 'read lock' and 'write lock' but it's easier to think >> of there just being a lock. >> >> BEGIN IMMEDIATE gets a lock right there at the BEGIN IMMEDIATE command. >> It doesn't have to wait for anything later. Now nothing else can happen >> to the database until the COMMIT/ROLLBACK. >> >> Simon. >> _______________________________________________ >> 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
