[sqlite] Understanding table and database locking mechanism in shared cache mode and normal mode
Hi
In shared cache mode the locking seems to be per table level basis. Which
seems to not block my operations on the other tables in the same database.
When there are multiple connections, any transaction is going to create a
data base level lock, which may result in many write as well as read
starvation threads.
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.
On Wed, Mar 30, 2016 at 11:19 PM, Srikanth Bemineni <
bemineni.srikanth at gmail.com> wrote:
> Hi,
>
> if( (lastStatus == SQLITE_OK) && mystatement)
> {
> m_lastStatus = sqlite3_step(mp_statement);
> while( lastStatus == SQLITE_LOCKED || lastStatus == SQLITE_BUSY)
> {
>
>sleep(randomtime)
>lastStatus = sqlite3_step(mp_statement);
> }
> }
>
>
> My common execute for all the my thread is some what like this. In shared
> cache mode I used always end up with SQLITE_LOCKED as Roger Binns
> mentioned. Now I end up with an hanging SQLITE_BUSY. Is there some kind of
> a dead lock situation between threads that can cause this in normal mode.
>
> I did change all my transaction with BEGIN IMMEDIATE to get a lock for the
> each thread, so that my write and updates finish.
>
> Srikanth
>
>
> On Wed, Mar 30, 2016 at 8:02 PM, Roger Binns
> wrote:
>
>> -BEGIN PGP SIGNED MESSAGE-
>> Hash: SHA1
>>
>> On 30/03/16 16:58, Simon Slavin wrote:
>> > In both modes (whether you're using 'shared cache' or not) use
>> > either
>> >
>> > https://www.sqlite.org/c3ref/busy_timeout.html
>>
>> The last time I dealt with shared cache mode, the busy timeout did not
>> apply. You had to manually manage the timeout/retries yourself. This
>> is by design:
>>
>> https://sqlite.org/src/tktview/ebde3f66fc64e21e61ef2854ed1a36dfff884a2f
>>
>> In the APSW doc I recommend against using shared cache mode, as it
>> doesn't have any benefits except for very small memory systems.
>>
>> Roger
>>
>> -BEGIN PGP SIGNATURE-
>> Version: GnuPG v2
>>
>> iEYEARECAAYFAlb8dyEACgkQmOOfHg372QQhhQCbBoKrBu40ZgroyJOPB8WVy4To
>> hcsAn0f8rx1h+foMBH0r4YVYo3pmc9Nc
>> =lNHi
>> -END PGP SIGNATURE-
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
[sqlite] Understanding table and database locking mechanism in shared cache mode and normal mode
Hi,
if( (lastStatus == SQLITE_OK) && mystatement)
{
m_lastStatus = sqlite3_step(mp_statement);
while( lastStatus == SQLITE_LOCKED || lastStatus == SQLITE_BUSY)
{
sleep(randomtime)
lastStatus = sqlite3_step(mp_statement);
}
}
My common execute for all the my thread is some what like this. In shared
cache mode I used always end up with SQLITE_LOCKED as Roger Binns
mentioned. Now I end up with an hanging SQLITE_BUSY. Is there some kind of
a dead lock situation between threads that can cause this in normal mode.
I did change all my transaction with BEGIN IMMEDIATE to get a lock for the
each thread, so that my write and updates finish.
Srikanth
On Wed, Mar 30, 2016 at 8:02 PM, Roger Binns wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 30/03/16 16:58, Simon Slavin wrote:
> > In both modes (whether you're using 'shared cache' or not) use
> > either
> >
> > https://www.sqlite.org/c3ref/busy_timeout.html
>
> The last time I dealt with shared cache mode, the busy timeout did not
> apply. You had to manually manage the timeout/retries yourself. This
> is by design:
>
> https://sqlite.org/src/tktview/ebde3f66fc64e21e61ef2854ed1a36dfff884a2f
>
> In the APSW doc I recommend against using shared cache mode, as it
> doesn't have any benefits except for very small memory systems.
>
> Roger
>
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v2
>
> iEYEARECAAYFAlb8dyEACgkQmOOfHg372QQhhQCbBoKrBu40ZgroyJOPB8WVy4To
> hcsAn0f8rx1h+foMBH0r4YVYo3pmc9Nc
> =lNHi
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
[sqlite] Understanding table and database locking mechanism in shared cache mode and normal mode
My application is a multi threaded application, which uses sqlite to store data on a file. Our current architecture of using sqlite in shared cache mode seems to be working absolutely fine. In this case a new connection is being given to a thread in shared cache mode to the database. If one of the thread gets a SQLITE_LOCKED status for statement execution, we use try again after some time assuming the other thread holding the lock would have either failed or committed the transaction. Recently we moved to our connection type from shared cache to normal mode, to see if there is any significant improvement in performance as suggested by team member. Right now we have multiple database connections to same database. We started to see lot of SQLITE_LOCKED hangs. I am just putting forth this question to know how the locking mechanism happens in normal mode. 1. Does a transaction lock ,will lock the table or the whole data base connection.? 2. What is the major difference in locking when compared to shared cache mode. ? I am sure we haven't changed anything else except for the connection type. Srikanth
Re: [sqlite] Sqlite in dead lock state when deleting records from the same table from different threads
Hi, How can I find this in the statement ? Will BEGIN immediate get an exclusive lock.? or like Igor specified if I call "Delete * from where 0" will it be able to get an immediate lock on the table. Srikanth On Sun, Jul 6, 2014 at 9:21 PM, mm.w <0xcafef...@gmail.com> wrote: > what's the syscall set behind the scene might help, os? > > > On Sun, Jul 6, 2014 at 6:04 PM, Srikanth Bemineni < > bemineni.srika...@gmail.com> wrote: > > > 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 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 > > > 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 > > >> > 10:00.234 Thread 1 select * from > > >> > 10:00.456 Thread 1 delete from > > >> > 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
Re: [sqlite] Sqlite in dead lock state when deleting records from the same table from different threads
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 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 > wrote: > >> >> On 3 Jul 2014, at 6:11pm, Srikanth Bemineni >> 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 >> > 10:00.234 Thread 1 select * from >> > 10:00.456 Thread 1 delete from >> > 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
Re: [sqlite] Sqlite in dead lock state when deleting records from the same table from different threads
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 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 wrote: > > On 3 Jul 2014, at 6:11pm, Srikanth Bemineni > 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 > > 10:00.234 Thread 1 select * from > > 10:00.456 Thread 1 delete from > > 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
Re: [sqlite] Sqlite in dead lock state when deleting records from the same table from different threads
Hi, For some reason I was not getting any mails from the groups. At last Mike Owens was a able to resolve the issue. I did used to get the digest mails which contained the conversation with respect to this thread. Just to rephrase from what ever I understood till now 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 10:00.234 Thread 1 select * from 10:00.456 Thread 1 delete from 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.? Srikanth Bemineni ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite in dead lock state when deleting records from the same table from different threads
Hi, We are using the sqlite 3.7.14.1 code in our application. Lately we are seeing a dead lock kind of state while deleting records from a table. The deletion is done two different threads and acting upon the same table. Sqlite is configured in WAL mode. All threads open their own shared connection to the database. The application is complied with SQLITE_THREADSAFE=1 and SQLITE_ENABLE_MEMORY_MANAGEMENT. m_init = sqlite3_open_v2( m_dbfilename.toUtf8().data(), /* Database filename (UTF-8) */ &m_dbHandler, /* OUT: SQLite db handle */ SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX | SQLITE_OPEN_SHAREDCACHE,/* Flags */ NULL /* Name of VFS module to use */ ); All update,insert and deletion of records in multiple threads happen in transactions. The update/insert statement works absolutely fine with out any dead lock. But when two or more threads try to remove an entry in the same table, then they go into a dead lock state where sqlite3_step keeps on returning SQLITE_LOCKED. When we did debug the code 10:00.234 Thread 1 BEGIN 10:00.235 Thread 1 select * from 10:00.234 Thread 1 select * from 10:00.456 Thread 1 delete from 10:00.456 Thread 2 BEGIN 10:00.456 Thread 2 select * from 10:00.906 Thread 2 select * from 10:01.156 Thread 2 delete from Thread 1 SQLITE_LOCKED(6) Error is locked Thread 2 SQLITE_LOCKED(6) Error database table is locked Thread 1 which is the first one to enter BEGIN and do modifications to the table, and it should have gained the WRITE lock on the table. Even If we consider Thread 2 was performing the select on the same table at the same time, then Thread 2 should have locked table in its delete call. In this case none of the threads are getting lock on the table and are waiting for ever. In each of the thread we are waiting for a random amount of time re-executing(sqlite3_step) the same prepared statement after calling reset on the prepared statement. In any of this case one thread should be the winner in getting the lock on this table. My question is how can I find who is locking the table. Is there any way I can get this info ? why is this happening for delete when concurrent update and inserts are happening properly ? Srikanth Bemineni ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ORDER BY clause with column values not case-sensitive
Dennis, It works. Thanks a lot. Srikanth. On 6/27/06, Dennis Cote <[EMAIL PROTECTED]> wrote: Srikanth wrote: > Is there a way to order the results of a select statement using the > values of a column name, with the values in that column insensitive? > E.g., If the column has the following values: Zambia, italy,Iceland, > then a regular "ORDER BY tablename.country DESC" would result in: > > italy > Zambia > Iceland > > I want my output to be: > Zambia > italy > Iceland. > Srikanth, Add COLLATE NOCASE to the column definition of the country column in your table. CREATE TABLE name ( ... countrytext collate nocase, ... ); HTH Dennis Cote
[sqlite] ORDER BY clause with column values not case-sensitive
Hi, Is there a way to order the results of a select statement using the values of a column name, with the values in that column insensitive? E.g., If the column has the following values: Zambia, italy,Iceland, then a regular "ORDER BY tablename.country DESC" would result in: italy Zambia Iceland I want my output to be: Zambia italy Iceland. Thanks. Srikanth.
Re: [sqlite] Inserting image files into a database table
Boris,
Thanks. I have few more questions, though. How do I convert an image into
its hex equivalent? This is my situation: I would like to a) insert pictures
and their captions into a database table and, when needed fetch them and
display them in a webpage. I am going to use Python for inserting/fetching
the images and their captions. I am not sure, though, how an image can be
converted into its hex form, inserted, fetched and then reconverted into an
image. I am new to this process, so am not sure how this is done.
Thanks
Srikanth.
On 3/27/06, Boris Popov <[EMAIL PROTECTED]> wrote:
>
> You should bind the blob to the prepared insert statement,
>
> insert into first_table (picture_name, picture) values (?,?)
>
> Then sqlite3_bind_text() your name and sqlite3_bind_blob() your image
> bytes
> to the sqlite3_stmt that's the result of sqlite3_prepare().
>
> http://www.sqlite.org/capi3ref.html#sqlite3_prepare
>
> http://www.sqlite.org/capi3ref.html#sqlite3_bind_blob
>
> Or you could use X'53514697465' notation, where any blob can be expressed
> as
> a string in hex preceded by x or X.
>
> insert into first_table (picture_name, picture) values
> ('dog',X'53514697465')
>
> Hope this helps,
>
> -Boris
>
> --
> +1.604.689.0322
> DeepCove Labs Ltd.
> 4th floor 595 Howe Street
> Vancouver, Canada V6C 2T5
>
> [EMAIL PROTECTED]
>
> CONFIDENTIALITY NOTICE
>
> This email is intended only for the persons named in the message
> header. Unless otherwise indicated, it contains information that is
> private and confidential. If you have received it in error, please
> notify the sender and delete the entire message including any
> attachments.
>
> Thank you.
>
> -Original Message-
> From: Srikanth [mailto:[EMAIL PROTECTED]
> Sent: Monday, March 27, 2006 2:15 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Inserting image files into a database table
>
> Hi,
> Could someone give me the procedure for inserting images into a database
> table?
> E.g., say I created a table thus:
> create table first_table( picture_name string, picture BLOB);
>
> How do I insert an image into the picture field?
>
> Thanks.
> Srikanth.
>
>
>
[sqlite] Inserting image files into a database table
Hi, Could someone give me the procedure for inserting images into a database table? E.g., say I created a table thus: create table first_table( picture_name string, picture BLOB); How do I insert an image into the picture field? Thanks. Srikanth.
Re: [sqlite] help with sqlite command
The following statement should help: delete from "table_name" where "condition"; Of course, you'd make appropriate substitutions for "table_name" and "condition". HTH On 3/27/06, Uma Venkataraman <[EMAIL PROTECTED]> wrote: > > I would like to delete n records from a table, based on some condition. > Can > some one please let me know how to do this with sqlite? > > Thanks > >
