Re: [sqlite] Sqlite in dead lock state when deleting records from the same table from different threads

[dada]

> 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 From 
> docs: After a BEGIN IMMEDIATE, no other database connection will be able to 
> write to the database or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE. Other 
> processes can continue to read from the database, however. So yes, it will 
> very much get an exclusive lock. 
___
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

[mm.w]

your sunglasses won't stop my gaze


On Mon, Jul 7, 2014 at 4:21 PM, mm.w <0xcafef...@gmail.com> wrote:

> comply to my request or stop, sorry but truth must be told at some point.
>
>
> On Mon, Jul 7, 2014 at 6:02 AM, Srikanth Bemineni <
> bemineni.srika...@gmail.com> wrote:
>
>> 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 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
>> > > 

Re: [sqlite] Sqlite in dead lock state when deleting records from the same table from different threads

[mm.w]

comply to my request or stop, sorry but truth must be told at some point.


On Mon, Jul 7, 2014 at 6:02 AM, Srikanth Bemineni <
bemineni.srika...@gmail.com> wrote:

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

Re: [sqlite] Sqlite in dead lock state when deleting records from the same table from different threads

[Srikanth Bemineni]

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 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
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list

Re: [sqlite] Sqlite in dead lock state when deleting records from the same table from different threads

[mm.w]

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

[Srikanth Bemineni]

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

[Srikanth Bemineni]

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

[Simon Slavin]

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

Re: [sqlite] Sqlite in dead lock state when deleting records from the same table from different threads

[Srikanth Bemineni]

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

Re: [sqlite] Sqlite in dead lock state when deleting records from the same table from different threads

[Igor Tandetnik]

On 7/2/2014 2:06 PM, Clemens Ladisch wrote:

Igor Tandetnik wrote:

On 7/2/2014 3:04 AM, Clemens Ladisch wrote:

If all else fails, one could try and simulate BEGIN IMMEDIATE by running
a dummy modifying statement right after BEGIN - e.g.

delete from table1 where 0;


Would this be atomic?


It would be part of the same transaction, wouldn't it?


In shared-cache mode, accesses from all threads are part of the same
transaction (as far as the database file is concerned).
SQL transactions are implemented on top of that.


File-level locks, yes - but not table-level locks, which is what appears 
to be at issue here. Each connection to the shared cache maintains a 
separate set of table-level locks.


In the OP's scenario, two connections to the same shared cache are 
locking each other out - that can't possibly be due of file-level 
locking (as you note, they both use the same underlying connection to 
the file). Besides, a contention on file-level lock is reported as 
SQLITE_BUSY, not SQLITE_LOCKED.


The hope is that issuing a "delete * from table1 where 0;" right after 
BEGIN on a connection to a shared cache will cause that connection to 
acquire a table-level write lock on table1 right from the start (or be 
blocked trying), thus avoiding a deadlock scenario.

--
Igor Tandetnik

___
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

[Simon Slavin]

On 2 Jul 2014, at 7:06pm, Clemens Ladisch  wrote:

> There is no documented way to start a transaction with a write lock,
> thus I see no mechanism that could reliably prevent deadlocks.

Right.  Sharing your cache means you're sharing your transactions and sharing 
your locks.  If you need to lock something away from you, don't share your 
cache with it.

Simon.
___
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

[Clemens Ladisch]

Igor Tandetnik wrote:
> On 7/2/2014 3:04 AM, Clemens Ladisch wrote:
>>> If all else fails, one could try and simulate BEGIN IMMEDIATE by running
>>> a dummy modifying statement right after BEGIN - e.g.
>>>
>>> delete from table1 where 0;
>>
>> Would this be atomic?
>
> It would be part of the same transaction, wouldn't it?

In shared-cache mode, accesses from all threads are part of the same
transaction (as far as the database file is concerned).
SQL transactions are implemented on top of that.

> However, I have no reason to believe, other than your word, that BEGIN
> IMMEDIATE would not just work in this case. What makes you think it
> wouldn't?

