Re: [sqlite] SQlite exclusive table lock

[Pavel Ivanov]

> So currently from .net code I just say BeginTransaction(ReadCommitted) and do 
> a write operation this locks the complete database file and the write 
> operations are really long. So currently I want to solve the
> locking issue by bringing them down to table locks.

To solve locking issue in SQLite you have to use WAL journal mode.

If you think you absolutely need to use table locks (e.g. to allow
several parallel writers to different tables), you have to use other
DBMS.

But even in other DBMS you will have hard time trying to force readers
to wait while writer transaction is in progress. For that sort of
behavior you need to use your programming language.


Pavel


On Thu, May 3, 2012 at 3:29 PM, Harnek Manj  wrote:
> Hi Simon,
>
> Yes I have multiple Threads which are accessing the database. Currently if I 
> am doing a write operation the whole database file is locked, I want the 
> locking applied only to the table in operation.
>
> So currently from .net code I just say BeginTransaction(ReadCommitted) and do 
> a write operation this locks the complete database file and the write 
> operations are really long. So currently I want to solve the
> locking issue by bringing them down to table locks.
>
> You mentioned "BEGIN EXCLUSIVE TRANSACTION", but I don't see where I can set, 
> that I want to start a transaction with exclusive lock, when I call 
> BeginTransaction on the connection.
>
> Thanks
> Harnek
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
> Sent: May-02-12 5:28 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SQlite exclusive table lock
>
>
> On 3 May 2012, at 1:03am, Harnek Manj  wrote:
>
>> So does it mean that in SQlite there is no way to stop the read operation 
>> while a write operation is running, like table level exclusive lock.
>
> You seem to have jumped straight over the basic features of SQL and looked at 
> some of the most advanced and complicated features.  When you want to block 
> everything else use
>
> BEGIN EXCLUSIVE TRANSACTION
>
> See
>
> <http://sqlite.org/lang_transaction.html>
>
> However, I don't understand why you would want to block a read operation.  
> The difference can matter only when you have multiple threads or processes 
> running at once.  And if you're doing that then there's no harm in getting 
> the data as it was before the write happened.  If the first operation had to 
> finish before the second started, who wouldn't you be doing both operations 
> in the same thread of the same process ?
>
> 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 exclusive table lock

[Simon Davies]

On 3 May 2012 20:29, Harnek Manj  wrote:
> Hi Simon,
>
> Yes I have multiple Threads which are accessing the database. Currently if I 
> am doing a write operation the whole database file is locked, I want the 
> locking applied only to the table in operation.
>
> So currently from .net code I just say BeginTransaction(ReadCommitted) and do 
> a write operation this locks the complete database file and the write 
> operations are really long. So currently I want to solve the
> locking issue by bringing them down to table locks.

http://www.sqlite.org/whentouse.html
look at last paragraph - SQLite locks the whole database file.

>
> You mentioned "BEGIN EXCLUSIVE TRANSACTION", but I don't see where I can set, 
> that I want to start a transaction with exclusive lock, when I call 
> BeginTransaction on the connection.
>
> Thanks
> Harnek
>

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQlite exclusive table lock

[Harnek Manj]

Hi Simon,

Yes I have multiple Threads which are accessing the database. Currently if I am 
doing a write operation the whole database file is locked, I want the locking 
applied only to the table in operation.

So currently from .net code I just say BeginTransaction(ReadCommitted) and do a 
write operation this locks the complete database file and the write operations 
are really long. So currently I want to solve the
locking issue by bringing them down to table locks.

You mentioned "BEGIN EXCLUSIVE TRANSACTION", but I don't see where I can set, 
that I want to start a transaction with exclusive lock, when I call 
BeginTransaction on the connection.

Thanks
Harnek

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Simon Slavin
Sent: May-02-12 5:28 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQlite exclusive table lock


On 3 May 2012, at 1:03am, Harnek Manj  wrote:

> So does it mean that in SQlite there is no way to stop the read operation 
> while a write operation is running, like table level exclusive lock.

You seem to have jumped straight over the basic features of SQL and looked at 
some of the most advanced and complicated features.  When you want to block 
everything else use

BEGIN EXCLUSIVE TRANSACTION

See

<http://sqlite.org/lang_transaction.html>

However, I don't understand why you would want to block a read operation.  The 
difference can matter only when you have multiple threads or processes running 
at once.  And if you're doing that then there's no harm in getting the data as 
it was before the write happened.  If the first operation had to finish before 
the second started, who wouldn't you be doing both operations in the same 
thread of the same process ?

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 exclusive table lock

[Simon Slavin]

On 3 May 2012, at 1:03am, Harnek Manj  wrote:

> So does it mean that in SQlite there is no way to stop the read operation 
> while a write operation is running, like table level exclusive lock.

You seem to have jumped straight over the basic features of SQL and looked at 
some of the most advanced and complicated features.  When you want to block 
everything else use

BEGIN EXCLUSIVE TRANSACTION

See



However, I don't understand why you would want to block a read operation.  The 
difference can matter only when you have multiple threads or processes running 
at once.  And if you're doing that then there's no harm in getting the data as 
it was before the write happened.  If the first operation had to finish before 
the second started, who wouldn't you be doing both operations in the same 
thread of the same process ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQlite exclusive table lock

[Harnek Manj]

Pavel,

So does it mean that in SQlite there is no way to stop the read operation while 
a write operation is running, like table level exclusive lock.

Thanks
Harnek

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Pavel Ivanov
Sent: May-02-12 4:26 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQlite exclusive table lock

> I can't use WAL because it doesn't stop readers from reading.

For this sort of synchronization you should use OS synchronization primitives, 
especially if you want to do that among different processes.

Pavel


On Wed, May 2, 2012 at 5:11 PM, Harnek Manj  wrote:
> Hi,
>
> Should I use Shared-Cache in sqlite to have the exclusive table lock, so that 
> readers have to wait for the write operation to finish. I don't want the 
> complete database file locked while there is write operation. I can't use WAL 
> because it doesn't stop readers from reading.
>
> Thanks
> Harnek
> ___
> 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 exclusive table lock

[Pavel Ivanov]

> I can't use WAL because it doesn't stop readers from reading.

For this sort of synchronization you should use OS synchronization
primitives, especially if you want to do that among different
processes.

Pavel


On Wed, May 2, 2012 at 5:11 PM, Harnek Manj  wrote:
> Hi,
>
> Should I use Shared-Cache in sqlite to have the exclusive table lock, so that 
> readers have to wait for the write operation to finish. I don't want the 
> complete database file locked while there is write operation. I can't use WAL 
> because it doesn't stop readers from reading.
>
> Thanks
> Harnek
> ___
> 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] SQlite exclusive table lock

[Harnek Manj]

Hi,

Should I use Shared-Cache in sqlite to have the exclusive table lock, so that 
readers have to wait for the write operation to finish. I don't want the 
complete database file locked while there is write operation. I can't use WAL 
because it doesn't stop readers from reading.

Thanks
Harnek
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users