Re: [sqlite] Row locking sqlite3

[Thomas Kurz]

I apologize that I am currently unable to reproduce the problem. The files I 
have just tested at home (same schema as the one I had in the office this week) 
behave as expected (i.e. no difference with or without transaction). I will try 
again in the office next week. If I can find the database with which I observed 
this issue, I will send it to you.


- Original Message - 
From: Dan Kennedy 
To: sqlite-users@mailinglists.sqlite.org 
Sent: Friday, March 29, 2019, 19:33:51
Subject: [sqlite] Row locking sqlite3


On 28/3/62 01:04, Thomas Kurz wrote:
>> I wonder whether SQLite is treating each DELETE as a single transaction.  
>> Could you try wrapping the main delete in BEGIN ... END and see whether that 
>> speeds up the cascaded DELETE ?  Would you be able to find timings (either 
>> in your code or in the command-line tool) and tell us whether it's the 
>> DELETE or the END which takes the time ?
> Ok, well very interesting and I'd never have had this idea, but indeed it 
> works: within a transaction, it takes only a few seconds. This is very 
> surprising as to me, a single DELETE statement is nothing more than that: a 
> single atomic operation which should automatically be treated as a 
> transaction (auto-commit-mode).

> *confused*


Me too. For the BEGIN/COMMIT version, you're counting the time spent in 
COMMIT as well, correct?

If this is repeatable, we'd be very interesting in figuring out what is 
going on.

Dan.



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

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

Re: [sqlite] Row locking sqlite3

[Dan Kennedy]

On 28/3/62 01:04, Thomas Kurz wrote:

I wonder whether SQLite is treating each DELETE as a single transaction.  Could 
you try wrapping the main delete in BEGIN ... END and see whether that speeds 
up the cascaded DELETE ?  Would you be able to find timings (either in your 
code or in the command-line tool) and tell us whether it's the DELETE or the 
END which takes the time ?

Ok, well very interesting and I'd never have had this idea, but indeed it 
works: within a transaction, it takes only a few seconds. This is very 
surprising as to me, a single DELETE statement is nothing more than that: a 
single atomic operation which should automatically be treated as a transaction 
(auto-commit-mode).

*confused*



Me too. For the BEGIN/COMMIT version, you're counting the time spent in 
COMMIT as well, correct?


If this is repeatable, we'd be very interesting in figuring out what is 
going on.


Dan.



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

Re: [sqlite] Row locking sqlite3

[Jean-Christophe Deschamps]

You can use a simple 'L' flag on the rows you want locked and add a 
where to don't touch them.


I'm afraid things are more complicated in many real-world cases. 
Locking a single row isn't enough.


What if the UPDATE or DELETE forces deep changes in one or more indices?
What if the UPDATE or DELETE cascades to 1 to N levels?
What if some change launches a trigger that itself changes things 
elsewhere, possibly a LOT of things, that may themselves change several 
other parts of the DB?


In all cases above, if a read operation occurs in the middle of the 
write process, DB integrity is jeopardized.
I haven't looked at the proposed patch but I seriously doubt all of 
this is taken care in all situations.


JcD 


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

Re: [sqlite] Row locking sqlite3

[Keith Medcalf]

On Wednesday, 27 March, 2019 12:04, Thomas Kurz  wrote:

>> I wonder whether SQLite is treating each DELETE as a single
>transaction.  Could you try wrapping the main delete in BEGIN ... END
>and see whether that speeds up the cascaded DELETE ?  Would you be
>able to find timings (either in your code or in the command-line
>tool) and tell us whether it's the DELETE or the END which takes the
>time ?

>Ok, well very interesting and I'd never have had this idea, but
>indeed it works: within a transaction, it takes only a few seconds.
>This is very surprising as to me, a single DELETE statement is
>nothing more than that: a single atomic operation which should
>automatically be treated as a transaction (auto-commit-mode).

It is.  In auto-commit mode the transaction is started automatically when the 
VDBE program commences execution, and terminated when the VDBE program 
completes.  There is therefore NO difference between BEGIN; ; 
COMMIT; and just running the bare  ...

You do have the necessary indexes do you not?

>*confused*

sqlite> create table p(id integer primary key);
sqlite> create table c(p integer references p on delete cascade, q);
sqlite> create index c_p on c(p);
sqlite> insert into p values (1), (2), (3);
sqlite> insert into c select 1, value from generate_series where start=1 and 
stop=1000;
sqlite> insert into c select 2, value from generate_series where start=1 and 
stop=1000;
sqlite> insert into c select 3, value from generate_series where start=1 and 
stop=1000;
sqlite> .timer on
sqlite> delete from p where id == 2;
Run Time: real 12.022 user 11.140625 sys 0.734375
sqlite> begin;
Run Time: real 0.000 user 0.00 sys 0.00
sqlite> delete from p where id == 3;
Run Time: real 12.073 user 10.218750 sys 0.843750
sqlite> commit;
Run Time: real 0.114 user 0.031250 sys 0.031250
sqlite> select distinct p from c;
1
Run Time: real 0.545 user 0.546875 sys 0.00

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




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

