Re: [sqlite] Database locking problems
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. If A tries to get SHARED while B holds EXCLUSIVE, I'd like to see A's desire for SHARED to be registered somehow so that A is granted SHARED the moment B drops EXCLUSIVE. Then when B tries to get EXCLUSIVE a second time, it is forced to wait in PENDING until A releases SHARED. I imagine one way to do this would be for A to block inside fcntl() rather than usleep(), so that the OS can atomically switch the lock over to A the very instant B releases it. But as far as I know, the only API that can give fcntl() locking a timeout is setitimer() and SIGALRM. I wouldn't want SQLite to mess with that, not without being given permission by a special preprocessor #define. SQLITE_ENABLE_SETLK_TIMEOUT, perhaps? In the above case, A is just trying to get SHARED. This is a difference from the test program I posted, which only needs write transactions. My real application needs to do a read transaction as part of database initialization in order to confirm the schema version is correct. That's probably a waste of time, but it's in there for now. But I think the more important difference is that B is doing many transactions in a tight loop. I guess I could put a sleep in the loop to give other processes a chance. 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. Okay, why does disabling syncing help? Taking fdatasync() out of the picture means B doesn't have to go to sleep holding EXCLUSIVE. It grabs EXCLUSIVE, tells the OS what it wants written to disk, then releases EXCLUSIVE without waiting to confirm that all the bits landed on the platters. --- CONFIDENTIALITY NOTICE: This email and any attachments are for the sole use of the intended recipient and may contain material that is proprietary, confidential, privileged or otherwise legally protected or restricted under applicable government laws. Any review, disclosure, distributing or other use without expressed permission of the sender is strictly prohibited. If you are not the intended recipient, please contact the sender and delete all copies without reading, printing, or saving.. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Variable Declaration
There does exist an experimental branch which introduces the concept of shell variables. https://www.sqlite.org/src/timeline?r=shell-bindings The check-in comment describes the usage: "Add the ability to use bind parameters in the CLI. The new ".set KEY=VALUE" dot-command works to set bindings. Or use the "-Dkey=value" command-line option. Or use the built-in shell_bindings(k,v) virtual table to set, delete, or changing bindings." Note: A bug exists when setting text/blob values via the shell_bindings virtual table. The bindvtabUpdate method fails to set p->len. Adding p->len = len resolves the issue. On Sun, Jan 20, 2019 at 1:15 PM wrote: > > I'm jumping into the middle of the conversation, possibly without seeing the > beginning, but from what I've seen so far, it seems to me that the key is > recognizing there are many tools in a programmer's toolbox. Each tool covers > a certain area of functionality really well, then there are areas where it > can be applied but with difficulty, and then the rest is off-limits for any > serious applications. There is considerable overlap between the > applicability of different tools, so you get to pick your favorites. But you > have to be willing to incorporate additional tools when you get to the limits > of comfort for your favorite tool. > > However, instead what I often see is people trying to expand the scope of > their favorite. Sometimes this is the right choice, but usually it is not. > It takes considerable experience and good taste to be able to wisely judge > the difference and to chart a roadmap that legitimately enhances tools in a > way that justifies the cost to maintenance, documentation, ease of learning, > and so forth. I know I've been on the wrong side of this many times, but I > try to learn from my mistakes. > > SQLite is a special sort of tool in that it expressly acknowledges its > limitations, and it lets those limitations be its strength. I mean, "Lite" > is right there in the name. The idea of having a larger number of more > limited tools, with a stronger focus on interoperability, is a very powerful > and transformative engineering mindset and is central to the Unix design > philosophy (which, by the way, is often at odds with the Unix design > practice). The point of SQLite, in contrast to SQL servers, is that it be > embeddable in your application. Thus, SQLite has no need to replicate the > capabilities already present in every reasonable programming language. > Namely, variables. > > I do see a valid objection earlier in the thread: the SQLite shell. It > indeed lacks variables, though they can be emulated with temporary tables. > This is what I was talking about in the first paragraph. It's possible to > stretch tools beyond their core competency, but it can be difficult, so > you're better off letting other tools fill the gaps. But when you're using > the SQLite shell, that's not really a good option. You can let the SQLite > shell be the backend to a shell script, but you're not going to want to call > it more than once or twice per complete run of a program. > > So rather than discuss adding variable declarations to SQLite, maybe we > should be talking about adding variable declarations to the SQLite shell. > Please don't think I'm making a serious proposal. Instead it's just for the > sake of argument. Consider what would come of doing that. We'd be taking > the SQLite shell in the direction of becoming a programming language unto > itself, complementary to yet distinct from SQLite. Once the SQLite shell > gets variables, there would also be clamor for loops and conditionals and > function declarations and fine-grained I/O and so forth. That would be cool, > I suppose. And then, once all that is implemented, why not migrate the new > functionality from the shell into SQLite proper? But, umm, don't we already > have dozens or hundreds of programming languages to choose from? Does SQLite > really need to compete in that arena? > > Instead, here's what I think. SQLite is extensible by virtue of being > embeddable; it can be embedded in a wrapper that expands its capabilities. > Furthermore, the sources are maximally open and free, including the Lemon > parser. If you want to add new syntax to SQLite to ease its integration with > another programming language, start by duplicating and extending its parser > to recognize variable declaration and usage. The output of this new parser > would be plain-Jane SQLite SQL, augmented with all the necessary variable > declarations, statement compiles, parameter bindings, statement steps, column > extractions, error checks, and so on needed to make it happen. > > So, figure out exactly what you want the new syntax to look like, and write a > lot of sample queries in the updated language. For each example, write the > equivalent C code. Then, it will be your task to make a parser that knows > how to
Re: [sqlite] Database locking problems
Keith Medcalf wrote: > Andy Goth wrote: >> There are two possibilities: >> >> 1. Transactions do work, but I'm misusing them and must learn how to be >> more careful. In this case, I will update documentation to properly >> explain their use to others. >> >> 2. Transactions don't work, at least not for my task. In this case, I >> will do my best to investigate the problem and suggest a correction. >> >> Either way, the right thing for me to do is continue digging in. >> >> Going from BEGIN to BEGIN IMMEDIATE indeed fixed the test program I >> wrote, but my application is still broken, and I don't know what's >> different about it. I'm working on instrumenting the fcntl() calls to >> log the sequence of operations. > > Make sure you are doing nothing foolish like using "shared cache". I investigated shared cache because the notifier sounded like a fun time, but I quickly realized the cache sharing is only between different threads that are already sharing a single database connection. While I need to be prepared for multiple threads within an application, my problem at the moment is with multiple processes, each with their own connection, to a shared database file. > Make sure that you are using the default SERIALIZED or FULLMUTEX thread > management on all connections whether you think you know better or do > not need it or whatever. A few microseconds of CPU might save you many > MONTHS of trying to locate a defect in your code. I am using the default serialized mode. At first I was explicitly asking for FULLMUTEX, but when I traced through the execution I saw that it was merely reinforcing the default I was getting anyway. > If you think that you need to use either "shared cache" or some > threading mode other than the default serialized, then do that ONLY > AFTER your application is working using the defaults. Do not fall into > the premature optimization trap. It's vastly more cost-effective to optimize engineer time than CPU time. > Place all read and write statements which require a consistent database > context in the same transaction. Indeed, this is half the reason I'm using transactions to begin with. The other half is to avoid having to wait for a disk sync every row I insert. > IF THE TRANSACTION WILL OR MAY WRITE TO THE DATABASE AT SOME POINT > BEFORE COMMIT/ROLLBACK then start the transaction with BEGIN IMMEDIATE. > > [...] > > An attempt to UPGRADE a READ transaction to a WRITE transaction will > DEADLOCK (and immediately return an error) if another WRITE TRANSACTION > is pending. (Hence always signal your intent when commencing a > transaction by using the appropriate BEGIN syntax in order to avoid this > state of affairs). This is the major thing I was missing and was exactly the fault with the test program I posted. Adding "IMMEDIATE" completely fixed it. Oh, a bit more on that. I still could get SQLITE_BUSY in that test program, but only if I made it fork() so many times that the cumulative sync times added up to more than the busy timeouts I was setting. The later processes would give up if they had to wait for a hundred predecessors to insert their row. This is expected and correct operation, proving that SQLite really is serious about syncing to disk and that it is honoring the specified timeout. Of course, to make this happen, I was going off the deep end and forking hundreds of processes at once. Then when I increased the timeout to match, every last one of the processes was able to get its business done without complaint despite them all starting at the same time. Very nice work, SQLite. > WAL journalling only works where all database user processes are local > to each other (ie, between connections all originating from the same > computer -- where "same computer" means all processes have access to the > same shared memory, so that you may determine whether a SYSPLEX or NUMA > architecture constitutes "same computer" or not, by whether or not they > can all access the same "shared memory" region). I'm not using WAL. The nature of the application is that the initial database population phase is dominated by (sometimes simultaneous) writes, but after that point all access is purely read-only. > File locking only works reliably on local filesystems. It is unreliable > for *ALL* remote filesystems, even if the target "remote" filesystem is > local. Sadly, I am going to need NFS before I'm done, but since I know NFS is a problem, I'm sticking with local filesystems until things are stable. For this specific application, I'll bypass NFS during the population phase by keeping the database in /tmp, then I'll move it to NFS for read-only access. I have another application on the horizon that will need to be able to write over NFS, but I think it will be able to tolerate the theoretical possibility of losing data once in a blue moon. It'll be an append-only log where it's not a big deal if a record occasionally goes missing just
Re: [sqlite] Database locking problems
On Sunday, 20 January, 2019 17:19, Simon Slavin wrote: >> If the issue is the dead lock, you get similar issues with all >> DBMSes. > I'm not perfectly sure of my logic here, but OP posted elsewhere that > replacing BEGIN with BEGIN IMMEDIATE cures the problem. I think this > indicates that his problem isn't deadlock. This indicates that the problem was indeed a deadlock. A deadlock (in this case) occurs when there are two transactions in progress, each of them has a SHARED (READ) lock, one of them (we will call it "A") successfully upgrades to a PENDING lock on the way to an EXCLUSIVE (WRITE) lock, and the other transaction (which we shall call "B") rather than releasing the SHARED lock to permit the PENDING LOCK to upgrade to EXCLUSIVE (WRITE) status, instead tries to acquire a PENDING lock on the way to an EXCLUSIVE (WRITE) lock. The EXCLUSIVE lock cannot be granted to A until the SHARED lock is released by B. However, instead of releasing the SHARED lock, B has also tried to acquire a PENDING lock on the way to an EXCLUSIVE lock. If the PENDING lock is granted to B then you will have two processes that are "deadlocked" (also known as a Mexican Standoff or any number of other colourful names) each waiting forever for the other to give up its SHARED lock so it can proceed. Since this outcome would be double-plus-ungood, the second transaction (B) is terminated by tossing it to the wolves (Return SQLITE_LOCKED or some such error to indicate a deadlock situation). This will cause the second transaction (B) to roll-back and release its SHARED lock permitting A to proceed to completion. (The SHARED lock must also be forced to be released because it represents a lock against a state which no longer exists now that A has been permitted to proceed). >The problem is more about how SQLite implements BEGIN without >IMMEDIATE. As others have explained in this thread, SQLite does its >best to avoid locking the database until it really needs to. But it >does need to obtain a shared lock, and this fails in the specific >pattern OP uses. >Which is why BEGIN IMMEDIATE exists. So use BEGIN IMMEDIATE. Done. The procedure to be used in order to avoid deadlocks in multiprocessing code has been known for a long time. To avoid deadlocks, concurrent code must be able to acquire all the locks it requires at once. The "at once" part can be spread out in time by always acquiring the same set of locks in the same order. If a process fails to acquire any one of them it must relinquish all the locks it has obtained before trying again (that is, it must rebuild its state anew from nothingness). BEGIN IMMEDIATE acquires the SHARED and WRITE lock from the get-go and if successful cannot be a "deadlock victim" since it has acquired all the locks it will ever need. A bare BEGIN, however, can be a deadlock victim since it has only acquired a SHARED lock and if it tries and fails to acquire an additional (WRITE) lock it does not hold but cannot obtain, then it must relinquish all locks (including the SHARED lock) before being permitted to try again. Fully opportunistic locking systems (such as you get in network filesystems or in database servers) often randomly choose the victim in order to avoid deadlocks unless they are able to determine an appropriate victim. --- 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] Database locking problems
On 20 Jan 2019, at 11:54pm, Richard Damon wrote: > If the issue is the dead lock, you get similar issues with all DBMSes. I'm not perfectly sure of my logic here, but OP posted elsewhere that replacing BEGIN with BEGIN IMMEDIATE cures the problem. I think this indicates that his problem isn't deadlock. The problem is more about how SQLite implements BEGIN without IMMEDIATE. As others have explained in this thread, SQLite does its best to avoid locking the database until it really needs to. But it does need to obtain a shared lock, and this fails in the specific pattern OP uses. Which is why BEGIN IMMEDIATE exists. So use BEGIN IMMEDIATE. Done. Simon. ___ 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 Sunday, 20 January, 2019 16:32, Thomas Kurz wrote: >Just for curiosity: how do other DBMS (MySQL, etc.) solve this issue? >I guess the keypoint is that no matter where the query comes from, >the database files are always under control of the same process which >then can take care of the correct order in which to read and write >data. >But the problem that the same database page cannot be written to from >2 different statements (or being read while another statement is >writing into it) should arise there as well, shouldn't it? However, I >have never seen MySQL return some kind of "busy" (whatsoever it may >be named there). >As I said, just for curiosity - no offense against SQlite because I >can well understand the problem that SQlite has to deal with :-) The "other DBMS" to which you refer are all of a class called a Database Server. As such they are accessed remotely and thus have huge latency between when "your application" requests for something to be done and when that request gets to the server so that it can be processed. This means that these database engines have to be optimized for concurrency and this is done by using what is called "row level locking" in which locks apply to a specific row in a specific table. You can indeed "write" the same page at the same time from a single process, you just need to make sure that the row of data that "A" is writing to is not the same row of data that "B" is writing to. Furthermore, many client/server systems use the "opportunistic locking" model which is based on the "plow ahead at full speed but keep enough history to go "Oh Shit" when required" because it is probabilistically unlikely that your 1000 clients will all try and modify the same thing at once and you only need to be able to detect that and go "Oh Shit" when necessary. On the balance the "plow ahead and pray" (also known as opportunistic locking) works adequately for most things. That is, it is probabilistically unlikely that your clients will all try to update the same row in the same table at the same time (a given customer can only be calling ONE of your 5000 customer service representatives to update their shipping address, for example, so having TWO agents submitting an update for the same row at the same time is extremely highly improbable). And if they do, you can raise the "Oh Shit" flag and cancel the transaction(s). So a client/server architecture has massive latencies on the order of hundreds of milliseconds but uses very fine grained locking to allow thousands of turnarounds to occur simultaneously and the updates to "appear to a lay external observer" to be occurring concurrently. SQLite3 however has latencies on the order of microseconds and does not need to utilize the overhead associated with managing such fine grained locking in order to obtain the same or better local throughput, and being a local database at the whim of one client application accessing the database, it 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. --- 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] Database locking problems
On 1/20/19 6:32 PM, Thomas Kurz wrote: > Just for curiosity: how do other DBMS (MySQL, etc.) solve this issue? I guess > the keypoint is that no matter where the query comes from, the database files > are always under control of the same process which then can take care of the > correct order in which to read and write data. > > But the problem that the same database page cannot be written to from 2 > different statements (or being read while another statement is writing into > it) should arise there as well, shouldn't it? However, I have never seen > MySQL return some kind of "busy" (whatsoever it may be named there). > > As I said, just for curiosity - no offense against SQlite because I can well > understand the problem that SQlite has to deal with :-) > If the issue is the dead lock, you get similar issues with all DBMSes. One thing that does help is that many DBMS make the transaction locking on a record level, not the full database (at an overhead cost). somewhat like that experimental concurrency option. Running into deadlocks is a real issue with MySQL databases. If the issue is improper sharing between threads, then the fact that other DBMS run the database server in a separate process handles a lot of the sharing issues (at the cost of overhead). SQLite, gets rid of a lot of that overhead at the cost of the application needs to follow a set of rules. -- 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
Just for curiosity: how do other DBMS (MySQL, etc.) solve this issue? I guess the keypoint is that no matter where the query comes from, the database files are always under control of the same process which then can take care of the correct order in which to read and write data. But the problem that the same database page cannot be written to from 2 different statements (or being read while another statement is writing into it) should arise there as well, shouldn't it? However, I have never seen MySQL return some kind of "busy" (whatsoever it may be named there). As I said, just for curiosity - no offense against SQlite because I can well understand the problem that SQlite has to deal with :-) - Original Message - From: Richard Damon To: sqlite-users@mailinglists.sqlite.org Sent: Monday, January 21, 2019, 00:21:48 Subject: [sqlite] Database locking problems On 1/20/19 4:51 PM, andrew.g...@l3t.com wrote: > James K. Lowden wrote: >> On Sat, 19 Jan 2019 08:07:42 -0500 Richard Hipp wrote: >>> The busy timeout is not working because you start out your transaction >>> using a read operation - the first SELECT statement - which gets a read >>> lock. Later when you go to COMMIT, this has to elevate to a write >>> lock. But SQLite sees that some other process has already updated the >>> database since you started your read. >> Another solution is to rely on atomicity in SQL: >> 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. > Thank you for the suggestion, but I don't believe this is an option in my > application. 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. There are two possibilities: > 1. Transactions do work, but I'm misusing them and must learn how to be more > careful. In this case, I will update documentation to properly explain their > use to others. > 2. Transactions don't work, at least not for my task. In this case, I will > do my best to investigate the problem and suggest a correction. > Either way, the right thing for me to do is continue digging in. > Going from BEGIN to BEGIN IMMEDIATE indeed fixed the test program I wrote, > but my application is still broken, and I don't know what's different about > it. I'm working on instrumenting the fcntl() calls to log the sequence of > operations. Transactions work, but the way you have described your use of them has a predictable issue. The problem being if you begin with a shared lock, try to promote to an exclusive lock to write, then you need to wait for all the other shared locks to clear, and if another of them also tries to promote to an exclusive lock, which cause a deadlock, which sounds to be your issue. Starting with a BEGIN IMMEDIATE breaks this deadlock situation, so it can fix your problem, at the cost that you get less concurrency. The experimental concurrency option might also help, in that a write operation doesn't need an exclusive lock for the whole database, but does introduce more points where a transaction might fail and need to be rolled back. -- Richard DamonTh ___ 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 1/20/19 4:51 PM, andrew.g...@l3t.com wrote: > James K. Lowden wrote: >> On Sat, 19 Jan 2019 08:07:42 -0500 Richard Hipp wrote: >>> The busy timeout is not working because you start out your transaction >>> using a read operation - the first SELECT statement - which gets a read >>> lock. Later when you go to COMMIT, this has to elevate to a write >>> lock. But SQLite sees that some other process has already updated the >>> database since you started your read. >> Another solution is to rely on atomicity in SQL: >> >> 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. > Thank you for the suggestion, but I don't believe this is an option in my > application. 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. There are two possibilities: > > 1. Transactions do work, but I'm misusing them and must learn how to be more > careful. In this case, I will update documentation to properly explain their > use to others. > > 2. Transactions don't work, at least not for my task. In this case, I will > do my best to investigate the problem and suggest a correction. > > Either way, the right thing for me to do is continue digging in. > > Going from BEGIN to BEGIN IMMEDIATE indeed fixed the test program I wrote, > but my application is still broken, and I don't know what's different about > it. I'm working on instrumenting the fcntl() calls to log the sequence of > operations. Transactions work, but the way you have described your use of them has a predictable issue. The problem being if you begin with a shared lock, try to promote to an exclusive lock to write, then you need to wait for all the other shared locks to clear, and if another of them also tries to promote to an exclusive lock, which cause a deadlock, which sounds to be your issue. Starting with a BEGIN IMMEDIATE breaks this deadlock situation, so it can fix your problem, at the cost that you get less concurrency. The experimental concurrency option might also help, in that a write operation doesn't need an exclusive lock for the whole database, but does introduce more points where a transaction might fail and need to be rolled back. -- Richard DamonTh ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locking problems
>There are two possibilities: >1. Transactions do work, but I'm misusing them and must learn how to >be more careful. In this case, I will update documentation to >properly explain their use to others. >2. Transactions don't work, at least not for my task. In this case, >I will do my best to investigate the problem and suggest a >correction. >Either way, the right thing for me to do is continue digging in. >Going from BEGIN to BEGIN IMMEDIATE indeed fixed the test program I >wrote, but my application is still broken, and I don't know what's >different about it. I'm working on instrumenting the fcntl() calls >to log the sequence of operations. Transactions do work provided you use them properly. The rules you need to obey are: Make sure you are doing nothing foolish like using "shared cache". Doing so changes how things work considerably and open a completely different kettle of fish. Make sure that you are using the default SERIALIZED or FULLMUTEX thread management on all connections whether you think you know better or do not need it or whatever. A few microseconds of CPU might save you many MONTHS of trying to locate a defect in your code. If you think that you need to use either "shared cache" or some threading mode other than the default serialized, then do that ONLY AFTER your application is working using the defaults. Do not fall into the premature optimization trap. Place all read and write statements which require a consistent database context in the same transaction. Context may change BETWEEN transactions but will not change WITHIN a transaction. That is, if you are executing multiple SELECT statements which require a consistent view of the database then they should all be located in the same read transaction. If you are selecting data and then performing updates based on the retrieved data, those operations need to occur within the same write transaction. SQLite3 does not have cursors and does not do UPDATE ... WHERE CURRENT OF CURSOR nor does it do SELECT ... FOR UPDATE OF ... which means that processing an update may mutate what you are reading and therefore you should complete your read before performing the update. IF THE TRANSACTION WILL OR MAY WRITE TO THE DATABASE AT SOME POINT BEFORE COMMIT/ROLLBACK then start the transaction with BEGIN IMMEDIATE. IF THE TRANSACTION WILL ONLY READ AND CANNOT EVER WRITE TO THE DATABASE BEFORE COMMIT/ROLLBACK then start the transaction with BEGIN DEFERRED (this is the default for a naked BEGIN). Transactions are an attribute of the DATABASE CONNECTION and not of a STATEMENT (a STATEMENT is an attribute of a CONNECTION). Multiple STATEMENTs may execute concurrently sharing a single CONNECTION (and hence transaction) context. 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. WAL mode permits READ and WRITE to proceed independantly provided that the WRITE transaction must only be performed against the "current" (head) database context and will fail if the context is not the head context at the time the attempt is made to obtain the WRITE lock (and since there is only one "current" head, there can only be one WRITE in progress at a time). An attempt to UPGRADE a READ transaction to a WRITE transaction will DEADLOCK (and immediately return an error) if another WRITE TRANSACTION is pending. (Hence always signal your intent when commencing a transaction by using the appropriate BEGIN syntax in order to avoid this state of affairs). WAL journalling only works where all database user processes are local to each other (ie, between connections all originating from the same computer -- where "same computer" means all processes have access to the same shared memory, so that you may determine whether a SYSPLEX or NUMA architecture constitutes "same computer" or not, by whether or not they can all access the same "shared memory" region). File locking only works reliably on local filesystems. It is unreliable for *ALL* remote filesystems, even if the target "remote" filesystem is local. A local filesystem is defined as one wherein the filesystem code resides and is executed "on the same computer" as the application. See the requirement 2 above to determine the meaning of "same computer". The cable between the "computer running the filesystem code" and the "block storage device" may have a length varying from inches to many thousands of miles. The command language the "filesystem" uses to access the underlying block storage is irrelevant (it may be ATA, SATA, SCSI, SAS, iSCSI, SAC (SCSI over Avian Carriers) or what ever your heart may desire). --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.
Re: [sqlite] Database locking problems
James K. Lowden wrote: > On Sat, 19 Jan 2019 08:07:42 -0500 Richard Hipp wrote: >> The busy timeout is not working because you start out your transaction >> using a read operation - the first SELECT statement - which gets a read >> lock. Later when you go to COMMIT, this has to elevate to a write >> lock. But SQLite sees that some other process has already updated the >> database since you started your read. > > Another solution is to rely on atomicity in SQL: > > 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. Thank you for the suggestion, but I don't believe this is an option in my application. 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. There are two possibilities: 1. Transactions do work, but I'm misusing them and must learn how to be more careful. In this case, I will update documentation to properly explain their use to others. 2. Transactions don't work, at least not for my task. In this case, I will do my best to investigate the problem and suggest a correction. Either way, the right thing for me to do is continue digging in. Going from BEGIN to BEGIN IMMEDIATE indeed fixed the test program I wrote, but my application is still broken, and I don't know what's different about it. I'm working on instrumenting the fcntl() calls to log the sequence of operations. --- CONFIDENTIALITY NOTICE: This email and any attachments are for the sole use of the intended recipient and may contain material that is proprietary, confidential, privileged or otherwise legally protected or restricted under applicable government laws. Any review, disclosure, distributing or other use without expressed permission of the sender is strictly prohibited. If you are not the intended recipient, please contact the sender and delete all copies without reading, printing, or saving.. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] JOINning two tables provides no data
Darn ti! Thanks. On Saturday, January 19, 2019 07:20 AM, J. King wrote, >On January 18, 2019 12:54:32 PM EST, Jose Isaias Cabrera >wrote: >> >>sqlite> select a.a, a.b, a.c, b.d, b.e as q from a join b on a.a=b.a >>where a.a = 1; >>sqlite> >> >>This provides no result. I expected to get, >> >>1|2|3|| > > >You appear to seek the result of a LEFT JOIN. Yep, that's it. Thanks. josé ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Variable Declaration
On Sat, 19 Jan 2019 12:01:34 -0700 "Keith Medcalf" wrote: > Microsoft took the OS/2 3.0 Beta 2 code and generated their OS/2 New > Technology. The "New Technology" part was considered to be a bit to > long, so Microsoft shortened it to NT, replaced the Presentation > Manager with with Windows layer, added the "Windows Subsystem" and > released it as Windows NT. The whole message was fun to read; I just wanted to correct this part. You'll remember Microsoft hired David Cutler from DEC to create Windows NT. AFAIK there was no OS/2 technology per se in NT. Some of the Windows API was shared across all three -- DOS/Windows, OS/2, and NT -- but the underlying OS functionality -- scheduling, memory model, I/O -- was utterly different. Of the 3, NT was the only one with demand-paged virtual memory and isolated per-process virtual addressing on the i386 platform. Notably, early versions of NT put the video driver in userspace, not in the kernel. That made processing more reliable and games slower. History has since shown that Microsoft, when faced with a choice between correct and fast, always chose fast. --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 Sat, 19 Jan 2019 08:07:42 -0500 Richard Hipp wrote: > The busy timeout is not working because you start out your transaction > using a read operation - the first SELECT statement - which gets a > read lock. Later when you go to COMMIT, this has to elevate to a > write lock. But SQLite sees that some other process has already > updated the database since you started your read. Another solution is to rely on atomicity in SQL: 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. --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Ideas or Guide on SQLite Search Engine for a relational database?
Hello Scoott ! I have done some work with a prototyping tool for data applications using sqlite https://github.com/mingodad/db-api-server and use for navigate several opendata sources like (sorry it is in Spainsh) https://meimporta.eu look here https://borme.w.meimporta.eu/bormeMalaga-lm.app . With this tool we can create links that make easy navigate/filter/search non trivial amounts of relational data. I used this repository https://github.com/mingodad/db-api-server for a talk about prototyping database applications, I hope it can be helpful for you or other people with similar needs. Cheers ! On 20/01/2019 18:02, Andy Goth wrote: You can try reading the Fossil source code to see how it handles full-text searching across multiple tables. https://fossil-scm.org/index.html/artifact?fn=src/search.c=trunk On Sat, Jan 19, 2019, 06:10 Scott I apologize, I sent this from a different email than I registered accidentally. Hi Everyone! The Ask and what I’ve done: I'm building a research database with an embedded SQLite DB using Intellij for development. I've created the data entry point for the users; however, I'm looking for anything anyone might want to share or suggest as a code, link, or design, whatever it may be, on developing a search engine for a relational database. I’ve been reading through tutorials on SQLiteTutorials.net to learn a little more about indexing and searching. I’ve come across the FTS5 (“Full Text Search”) tutorial, but I’m having difficulty finding anything that may assist with a multi-table search. I guess it would nice to have an example to follow. I wouldn’t think this would be the first-time for this, but I’m a little naïve too. I'm not asking for someone to solve the problem but just provide a direction that may save a lot of research time. I don’t know if my images will post or be available through this email, but I've provided some images below of the GUI and the SQLite DB which has 8 tables all linking back to the primary table called "Source". This is a new task for me and my development skills in Java. The Goal: This Research DB users want to be able to search the database either by Source, Topic, Question, Comments, Quotes, Authors, which most are a one to many with one a many to many relationship. A single source, for example a book entered in the database, could be linked to multiple topics, questions, Comments, Quotes, etc. So the users may want to search each of these separately on a particular subject, or possibly all the tables for a particular subject. Topic will help the mostly, but a topic may be addressed in a comment or quote, etc, that may not be associated directly with a particular topic. So there is a need to search a number of fields over 8 tables. My thoughts are that the search criteria should probably return into a table list, then when the user selects a particular row, this data would populate into corresponding fields for readability similar to the "Entry" tab (see below). The only thing I suspect may make this easier than expected is that no matter whether the user searches by Topic, or Question, or Comment, etc, the search will always join all the tables and return all the fields for anything linked back to a source. However, the source will be associated to many of each of those. Thanks, Scott ___ 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] SQLite 3.24.0 Solaris 9 build failure
Which problem did you experience on SPARC? It is a nearly impossible process. Can not be done unless you have a very specific class of hardware. Really? SQLite3 builds quite happily on the various SPARC S8 ... Can we stop talking about historical hardware? I have Apache httpd running neatly on new shiney Oracle M-class hardware. The point is Solaris 11.4 and Oracle Studio. Nothing else. Dennis Clarke ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Ideas or Guide on SQLite Search Engine for a relational database?
You can try reading the Fossil source code to see how it handles full-text searching across multiple tables. https://fossil-scm.org/index.html/artifact?fn=src/search.c=trunk On Sat, Jan 19, 2019, 06:10 Scott > I apologize, I sent this from a different email than I registered > accidentally. > Hi Everyone! > The Ask and what I’ve done: > I'm building a research database with an embedded SQLite DB using Intellij > for development. I've created the data entry point for the users; however, > I'm looking for anything anyone might want to share or suggest as a code, > link, or design, whatever it may be, on developing a search engine for a > relational database. I’ve been reading through tutorials on > SQLiteTutorials.net to learn a little more about indexing and searching. > I’ve come across the FTS5 (“Full Text Search”) tutorial, but I’m having > difficulty finding anything that may assist with a multi-table search. I > guess it would nice to have an example to follow. I wouldn’t think this > would be the first-time for this, but I’m a little naïve too. > > I'm not asking for someone to solve the problem but just provide a > direction that may save a lot of research time. I don’t know if my images > will post or be available through this email, but I've provided some images > below of the GUI and the SQLite DB which has 8 tables all linking back to > the primary table called "Source". This is a new task for me and my > development skills in Java. > > The Goal: > This Research DB users want to be able to search the database either by > Source, Topic, Question, Comments, Quotes, Authors, which most are a one to > many with one a many to many relationship. A single source, for example a > book entered in the database, could be linked to multiple topics, > questions, Comments, Quotes, etc. So the users may want to search each of > these separately on a particular subject, or possibly all the tables for a > particular subject. Topic will help the mostly, but a topic may be > addressed in a comment or quote, etc, that may not be associated directly > with a particular topic. So there is a need to search a number of fields > over 8 tables. My thoughts are that the search criteria should probably > return into a table list, then when the user selects a particular row, this > data would populate into corresponding fields for readability similar to > the "Entry" tab (see below). > > The only thing I suspect may make this easier than expected is that no > matter whether the user searches by Topic, or Question, or Comment, etc, > the search will always join all the tables and return all the fields for > anything linked back to a source. However, the source will be associated to > many of each of those. > > Thanks, > > Scott > > > > > > ___ > 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] SQLite 3.24.0 Solaris 9 build failure
On 20/01/2019 15:03, Dennis Clarke wrote: On 1/19/19 10:55 AM, Igor Korot wrote: Dennis, On Sat, Jan 19, 2019 at 9:31 PM Dennis Clarke wrote: And SPARC version is still available for download... Let us know when you get that running. Install of x86 went very smooth. The x86_64 process is trivial. And I was able to compile fairly recent SQLite with Oracle Studio 12.6 with just couple of warnings... Which problem did you experience on SPARC? It is a nearly impossible process. Can not be done unless you have a very specific class of hardware. Really? SQLite3 builds quite happily on the various SPARC S8 systems we keep alive, because, customers, and on the S8 Zone on the T1000 running S10. (I have the zone (and an S9 zone) because keeping old hardware alive is a worry.) (Not using Studio 12.6, of course :-) ) Cheers, GaryB-) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 3.24.0 Solaris 9 build failure
On 1/20/19 12:50 AM, Gary R. Schmidt wrote: On 20/01/2019 15:03, Dennis Clarke wrote: On 1/19/19 10:55 AM, Igor Korot wrote: Dennis, On Sat, Jan 19, 2019 at 9:31 PM Dennis Clarke wrote: And SPARC version is still available for download... Let us know when you get that running. Install of x86 went very smooth. The x86_64 process is trivial. And I was able to compile fairly recent SQLite with Oracle Studio 12.6 with just couple of warnings... Which problem did you experience on SPARC? It is a nearly impossible process. Can not be done unless you have a very specific class of hardware. Really? We are talking about Solaris 11.4 and not ye old Solaris 10 or other historical artifacts. Dennis ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
