[sqlite] Is this SQLite implementation doing something stupid?
Hey guys. I'm using an SQLite implementation that someone else made for my high-level language of choice. While looking through the imp, I've just found this function, which is used as the callback argument to sqlite3_exec. Does this look like an ok useage? It seems to me like this might be doing a lot of work for some data I may never use. Any help much appreciated, Thanks, Ian int Callback(void *pArg, int argc, char **argv, char **columnNames) { // basically this callback is called for each row in the SQL query result. // for each row, argc indicates how many columns are returned. // columnNames[i] is the name of the column // argv[i] is the value of the column sqlite_resultrow* pRow; sqlite_resultset* pResultSet; char* name; char* value; int i; if (argc == 0) return 0; pResultSet = (sqlite_resultset*)pArg; if (!pResultSet) return -1; // create a new result row pRow = new sqlite_resultrow; pResultSet-iNumCols = argc; // loop through all the columns and stuff them into our row for (i = 0; i argc; i++) { // DBEUG CODE // Con::printf(%s = %s\n, columnNames[i], argv[i] ? argv[i] : NULL); name = new char[dStrlen(columnNames[i]) + 1]; dStrcpy(name, columnNames[i]); pRow-vColumnNames.push_back(name); if (argv[i]) { value = new char[dStrlen(argv[i]) + 1]; dStrcpy(value, argv[i]); pRow-vColumnValues.push_back(value); } else { value = new char[10]; dStrcpy(value, NULL); pRow-vColumnValues.push_back(value); } } pResultSet-iNumRows++; pResultSet-vRows.push_back(pRow); // return 0 or else the sqlexec will be aborted. return 0; } ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite management
Hello, I am planning to write an open source software with wxWidgets to simplify SQLite operations(select,insert etc) There are open source alternative softwares. http://sourceforge.net/projects/sqlitemanager/ http://sqlitebrowser.sourceforge.net/index.html http://www.sqlite.org/cvstrac/wiki?p=ManagementTools Writing new a management tool is wasting time or SQLite doesn't need a software to simply SQLite operations. Could you please give a suggestion? Mustafa Onur Aysan Regards ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite management
On Sat, Feb 5, 2011 at 6:36 AM, mustafa mstf...@gmail.com wrote: Hello, I am planning to write an open source software with wxWidgets to simplify SQLite operations(select,insert etc) There are open source alternative softwares. http://sourceforge.net/projects/sqlitemanager/ http://sqlitebrowser.sourceforge.net/index.html http://www.sqlite.org/cvstrac/wiki?p=ManagementTools Writing new a management tool is wasting time or SQLite doesn't need a software to simply SQLite operations. Could you please give a suggestion? The big question is what will you offer that is not available on the currently available opensource or free tools? John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite management
2011/2/5 John Drescher dresche...@gmail.com On Sat, Feb 5, 2011 at 6:36 AM, mustafa mstf...@gmail.com wrote: Hello, I am planning to write an open source software with wxWidgets to simplify SQLite operations(select,insert etc) There are open source alternative softwares. http://sourceforge.net/projects/sqlitemanager/ http://sqlitebrowser.sourceforge.net/index.html http://www.sqlite.org/cvstrac/wiki?p=ManagementTools Writing new a management tool is wasting time or SQLite doesn't need a software to simply SQLite operations. Could you please give a suggestion? The big question is what will you offer that is not available on the currently available opensource or free tools? John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Program will not be web base. I will support software as long as I will. If contributors want join to the project they are welcome. Regards ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sub-Select reports only row one value?
I am having problems with Sub-Select apparently working on one row rather than the whole table. registrations table: Class | Term | (Other student columns) CC123 | 101 | ... CC002 | 101 CC050 | 111 CC123 | 101 CC123 | 102 ... Desired ultimate output counts by class and term, with totals and averages by row and by column: Class | 093 | 101 | 102 | 103 | 111 | Total | Average CC001 | 10 | 8 | 12 | 7 | 8 |45 |9 CC002 | 6 | 11 | | 13 | |30 | 10 ... | | | | | | | CC278 | | 6 | 8 | | |14 |7 Totals: | ... I want to be able to use Order By on any column, including Average. The first columns aren't too difficult. But Averages within a row (or even Counts to calculate an average) has me baffled. I don't know if it is (in order of probability) 1. My ignorance, 2. SQLite bug, or 3. SQL problem. When I use this not so pretty Select, I can get good counts of non-NULL terms: SELECT COUNT(term) FROM (SELECT DISTINCT class, term FROM registrations AS r2 GROUP BY r2.class, r2.term ORDER BY r2.class, r2.term) GROUP BY class But when I use it as a Sub-Select (or in a Join), it gives, for ALL rows, the result from the FIRST row. SELECT r.class, r.term, COUNT(*) AS c, (SELECT COUNT(term) FROM (SELECT DISTINCT class, term FROM registrations AS r2 GROUP BY r2.class, r2.term ORDER BY r2.class, r2.term) GROUP BY class) AS cnt FROM registrations AS r GROUP BY r.class, r.term ORDER BY r.class All rows get the same value for cnt. I can verify that it is getting the cnt from the first row by introducing a class CC000 with just one student. Using a self-Join rather than sub-Select has the same result. Environments: 1. SQLite embedded in PHP5 called via PDO (Windows or FreeBSD), 2. SQLite Manager addon for Firefox. Same results from each. Apparently the optimization is going overboard. I have, as you see, tried to help defeat it using aliases on the two uses of the table. My workaround, when the Order By is the averages column, is to post process in PHP which involves a full array and sorting it. Kind of defeats the use of SQL. Any help or insights would be most welcomed. Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this SQLite implementation doing something stupid?
Ian Hardingham i...@omroth.com wrote: I'm using an SQLite implementation that someone else made for my high-level language of choice. While looking through the imp, I've just found this function, which is used as the callback argument to sqlite3_exec. Does this look like an ok useage? It seems to me like this might be doing a lot of work for some data I may never use. I imagine that, lacking prescience, the callback has no way to know which data you will eventually use and which you won't, and has no choice but to store it all. If at all possible, I would suggest moving away from sqlite3_exec towards a forward cursor based on sqlite3_prepare / sqlite3_step. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sub-Select reports only row one value?
Jeff Hennick sqli...@jeff-h.com wrote: I am having problems with Sub-Select apparently working on one row rather than the whole table. When syntactically used as an expression (e.g. in SELECT clause), a subselect produces a single value. When used as a table (e.g. in FROM clause), it may produce multiple rows. registrations table: Class | Term | (Other student columns) CC123 | 101 | ... ... Desired ultimate output counts by class and term, with totals and averages by row and by column: SQL is not well suited for building pivot tables. Its resultset is a table with a fixed number of columns and a variable number of rows. I suggest you write a query that produces a table with three columns (Class, Term, SomeNumber), and process it into a pivot table in your application code. But when I use it as a Sub-Select (or in a Join), it gives, for ALL rows, the result from the FIRST row. SELECT r.class, r.term, COUNT(*) AS c, (SELECT COUNT(term) FROM (SELECT DISTINCT class, term FROM registrations AS r2 GROUP BY r2.class, r2.term ORDER BY r2.class, r2.term) GROUP BY class) AS cnt FROM registrations AS r GROUP BY r.class, r.term ORDER BY r.class All rows get the same value for cnt. The subselect has no dependencies on the current row from registrations AS r table. It could as well be calculated once, and the resulting value inserted into every row. In what way do you expect the value to vary? What exactly was this subselect supposed to achieve? It makes no sense to me. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this SQLite implementation doing something stupid?
Ian Hardingham wrote: I'm using an SQLite implementation that someone else made for my high-level language of choice. While looking through the imp, I've just found this function, which is used as the callback argument to sqlite3_exec. Does this look like an ok useage? It seems to me like this might be doing a lot of work for some data I may never use. If it's not buggy, and you don't need it to perform better, then leave it alone. If you need it to perform better, don't do any optimizing until you've measured where your hotspots really are by using, e.g., gprof and/or whatever profiling tools exist for your language. If you find that this interface is really your issue, scope out how feasible/cost-effective it is to change it. This will be determined by a great many variables -- size of the code base number of entry points to this interface, quality of client code, level of abstraction client code provides from this interface to higher layers, availability of a covering test suite, amount of time you have to do the work, number of round-trips you can make with your QA team (if they exist) before delivery, how much new client code you think will be written that eventually calls down to this interface over the next weeks/months/years, etc etc etc. If so, and if it makes sense, consider creating a new interface instead of editing the existing one. This allows you to upgrade the client code a little bit at a time to use the new interface. The drawback is that now you're maintaining two interfaces. Probable hot spots in the existing code: string scans (dStrlen), string copies (dStrcpy) and calls to 'new'. The fewer of those you can do, the better off you'll be. I'd say a good reference as to the proper way to hook SQLite into a high-level language is the SQLite devs' own implementation for Tcl. You want to grab e.g. http://sqlite.org/sqlite-tea-3070500.tar.gz and have a look at tclsqlite3.c, function DbEvalNextCmd on line 124807 and function dbEvalColumnValue on line 124743. Things this gets right that your implementation doesn't, from my quick glance: 1. It keeps a cache of prepared statements and re-uses statements that have been run recently. You are re-preparing statements every time. 2. It only asks for columns that the client is asking for (only you can know whether higher layers are getting this right). 3. It only computes the column names once during the statement. You are computing them on every row. 4. It only processes one result row at a time (synchronously calling up to the high-level language), keeping memory usage low. You are stuffing the whole result set into memory before returning it to the client. Fixing this might be the biggest hassle on this list, because it might impact many layers above. 5. It does not force all values to strings. You are (and, depending on which language you're using, you'll probably convert them back to native types higher up somewhere). Shared drawback: 1. Text data is copied. This can't be helped for Tcl because it's enforced by the Tcl extention API. Maybe your language lets you just point directly at a const char* and use copy-on-write semantics or some such. Eric Any help much appreciated, Thanks, Ian int Callback(void *pArg, int argc, char **argv, char **columnNames) { // basically this callback is called for each row in the SQL query result. // for each row, argc indicates how many columns are returned. // columnNames[i] is the name of the column // argv[i] is the value of the column sqlite_resultrow* pRow; sqlite_resultset* pResultSet; char* name; char* value; int i; if (argc == 0) return 0; pResultSet = (sqlite_resultset*)pArg; if (!pResultSet) return -1; // create a new result row pRow = new sqlite_resultrow; pResultSet-iNumCols = argc; // loop through all the columns and stuff them into our row for (i = 0; i argc; i++) { // DBEUG CODE // Con::printf(%s = %s\n, columnNames[i], argv[i] ? argv[i] : NULL); name = new char[dStrlen(columnNames[i]) + 1]; dStrcpy(name, columnNames[i]); pRow-vColumnNames.push_back(name); if (argv[i]) { value = new char[dStrlen(argv[i]) + 1]; dStrcpy(value, argv[i]); pRow-vColumnValues.push_back(value); } else { value = new char[10]; dStrcpy(value, NULL); pRow-vColumnValues.push_back(value); } } pResultSet-iNumRows++; pResultSet-vRows.push_back(pRow); // return 0 or else the sqlexec will be aborted. return 0; } ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Eric A. Smith Stocks have reached what looks like a
[sqlite] threads and transactions
i'm sure this topic has been beaten to death but i just really want to make sure. i'm using ONE database, and one handle to it on all threads here's a theoretical timeline -- 1) thread 1 begin transaction do bunches of stuff 2) thread 2 begin transaction do bunches of stuff 3) thread 1 do more stuff 4) thread 0 (main thread) do atomic operation 5) thread 2 do more stuff commit transaction 6) thread 1 rollback transaction -- presuming this timeline is chronological, may i assume that step 4 is committed first in the database? and that steps 5 and 6 operate independently? even when threads 1 and 2 open their individual transactions, i see only ONE journal file logically i guess i would have expected to see one journal file per open transaction per thread? does step five commit thread 6? does thread 6's rollback only operate on the stuff done in thread 6? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] threads and transactions
presuming this timeline is chronological, may i assume that step 4 is committed first in the database? You mean as a third transaction? No. and that steps 5 and 6 operate independently? No. even when threads 1 and 2 open their individual transactions, i see only ONE journal file logically i guess i would have expected to see one journal file per open transaction per thread? You are wrong. Transactions are per-connection and have nothing to do with threads. If you want different transactions in each thread you need to make one connection for each thread. But those transactions won't be able to execute simultaneously. does step five commit thread 6? does thread 6's rollback only operate on the stuff done in thread 6? Step 5 commits everything in thread 1, thread 1's rollback does nothing. And btw if you checked your return codes from SQLite you'd notice that begin transaction fails either in thread 2 or thread 1 (whichever comes last). Pavel On Sat, Feb 5, 2011 at 5:27 PM, David M. Cotter m...@davecotter.com wrote: i'm sure this topic has been beaten to death but i just really want to make sure. i'm using ONE database, and one handle to it on all threads here's a theoretical timeline -- 1) thread 1 begin transaction do bunches of stuff 2) thread 2 begin transaction do bunches of stuff 3) thread 1 do more stuff 4) thread 0 (main thread) do atomic operation 5) thread 2 do more stuff commit transaction 6) thread 1 rollback transaction -- presuming this timeline is chronological, may i assume that step 4 is committed first in the database? and that steps 5 and 6 operate independently? even when threads 1 and 2 open their individual transactions, i see only ONE journal file logically i guess i would have expected to see one journal file per open transaction per thread? does step five commit thread 6? does thread 6's rollback only operate on the stuff done in thread 6? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] threads and transactions
Transactions are per-connection and have nothing to do with threads. If you want different transactions in each thread you need to make one connection for each thread. But those transactions won't be able to execute simultaneously. so if i open a separate connection on each thread then each thread begins a transaction you're saying one thread will block? i understand that one commit will block all other threads from doing a commit, rollback or any atomic transaction, until it's done, but are you saying i can't even add data on another thread while one has an open transaction? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] threads and transactions
i understand that one commit will block all other threads from doing a commit, rollback or any atomic transaction, until it's done, but are you saying i can't even add data on another thread while one has an open transaction? There can be several simultaneous read-only transactions. But as long as one connection started a writing transaction (by executing begin immediate or by executing insert/update/delete after begin) no other connection can start a writing transaction (it still can do read-only transactions for a while). If you need a different behavior you need to use some other DBMS. Pavel On Sat, Feb 5, 2011 at 5:48 PM, David M. Cotter m...@davecotter.com wrote: Transactions are per-connection and have nothing to do with threads. If you want different transactions in each thread you need to make one connection for each thread. But those transactions won't be able to execute simultaneously. so if i open a separate connection on each thread then each thread begins a transaction you're saying one thread will block? i understand that one commit will block all other threads from doing a commit, rollback or any atomic transaction, until it's done, but are you saying i can't even add data on another thread while one has an open transaction? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] threads and transactions
i may not have been clear i want to begin transactions on different threads at once in each thread begin a transaction insert lots of data, this may take a long time commit transaction i understand that one commit will block the other but does inserting data during a transaction actually block too? is inserting considered a writing transaction if there is a begin before it? cuz it's not actually writing to the DB proper, it's writing to it's journal file, saving things up until the commit or rollback. i'm not using begin immediate, just using begin On Feb 5, 2011, at 2:53 PM, Pavel Ivanov wrote: i understand that one commit will block all other threads from doing a commit, rollback or any atomic transaction, until it's done, but are you saying i can't even add data on another thread while one has an open transaction? There can be several simultaneous read-only transactions. But as long as one connection started a writing transaction (by executing begin immediate or by executing insert/update/delete after begin) no other connection can start a writing transaction (it still can do read-only transactions for a while). If you need a different behavior you need to use some other DBMS. Pavel On Sat, Feb 5, 2011 at 5:48 PM, David M. Cotter m...@davecotter.com wrote: Transactions are per-connection and have nothing to do with threads. If you want different transactions in each thread you need to make one connection for each thread. But those transactions won't be able to execute simultaneously. so if i open a separate connection on each thread then each thread begins a transaction you're saying one thread will block? i understand that one commit will block all other threads from doing a commit, rollback or any atomic transaction, until it's done, but are you saying i can't even add data on another thread while one has an open transaction? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] threads and transactions
On 5 Feb 2011, at 11:00pm, David M. Cotter wrote: i may not have been clear i want to begin transactions on different threads at once in each thread begin a transaction insert lots of data, this may take a long time commit transaction Okay, here's some background. In SQLite every write is in a transaction whether you declare one with BEGIN or not. If you don't declare a transaction, SQLite invisibly surrounds each individual INSERT or UPDATE with a BEGIN and COMMIT. i understand that one commit will block the other but does inserting data during a transaction actually block too? It is each transaction which blocks each other transaction, but the default behaviour is not to lock the file until the first command that makes a change. So once one thread has executed an INSERT, all other threads will be blocked at their own BEGINs and will never even get to do an INSERT until the first thread has done its COMMIT. is inserting considered a writing transaction if there is a begin before it? cuz it's not actually writing to the DB proper, it's writing to it's journal file, saving things up until the commit or rollback. The natural question you're asking is something like Well, if each thread is just inserting new rows, why do they need to block each-other ? They don't care what data the other threads are inserting.. The answer is that even the process of inserting new data needs to look at existing data. There's the problem of dealing with columns marked as UNIQUE: each thread needs to be stopped if it tries to insert a new row that would violate database restrictions, and it has to be stopped on that instruction, not long after it has forgotten what it did that caused the problem. And for the system to decide which thread should get the rejection it has to consider the operations in a particular order. Which is why it only considers one transaction at a time. For a fuller explanation of what causes and is blocked by locking see http://www.sqlite.org/lang_transaction.html Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] threads and transactions
forgive my not understanding this but i'm trying to be extremely clear and i am not sure from your answer whether you have understood my question. In SQLite every write is in a transaction whether you declare one with BEGIN or not. If you don't declare a transaction, SQLite invisibly surrounds each individual INSERT or UPDATE with a BEGIN and COMMIT. sure, that's fine. but if you do your own BEGIN, then any INSERT you do after that, *before* you do the COMMIT isn't actually writing anything into the database, right? it's really only the COMMIT that actually writes to the database, right? i understand that one commit will block the other but does inserting data during a transaction actually block too? It is each transaction which blocks each other transaction, but the default behaviour is not to lock the file until the first command that makes a change. So once one thread has executed an INSERT, all other threads will be blocked at their own BEGINs and will never even get to do an INSERT until the first thread has done its COMMIT. but you're not addressing the case where i have manually started a BEGIN. after i manually do BEGIN, does the next INSERT need to block, or is it the COMMIT that actually does the blocking? do you see what i'm asking? is inserting considered a writing transaction if there is a begin before it? cuz it's not actually writing to the DB proper, it's writing to it's journal file, saving things up until the commit or rollback. The natural question you're asking is something like Well, if each thread is just inserting new rows, why do they need to block each-other ? They don't care what data the other threads are inserting. well, i would phrase it this way: if each thread is collecting several rows that it will insert in one fell swoop, why should the mere gathering of rows (without actually committing them yet) block another thread from gathering it's own set of rows? it's only when all the rows are gathered that i actually commit the change. Since the database isn't being written to during the gathering phase, they don't care what other threads are gathering. am i making sense? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] threads and transactions
On 6 Feb 2011, at 1:30am, David M. Cotter wrote: In SQLite every write is in a transaction whether you declare one with BEGIN or not. If you don't declare a transaction, SQLite invisibly surrounds each individual INSERT or UPDATE with a BEGIN and COMMIT. sure, that's fine. but if you do your own BEGIN, then any INSERT you do after that, *before* you do the COMMIT isn't actually writing anything into the database, right? it's really only the COMMIT that actually writes to the database, right? An alternative point of view is that the transactions hit the database immediately, and that either ROLLBACK or failure to do a COMMIT backs them out. It depends what you think 'the database' is. Remember that it's possible to run SQLite without any journaling at all: even uncommitted changes go to the database file. If a journal file exists, then I feel 'the database' includes both the database file and the journal file. i understand that one commit will block the other but does inserting data during a transaction actually block too? It is each transaction which blocks each other transaction, but the default behaviour is not to lock the file until the first command that makes a change. So once one thread has executed an INSERT, all other threads will be blocked at their own BEGINs and will never even get to do an INSERT until the first thread has done its COMMIT. but you're not addressing the case where i have manually started a BEGIN. after i manually do BEGIN, does the next INSERT need to block, or is it the COMMIT that actually does the blocking? The first INSERT after the BEGIN starts the blocking. Unless you specifically override the behaviour by doing a BEGIN IMMEDIATE (which you've previously said you weren't doing). is inserting considered a writing transaction if there is a begin before it? cuz it's not actually writing to the DB proper, it's writing to it's journal file, saving things up until the commit or rollback. The natural question you're asking is something like Well, if each thread is just inserting new rows, why do they need to block each-other ? They don't care what data the other threads are inserting. well, i would phrase it this way: if each thread is collecting several rows that it will insert in one fell swoop, why should the mere gathering of rows (without actually committing them yet) block another thread from gathering it's own set of rows? it's only when all the rows are gathered that i actually commit the change. Since the database isn't being written to during the gathering phase, they don't care what other threads are gathering. See my previous answer. If a program tries to add a row to the database that conflicts with one that's already there, it needs to get an error immediately. Not later when it has forgotten the context of the row that's causing the problem and can no longer figure out how to handle the problem. So a thread that's trying to do an INSERT must be blocked until all the changes that will be done before it have been completed. Also there's the problem that some applications need to know the rowid of a freshly-inserted row. If you don't need this behaviour because you're confident you'll never get a clash, then you could accumulate your INSERTs in memory, then blast through them when you would previously have just done the COMMIT. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] threads and transactions
David M. Cotter m...@davecotter.com wrote: In SQLite every write is in a transaction whether you declare one with BEGIN or not. If you don't declare a transaction, SQLite invisibly surrounds each individual INSERT or UPDATE with a BEGIN and COMMIT. sure, that's fine. but if you do your own BEGIN, then any INSERT you do after that, *before* you do the COMMIT isn't actually writing anything into the database, right? it's really only the COMMIT that actually writes to the database, right? Wrong. There is a limited in-memory cache, but once the amount of data grows large enough, intermediate changes need to be spilled to disk and, indeed, written to the database file. but you're not addressing the case where i have manually started a BEGIN. after i manually do BEGIN, does the next INSERT need to block, or is it the COMMIT that actually does the blocking? The first INSERT acquires a reserved lock on the database. No other connection will then be able to acquite a reserved lock, so there would be no other writer. For details, see http://www.sqlite.org/lockingv3.html if each thread is collecting several rows that it will insert in one fell swoop, why should the mere gathering of rows (without actually committing them yet) block another thread from gathering it's own set of rows? You are free to gather the data that would go into the database before starting the write transaction. it's only when all the rows are gathered that i actually commit the change. There's no mechanism in SQLite to control when precisely intermediate data is written to the database file. It may happen before COMMIT. am i making sense? Yes, but you are laboring under incorrect assumptions, and thus reaching incorrect conclusions. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] threads and transactions
David M. Cotter m...@davecotter.com wrote: i may not have been clear i want to begin transactions on different threads at once Why, if you don't mind me asking? Your hard drive has only one write head. What makes you feel that writing to the same file from multiple threads would be any faster than doing it from one thread? In any case, you can't do with SQLite what you think you want to do. Personally, I'd have one thread dedicated to SQLite work, then all the other threads would generate the data to be inserted, and feed it to the SQLite worker via a producer-consumer queue. but does inserting data during a transaction actually block too? Yes. is inserting considered a writing transaction if there is a begin before it? Yes. cuz it's not actually writing to the DB proper, it's writing to it's journal file Incorrect. SQLite writes previous, unmodified data into journal file, prior to overwriting parts of actual database file with new data. Committing a transaction consists simply of deleting the journal file. Rolling back means copying the data over from the journal file back to the database file. i'm not using begin immediate, just using begin This just means the transaction becomes a write transaction a split second later, when the first INSERT statement is executed. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users