Re: [sqlite] Row locking sqlite3

[Eduardo Morras]

On Fri, 22 Mar 2019 09:25:24 -0500
Peng Yu  wrote:

> Hi,
> 
> I see that sqlite3 still does not support row locking. This package
> tries to resolve this problem. But it does not have a standard build
> process for Linux.
> 
> https://github.com/sqlumdash/sqlumdash/
> 
> Are there other packages similar to sqlite3 but support row locking?

Row locking of how many rows?

In some projects I use a hidden column with a simple letter to flag the row 
state and make queries filtering that flag. You can use a simple 'L' flag on 
the rows you want locked and add a where to don't touch them.

I use it for softdelete rows, this allows fight against internal fragmentation, 
but adds cost of adding the column to all indexes. At idle times delete all of 
them or recreate the table with live rows.

Of course, if you plan to lock almost all rows, reverse the logic. If you want 
to lock and unlock fast, use other method or PostgreSQL.

You can create a virtual table that adds this feature pretty easily.

> Thanks.
> 
> -- 
> Regards,
> Peng
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


---   ---
Eduardo Morras 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row locking sqlite3

[Thomas Kurz]

Integrity check is ok. I'm deleting using primary keys only, so it shouldn't be 
an index problem either.

- Original Message - 
From: Simon Slavin 
To: SQLite mailing list 
Sent: Wednesday, March 27, 2019, 19:25:17
Subject: [sqlite] Row locking sqlite3

On 27 Mar 2019, at 6:04pm, Thomas Kurz  wrote:

> Ok, well very interesting and I'd never have had this idea, but indeed it 
> works: within a transaction, it takes only a few seconds. This is very 
> surprising as to me, a single DELETE statement is nothing more than that: a 
> single atomic operation which should automatically be treated as a 
> transaction (auto-commit-mode).

I don't think this is the problem.  I think it's more likely the other problem 
I mentioned: difficulty in finding which cascade rows to delete.  Did you run 
your integrity check and make sure you had an appropriate UNIQUE index (or that 
you were keying on your primary key) ?

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

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

Re: [sqlite] Row locking sqlite3

[Simon Slavin]

On 27 Mar 2019, at 6:04pm, Thomas Kurz  wrote:

> Ok, well very interesting and I'd never have had this idea, but indeed it 
> works: within a transaction, it takes only a few seconds. This is very 
> surprising as to me, a single DELETE statement is nothing more than that: a 
> single atomic operation which should automatically be treated as a 
> transaction (auto-commit-mode).

I don't think this is the problem.  I think it's more likely the other problem 
I mentioned: difficulty in finding which cascade rows to delete.  Did you run 
your integrity check and make sure you had an appropriate UNIQUE index (or that 
you were keying on your primary key) ?

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

Re: [sqlite] Row locking sqlite3

[Thomas Kurz]

> I wonder whether SQLite is treating each DELETE as a single transaction.  
> Could you try wrapping the main delete in BEGIN ... END and see whether that 
> speeds up the cascaded DELETE ?  Would you be able to find timings (either in 
> your code or in the command-line tool) and tell us whether it's the DELETE or 
> the END which takes the time ?

Ok, well very interesting and I'd never have had this idea, but indeed it 
works: within a transaction, it takes only a few seconds. This is very 
surprising as to me, a single DELETE statement is nothing more than that: a 
single atomic operation which should automatically be treated as a transaction 
(auto-commit-mode).

*confused*

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

Re: [sqlite] Row locking sqlite3

[Dominique Devienne]

On Wed, Mar 27, 2019 at 1:02 PM Simon Slavin  wrote:

> On 27 Mar 2019, at 11:48am, Thomas Kurz  wrote:
> Locking by rows is a slow operation.  You first have to lock the entire
> database, then lock the row, then release the database.  And each of those
> locks is a test-and-lock operation.  Also, because of the way indexes work,
> locking a row often locks that row's neighbours too in each index.  Code
> which just locks the entire database is simpler and faster.
>

It's not so much about performance Simon, although that remains a concerns
of course,
it's about *concurrency*. Yes it adds overhead compared to simpler and less
granular locking,
but you gain better concurrency in exchange.

Just like single threaded typically achieves better per-thread throughput
than multi-threaded.
But when you have many users or many threads, you typically do not want
them sitting idle
while only a single one at a time is busy. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row locking sqlite3