In shared-cache mode,
| a transaction is implicitly a read-transaction until it first writes
| to a database table, at which point it becomes a write-transaction.
(http://www.sqlite.org/sharedcache.html)

There is no documented way to start a transaction with a write lock,
thus I see no mechanism that could reliably prevent deadlocks.


Regards,
Clemens
___
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

[Igor Tandetnik]

On 7/2/2014 3:04 AM, Clemens Ladisch wrote:

If all else fails, one could try and simulate BEGIN IMMEDIATE by running
a dummy modifying statement right after BEGIN - e.g.

delete from table1 where 0;


Would this be atomic?


It would be part of the same transaction, wouldn't it? The idea is to 
make the first statement a writer, so the transaction acquires write 
locks from the start.


However, I have no reason to believe, other than your word, that BEGIN 
IMMEDIATE would not just work in this case. What makes you think it 
wouldn't?

--
Igor Tandetnik

___
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

[Clemens Ladisch]

Igor Tandetnik wrote:
> On 7/1/2014 4:55 PM, Clemens Ladisch wrote:
>> To prevent deadlocks, transactions that will modify the database should
>> be started with BEGIN IMMEDIATE.  (This kind of lock is not available
>> in shared cache mode.)
>
> Are you sure?

Sorry, I meant "locking", not "lock".  The important thing is that this
gets a write lock without holding a read lock first.

> If all else fails, one could try and simulate BEGIN IMMEDIATE by running
> a dummy modifying statement right after BEGIN - e.g.
>
> delete from table1 where 0;

Would this be atomic?


Regards,
Clemens
___
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

[Simon Slavin]

> On 1 Jul 2014, at 10:36pm, Igor Tandetnik  wrote:
> 
> On 7/1/2014 5:20 PM, Igor Tandetnik wrote:
>> On 7/1/2014 4:55 PM, Clemens Ladisch wrote:
>>> To prevent deadlocks, transactions that will modify the database should
>>> be started with BEGIN IMMEDIATE.  (This kind of lock is not available
>>> in shared cache mode.)
>> 
>> Are you sure? Nothing in the documentation appears to suggest that.
> 
> Upon rereading, this sounds ambiguous. I meant - are you sure BEGIN IMMEDIATE 
> doesn't work in shared cache mode?

My guess is that the OP tested the originally quoted sequence

On 7/1/2014 4:40 PM, Srikanth Bemineni wrote:
> 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  

using BEGIN IMMEDIATE instead of BEGIN.  Of course, in this case the second 
BEGIN IMMEDIATE does its job and thread 2 can't get started.  OP saw the second 
BEGIN return an error and concluded that it wasn't working.  Because it's hard 
to see why a SELECT should lock a database until you know what you explained in 
your previous post: within a transaction SELECT must be repeatable.

The solution is to use BEGIN IMMEDIATE as you wrote, but also to set a 
realistic and acceptable value for timeout using the API call



or the PRAGMA



Just a reminder to others that a realistic value for timeout is 2 minutes 
rather than 2 seconds.  Long enough that you are sure that your app has crashed 
rather than is just handling an unusual but reasonable amount of work.

Simon.
___
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

[Igor Tandetnik]

On 7/1/2014 5:20 PM, Igor Tandetnik wrote:

On 7/1/2014 4:55 PM, Clemens Ladisch wrote:

To prevent deadlocks, transactions that will modify the database should
be started with BEGIN IMMEDIATE.  (This kind of lock is not available
in shared cache mode.)


Are you sure? Nothing in the documentation appears to suggest that.


Upon rereading, this sounds ambiguous. I meant - are you sure BEGIN 
IMMEDIATE doesn't work in shared cache mode?


I didn't mean to cast doubt on the first part - the fact that 
transactions that start as readers and later try to upgrade to writers 
are deadlock-prone.

--
Igor Tandetnik

___
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

[Igor Tandetnik]

On 7/1/2014 4:55 PM, Clemens Ladisch wrote:

To prevent deadlocks, transactions that will modify the database should
be started with BEGIN IMMEDIATE.  (This kind of lock is not available
in shared cache mode.)


Are you sure? Nothing in the documentation appears to suggest that. But 
I must admit I haven't tried it myself.


If all else fails, one could try and simulate BEGIN IMMEDIATE by running 
a dummy modifying statement right after BEGIN - e.g.


delete from table1 where 0;

--
Igor Tandetnik

___
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

[Igor Tandetnik]

On 7/1/2014 4:40 PM, Srikanth Bemineni wrote:

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.


It can't - Thread 2 is holding a read lock on that 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.


It can't - Thread 1 is holding a read lock on that table.


In this
case none of the threads are getting lock on the table and are waiting for
ever.


To be precise, both threads have acquired a read lock on the same table; 
both are now trying and failing to promote it to a write lock.



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.


Won't help: once acquired, all locks are held until the end of 
transaction. You may choose to re-read the same table later in the 
transaction, and SQLite must guarantee that you see the same data. The 
only way to resolve a deadlock is for one thread to roll back its 
transaction.



In any of this case one thread should be the winner in getting the lock on
this table.


Which part of the documentation makes you believe that?


why is this happening for delete when concurrent update
and inserts are happening properly ?


With all due respect, I find this difficult to believe. All other things 
equal, the same problem should manifest if DELETE statement is replaced 
with INSERT or UPDATE. There must be something else different about the 
scenarios where those operations work.

--
Igor Tandetnik

___
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

[Clemens Ladisch]

Srikanth Bemineni wrote:
> Lately we are seeing a dead lock kind of state while deleting records
> from a table.
>
> All threads open their own shared connection to the database.

Why are you using shared cache mode?

To prevent deadlocks, transactions that will modify the database should
be started with BEGIN IMMEDIATE.  (This kind of lock is not available
in shared cache mode.)


Regards,
Clemens
___
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

[Srikanth Bemineni]

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) */
_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