Re: [sqlite] Concurrency in SQLite
> It looks to me that several users are (a) in a uniprocess environment, > and (b) inventing their own SQLite db access synchronization code. An > SQLite fine grained lock manager for threads in a single process would > address these same issues, with better concurrency as well. > I'm all for finer grained lock management as long as it doesn't mean the loss of some important aspects of SQLite that I particularly like. For example, I remember back when I was doing something in MS SQL, the only way to get speeds that I could deal with, was to use pure transactions, which was fine. But I found that a transaction had to complete before I could get result sets back. While I can understand this in a finer grained lock type of SQL, it became somewhat a task to work around the fact that if any SQL errored in the transaction, you had no real way of figuring out which line errored in the transaction. Now, this was many years ago, and my understanding of the API might not have been up to par, but I recall working with a DBA that said to me, "Just make sure all your SQL statements are perfect before throwing them into a transaction.". This made me feel like I had to work around the database, where really I wanted the database to work around my code. The transaction was not a set number of statements, basically what was being done was there was a collection of SQL statements over 200ms, then a commit, so the transaction was actually very dynamic. And from what I can recall, I had a terrible time figuring out which SQL statement in the transaction actually caused the error, not to mention no result sets were returned because of that one error. In the end I had to ditch this method of doing things and port a lot of code into tedious compiled SQL functions, which really made me feel like I had to work around the database. Now this was many years ago, but that was one miserable SQL experience that I hope never to repeat. Jay Macaulay - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Concurrency in SQLite
At 16:21 24/11/2003, Doug Currie wrote: It looks to me that several users are (a) in a uniprocess environment, and (b) inventing their own SQLite db access synchronization code. An SQLite fine grained lock manager for threads in a single process would address these same issues, with better concurrency as well. Are others in the position of having to create their own SQLite db access synchronization code? Yes, we have to do that. It's made a bit harder by the fact that the DB file could be accessed by other software so we have to handle busy states as well as trying to stop them with our own synchronisation code. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] Concurrency in SQLite
Hi - I wanted to answer some of the survey questions about concurrency in SQLite. Finer-grained locking would not help our particular application. We have a multi-threaded application where there are many reader threads and a single writer thread. It's not a big deal for us if the readers read uncommitted data. A pragma to allow 'dirty' reads in a read-only connection would really help concurrency in our case. My favorite features of SQLite are the small size, code portability, small memory footprint, and that it is easily embedded. (Plus the source code is some of the best I've ever seen from a commenting and clarity standpoint.) With some minor tuning of the DB parameters, our schema and queries, we've been able to make it fast enough for our needs, so I'd be sad to see changes in database locking make it harder to embed the database or make it less portable. As for the questions: > * What SQL are you running that takes more than a fraction of a second to complete? We have a number of queries that take a few seconds to run. They tend to involve tables with 100,000 or more rows, where the query criteria matches about 1000 of them, sorts and returns, say, the most recent 50 of those 1000 rows, via an SQL LIMIT clause. As a non-sequiter, we experimented with a number of btree page sizes and we found 4k to be the fastest for our particular application. It seems that modern OS's can read 4k in about the same amount of time that they can read 1k from disk. > * Are you holding transactions open for an extended period of time? Why? We do hold transactions open for an extended period of time - our application acquires data from disparate sources, many of them over the net and stores metadata in the database. To make inserting a large number of rows faster, we wrap them in a transaction. We haven't experimented yet with committing periodically to make the application more live while it is gathering data. > * How many processes do you have trying to access the database at once? One process with multiple threads. We have one writer thread and any number of reader threads. > * How do you currently handle SQLITE_BUSY replies? Do you use the > sqlite_busy_handler() or sqlite_busy_timeout() APIs? We use the sqlite_busy_handler() APIs. > * How large are your databases? In the hundreds of megabytes. > * Do you ever put database files on a shared filesystem? No. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Concurrency in SQLite
It looks to me that several users are (a) in a uniprocess environment, and (b) inventing their own SQLite db access synchronization code. An SQLite fine grained lock manager for threads in a single process would address these same issues, with better concurrency as well. Jay said: > All database access is handled in a single thread which > synchronizing DB access so that only one SQL command can happen at a > time. Benjamin said: > The most important change for me is one that I introduced into my > copy: Blocking locks. These are important because there is only an > instant between the last transaction closing and the next beginning. > In this scenareo the poll-based locking mechnism currently used by > sqlite is just not lucky enough to try at that instant. Only > blocking locks with their operating-system support are sufficient to > ensure that the readers get in at all. I also have a situation where > I have multiple writers on the database that can run into the same > problem. Allan said: > We don't ever hit the busy timeout handler. We can't since we > synchronize in our data access layer. Doug said: > .. due to the coarse grained locking, I ended up serializing access > to SQLite in my own uni-process multi-thread bottleneck. Are others in the position of having to create their own SQLite db access synchronization code? e - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Concurrency in SQLite
G'day, "D. Richard Hipp" <[EMAIL PROTECTED]> 24/11/2003 03:22 AM To: [EMAIL PROTECTED] cc: Subject:[sqlite] Concurrency in SQLite > Please, give me some examples of the kinds of things you are > doing which could benefit from improved concurrency. > * Are you holding transactions open for an extended period > of time? Why? This is my situation. I have a large amount of data flowing into a database which shows historical records (maybe a couple of thousand inserts per second). Queries are much rarer. To keep inserts efficient I hold transactions open for one second at a time. The most important change for me is one that I introduced into my copy: Blocking locks. These are important because there is only an instant between the last transaction closing and the next beginning. In this scenareo the poll-based locking mechnism currently used by sqlite is just not lucky enough to try at that instant. Only blocking locks with their operating-system support are sufficient to ensure that the readers get in at all. I also have a situation where I have multiple writers on the database that can run into the same problem. If you could ensure that readers could still read the untouched version of database blocks while a writer is working on "dirty" version of the same blocks I wouldn't have any problems as far as reading is going. Writing would still be problem, though. It's not the amount of concurrency that's a problem for me. One at a time is fine. It's just the ability to schedule the accesses that do happen very tightly together that I care about. >* How many processes do you have trying to access the database > at once? Usually at most two or three. > * How do you currently handle SQLITE_BUSY replies? Do you use > the sqlite_busy_handler() or sqlite_busy_timeout() APIs? The problem with both of these apis is that they use timers beetween attempts. If I could put a blocking lock on the database in the busy handler, allow the database access to occur, then get called back to unlock the database, it would be almost as good as the current blocking lock situation. > * How large are your databases? Usually less than a gig :) > * Do you ever put database files on a shared filesystem? No. Benjamin. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Concurrency in SQLite
Hello, On 11/23/03 6:22 PM, D. Richard Hipp wrote: Lots of people seem to think that better concurrency in SQLite would be useful. But I am having trouble understanding why. I have SQLite or Server systems. I would like not to make that choice - soft option I mean. As you can read I'm useing "would". If the change means lost of too much - forget it. I'll stay with closeing transactions. Please, give me some examples of the kinds of things you are doing which could benefit from improved concurrency. * What SQL are you running that takes more than a fraction of a second to complete? In simple way - large, compiled, selects. * Are you holding transactions open for an extended period of time? Why? All the process operates in transaction - it can be changed... I'm going to close transactions after the cnage to unlock the file. * How many processes do you have trying to access the database at once? If shared file - up to 5. * How do you currently handle SQLITE_BUSY replies? Do you use the sqlite_busy_handler() or sqlite_busy_timeout() APIs? None, but handler is my choice. * How large are your databases? If shared file - < 1MB. * Do you ever put database files on a shared filesystem? I need it, as I wrote. Right now app works as desktop. The better I understand the problems, the better job I will be able to do in resolving them. Thanks for you responses. > Thank you for your time. -- Regards, Michal Zaborowski (TeXXaS) http://sqlite4delphi.sourceforge.net/ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Concurrency in SQLite
> Please, give me some examples of the kinds of things you are > doing which could benefit from improved concurrency. One typical application for me is data recording for regulatory compliance (FDA 21 CFR 11). Instruments are polled or issue data frequently, say once a second. Data from several of these instruments are collected during the sample period, timestamped and digitally signed or hashed, and all of this is updated in the database in a single transaction. While this is going on, a few other things may be taking place as well: periodic data selects for user display refresh, data updates for user configurations or calibrations, and ad hoc query by regulators or users. >* What SQL are you running that takes more than a fraction > of a second to complete? Ad hoc queries can be troublesome since the database can get really big after several months. It's very important that the instrument data update is (and to only a slightly lesser degree display refresh and config updates are) not blocked too long. >* Are you holding transactions open for an extended period > of time? Why? No. >* How many processes do you have trying to access the database > at once? One would be fine. A separate pipe to an ODBC (read only) process would be nice, though, for ad hoc reports. Presently I manage this pipe within my process because... >* How do you currently handle SQLITE_BUSY replies? Do you use > the sqlite_busy_handler() or sqlite_busy_timeout() APIs? Various strategies have been tried, but in the end, and due to the coarse grained locking, I ended up serializing access to SQLite in my own uni-process multi-thread bottleneck. >* How large are your databases? They can grow to multiple G bytes; typically hundreds of M bytes. >* Do you ever put database files on a shared filesystem? No. > The better I understand the problems, the better job I will > be able to do in resolving them. Thanks for you responses. Thank you. As you can see from my description, I can make SQLite work, but there are risks associated with ad hoc queries. An ideal architecture for me would be "readers never block writers." MVCC does this, but I suspect it is too big a change for SQLite. Finer grained locking reduces the risk, but not to zero. With some kinds of fine grained locking I can perhaps devise schemas that control the risks. e - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] Concurrency in SQLite
My thoughts are that most programmers over use and abuse threading and I love the simplicity of your database as it is. Can't tell you how many C projects that have almost failed because of threading and memory issues (til I took over them and became Mr. Fix man). First thing I did was pull out all the over complexity. I avoid threading as best I can. For some reason everyone equates being smart with being complex, I think it is just the opposite. Being able to solve the problem optimally with as much simplicity as possible. We are putting finishing touches on a client server application for ship late this year. This application was originally slated to use SQL Server, Oracle, or DB2, but just for grins we tested out Sqlite as the server backend. It worked great. We are so pleased with it that we are not going to ship with support for a large scale RDBMS. Because of our tests we found that realistically we don't need huge scalability and with hardware continuing to get faster, Sqlite continues to get more scalable. We are doing thread synchronization at the data access layer which is well above the database. The system is just as fast as using the large scale except MUCH easier to maintain with Sqlite. And yes, it is just as fast if we don't thread sync at the data access layer. To answer your questions. 1* Don't know, Sqlite is so fast, not concerned with sub second queries! They all seem to run that fast. 2* Minimal use of transactions through design and business logic code, security seems to be the only transaction candidate. 3* One, it is either an application server, web server. These apps have multiple thread handlers but it is all synchronized at the data access layer so only one request can get to the db at a time. 4* We don't ever hit the busy timeout handler. We can't since we synchronize in our data access layer. 5* from minimal 500k - 50 GB (the 50 GB is tested, not in production) 6* All the time. In fact, we found that you get extremely good performance on USB 2.0 connected drives or RAID SCSI systems. Thanks, Allan -Original Message- From: D. Richard Hipp [mailto:[EMAIL PROTECTED] Sent: Sunday, November 23, 2003 11:22 AM To: [EMAIL PROTECTED] Subject: [sqlite] Concurrency in SQLite Lots of people seem to think that better concurrency in SQLite would be useful. But I am having trouble understanding why. Please, give me some examples of the kinds of things you are doing which could benefit from improved concurrency. * What SQL are you running that takes more than a fraction of a second to complete? * Are you holding transactions open for an extended period of time? Why? * How many processes do you have trying to access the database at once? * How do you currently handle SQLITE_BUSY replies? Do you use the sqlite_busy_handler() or sqlite_busy_timeout() APIs? * How large are your databases? * Do you ever put database files on a shared filesystem? The better I understand the problems, the better job I will be able to do in resolving them. Thanks for you responses. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