[Simon Slavin]

On 27 Mar 2019, at 11:48am, Thomas Kurz  wrote:

> Imho quite simple: There are operations that take a long time. I observe this 
> behavior especially with DELETE in combination with ON CASCADE DELETE. Can 
> take half an hour, and meanwhile the database remains locked.

A timing that long suggests that there is no index which allows fast 
identification of the keyed rows.  Or that the index is damaged.  Have you 
checked ?

I wonder whether SQLite is treating each DELETE as a single transaction.  Could 
you try wrapping the main delete in BEGIN ... END and see whether that speeds 
up the cascaded DELETE ?  Would you be able to find timings (either in your 
code or in the command-line tool) and tell us whether it's the DELETE or the 
END which takes the time ?

Locking by rows is a slow operation.  You first have to lock the entire 
database, then lock the row, then release the database.  And each of those 
locks is a test-and-lock operation.  Also, because of the way indexes work, 
locking a row often locks that row's neighbours too in each index.  Code which 
just locks the entire database is simpler and faster.

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

Re: [sqlite] Row locking sqlite3

[Thomas Kurz]

Imho quite simple: There are operations that take a long time. I observe this 
behavior especially with DELETE in combination with ON CASCADE DELETE. Can take 
half an hour, and meanwhile the database remains locked.


- Original Message - 
From: Simon Slavin 
To: SQLite mailing list 
Sent: Wednesday, March 27, 2019, 12:14:15
Subject: [sqlite] Row locking sqlite3

The other aspect of this is to wonder why OP wants row locking.  They might 
think that it's faster than locking the whole database, or that it will allow 
their program to work without any pauses.  Neither of these are necessarily 
true.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Row locking sqlite3

[Simon Slavin]

The other aspect of this is to wonder why OP wants row locking.  They might 
think that it's faster than locking the whole database, or that it will allow 
their program to work without any pauses.  Neither of these are necessarily 
true.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row locking sqlite3

[Wout Mertens]

See also
http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2017-August/074060.html
-
this branch has been around for a while and I think it was mentioned
elsewhere that it definitely is being considered for mainline inclusion at
some not immediate point.

Wout.


On Tue, Mar 26, 2019 at 4:37 PM Joshua Wise 
wrote:

> I’ve seen the server mode <
> https://sqlite.org/src/raw/README-server-edition.html?name=0c6bc6f55191b6900595fe37470bbe5772953ab5c64dae967d07a5d58a0c3508>
> branch, but to me it’s a deal-breaker that it requires SYNCHRONOUS = OFF.
>
> This BEGIN CONCURRENT idea really interests me. By using WAL mode, where I
> can have synchronous commits, the feature becomes much more valuable.
>
> Any word from Mr. Hipp on the status of this branch? Will it ever make it
> into the official version of SQLite3, perhaps under a compile-time option?
>
> > On Mar 22, 2019, at 5:07 PM, Jens Alfke  wrote:
> >
> >
> >
> >> On Mar 22, 2019, at 1:38 PM, Barry Smith 
> wrote:
> >>
> >> You might be interested in the BEGIN CONCURRENT branch. It does page
> level locking (not quite as granular as row level).
> >>
> >>
> https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begin_concurrent.md
> <
> https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begin_concurrent.md
> >
> >
> > Oh, this is interesting. Is this a feature in development that will be
> released soon?
> >
> > [And to be pedantic: according to those docs, this feature does not do
> page level locking; it’s optimistic not pessimistic concurrency.]
> >
> > —Jens
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row locking sqlite3

[Joshua Wise]

I’ve seen the server mode 

 branch, but to me it’s a deal-breaker that it requires SYNCHRONOUS = OFF.

This BEGIN CONCURRENT idea really interests me. By using WAL mode, where I can 
have synchronous commits, the feature becomes much more valuable.

Any word from Mr. Hipp on the status of this branch? Will it ever make it into 
the official version of SQLite3, perhaps under a compile-time option?

> On Mar 22, 2019, at 5:07 PM, Jens Alfke  wrote:
> 
> 
> 
>> On Mar 22, 2019, at 1:38 PM, Barry Smith  wrote:
>> 
>> You might be interested in the BEGIN CONCURRENT branch. It does page level 
>> locking (not quite as granular as row level).
>> 
>> https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begin_concurrent.md 
>> 
> 
> Oh, this is interesting. Is this a feature in development that will be 
> released soon?
> 
> [And to be pedantic: according to those docs, this feature does not do page 
> level locking; it’s optimistic not pessimistic concurrency.]
> 
> —Jens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] Row locking sqlite3

[Jens Alfke]

