Re: [sqlite] Any way to debug which query/connection is holding a share lock?
On 2014/06/14 02:00, Sohail Somani wrote: I think I'm pretty certain that my program *is* the culprit. I'd like to add the tracing to SQLite itself. Which functions do you suggest? I know you mentioned opening/closing so that would be sqlite3_open* and sqlite3_finalize? Just to clarify, this is all happening within one process: Write thread: write Read thread: read something Write thread: write Write thread: write Read thread: read something <-- this is a "special" query but I can't see anything special about it except that it uses FTS Write thread: hang until app exits Also, I tried using sqlite3_config to add logging but that didn't seem to do much. Time for some printfs... Hi again Sohail, The things for which you should log with any returned object/memory pointers are: (Jotting this down from memory as I am not at my desk, so spellings might be off, but you'd know the things I refer to) sqlite3_openXX() // because I have in the past accidentally opened a connection twice, which is perfectly ok in sqlite terms, but may not be the intention of your code. sqlite3_close()// To match to the opens. sqlite3_prepareXX() // whichever you are using sqlite3_reset() sqlite3_unprepare() // Yes I forgot the name for this, _finalize maybe? Whatever kills/frees the prepared statement. sqlite3_execute() // In case you bypass the whole prepare-step-free business by using some direct SQL Also please log in terms of the TCL or sending any SQL statements like BEGIN or SAVEPOINT or any of their aliases and other transaction-flow/ending functions. That should supply you with a large enough view to trap any out-of-place or non-closed/non-finalized stuff. Anyone else reading this with an experience like the OP's that was solved, kindly add to this list, or any other thoughts that may help. Cheers! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Any way to debug which query/connection is holding a share lock?
On 13/06/2014 4:38 PM, RSmith wrote: If none of these finds you a culprit, it means your program is the only thing left to blame. Here we need to start with logging every time you create a connection and start a query or creating a prepared statement. Log the handles or object IDs you get back from any call too. Compare these to the logs when you reset or close the statements and when you free the statements and the DB connection. Go through these with a fine-tooth comb, make sure every resource made is linked to a release in the correct order. I think I'm pretty certain that my program *is* the culprit. I'd like to add the tracing to SQLite itself. Which functions do you suggest? I know you mentioned opening/closing so that would be sqlite3_open* and sqlite3_finalize? Just to clarify, this is all happening within one process: Write thread: write Read thread: read something Write thread: write Write thread: write Read thread: read something <-- this is a "special" query but I can't see anything special about it except that it uses FTS Write thread: hang until app exits Also, I tried using sqlite3_config to add logging but that didn't seem to do much. Time for some printfs... Sohail ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Any way to debug which query/connection is holding a share lock?
On 2014/06/13 17:38, Sohail Somani wrote: Thanks for your response. I had been trying to avoid the "writing little bits" over time to avoid an inconsistent state in the case of a crash or user exit, but the inconsistency is not that big of a deal as the data integrity is still there and eventually, it becomes consistent. So this solution is not the end of the world. I actually don't have a problem with readers or writers waiting, but the problem that I am having is something holds on to the read lock well beyond where it should. I cannot figure out who (I suspect it's a third-party framework) and what I'd like to do is figure out what piece of code is refusing to relinquish the lock regardless of the eventual strategy I take. Even if I ended up doing everything in the same thread, with piecemeal updates, this means that there is still something, somewhere doing the wrong thing and I think that will probably end up biting me in an unwelcome place. So rather than actually solve the problem, I'm looking to find the source of the problem. Does that make sense? For what it's worth, I did rewire the code to do everything in one thread, with piecemeal writes and it works fine, but is not a good solution as far as I am concerned because it is just papering over the actual problem. Apologies, did not get this from your original post, and you are right, piecemeal is not the best way, and it seems is not necessary if you do actually find the culprit that holds the read-lock way past necessary. As for finding the culprit, are there any apps other than your own that uses the Database? (You seem to suggest there might be if I am interpreting it correctly). If there are, then simply stop them and test the system, start them one by one until the error happens. You did not mention the operating system so I cannot suggest much there other to say that some OSes might add external locks on a file, such as when the file is in a protected area in Windows UAC, or in a root area and you are not a superuser or administrator and one of the threads reading/writing is not "cleared" by the UAC or whatever other system marshal, or maybe a virus scanner is trying to assess the file after it changes, making sure no naughty stuff were injected - some of them hold the read lock a while because they slowly chew through the file to avoid hogging system resources - very clever, but a pain to wait for. If none of these finds you a culprit, it means your program is the only thing left to blame. Here we need to start with logging every time you create a connection and start a query or creating a prepared statement. Log the handles or object IDs you get back from any call too. Compare these to the logs when you reset or close the statements and when you free the statements and the DB connection. Go through these with a fine-tooth comb, make sure every resource made is linked to a release in the correct order. If it still eludes you, try to use Valgrind or something you are familiar with - make sure the compiler errors are strict or complete or throwing exceptions on all errors (or whatever that sort of thing is called in your development platform). One thing is certain - It isn't magic, there is a real reason for the lock and it must be something traceable. "Bad Programmers add corrective code. Good programmers remove defective code." ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Any way to debug which query/connection is holding a share lock?
On 13/06/2014 11:04 AM, RSmith wrote: On 2014/06/13 15:02, Sohail Somani wrote: My application's architecture looks roughly like this: - A main thread which does only database reads - A dedicated database write thread I needed to create this because while reads are fast, there are occasions where writes take a significant portion of time and that can cause the main thread to lock up, which results in a sub-par user experience. Let me pause right here. It doesn't matter which thread is writing to the DB in which Journal mode, the lock is because you cannot change the state of Data in the DB "while other things are reading it". So whether it is the same thread or another thread doesn't matter, the moment you start writing to a DB, the readers have to wait. There are Pragma settings able to change this behaviour, if you use proper journal mode and cache-sharing settings in combination with allowing uncomitted reads, BUT before we even point you to that literature, let me ask whether it actually matters? In other words, let's say you have a read operation that is scanning through the table, would it matter if some of the records are more up-to-date than others? If this doesn't matter then you might use the said Pragmas to achieve it. If it /DOES/ matter, then you need to rethink the update concurrency strategy - such as writing little bits over time in stead of big chunks etc. (This is a recurring theme on here, so lots of people here, other than me, are quite knowledgeable on that subject, but a lot more detail about your strategy or problem is needed). As to the literature on the said Pragmas, kindly see these links: http://www.sqlite.org/pragma.html#pragma_read_uncommitted http://www.sqlite.org/isolation.html http://www.sqlite.org/sharedcache.html Have a great day! Thanks for your response. I had been trying to avoid the "writing little bits" over time to avoid an inconsistent state in the case of a crash or user exit, but the inconsistency is not that big of a deal as the data integrity is still there and eventually, it becomes consistent. So this solution is not the end of the world. I actually don't have a problem with readers or writers waiting, but the problem that I am having is something holds on to the read lock well beyond where it should. I cannot figure out who (I suspect it's a third-party framework) and what I'd like to do is figure out what piece of code is refusing to relinquish the lock regardless of the eventual strategy I take. Even if I ended up doing everything in the same thread, with piecemeal updates, this means that there is still something, somewhere doing the wrong thing and I think that will probably end up biting me in an unwelcome place. So rather than actually solve the problem, I'm looking to find the source of the problem. Does that make sense? For what it's worth, I did rewire the code to do everything in one thread, with piecemeal writes and it works fine, but is not a good solution as far as I am concerned because it is just papering over the actual problem. Sohail ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Any way to debug which query/connection is holding a share lock?
On 2014/06/13 15:02, Sohail Somani wrote: My application's architecture looks roughly like this: - A main thread which does only database reads - A dedicated database write thread I needed to create this because while reads are fast, there are occasions where writes take a significant portion of time and that can cause the main thread to lock up, which results in a sub-par user experience. Let me pause right here. It doesn't matter which thread is writing to the DB in which Journal mode, the lock is because you cannot change the state of Data in the DB "while other things are reading it". So whether it is the same thread or another thread doesn't matter, the moment you start writing to a DB, the readers have to wait. There are Pragma settings able to change this behaviour, if you use proper journal mode and cache-sharing settings in combination with allowing uncomitted reads, BUT before we even point you to that literature, let me ask whether it actually matters? In other words, let's say you have a read operation that is scanning through the table, would it matter if some of the records are more up-to-date than others? If this doesn't matter then you might use the said Pragmas to achieve it. If it /DOES/ matter, then you need to rethink the update concurrency strategy - such as writing little bits over time in stead of big chunks etc. (This is a recurring theme on here, so lots of people here, other than me, are quite knowledgeable on that subject, but a lot more detail about your strategy or problem is needed). As to the literature on the said Pragmas, kindly see these links: http://www.sqlite.org/pragma.html#pragma_read_uncommitted http://www.sqlite.org/isolation.html http://www.sqlite.org/sharedcache.html Have a great day! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Any way to debug which query/connection is holding a share lock?
Hi, My application's architecture looks roughly like this: - A main thread which does only database reads - A dedicated database write thread I needed to create this because while reads are fast, there are occasions where writes take a significant portion of time and that can cause the main thread to lock up, which results in a sub-par user experience. The problem I am having is that somewhere in the main thread, something is grabbing a shared lock on the SQLite DB and not letting go. the effect of this is that the dedicated write thread ends up waiting until the application exits(!) in order to do some writes that occur after this. I've tried debugging manually, disabling various features but I haven't been able to pinpoint which bit actually grabs the lock. Are there any debugging strategies I can employ to get to the bottom of it? My current journal_mode is DELETE but WAL has similar problems, in that the database writes are not seen, presumably because some reader has an open transaction somewhere. Thanks for your help. Sohail ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
