Re: [sqlite] Feedback on article about SQLITE_BUSY
> I think "single writer, multiple readers" is the simplest way to describe sqlite's approach to isolation I’m not sure if this summarization paints enough of a picture about how SQLite restricts interleaving of read & write operations between concurrent transactions, to guarantee isolation. For eg, in Rollback journal, a writing transaction looking to commit blocks other readers (as you mentioned), but WAL does not. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locking problems
On Mon, 21 Jan 2019 18:12:25 -0500 Richard Damon wrote: > Some operations can be order of microseconds if the data resides in > cache, Thank you, I hadn't considered that. I was thinking that seek times on "spinning rust" -- which is the only economically feasible technology for large databases, which would be typical of a database with many concurrent users -- is still measured in milliseconds. And the larger the database, the lower the cache hit ratio. > SQLite is NOT intended to be used over a network No emphasis required. I didn't imply network access. I posited multple clients, of which there could be many on a large shared-logic machine. --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locking problems
On 1/21/19 4:38 PM, James K. Lowden wrote: > On Sun, 20 Jan 2019 17:01:25 -0700 > "Keith Medcalf" wrote: > >> SQLite3 however has latencies on the order of microseconds > Is that really true? Are there machines for which SQLite's throughput > can be measured in transactions per millisecond? > > I think you're referring to the latency of the function-call overhead, > as opposed to using a network interface. But since DBMSs are basically > I/O machines, and the most interesting operations involve I/O, it's not > clear to me why function-call overhead is a relevant measure. > >> [SQLite] does not have the same opportunity for opportunistic >> behaviour as does a client/server database which may be serving >> thousands of concurrent (but different) applications. > That I think is the relevant measure. It's the number of clients, not > latency, that makes SQLite's lock-the-whole database feasible. On a > large machine supporting thousands of clients, the latency advantage > would be overwhelmed by the concurrency disadvantage, depending of > course on the rate of updates. > > --jkl SQLite is NOT intended to be used over a network, and in fact there are warnings about accessing the database file over a network file system, as this often doesn't support the assumed locking primitives. SQLite is intended to be accessed over the local file system. Some operations can be order of microseconds if the data resides in cache, slightly longer if the database is in flash memory, and perhaps 100s of microseconds to milliseconds if the database is on spinning rust, and the operation needs to access the drive to get the needed data. -- Richard Damon ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locking problems
On Sun, 20 Jan 2019 21:51:19 + wrote: > > insert into t > > select :pid, nrows, N > > from (select 1 as N union select 2 union select 3) as cardinals > > cross join (select :pid, count(*) as nrows from t) as how_many; > > > > By using a single SQL statement, you avoid a user-defined > > transaction and any proprietary transaction qualifiers. > > There's too much back-and-forth between the database and my logic to > put it all into a single statement. Thus, transactions are > necessary. Transactions exist to allow multiple statements to become > an atomic unit, so eschewing them is basically the same thing as > admitting they don't work. I don't suggest you eschew user-defined transactions, and I don't admit they don't work. I suggest not relying on proprietary features. BEGIN IMMEDIATE may do what you want, but at the cost of not learning how to accomplish the same thing using standard SQL. In general, the formulation select ... do stuff insert ... will get you in trouble in most SQL setups, because selected data are subject to change, whether or not the logic is in a user-defined transaction. If the DBMS is set up with serialized isolation, concurrency will be compromised because you're holding a transaction open across application logic. That's especially true when it would be most convenient: when "do stuff" involves user interaction, and said user might choose that moment for a coffee break. The most common solution is to check your assumptions at the door, otherwise known as "optimistic concurrency". At time of insert, make sure the previously obtained data still obtain, i.e., that nothing has changed meanwhile. If it has, the precondition for the update has been violated, and you start over. If it hasn't -- and, in practice, usually it hasn't -- you're good to go. In your case, you might be able to do something like select count(*) as nrows from t do stuff begin transaction insert into t select ... where nrows = (select count(*) from t) verify rows affected == 1 insert into t select ... where nrows = (select count(*) from t) verify rows affected == 1 insert into t select ... where nrows = (select count(*) from t) verify rows affected == 1 commit putting whatever the relevant test is in the WHERE clause. The test can be simplified in SQLite to just the first insert, because with SQLite's single-writer design, they're redundant once the transaction has begun to take effect (ie, once other writers really are blocked). User-defined transactions weren't invented to deal with read-write-write errors. They were invented to enforce referential integrity. SQL has no syntax to update several related tables at the same time. If updates to different tables would leave the database in an inconsistent state, some mechanism is needed to convert those several SQL statements into a single all-or-nothing update. Thus were born user-defined transactions. --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locking problems
On Sun, 20 Jan 2019 17:01:25 -0700 "Keith Medcalf" wrote: > SQLite3 however has latencies on the order of microseconds Is that really true? Are there machines for which SQLite's throughput can be measured in transactions per millisecond? I think you're referring to the latency of the function-call overhead, as opposed to using a network interface. But since DBMSs are basically I/O machines, and the most interesting operations involve I/O, it's not clear to me why function-call overhead is a relevant measure. > [SQLite] does not have the same opportunity for opportunistic > behaviour as does a client/server database which may be serving > thousands of concurrent (but different) applications. That I think is the relevant measure. It's the number of clients, not latency, that makes SQLite's lock-the-whole database feasible. On a large machine supporting thousands of clients, the latency advantage would be overwhelmed by the concurrency disadvantage, depending of course on the rate of updates. --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Building SQLite DLL with Visual Studio 2015
There are a few reasons for putting code in a DYNAMIC link library (linked at runtime) versus statically linked into the application (the same applies to all operating systems that support runtime linkage no matter what they call the runtime linked modules): 1) You need to be able to replace the version of the DYNAMIC library code without rebuilding anything else or 2) You have multiple executables (A) (B) and (C) (for example) that will all use the same DYNAMIC library code and you are hard-up for memory such that you only want the DYNAMIC library code loaded once and used by all requestors in order to save memory. or both. Note that there is a third reason for using a dynamic (runtime) linked library and that is that it provides something that you want all comers to be able to use the same way. Examples of this include the interfaces to the operating system and its platform libraries themselves which are always dynamically linked (this is the primary job of the program loader after all), and code which is "near to" such status such as widely used utility routines (such as zlib, X, etc). You may use item (1) for example so that your application uses SQLite3 in a DLL. Without making any change to your application at all, you can change the version of SQLite3 being used simply by changing the SQLite3.DLL. This works well provided that the API contract does not change between versions of the DLL and that you are dependent on that API contract, not on the implementation details of the contract "under the hood" -- if it does (ie, you are version/implementation dependent) then you are in what is known as "DLL Hell" because your application is not actually dependent on the SQLite3 API, it is dependent on a certain VERSION of the SQLite3.DLL. Mutatis mutandis to any DLL providing any API. You may use item (2) for example so that your application uses a standard facility in a way that is compatible with item (1) in order to save loading the code into every process. An example of this is the language/subsystem runtime libraries. Since there may be many thousands of these executables running concurrently -- such as every single process requiring the use of the platform "C" library. Rather than statically link the several megabytes of runtime into each and every executable (and loading A COPY of the same code with each executable), the library is only loaded ONCE and each process MAPS the loaded module into its own virtual address space thus perhaps saving gigabytes or terabytes of memory by only actually loading one copy of the code which everyone uses. Putting something into a DLL "just cuz you can" is not a good reason for putting code in a DLL and unless you require the capabilities provided by (1) or (2) and are absolutely sure that you are dependent on the API contract and not the implementation contract, putting code in a DLL is almost always a bad idea. It may be "cute" and "what the jonses are doing", but it serves no useful purpose other than add complication for complication sake. That is to say that if you have to change anything other than just "copying over" the DLL to change the implementation of the API functionality that DLL provides to your application, then using a DLL was the wrong thing to do. So, the only one who can answer your question is you by your knowledge of how your application works. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of John Smith >Sent: Monday, 21 January, 2019 03:16 >To: SQLite mailing list >Subject: [sqlite] Building SQLite DLL with Visual Studio 2015 > >Hi, > > >I need to build a data-layer DLL for a large project. > >My project is 64-bit built with Visual-Studio 2015. > >I want to ask about what would be the preferred way to build SQLite: > >1. Build SQLite code as a separate DLL and use it from my data-layer >DLL, > >2. Use the ready-built binary of 64-bit SQLite DLL for Windows >(sqlite-dll-win64-x64-326.zip), > >3. Or, build my data-layer code with SQLite code as a single DLL. > >Thanks in advanced, > >John > > >___ >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] Building SQLite DLL with Visual Studio 2015
Just curious as to why you wouldn't choose option #2 - as that's what I use with my VB .NET applications, which work quite well. Just distribute the .exe, then the two SQLite DLLs (well, three technically, as there are two versions of the interop.dll) Thanks, Chris On Mon, Jan 21, 2019 at 4:19 PM Simon Slavin wrote: > > > On 21 Jan 2019, at 11:16am, J Decker wrote: > > > 4. Statically linked to and compiled with your datalayer code. > > What he said. Also, you should be using the 'Amalgamation' download to do > this, unless you need some compiler switches which are not supported by > that source-set. > > 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] Building SQLite DLL with Visual Studio 2015
On 21 Jan 2019, at 11:16am, J Decker wrote: > 4. Statically linked to and compiled with your datalayer code. What he said. Also, you should be using the 'Amalgamation' download to do this, unless you need some compiler switches which are not supported by that source-set. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feedback on article about SQLITE_BUSY
> There seems to be a few misconceptions in the article regarding the > difference between DEFERRED/IMMEDIATE/EXCLUSIVE Looks like I mistakes in the 2PL section, it had contradictory content about `RESERVED` locks. Thanks for your feedback, have pushed corrections. > Specifically, your diagrams suggest that once a writer obtains a RESERVED lock (as happens when an IMMEDIATE transaction begins), no readers will be able to obtain a SHARED lock. The idea was to portray a scenario which forces serial execution of transactions. If all transactions are started in either `IMMEDIATE`/`EXCLUSIVE` modes, they try to acquire `RESERVED` /`EXCLUSIVE` locks which block other `IMMEDIATE` and `EXCLUSIVE` transactions. > It looks like you go into more detail on the locks later in the article - > only got to the "Shared cache mode" section. The sections on locking and WAL were meant to build up concepts like deadlocks and stale snapshots (discussed later in article), and the fact that SQLite can’t make progress on transactions by retrying individual queries in those cases. Rahul ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Building SQLite DLL with Visual Studio 2015
On Mon, Jan 21, 2019 at 2:16 AM John Smith wrote: > Hi, > > > I need to build a data-layer DLL for a large project. > > My project is 64-bit built with Visual-Studio 2015. > > I want to ask about what would be the preferred way to build SQLite: > > 1. Build SQLite code as a separate DLL and use it from my data-layer DLL, > > 2. Use the ready-built binary of 64-bit SQLite DLL for Windows > (sqlite-dll-win64-x64-326.zip), > > 3. Or, build my data-layer code with SQLite code as a single DLL. > 4. Statically linked to and compiled with your datalayer code. > Thanks in advanced, > > John > > > ___ > 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] Database locking problems
On Mon, 21 Jan 2019 at 07:21, Keith Medcalf wrote: > In DELETE or TRUNCATE (that is, all modes except WAL) a READ transaction > in progress blocks a WRITE transaction and a WRITE transaction in progress > blocks all other attempts to commence a transaction of any type on any > other connection. > Nitpick: an active READ transaction blocks the COMMIT stage of a WRITE transaction. An active WRITE transaction blocks all attempts to start another WRITE transaction, and once it is ready to COMMIT blocks attempts to start READ transactions. -Rowan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locking problems
On Mon, 21 Jan 2019 at 15:46, wrote: > For the moment, the solution that is working for me is to disable syncing > with PRAGMA synchronous = OFF. This is acceptable in this particular > application because a power failure or OS crash will necessitate restarting > the data gathering process anyway. > If the data gathering process is creating a database for later consumption by other applications, and you potentially want to use this on a network file system, note that you want to turn synchronous back ON and run one final transaction to update the database on each connection you've opened. This is because sqlite ignores errors from close() - which doesn't matter in normal circumstances because any i/o errors should have been collected by fdatasync(). But when you're not syncing it opens the possibility of a silently corrupt data set, because even though the file server reported the error sqlite suppressed it. > In looking at the logs, I'm seeing several unlucky locking patterns. > Let's just pick one. Process A gets starved by process B since B does many > write transactions in a row, each time successfully getting an EXCLUSIVE > lock. When B calls fdatasync() (actually fsync()), it blocks while holding > EXCLUSIVE. A wakes up, but A can't immediately get a SHARED lock because B > holds EXCLUSIVE. A goes to sleep while holding no locks, then B wakes up > when the fsync() completes. B then releases locks but grabs EXCLUSIVE > again to complete its next transaction, and the cycle repeats. A still > can't get its SHARED lock, so it goes to sleep again, and then B continues > to monopolize the lock. This goes on long enough to exhaust A's patience, > at which point SQLITE_BUSY propagates to the application. > Yeah, I've seen a tight loop of write transactions starve readers before - since they've most likely backed off to sleep for 100ms at a time they have to get really lucky to wake up while the writer is idle. It doesn't strike me as a common workload though? Like if you need that kind of constant throughput without disrupting readers it may be time to consider an RDMS. > I saw another pattern that I'm having a harder time explaining. It looked > almost like the process holding EXCLUSIVE got stuck waiting for the process > trying to get EXCLUSIVE. The latter would eventually timeout, at which > point the former would proceed. Shrug! I should investigate this further, > but look at the time. > I don't understand this one, and it doesn't really add up because: 1. A process holding EXCLUSIVE has the highest lock possible; it's not waiting for anyone 2. Why is any other process blocked trying to acquire EXCLUSIVE at this point? It shouldn't even be able to get a SHARED/RESERVED lock. -Rowan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Building SQLite DLL with Visual Studio 2015
Hi, I need to build a data-layer DLL for a large project. My project is 64-bit built with Visual-Studio 2015. I want to ask about what would be the preferred way to build SQLite: 1. Build SQLite code as a separate DLL and use it from my data-layer DLL, 2. Use the ready-built binary of 64-bit SQLite DLL for Windows (sqlite-dll-win64-x64-326.zip), 3. Or, build my data-layer code with SQLite code as a single DLL. Thanks in advanced, John ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite error (5): database is locked
On Tue, 15 Jan 2019 at 02:54, Simon Slavin wrote: > The "just-in-time" idea mentioned in your question doesn't work in real > life, since constantly checking mutex status keeps one core completely > busy, using lots of power and generating lots of heat. > Technically "just-in-time" could be implemented fine; the normal file locking primitives used on both windows and unix can operate in a blocking mode, where the kernel wakes the process up once the lock has been relinquished. sqlite just doesn't use that API. It would be a pretty significant change for sqlite to invoke said API, and may affect the ability to support other existing locking modes which don't provide the same semantics and where polling _is_ required (eg. dotfile). Also I'm not 100% sure whether it would be safe to drop in, or whether blocking the process for lock acquisition within sqlite's locking protocol would introduce deadlock scenarios. FYI sqlite's backoff reaches a maximum sleep time of 100ms. Unless you're on unix and compile without -DHAVE_USLEEP=1 in which case there's no backoff and every sleep is 1000ms long. -Rowan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feedback on article about SQLITE_BUSY
There seems to be a few misconceptions in the article regarding the difference between DEFERRED/IMMEDIATE/EXCLUSIVE, and the fine details of how different lock states interact. Specifically, your diagrams suggest that once a writer obtains a RESERVED lock (as happens when an IMMEDIATE transaction begins), no readers will be able to obtain a SHARED lock. But this is not the case - while a process holds the RESERVED lock it's only other attempts to _write_ the database which will be met with SQLITE_BUSY. It's only once the writer upgrades to a PENDING lock that readers will be rejected via SQLITE_BUSY. Historical note: this wasn't part of sqlite's original locking protocol, but was added later to solve a writer starvation problem. Anyway, the RESERVED -> PENDING transition only happens when either (a) the writer is ready to commit the transaction, or (b) there's a cache spill, ie. the transaction has modified more database page then will fit in the configured cache (see pragmas cache_size and cache_spill for more info). It looks like you go into more detail on the locks later in the article - I only got to the "Shared cache mode" section. The content before that felt misleading in terms of reader/writer locking interaction. I think "single writer, multiple readers" is the simplest way to describe sqlite's approach to isolation, but I'm also pretty biased because I have a _lot_ more experience with sqlite compared to other DBs and I'm only just getting my head around the idea of non-isolated transactions or multiple concurrent writers! -Rowan On Wed, 9 Jan 2019 at 21:48, Rahul Jayaraman wrote: > I wrote an article about my high-level understanding of `SQLITE_BUSY` > errors, hoping it might help others understand concurrency in SQLite > better. It covers scenarios under which the error shows up, while SQLite > tries to respect its isolation guarantee. > > https://www.activesphere.com/blog/2018/12/24/understanding-sqlite-busy > > I’d appreciate feedback on the article. Apologies if this is the wrong > place to post such content. > > Thanks, > Rahul > ___ > 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] Database locking problems
On 21/01/2019 18:46, andrew.g...@l3t.com wrote: Okay, I put in some instrumentation. Basically I print out all database queries as they happen, along with all calls to sqlite3OsLock() and sqlite3OsUnlock() (including their lockType argument and any abnormal return code). Also I print out how many times sqlite3InvokeBusyHandler() has to call the busy handler. For the moment, the solution that is working for me is to disable syncing with PRAGMA synchronous = OFF. This is acceptable in this particular application because a power failure or OS crash will necessitate restarting the data gathering process anyway. I'll explain later why this change helps. In looking at the logs, I'm seeing several unlucky locking patterns. Let's just pick one. Process A gets starved by process B since B does many write transactions in a row, each time successfully getting an EXCLUSIVE lock. When B calls fdatasync() (actually fsync()), it blocks while holding EXCLUSIVE. A wakes up, but A can't immediately get a SHARED lock because B holds EXCLUSIVE. A goes to sleep while holding no locks, then B wakes up when the fsync() completes. B then releases locks but grabs EXCLUSIVE again to complete its next transaction, and the cycle repeats. A still can't get its SHARED lock, so it goes to sleep again, and then B continues to monopolize the lock. This goes on long enough to exhaust A's patience, at which point SQLITE_BUSY propagates to the application. Long ago, when I was at PowerFlex, on an OS long dead (Siemens-Nixdorf SINIX) using the MIPS RM series of processors, we saw a similar problem. In that case it was a batch process locking out interactive users, but *only* on the top-of-the-line machine, the lower end, slower CPUs were fine. One of S-N's finest engineers informed the customer that we "didn't know anything about file-locking on UNIX(TM) systems," which was slightly annoying, to say the least. So we started into space, and we drew diagrams on the whiteboard, and I scribbled on my notepad, and stared at my locking code, and eventually decided that it was probably down to the batch process on the faster CPU just not giving up the lock when the interactive processes were ready to run. I.e. "Here's the lock... Oh, I've still got some time left, I'll hang on to it... My time has run out but I've still got the lock, nyah, nyah, nyah." We added "sleep(0)" after the "unlock()" call. The problem went away. This may help you, it may not. Cheers, GaryB-) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
