Re: [sqlite] Transactions

> On Feb 6, 2017, at 11:08 AM, James K. Lowden wrote: > > It's fascinating, and emblematic of our times, that > something like iTunes had (or has) DBMS interaction amidst low-level > operations like capture and playback. Oh, it didn’t use a database! It was just

Re: [sqlite] Transactions

On Mon, 6 Feb 2017 09:38:20 -0800 Jens Alfke wrote: > In some cases there?d be multiple events in a second that triggered a > database write in a transaction; when this happened down in my humble > process, it could cause iTunes playback to stutter and video capture > to lose

Re: [sqlite] Transactions

On Sun, Feb 05, 2017 at 09:41:48AM +0100, Michele Pradella wrote: > Do you think transactions are useful only when you have to do a sequence > of statements that depends on each other and you need a way to rollback > all statements if something goes wrong? or you can use transactions even > with

Re: [sqlite] Transactions

> On Feb 6, 2017, at 12:07 AM, Hick Gunter wrote: > > The optimal number of inserts/transaction depends on your hardware setup and > who else needs access to CPU and I/O resources. Too many transactions can definitely be a problem! It depends on the OS, but the filesystem

Re: [sqlite] Transactions

Yes, putting a large number of inserts that affect the same table(s) into ona bulk transaction can be a huge speedup, because the operations can take place in memory without having to reach the disk surface until commit time. The optimal number of inserts/transaction depends on your hardware

Re: [sqlite] Transactions

Michele Pradella wrote: > I have a question about transactions and SQLite: http://www.sqlite.org/faq.html#q19 Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Transactions for read operations

Thanks to those who responded to my query. Simon: It will be easier to code if every page commits, regardless of whether any write operations occurred (and rollback only error) - so your answer pleased me greatly. Keith: Yes, you're right. I'm not passing a single database connection around,

Re: [sqlite] Transactions for read operations

>To: General Discussion of SQLite Database >Subject: Re: [sqlite] Transactions for read operations > > >On 7 Sep 2014, at 3:49am, Keith Medcalf <kmedc...@dessus.com> wrote: > >> You say "the database connection". Did you use the language >imprecisely or ar

Re: [sqlite] Transactions for read operations

On 7 Sep 2014, at 3:49am, Keith Medcalf wrote: > You say "the database connection". Did you use the language imprecisely or > are you using only one database connection? One presumes that you may have > half-a-million pages and half-a-billion concurrent HTTP operations,

Re: [sqlite] Transactions for read operations

As a general principle, database transactions should be held for as short a time as possible. You should start your transaction, then do all of the operations immediately that need to be mutually consistent, and then end the transaction appropriately; ideally a transaction is only open for a

Re: [sqlite] Transactions for read operations

On Saturday, 6 September, 2014, at 20:23, Richard Warburton inquired: >Brief: >Should transactions be used for ensuring consistency between multiple >queries? And if so, after I've finished is there a reason why I should >not call commit? >Background: >I'm

Re: [sqlite] Transactions for read operations

On 7 Sep 2014, at 3:22am, Richard Warburton wrote: > Should transactions be used for ensuring consistency between multiple > queries? Good idea. > And if so, after I've finished is there a reason why I should not > call commit? You should finish the

Re: [sqlite] transactions do not respect delete

> Date: Tue, 6 May 2014 14:57:30 +0200 > From: Mark Lawrence <no...@null.net> > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] transactions do not respect delete > Message-ID: <20140506125730.ga23...@rekudos.net> > Content-Type: text/plain; charset=us-ascii &g

Re: [sqlite] transactions do not respect delete

On Tue, May 6, 2014 at 2:59 PM, Clemens Ladisch wrote: > With the COMMIT, you told the database that the transaction succeeded > (which means that the effects of all successful statements are saved > permanently.) If you want the transaction to fail, execute ROLLBACK >

Re: [sqlite] transactions do not respect delete