> On Mar 22, 2019, at 1:38 PM, Barry Smith  wrote:
> 
> You might be interested in the BEGIN CONCURRENT branch. It does page level 
> locking (not quite as granular as row level).
> 
> https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begin_concurrent.md 
> 

Oh, this is interesting. Is this a feature in development that will be released 
soon?

[And to be pedantic: according to those docs, this feature does not do page 
level locking; it’s optimistic not pessimistic concurrency.]

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row locking sqlite3

[Thomas Kurz]

Ah, great, thank you very much for this information.

- Original Message - 
From: Barry Smith 
To: SQLite mailing list 
Sent: Friday, March 22, 2019, 21:38:10
Subject: [sqlite] Row locking sqlite3

You might be interested in the BEGIN CONCURRENT branch. It does page level 
locking (not quite as granular as row level).

https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begin_concurrent.md

> On 22 Mar 2019, at 11:48 am, Thomas Kurz  wrote:

> This sounds interesting. I have some questions about:

>> Row lock information is shared with processes. If a process finished 
>> unexpectedly, unnecessary lock information might be stayed. In order to 
>> unlock them, please use sqlumdash_cleaner.exe which clears all record 
>> information. If there is a process which is in a transaction, 
>> sqlumdash_cleaner.exe should be called after end the transaction.

> - Where is row lock information stored? In database file, in journal file, in 
> WAL file, or in memory?
> - Why should the cleaner be called after the end of a transaction?
> - I don't like the idea of calling an external exe in case of problems (and 
> more than that, after every transaction??). Couldn't you introduce a PRAGMA 
> for unlocking rows?



> - Original Message - 
> From: Peng Yu 
> To: SQLite mailing list 
> Sent: Friday, March 22, 2019, 15:25:24
> Subject: [sqlite] Row locking sqlite3

> Hi,

> I see that sqlite3 still does not support row locking. This package
> tries to resolve this problem. But it does not have a standard build
> process for Linux.

> https://github.com/sqlumdash/sqlumdash/

> Are there other packages similar to sqlite3 but support row locking? Thanks.

> -- 
> Regards,
> Peng
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

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

Re: [sqlite] Row locking sqlite3

[Barry Smith]

You might be interested in the BEGIN CONCURRENT branch. It does page level 
locking (not quite as granular as row level).

https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begin_concurrent.md

> On 22 Mar 2019, at 11:48 am, Thomas Kurz  wrote:
> 
> This sounds interesting. I have some questions about:
> 
>> Row lock information is shared with processes. If a process finished 
>> unexpectedly, unnecessary lock information might be stayed. In order to 
>> unlock them, please use sqlumdash_cleaner.exe which clears all record 
>> information. If there is a process which is in a transaction, 
>> sqlumdash_cleaner.exe should be called after end the transaction.
> 
> - Where is row lock information stored? In database file, in journal file, in 
> WAL file, or in memory?
> - Why should the cleaner be called after the end of a transaction?
> - I don't like the idea of calling an external exe in case of problems (and 
> more than that, after every transaction??). Couldn't you introduce a PRAGMA 
> for unlocking rows?
> 
> 
> 
> - Original Message - 
> From: Peng Yu 
> To: SQLite mailing list 
> Sent: Friday, March 22, 2019, 15:25:24
> Subject: [sqlite] Row locking sqlite3
> 
> Hi,
> 
> I see that sqlite3 still does not support row locking. This package
> tries to resolve this problem. But it does not have a standard build
> process for Linux.
> 
> https://github.com/sqlumdash/sqlumdash/
> 
> Are there other packages similar to sqlite3 but support row locking? Thanks.
> 
> -- 
> Regards,
> Peng
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row locking sqlite3

[Thomas Kurz]

This sounds interesting. I have some questions about:

> Row lock information is shared with processes. If a process finished 
> unexpectedly, unnecessary lock information might be stayed. In order to 
> unlock them, please use sqlumdash_cleaner.exe which clears all record 
> information. If there is a process which is in a transaction, 
> sqlumdash_cleaner.exe should be called after end the transaction.

- Where is row lock information stored? In database file, in journal file, in 
WAL file, or in memory?
- Why should the cleaner be called after the end of a transaction?
- I don't like the idea of calling an external exe in case of problems (and 
more than that, after every transaction??). Couldn't you introduce a PRAGMA for 
unlocking rows?



- Original Message - 
From: Peng Yu 
To: SQLite mailing list 
Sent: Friday, March 22, 2019, 15:25:24
Subject: [sqlite] Row locking sqlite3

Hi,

I see that sqlite3 still does not support row locking. This package
tries to resolve this problem. But it does not have a standard build
process for Linux.

https://github.com/sqlumdash/sqlumdash/

Are there other packages similar to sqlite3 but support row locking? Thanks.

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

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