Re: [sqlite] Row locking sqlite3

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

Re: [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

Re: [sqlite] Row locking sqlite3

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

Re: [sqlite] Row locking sqlite3

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

Re: [sqlite] Row locking sqlite3

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

Re: [sqlite] Row locking sqlite3

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

Re: [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

Re: [sqlite] Row locking sqlite3

> 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

Re: [sqlite] Row locking sqlite3

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.

Re: [sqlite] Row locking sqlite3

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

Re: [sqlite] Row locking sqlite3

: 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

Re: [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. ___

Re: [sqlite] Row locking sqlite3

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,

Re: [sqlite] Row locking sqlite3

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,

Re: [sqlite] Row locking sqlite3

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

Re: [sqlite] Row locking sqlite3

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

Re: [sqlite] Row locking sqlite3

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

Re: [sqlite] Row locking sqlite3

- 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: [sqli

[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