[sqlite] Re[sqlite] garding software of SQlite2.1
Hi all, I need small help regarding software .Acutually my application was developed in SQLite2.1 by some body now i need SQLite2.1 version.Actually I found SQLite3 version but by using this software i am unable to run my application.please let me know from which site i can get SQLite2.1 version software. thank for ur help in advance. vijaya -- View this message in context: http://www.nabble.com/Regarding-software-of-SQlite2.1-tf4695062.html#a13420537 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
Trevor Talbot wrote: On 10/25/07, Richard Klein <[EMAIL PROTECTED]> wrote: Trevor Talbot wrote: The thing is, SQLite's synchronization mechanism is simpler than most full-featured SQL databases. In all cases, the point is that if you are attempting to do simultaneous writes from two connections, each connection must be prepared to receive an error, rollback the transaction, and try again. Personally, I don't see anything terribly > complicated about that. It's not complicated at all. It's just an aspect of database programming that I was unaware of, until now (I'm a database newbie). You're also implementing SQLite's locking system for your own platform, so you need to be concerned with these details anyway. You're not just blindly implementing it, you're also verifying your understanding of the goals and taking the time to question SQLite's behavior to make sure everything is correct. I think that's absolutely wonderful. Thank you for understanding that! :-) - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: Some Questions Regarding Access To a SQLite Database By More Than One Process
> -Original Message- > From: Ken [mailto:[EMAIL PROTECTED] > Sent: Wednesday, October 24, 2007 1:22 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Re: Some Questions Regarding Access To a SQLite > Database By More Than One Process > > If you are using seperate processes then a mutex will not help since it is > local to a process. A semaphore could be used however. Many (maybe even most) platforms support mutexes which work across multiple processes. (Mutex is, of course, short for "mutual exclusion semaphore"; there's no a priori reason to assume that mutexes are process-local while other semaphores are not.) -- James - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
On 10/25/07, Richard Klein <[EMAIL PROTECTED]> wrote: > Trevor Talbot wrote: > > The thing is, SQLite's synchronization mechanism is simpler than most > > full-featured SQL databases. In all cases, the point is that if you > > are attempting to do simultaneous writes from two connections, each > > connection must be prepared to receive an error, rollback the > > transaction, and try again. Personally, I don't see anything terribly > > complicated about that. > > It's not complicated at all. It's just an aspect of database programming > that I was unaware of, until now (I'm a database newbie). You're also implementing SQLite's locking system for your own platform, so you need to be concerned with these details anyway. You're not just blindly implementing it, you're also verifying your understanding of the goals and taking the time to question SQLite's behavior to make sure everything is correct. I think that's absolutely wonderful. I just wanted to point out to anyone seeing this thread and thinking "eek" that from the perspective of using SQLite one doesn't need to be concerned with these details, because the original implementors, and people like yourself, keep verifying those details do what they're supposed to do. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
I am sure that you are correct, that Sqlite's sync mechanism is not terribly complicated for you and for anyone else who understands the principles, however it does confuse many users as you see from the posts to this forum. Simple to use could become simpler to use. Synchronizing transactions is not intricate, particularly when it is achieved transparently to the application programmer by the Sqlite application support layer. Trevor Talbot wrote: The thing is, SQLite's synchronization mechanism is simpler than most full-featured SQL databases. In all cases, the point is that if you are attempting to do simultaneous writes from two connections, each connection must be prepared to receive an error, rollback the transaction, and try again. Personally, I don't see anything terribly complicated about that. Concerning yourself with the intricate details in order to get the best performance is complicated, sure, but the basic principle isn't. You have to do error checking anyway, the only difference is that it may affect your entire transaction rather than just one statement. That's true for working with SQL databases in general. For those applications don't want parallel access at all, SQLite provides BEGIN EXCLUSIVE. Adding your own mechanisms on top just means another thing for you to test independently, which seems like adding complexity. I do recognize that none of the above necessarily applies to specific environments, where you need to do your own thing anyway. I'm just commenting that in general, SQLite is already simple to use. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
Dan Kennedy wrote: On Wed, 2007-10-24 at 21:38 -0700, Richard Klein wrote: As I was thinking about the locking mechanism in SQLite 3, it occurred to me that the following race condition could occur. Imagine a joint bank account with a balance of $10,000. The wife makes a withdrawal of $1,000 at ATM 'A' (serviced by process A in the bank's mainframe), while at the same time the husband makes a deposit of $1,000 at ATM 'B' (serviced by process B). The steps performed by each process are as follows: Process A - BEGIN TRANSACTION; SELECT balance FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = WHERE accountId = '123-45-6789'; COMMIT; Process B - BEGIN TRANSACTION; SELECT balance FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = WHERE accountId = '123-45-6789'; COMMIT; Both processes open the accounts database, obtain SHARED locks, and proceed at about the same pace. Process A updates her local cache with a new balance of $900, while process B updates his local cache with a new balance of $11,000. Evaluating the UPDATE requires a RESERVED lock on the database file. Since only one process can hold the RESERVED lock, this particular scenario cannot occur. One or other of the updates will fail with SQLITE_BUSY. Dan. I went over the documentation again, and it appears that you are correct: A process wanting to do an UPDATE must first acquire a RESERVED lock. However, I believe there is still a race condition. Assume that process B is the first to reach the UPDATE statement, and so process B gets the RESERVED lock (which he later escalates to PENDING and EXCLUSIVE). Then process A is stuck in a busy wait until process B commits and releases his locks. At this process A acquires the RESERVED lock and does *her* UPDATE, but she does it using the old, now *stale*, value of the account balance ($10,000). That is, she computes the new balance as ($10,000 - $1,000) = $9,000, which is *wrong*. She needs to *re-execute* her SELECT statement to pick up the new balance of $11,000. Or, in general: She needs to start her transaction over from the beginning. - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] it seems sqlite3_prepare_v2 disappeared for me
Rafi Cohen wrote: Hi, I've written an application in C that integrates sqlite-3.3.12. Up to now for months everything was running fast and smooth. Now, I installed sqlite3 and compiled my application on a different computer. The installations were ok, but upon activating my application, it quits with the error: ...symbol lookup error.undefined symbol: sqlite3_prepare_v2. What can be the reason for that? I never had that error on my other computer, both running linux susse 9.3, by the way. It's the first time I see this one. I need help to resolve this problem. Thanks, Rafi. Rafi, You should check the version of the sqlite library you are linking against on the two computers. Write a simple test program that doesn't use sqlite3_prepare_v2 (so it will link), or modify your program to use sqlite3_prepare temporarily. Then print the version returned by a call to sqlite3_libversion(). The prepare_v2 API was added in version 3.3.9 so it will not not be present in earlier versions. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
Trevor Talbot wrote: The thing is, SQLite's synchronization mechanism is simpler than most full-featured SQL databases. In all cases, the point is that if you are attempting to do simultaneous writes from two connections, each connection must be prepared to receive an error, rollback the transaction, and try again. Personally, I don't see anything terribly > complicated about that. It's not complicated at all. It's just an aspect of database programming that I was unaware of, until now (I'm a database newbie). For those applications don't want parallel access at all, SQLite provides BEGIN EXCLUSIVE. That's true. If you begin your transaction with BEGIN EXCLUSIVE, then it cannot be interrupted by another transaction wishing to write to the database, so rollback won't be an issue. - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
Lee Crain wrote: I've wrapped all of my company's SQLite database accesses in my own API layer that encapsulates all of our applications' business rules and forces ALL transactions, no matter how lengthy or trivial, to be atomic by using a MUTEX to avoid the types of scenarios described below. This includes simple database accesses, even reads. I think that's a perfectly acceptable solution, if you can live with the performance hit (and it sounds like you can). - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
The thing is, SQLite's synchronization mechanism is simpler than most full-featured SQL databases. In all cases, the point is that if you are attempting to do simultaneous writes from two connections, each connection must be prepared to receive an error, rollback the transaction, and try again. Personally, I don't see anything terribly complicated about that. Concerning yourself with the intricate details in order to get the best performance is complicated, sure, but the basic principle isn't. You have to do error checking anyway, the only difference is that it may affect your entire transaction rather than just one statement. That's true for working with SQL databases in general. For those applications don't want parallel access at all, SQLite provides BEGIN EXCLUSIVE. Adding your own mechanisms on top just means another thing for you to test independently, which seems like adding complexity. I do recognize that none of the above necessarily applies to specific environments, where you need to do your own thing anyway. I'm just commenting that in general, SQLite is already simple to use. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Aggregation of data
Dear all, my aim is to aggregate some data. To be more specific: I have a table 'mytable' with variables a, b, c, and ctr. The values for variable ctr are all 1 (individual level data in the raw format). If I want to know how many people with the same covariate setting there are, I used the following SQL code in the past: SELECT a, b, c, sum(ctr) as aggregat FROM mytable GROUP by a, b, c; The problem I face now is that 'mytable' contains more records than my older datasets (the new data contain between one and five million records) and also more than three variables (10 INTEGER variables, 1 TEXT variables). I assume there is a better way to perform this aggregation than what I did so far. I'd be very happy if I get some suggestions. One possibility I see is to split the initial raw data into smaller chunks, perform the aggregation for all chunks and aggregate the chunks again. But I don't think this is a very good solution and I hope that someone here on this list can give me some hints. I am using sqlite 3.2.8 on WinXP Pro. I am not an expert on databases, but so far it was not necessary to really learn a lot since sqlite was very easy to use (Main use so far: storing my data in various tables and extract/aggregate/combine what I need from R via the RSqlite package (www.r-project.org). I hope this is the right place to ask this question and hopefully this question has not been asked zillions of times before (I searched a bit around but maybe not in the right places to be successful). Thank you very much in advance, Roland -- Ist Ihr Browser Vista-kompatibel? Jetzt die neuesten Browser-Versionen downloaden: http://www.gmx.net/de/go/browser - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
You make a sound point. From my perspective the Sqlite synchronization mechanisms are a flawed part of an otherwise elegantly simple design, as reading this forum indicates. Synchronization problems are the major item of confusion among users. A more robust and less intricate interface would be a productivity improver. Follow Einstein - "Make it as simple as possible, but no simpler". We use the mutex approach and are rewarded with simpler logic, no ugly busy waits or wasteful pauses, more confidence when deploying applications and no 3AM calls. A sophistication is to use read locks where appropriate. A read (shared) lock can be easily contrived from the Windows sync primitives and exists with pthreads. Threaded programs are here to stay, so the synchronization method would be better built from threads up instead of at process level with threading support overlaid later. Lee Crain wrote: Guys, I read this forum regularly and I've given a lot of thought to all of these conversations about low level logic and trying to squeeze the last ounce of performance out of SQLite. That's not for me. Simplicity equates to robustness and my company needs robustness. And my time is really expensive so I need to not spend too much time tweaking my SQLite based application to get everything to work reliably and efficiently. I've wrapped all of my company's SQLite database accesses in my own API layer that encapsulates all of our applications' business rules and forces ALL transactions, no matter how lengthy or trivial, to be atomic by using a MUTEX to avoid the types of scenarios described below. This includes simple database accesses, even reads. Yes, I know it makes things slower. But, it's solid. And I won't be getting calls at 3:00 a.m. about some mysterious database problem. If my company wants something faster, they need to buy SQL Server. At $5000, it's a bargain. At my salary, I'm not. My 2 cents, Lee Crain _ -Original Message- From: Richard Klein [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 24, 2007 10:39 PM To: sqlite-users@sqlite.org Subject: [sqlite] Race condition -- fixed? As I was thinking about the locking mechanism in SQLite 3, it occurred to me that the following race condition could occur. Imagine a joint bank account with a balance of $10,000. The wife makes a withdrawal of $1,000 at ATM 'A' (serviced by process A in the bank's mainframe), while at the same time the husband makes a deposit of $1,000 at ATM 'B' (serviced by process B). The steps performed by each process are as follows: Process A - BEGIN TRANSACTION; SELECT balance FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = WHERE accountId = '123-45-6789'; COMMIT; Process B - BEGIN TRANSACTION; SELECT balance FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = WHERE accountId = '123-45-6789'; COMMIT; Both processes open the accounts database, obtain SHARED locks, and proceed at about the same pace. Process A updates her local cache with a new balance of $900, while process B updates his local cache with a new balance of $11,000. Now suppose B gets to the COMMIT first. He tries to get a PENDING lock and succeeds. He then tries to promote his PENDING lock to EXCLUSIVE, but gets a SQLITE_BUSY instead, because process A holds a SHARED lock. So, he goes to sleep, hoping that when he awakens the SHARED lock will be gone. Meanwhile, process A reaches her COMMIT, tries to get a PENDING lock, but gets a SQLITE_BUSY instead, because process B already holds a PENDING lock. Process A then releases her SHARED lock (so that process B can be promoted from PENDING to EXCLUSIVE and do his commit), and goes to sleep, hoping that when she wakes up the PENDING lock will be gone. Process B then wakes up, finds the database UNLOCKED, obtains his EXCLUSIVE lock, commits his local cache's balance of $11,000 to the database, releases his lock, and exits. Process A then wakes up, finds the database UNLOCKED, obtains an EXCLUSIVE lock, commits her local cache's balance of $9,000 to the database, releases her lock, and exits. *The database now erroneously shows a balance of $9,000.* The problem is that the moment that process B commits his local cache's balance of $11,000 to the database, he causes process A's local cache to become *stale*, i.e. inconsistent with the database. After scouring the documentation, I came across the following article: http://www.sqlite.org/cvstrac/wiki?p=CorruptionFollowingBusyError which seems to describe the exact scenario I described above. According to this article, SQLite has been fixed so that if a process encounters a SQLITE_BUSY during an explicit trans- action, then the transaction will *automatically* be rolled back, and the app will receive an error code of SQLITE_IOERR *instead of* SQLITE_BUSY. I understand this to mean that whenever
Re: [sqlite] Race condition -- fixed?
A classic solution to that problem is not to perform updates but to insert transactions, The concept of log file systems to give concurrency is worth scrutiny. Richard Klein wrote: As I was thinking about the locking mechanism in SQLite 3, it occurred to me that the following race condition could occur. Imagine a joint bank account with a balance of $10,000. The wife makes a withdrawal of $1,000 at ATM 'A' (serviced by process A in the bank's mainframe), while at the same time the husband makes a deposit of $1,000 at ATM 'B' (serviced by process B). The steps performed by each process are as follows: Process A - BEGIN TRANSACTION; SELECT balance FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = WHERE accountId = '123-45-6789'; COMMIT; Process B - BEGIN TRANSACTION; SELECT balance FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = WHERE accountId = '123-45-6789'; COMMIT; Both processes open the accounts database, obtain SHARED locks, and proceed at about the same pace. Process A updates her local cache with a new balance of $900, while process B updates his local cache with a new balance of $11,000. Now suppose B gets to the COMMIT first. He tries to get a PENDING lock and succeeds. He then tries to promote his PENDING lock to EXCLUSIVE, but gets a SQLITE_BUSY instead, because process A holds a SHARED lock. So, he goes to sleep, hoping that when he awakens the SHARED lock will be gone. Meanwhile, process A reaches her COMMIT, tries to get a PENDING lock, but gets a SQLITE_BUSY instead, because process B already holds a PENDING lock. Process A then releases her SHARED lock (so that process B can be promoted from PENDING to EXCLUSIVE and do his commit), and goes to sleep, hoping that when she wakes up the PENDING lock will be gone. Process B then wakes up, finds the database UNLOCKED, obtains his EXCLUSIVE lock, commits his local cache's balance of $11,000 to the database, releases his lock, and exits. Process A then wakes up, finds the database UNLOCKED, obtains an EXCLUSIVE lock, commits her local cache's balance of $9,000 to the database, releases her lock, and exits. *The database now erroneously shows a balance of $9,000.* The problem is that the moment that process B commits his local cache's balance of $11,000 to the database, he causes process A's local cache to become *stale*, i.e. inconsistent with the database. After scouring the documentation, I came across the following article: http://www.sqlite.org/cvstrac/wiki?p=CorruptionFollowingBusyError which seems to describe the exact scenario I described above. According to this article, SQLite has been fixed so that if a process encounters a SQLITE_BUSY during an explicit trans- action, then the transaction will *automatically* be rolled back, and the app will receive an error code of SQLITE_IOERR *instead of* SQLITE_BUSY. I understand this to mean that whenever coding an explicit transaction, the programmer must always be prepared to receive an SQLITE_IOERR when stepping through any SQL statement, and must deal with this error by going back to the start of the transaction and starting over. - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Race condition -- fixed?
Guys, I read this forum regularly and I've given a lot of thought to all of these conversations about low level logic and trying to squeeze the last ounce of performance out of SQLite. That's not for me. Simplicity equates to robustness and my company needs robustness. And my time is really expensive so I need to not spend too much time tweaking my SQLite based application to get everything to work reliably and efficiently. I've wrapped all of my company's SQLite database accesses in my own API layer that encapsulates all of our applications' business rules and forces ALL transactions, no matter how lengthy or trivial, to be atomic by using a MUTEX to avoid the types of scenarios described below. This includes simple database accesses, even reads. Yes, I know it makes things slower. But, it's solid. And I won't be getting calls at 3:00 a.m. about some mysterious database problem. If my company wants something faster, they need to buy SQL Server. At $5000, it's a bargain. At my salary, I'm not. My 2 cents, Lee Crain _ -Original Message- From: Richard Klein [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 24, 2007 10:39 PM To: sqlite-users@sqlite.org Subject: [sqlite] Race condition -- fixed? As I was thinking about the locking mechanism in SQLite 3, it occurred to me that the following race condition could occur. Imagine a joint bank account with a balance of $10,000. The wife makes a withdrawal of $1,000 at ATM 'A' (serviced by process A in the bank's mainframe), while at the same time the husband makes a deposit of $1,000 at ATM 'B' (serviced by process B). The steps performed by each process are as follows: Process A - BEGIN TRANSACTION; SELECT balance FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = WHERE accountId = '123-45-6789'; COMMIT; Process B - BEGIN TRANSACTION; SELECT balance FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = WHERE accountId = '123-45-6789'; COMMIT; Both processes open the accounts database, obtain SHARED locks, and proceed at about the same pace. Process A updates her local cache with a new balance of $900, while process B updates his local cache with a new balance of $11,000. Now suppose B gets to the COMMIT first. He tries to get a PENDING lock and succeeds. He then tries to promote his PENDING lock to EXCLUSIVE, but gets a SQLITE_BUSY instead, because process A holds a SHARED lock. So, he goes to sleep, hoping that when he awakens the SHARED lock will be gone. Meanwhile, process A reaches her COMMIT, tries to get a PENDING lock, but gets a SQLITE_BUSY instead, because process B already holds a PENDING lock. Process A then releases her SHARED lock (so that process B can be promoted from PENDING to EXCLUSIVE and do his commit), and goes to sleep, hoping that when she wakes up the PENDING lock will be gone. Process B then wakes up, finds the database UNLOCKED, obtains his EXCLUSIVE lock, commits his local cache's balance of $11,000 to the database, releases his lock, and exits. Process A then wakes up, finds the database UNLOCKED, obtains an EXCLUSIVE lock, commits her local cache's balance of $9,000 to the database, releases her lock, and exits. *The database now erroneously shows a balance of $9,000.* The problem is that the moment that process B commits his local cache's balance of $11,000 to the database, he causes process A's local cache to become *stale*, i.e. inconsistent with the database. After scouring the documentation, I came across the following article: http://www.sqlite.org/cvstrac/wiki?p=CorruptionFollowingBusyError which seems to describe the exact scenario I described above. According to this article, SQLite has been fixed so that if a process encounters a SQLITE_BUSY during an explicit trans- action, then the transaction will *automatically* be rolled back, and the app will receive an error code of SQLITE_IOERR *instead of* SQLITE_BUSY. I understand this to mean that whenever coding an explicit transaction, the programmer must always be prepared to receive an SQLITE_IOERR when stepping through any SQL statement, and must deal with this error by going back to the start of the transaction and starting over. - Richard Klein - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: RE: [sqlite] Impact of no.of tables and records on Startup time
Can cache and page size configuration improve or worsen the situation? regards ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * - Original Message - From: "Samuel R. Neff" <[EMAIL PROTECTED]> Date: Thursday, October 25, 2007 6:16 pm Subject: RE: [sqlite] Impact of no.of tables and records on Startup time > > Complexity of the schema affects time required to open a connection > sincethe schema has to be read and processed. With about 70 tables > each with > lots of indexes and triggers, it takes us 17ms to open the connection. > > HTH, > > Sam > > > --- > We're Hiring! Seeking a passionate developer to join our team building > products. Position is in the Washington D.C. metro area. If interested > contact [EMAIL PROTECTED] > > -Original Message- > From: Kiran Kumar.M.R [mailto:[EMAIL PROTECTED] > Sent: Thursday, October 25, 2007 5:54 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] Impact of no.of tables and records on Startup time > > Hi, > > When SQLite is used in on disk mode, > > What is the impact of no. of tables and records on the startup time. > > For example, > > 1. number of tables (10, 100, 1000 tables, each having 100 > records, each > record 256 bytes) > > 2. number of records in a single table (1 table, having > 10,100,1000,10records, each record 256 bytes) > > Will the startup time get affected? If yes in what order? > > Thanks, > > Kiran > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Some Questions Regarding Access To a SQLite Database By More Than One Process
Here's a bit more locking info that I found useful to help understand it all out of the archives: http://www.mail-archive.com/sqlite-users@sqlite.org/msg02845.html If you are writing a Windows app, you can use a named mutex which can be shared across processes. I have need for a blocking (as opposed to SQLITE_BUSY) mechanism as well. As the referenced thread points out, it is not straightforward. Still working something out... Lee Crain wrote: Ken, Igor, I read the article you referenced. Much appreciated. http://sqlite.org/lockingv3.html I didn't want to complicate my original questions with the intricate details of the application requirements which involve not allowing any database access while certain other operations are executing. I think a MUTEX, even with its inherent performance limitations, is the best solution. Thanks for your replies, Lee Crain P.S. Ken, I'm pretty certain that a MUTEX is both an intra- and inter-process mutual exclusion object. -Original Message- From: Ken [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 24, 2007 2:22 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Re: Some Questions Regarding Access To a SQLite Database By More Than One Process If you are using seperate processes then a mutex will not help since it is local to a process. A semaphore could be used however. You can use a begin immediate around all statements that perform DML (ins/upd/sel) Then loop on the busy at the begin immediate command. This is a fairly simple thing to do. Then for selects you'll need only test the prepare/ and first step After the first step you should not get a sqlite busy. Lee Crain <[EMAIL PROTECTED]> wrote: Igor, I did say "controlled" concurrency. I'll rephrase question 3. 3) Would use of a MUTEX to avoid the dreaded "SQLite busy" condition be a good solution? Or is some other method of avoiding a busy condition recommended? Lee Crain __ -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 24, 2007 1:36 PM To: SQLite Subject: [sqlite] Re: Some Questions Regarding Access To a SQLite Database By More Than One Process Lee Crain wrote: 1. Can multiple processes "concurrently" access the same SQLite database? Yes. 2. If so, can multiple processes maintain an open connection to the database? Or must the connection be opened and closed, before and after, respectively, each database access? You can have multiple open connections, from the same or different processes, at any given time. You can keep a connection open as long as necessary. 3. Would the use of a MUTEX as access protection be adequate to successfully implement controlled "concurrency"? I'm not sure I understand this question. Mutexes are all about _not_ allowing concurrency. Igor Tandetnik -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Impact of no.of tables and records on Startup time
Complexity of the schema affects time required to open a connection since the schema has to be read and processed. With about 70 tables each with lots of indexes and triggers, it takes us 17ms to open the connection. HTH, Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Kiran Kumar.M.R [mailto:[EMAIL PROTECTED] Sent: Thursday, October 25, 2007 5:54 AM To: sqlite-users@sqlite.org Subject: [sqlite] Impact of no.of tables and records on Startup time Hi, When SQLite is used in on disk mode, What is the impact of no. of tables and records on the startup time. For example, 1. number of tables (10, 100, 1000 tables, each having 100 records, each record 256 bytes) 2. number of records in a single table (1 table, having 10,100,1000,10 records, each record 256 bytes) Will the startup time get affected? If yes in what order? Thanks, Kiran - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Race condition -- fixed?
Richard Klein <[EMAIL PROTECTED]> wrote: Process A - BEGIN TRANSACTION; SELECT balance FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = WHERE accountId = '123-45-6789'; COMMIT; Process B - BEGIN TRANSACTION; SELECT balance FROM accounts WHERE accountId = '123-45-6789'; UPDATE accounts SET balance = WHERE accountId = '123-45-6789'; COMMIT; Both processes open the accounts database, obtain SHARED locks, and proceed at about the same pace. Process A updates her local cache with a new balance of $900, while process B updates his local cache with a new balance of $11,000. Now suppose B gets to the COMMIT first. He tries to get a PENDING lock and succeeds. He then tries to promote his PENDING lock to EXCLUSIVE, but gets a SQLITE_BUSY instead, because process A holds a SHARED lock. So, he goes to sleep, hoping that when he awakens the SHARED lock will be gone. Meanwhile, process A reaches her COMMIT, tries to get a PENDING lock, but gets a SQLITE_BUSY instead, because process B already holds a PENDING lock. Process A then releases her SHARED lock (so that process B can be promoted from PENDING to EXCLUSIVE and do his commit), and goes to sleep, hoping that when she wakes up the PENDING lock will be gone. Note that "releases SHARED lock" here means "rolls back transaction". That's the only way for A to release the lock. Process B then wakes up, finds the database UNLOCKED, obtains his EXCLUSIVE lock, commits his local cache's balance of $11,000 to the database, releases his lock, and exits. Process A then wakes up, finds the database UNLOCKED, obtains an EXCLUSIVE lock, commits her local cache's balance of $9,000 to the database, releases her lock, and exits. *The database now erroneously shows a balance of $9,000.* The bug in the banking application, then. It relies on the balance obtained in a now-rolled-back transaction to still be valid, which of course is very much not guaranteed. Process A must rerun the original sequence from the beginning - start transcation, read balance, update balance, commit transaction. Only when both read and update operations are run within a single transaction is consistency guaranteed. The problem is that the moment that process B commits his local cache's balance of $11,000 to the database, he causes process A's local cache to become *stale*, i.e. inconsistent with the database. Process A would be wise to clear its cache when it rolls back its transaction. I understand this to mean that whenever coding an explicit transaction, the programmer must always be prepared to receive an SQLITE_IOERR when stepping through any SQL statement, and must deal with this error by going back to the start of the transaction and starting over. Yes. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] RE : [sqlite] primary key constraint
What is the difference between : 1/ userid UNSIGNED INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL 2/ userid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL CHECK (userid>0) --- David Nguyen <[EMAIL PROTECTED]> a écrit : > Hello, > > With php, I want to create 2 tables, userid is a > primary key in TUser and a foreign key in TRight. I > want to create a default user during database > creation. > > > $sql1 = 'CREATE TABLE TUser(userid UNSIGNED INTEGER > PRIMARY KEY AUTOINCREMENT NOT NULL, username TEXT, > pseudo TEXT, password TEXT, email TEXT, lang TEXT)'; > > $sql2 = 'CREATE TABLE TRight(userid INTEGER, > usermanager BOOL, chef BOOL, writer BOOL, reader > BOOL, > root BOOL)'; > > $sql3 = "INSERT INTO TUser(pseudo, password) VALUES > (\''$pseudo\'',\''$password\'')"; > > $sql4 = "INSERT INTO TRight(userid, usermanager, > chef, > writer, reader, root) > VALUES(1,TRUE,TRUE,TRUE,TRUE,TRUE)"; > > (as 1 is the first autoinc unsigned.) > > My question is : does 'unsigned' word recognized as > a > constraint ? > if it is not, how can I write the sql constraint ? > > userid UNSIGNED INTEGER PRIMARY KEY AUTOINCREMENT > NOT > NULL CONSTRAINT userid > 0 > > BR, > David > > > > _ > > Ne gardez plus qu'une seule adresse mail ! Copiez > vos mails vers Yahoo! Mail > > - > To unsubscribe, send email to > [EMAIL PROTECTED] > - > > _ Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! Mail - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Lemon not reducing
> I do not understand why lemon waits for one more token when it has > enough information to reduce ... > > >>> I don't think you can. Why do you want to? Why not just go > >>> ahead and send it the next token? > >>> > >> Most people find a way around this problem using white-space. This > >> could be a solution but then my grammar will be filled with > >> "white-space | nothing" rules and I thought Lemon could reduce when > >> there is no other way out of the current stack as it is more elegant. > >> > >> > LA(LR) is the answer - just drop Your's tokens as their arrive and give > a chance for the parser to be LALR, not LR or SLR :) > > mak > Lookahead improves what we can do with a grammar, but when there is no ambiguity, it should resolve without waiting for the next token. If your grammar is so simple it's an SLR, why not treat it so and ease the creation of grammars without needing to add "separator" tokens just so there is a lookahead (most languages use ";" or white space for that purpose). My grammar works with all "whitespace or nothing" rules, but I have to be careful to avoid conflicts. When I write it without the whitespace stuff (eaten in the tokenizer), I have no conflict. Simple example: main ::= commands. commands ::= . commands ::= commands ws command. command ::= variable EQUAL value. variable ::= ws IDENTIFIER ws. value::= ws NUMBER ws. ws ::= . ws ::= WHITE. > 2 parsing conflicts. Whithout whitespace stuff: main ::= commands. commands ::= . commands ::= commands command. command ::= variable EQUAL value. variable ::= IDENTIFIER. value::= NUMBER. > no conflicts, easier and cleaner. I know how to fix the first grammar, but you have to think of the actual succession to avoid two "ws" from coming next to the other, this thinking is not really related to the grammar from my point of view. Gaspard - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Race condition -- fixed?
On Wed, 2007-10-24 at 21:38 -0700, Richard Klein wrote: > As I was thinking about the locking mechanism in SQLite 3, > it occurred to me that the following race condition could > occur. > > Imagine a joint bank account with a balance of $10,000. > The wife makes a withdrawal of $1,000 at ATM 'A' (serviced > by process A in the bank's mainframe), while at the same > time the husband makes a deposit of $1,000 at ATM 'B' > (serviced by process B). The steps performed by each > process are as follows: > > Process A > - > BEGIN TRANSACTION; > SELECT balance FROM accounts WHERE accountId = '123-45-6789'; > UPDATE accounts SET balance = > WHERE accountId = '123-45-6789'; > COMMIT; > > Process B > - > BEGIN TRANSACTION; > SELECT balance FROM accounts WHERE accountId = '123-45-6789'; > UPDATE accounts SET balance = > WHERE accountId = '123-45-6789'; > COMMIT; > > Both processes open the accounts database, obtain SHARED > locks, and proceed at about the same pace. Process A > updates her local cache with a new balance of $900, while > process B updates his local cache with a new balance of > $11,000. Evaluating the UPDATE requires a RESERVED lock on the database file. Since only one process can hold the RESERVED lock, this particular scenario cannot occur. One or other of the updates will fail with SQLITE_BUSY. Dan. - To unsubscribe, send email to [EMAIL PROTECTED] -