Jim Carroll wrote: > BEGIN; > DELETE FROM A; > INSERT INTO A VALUES(1, "goodbye"); > INSERT INTO A VALUES(1, "world");-- fails > COMMIT; > > It would appear the DELETE was successful, and the first INSERT was > successful. But when the second INSERT failed (as it was intended

Re: [sqlite] transactions do not respect delete

> It would appear the DELETE was successful, and the first INSERT was > successful. But when the second INSERT failed (as it was intended to)..it > did not ROLLBACK the database. Even though the second INSERT fails, your script still calls COMMIT on an open transaction in which the DELETE and

Re: [sqlite] transactions do not respect delete

On Tue, May 6, 2014 at 2:31 PM, Jim Carroll wrote: > > CREATE TABLE A(id INT PRIMARY KEY, val TEXT); > INSERT INTO A VALUES(1, "hello"); > BEGIN; > DELETE FROM A; > INSERT INTO A VALUES(1, "goodbye"); > INSERT INTO A VALUES(1, "world"); > COMMIT; Sounds like you

Re: [sqlite] Transactions

On 2/28/2014 5:48 PM, L. Wood wrote: Is this legal? 1) Create "INSERT" prepared statement with parameters. 2) Create a second non-parameter "BEGIN TRANSACTION" prepared statement, execute it with _step(), and finalize it. 3) Attach values to the parameters of the first "INSERT" prepared

Re: [sqlite] transactions and locking

Thank you both, I now have a better understanding. Lars ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] transactions and locking

On 2 May 2012, at 12:16pm, Lars Hansen wrote: > I'm sorry I have to bother you readers but I haven't understood > http://sqlite.org/lockingv3.html chapter 7. I've answered your questions in order, but they're all interrelated, so please read them all before worrying about the

Re: [sqlite] transactions and locking

On Wed, May 02, 2012 at 01:16:41PM +0200, Lars Hansen scratched on the wall: > Hello, > > I'm sorry I have to bother you readers but I haven't understood > http://sqlite.org/lockingv3.html chapter 7. > > 1. "In autocommit mode, all changes to the database are committed as > soon as all

Re: [sqlite] Transactions for SELECT

On 11/22/2011 05:45 PM, Baruch Burstein wrote: I will when I get the chance, but I am trying to get a list of things to try to improve my SELECT speeds. If it is one SELECT, but returning +-1 rows, it probably won't make a difference, right? No advantage in wrapping a single statement, of

Re: [sqlite] Transactions for SELECT

On 22 Nov 2011, at 10:45am, Baruch Burstein wrote: > I will when I get the chance, but I am trying to get a list of things to > try to improve my SELECT speeds. If it is one SELECT, but returning +-1 > rows, it probably won't make a difference, right? Right. It'll do a lock, then the

Re: [sqlite] Transactions for SELECT

I will when I get the chance, but I am trying to get a list of things to try to improve my SELECT speeds. If it is one SELECT, but returning +-1 rows, it probably won't make a difference, right? On Tue, Nov 22, 2011 at 11:41 AM, Dan Kennedy wrote: > On 11/22/2011

Re: [sqlite] Transactions for SELECT

On 11/22/2011 04:34 PM, Baruch Burstein wrote: Do transactions speed up SELECT statements? They can a bit. If you put 10 SELECT statements in a transaction SQLite only has to lock and unlock the database file once. If you run them outside of a transaction the db is locked and unlocked 10

Re: [sqlite] TRANSACTIONs

On 2 Jun 2011, at 6:01pm, Jan Hudec wrote: > - Inside one transaction, running select may or may not see data inserted or > updated in parallel. Sorry to go on about this but you underestimate the problem. Suppose you're part-way through _step()ing through the results of a SELECT and an

Re: [sqlite] TRANSACTIONs

On Wed, Jun 01, 2011 at 19:23:16 +0100, Simon Slavin wrote: > On 1 Jun 2011, at 7:12pm, Jan Hudec wrote: > >> Do not update a table if there is some select statement currently > > > > Actually insert, update and delete are OK. Drop and alter table are > > a problem. > > Pavel is right. He left

Re: [sqlite] TRANSACTIONs

On 1 Jun 2011, at 7:57pm, Robert Myers wrote: > What about the I of ACID? The select should have an implicit transaction > around it. That would violate the SQL standard which says you can SELECT data which you haven't committed yet. Actually the bit of ACID you want for SELECT is really the

Re: [sqlite] TRANSACTIONs

> What about the I of ACID? The select should have an implicit transaction > around it. No, it shouldn't. It can be a part of some other transaction. I in ACID means Isolation of transactions, not Isolation of select statements. Otherwise there would be no way for select statement to read

Re: [sqlite] TRANSACTIONs

