[sqlite] Understanding table and database locking mechanism in shared cache mode and normal mode

[Simon Slavin]

On 31 Mar 2016, at 12:06am, Srikanth Bemineni  
wrote:
> 
> 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.

In both modes (whether you're using 'shared cache' or not) use either

https://www.sqlite.org/c3ref/busy_timeout.html

or

https://www.sqlite.org/pragma.html#pragma_busy_timeout

to set a timeout of a minute (really.  a minute).  See if that sorts out your 
problem.  If you do this you do not need any retry mechanism in your own code: 
If SQLite returned _BUSY or _LOCKED then it has already done the retrying you 
told it to do.

> 1. Does a transaction lock ,will lock the table or the whole data base
> connection.?

All locks in SQLite are for the whole database.

> 2. What is the major difference in locking when compared to shared cache
> mode. ?

Sharing the cache is sharing the lock.  In other words two connections sharing 
a cache are not able to lock each other out.

For a detailed description (which you don't really need at this time) see



Simon.

[sqlite] Understanding table and database locking mechanism in shared cache mode and normal mode

[Srikanth Bemineni]

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

[Srikanth Bemineni]

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

[Srikanth Bemineni]

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

[sqlite] Understanding table and database locking mechanism in shared cache mode and normal mode

[Roger Binns]

-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-