On 6/1/2011 1:47 PM, Pavel Ivanov wrote: >> Actually, you do know what SQLite does without knowing the internals. It >> claims to be serializable and ACID >> (http://www.sqlite.org/transactional.html), therefore it's fine. > "Serializable" there means that once transaction is started statements >

Re: [sqlite] TRANSACTIONs

> Actually, you do know what SQLite does without knowing the internals. It > claims to be serializable and ACID > (http://www.sqlite.org/transactional.html), therefore it's fine. "Serializable" there means that once transaction is started statements won't see any data committed in other

Re: [sqlite] TRANSACTIONs

On 6/1/2011 1:23 PM, Simon Slavin wrote: > On 1 Jun 2011, at 7:12pm, Jan Hudec wrote: > >> On Wed, Jun 01, 2011 at 10:17:02 -0400, Pavel Ivanov wrote: From within a BEGIN TRANSACTION and END TRANSACTION block, should I not update the same row in a table more than once? >>> You can

Re: [sqlite] TRANSACTIONs

On 1 Jun 2011, at 7:12pm, Jan Hudec wrote: > On Wed, Jun 01, 2011 at 10:17:02 -0400, Pavel Ivanov wrote: >>> From within a BEGIN TRANSACTION and END TRANSACTION block, should I not >>> update the same row in a table more than once? >> >> You can update it as many times as you need. >> >>>

Re: [sqlite] TRANSACTIONs

On Wed, Jun 01, 2011 at 10:17:02 -0400, Pavel Ivanov wrote: > >  From within a BEGIN TRANSACTION and END TRANSACTION block, should I not > > update the same row in a table more than once? > > You can update it as many times as you need. > > > What are the exact limitations on what I can do

Re: [sqlite] TRANSACTIONs

>  From within a BEGIN TRANSACTION and END TRANSACTION block, should I not > update the same row in a table more than once? You can update it as many times as you need. > What are the exact limitations on what I can do during a Transaction? Do not update a table if there is some select

Re: [sqlite] Transactions while using prepare and step

On 2 Dec 2010, at 7:38pm, cricketfan wrote: > { > Select a from tbl1 where b='yyy' c ='xxx' limit i offset (i + 1) ; > Update tbl1 set e=1 where a = some value from the select; > i = i + 1 > } There doesn't appear to be any purpose to anything but the UPDATE command. Can you not

Re: [sqlite] Transactions while using prepare and step

> Another question - What kind of impact does a limit clause have? The columns > being used in the where clause are indexed. My current design is bad, I am > forced to use limit to get one row at a time. Since I have an index the > impact should be minimal. Please let me know if I am wrong. You

Re: [sqlite] Transactions while using prepare and step

I have 2 threads in my program can someone take a look at them and provide some comments Pseudo code for thread 1 (a is the primary key) Thread1() { insert into tbl1(a,b,c,d,e,f,g,h,i,j,k); } So my pseudo code is(b and c have an index, a is primary key) Thread2() { sleep(200); prepare;

Re: [sqlite] Transactions while using prepare and step

On 2 Dec 2010, at 4:39pm, Pavel Ivanov wrote: > I didn't see in OP's email any information on whether he updates the > same rows or same table as he selects, so I'd say this statement is > too harsh in general case. While select is executing you shouldn't > update the same rows that were

Re: [sqlite] Transactions while using prepare and step

> Makes no difference.  Doing an UPDATE inside your SELECT violates the rule no > matter how you structure your transaction.  A SELECT is a single operation > and you can't do anything else until it is finished. I didn't see in OP's email any information on whether he updates the same rows or

Re: [sqlite] Transactions while using prepare and step

On 2 Dec 2010, at 3:44pm, cricketfan wrote: > Hello I have a basic question and would be glad if someone can answer it. I > understand that if we have "BEGIN TRANSACTION" and "END TRANSACTION" then > the transaction(s) within that block would be committed, number of > transactions could be

Re: [sqlite] Transactions + .read

On 31 Mar 2010, at 6:39pm, Kevin M. wrote: >>> So, is there an API or query I can run to load an sql file all in one go >>> (one transaction) without having to read in the file manually and query one >>> line at a time? > >> No, sorry. That's what programming languages are for. Open the

Re: [sqlite] Transactions + .read

>> Actually I was wanting to put that into a program. > I don't know how I guessed that but I'm feeling really smug now. I'm feeling very amused by your smugness ;-) >> So, is there an API or query I can run to load an sql file all in one go >> (one transaction) without having to read in the

Re: [sqlite] Transactions + .read

On 31 Mar 2010, at 4:48pm, Kevin M. wrote: >> Warning: From your phrasing it's possible you're thinking of putting a >> '.read' command into your program. The '.read' command is only a command to >> the command-line tool. It's not a function of SQLite, and you can't submit >> it as a query

Re: [sqlite] Transactions + .read

> Warning: From your phrasing it's possible you're thinking of putting a > '.read' command into your program. The '.read' command is only a command to > the command-line tool. It's not a function of SQLite, and you can't submit > it as a query in your program. > Simon. Actually I was

Re: [sqlite] Transactions + .read

On 31 Mar 2010, at 4:21pm, Kevin M. wrote: > Hopefully I'm not duplicating a question, but I looked on sqlite.org and did > not find the answer... > > Will this: > BEGIN TRANSACTION; > .read somefile.sql > END TRANSACTION; > > Put everything in the sql file into one transaction? Or do I need

Re: [sqlite] Transactions and attached databases

Hi Donald, On Feb 25, 2009, at 3:39 PM, Griggs, Donald wrote: > Greetings, Tito, > > Did you see page: >http://www.sqlite.org/lang_attach.html > > > Transactions involving multiple attached databases are atomic, > assuming > that the main database is not ":memory:". > > It then goes on to

Re: [sqlite] Transactions and attached databases

Hi Donald, On Feb 25, 2009, at 3:39 PM, Griggs, Donald wrote: > Greetings, Tito, > > Did you see page: >http://www.sqlite.org/lang_attach.html > > > Transactions involving multiple attached databases are atomic, > assuming > that the main database is not ":memory:". > > It then goes on to

Re: [sqlite] Transactions and attached databases

-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tito Ciuro Sent: Wednesday, February 25, 2009 6:36 PM To: sqlite-users@sqlite.org Subject: [sqlite] Transactions and attached databases Hello, If I attach one or more

Re: [sqlite] Transactions on attached databases

It's a very useful API, thanks a lot. -- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Dec 12, 2008, at 5:26 PM, D. Richard Hipp wrote: > > On Dec 12, 2008, at 11:16 AM, Marco Bambini wrote: > >> I have two databases, db1 and db2.

Re: [sqlite] Transactions on attached databases

On Dec 12, 2008, at 11:16 AM, Marco Bambini wrote: > I have two databases, db1 and db2. > At some point I attach db2 to db1 as 'destdb' then I do: > > sqlite3_exec(db1, "BEGIN", ...); > sqlite3_exec(db1, "INSERT INTO destdb.table1 SELECT * FROM > main.table1", ...); > sqlite3_exec(db1, "INSERT

Re: [sqlite] Transactions and Threads

Shawn Wilsher <[EMAIL PROTECTED]> wrote: > I'm looking to clarify the behavior of transactions when it comes to > threads. When using the same sqlite3 object, and you begin a > transaction on one thread, does it also group work that is being done > on another thread until you end the transaction?

Re: [sqlite] Transactions across attached databases

"Nakarada, Bob" <[EMAIL PROTECTED]> wrote: > Could someone definitively answer the following question about version > 2.8 of SQLite and transactions on an attached database? > > Is this expected behaviour? > At this point the "expected behavior" of 2.8 is whatever it does. If somebody

RE: [sqlite] Transactions and 'library routine called out of sequence' locks the database

:) /Michael -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 9. maj 2006 12:26 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Transactions and 'library routine called out of sequence' locks the database "Michael B. Hansen" <[EMAIL PROT

Re: [sqlite] Transactions and 'library routine called out of sequence' locks the database

"Michael B. Hansen" <[EMAIL PROTECTED]> wrote: > > However, I keep getting 'library routine called out of sequence' at > random interval. > This is caused when you do things like try to use a database connection that has already been closed or try to use a prepared statement that has already

RE: [sqlite] Transactions and 'library routine called out of sequence' locks the database

continues. -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: 8. maj 2006 17:12 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Transactions and 'library routine called out of sequence' locks the database Michael B. Hansen wrote: > I use my own global locking-mechan

Re: [sqlite] Transactions and 'library routine called out of sequence' locks the database

Michael B. Hansen wrote: I use my own global locking-mechanism, so only one connection can be opened at one time. Michael, Is it possible that your "own" locking mechanism is failing? You haven't said how the locks are implemented, and getting mutual exclusion stuff right in roll your own

Re: [sqlite] Transactions and sqlite3_last_insert_rowid

Dennis Cote wrote: What happens to the ROWID when the transaction is rolled back? Is it as if the record with that ROWID was deleted? Yes If I understand it correctly, connection C1 can do an INSERT, get ROWID 4, C2 does an INSERT, gets 5, and commits, and then C1 commits, with its 4; if

Re: [sqlite] Transactions and sqlite3_last_insert_rowid

Michael Ekstrand wrote: [EMAIL PROTECTED] wrote: The ROWID is generated after the BEFORE triggers fire and before the row is inserted. OK, I think I'm getting it. The INSERT statement generates the ROWID, fires triggers, then inserts the row. When the transaction is committed, all

Re: [sqlite] Transactions and sqlite3_last_insert_rowid

[EMAIL PROTECTED] wrote: The ROWID is generated after the BEFORE triggers fire and before the row is inserted. OK, I think I'm getting it. The INSERT statement generates the ROWID, fires triggers, then inserts the row. When the transaction is committed, all that is committed; when the

Re: [sqlite] Transactions and sqlite3_last_insert_rowid

Michael Ekstrand <[EMAIL PROTECTED]> wrote: > Will Leshner wrote: > > On 4/17/06, Michael Ekstrand <[EMAIL PROTECTED]> wrote: > > > >> So, if someone could enlighten me as to the defined behavior of > >> sqlite3_last_insert_rowid with regards to transactions, I would be most > >> grateful. > > >

Re: [sqlite] Transactions and sqlite3_last_insert_rowid

Will Leshner wrote: On 4/17/06, Michael Ekstrand <[EMAIL PROTECTED]> wrote: So, if someone could enlighten me as to the defined behavior of sqlite3_last_insert_rowid with regards to transactions, I would be most grateful. The last insert id is a property of a connection. So there is no way

Re: [sqlite] Transactions and sqlite3_last_insert_rowid

On 4/17/06, Michael Ekstrand <[EMAIL PROTECTED]> wrote: > So, if someone could enlighten me as to the defined behavior of > sqlite3_last_insert_rowid with regards to transactions, I would be most > grateful. The last insert id is a property of a connection. So there is no way that one connection

Re: [sqlite] Transactions

On Thu, 6 Oct 2005, Jens Miltner wrote: > >Am 05.10.2005 um 13:17 schrieb Christian Smith: > >> On Tue, 4 Oct 2005, Martin Engelschalk wrote: >> >> >>> Hello Christian, >>> >>> thank you, but synchronous is already off. What i aim to avoid is >>> writing the rollback - journal. In order to

Re: [sqlite] Transactions

Am 05.10.2005 um 13:17 schrieb Christian Smith: On Tue, 4 Oct 2005, Martin Engelschalk wrote: Hello Christian, thank you, but synchronous is already off. What i aim to avoid is writing the rollback - journal. In order to rollback, some additional writing to disk is surely unaviodable.

Re: [sqlite] Transactions

On Tue, 4 Oct 2005, Martin Engelschalk wrote: >Hello Christian, > >thank you, but synchronous is already off. What i aim to avoid is >writing the rollback - journal. In order to rollback, some additional >writing to disk is surely unaviodable. You'll have to write your own pager layer, as there

Re: [sqlite] Transactions

Hello Christian, thank you, but synchronous is already off. What i aim to avoid is writing the rollback - journal. In order to rollback, some additional writing to disk is surely unaviodable. Martin Christian Smith schrieb: On Tue, 4 Oct 2005, Martin Engelschalk wrote: Hi all, it

Re: [sqlite] Transactions

On Tue, 4 Oct 2005, Martin Engelschalk wrote: >Hi all, > >it may sound strange, but I do not need transactions. Also i do not >care if the database is corrupted in case of a program or system crash. >So: is it possible to disable transactions in sqlite? Mr. Mark Allan >seems to have done this.

Re: [sqlite] Transactions and in-memory databases

--- Dan Kennedy <[EMAIL PROTECTED]> wrote: > > --- Cory Nelson <[EMAIL PROTECTED]> wrote: > > If I open two handles to "sqlite.db", and attach a :memory: database > > to one of them, then start a transaction on the handle without the > > :memory: database, will this prevent me from using the

Re: [sqlite] Transactions and in-memory databases

--- Cory Nelson <[EMAIL PROTECTED]> wrote: > If I open two handles to "sqlite.db", and attach a :memory: database > to one of them, then start a transaction on the handle without the > :memory: database, will this prevent me from using the :memory: > database in the other handle until the