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,
Hi,
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 using SQLite for a web service. The database reference is passed to
Page objects, which handle their specific
On 7 Sep 2014, at 3:22am, Richard Warburton rich...@skagerraksoftware.com
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 transaction some
On Saturday, 6 September, 2014, at 20:23, Richard Warburton
rich...@skagerraksoftware.com 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 using
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
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
are you using only one database connection? One presumes that you may have
half-a-million pages and half-a-billion concurrent HTTP operations, in
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 are you using only one database connection? One presumes
that you may have half
I need to modify all the content in a table. So I wrap the modifications
inside a transaction to ensure either all the operations succeed, or none
do. I start the modifications with a DELETE statement, followed by INSERTs.
What I've discovered is even if an INSERT fails, the DELETE has still
On Tue, May 6, 2014 at 2:31 PM, Jim Carroll j...@carroll.com 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 want ON CONFLICT
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 first
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 to)..it
did
On Tue, May 6, 2014 at 2:59 PM, Clemens Ladisch clem...@ladisch.de 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
instead.
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
It would appear the DELETE was successful
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 statement,
_step() it, _reset() it.
Repeat 3)
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 statement,
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 operations associated with the current database connection complete.
How does SQLite know when ALL
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 operations
On 2 May 2012, at 12:16pm, Lars Hansen la...@me.com 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 first
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
Do transactions speed up SELECT statements?
--
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning. - Rich Cook
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
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 danielk1...@gmail.com wrote:
On 11/22/2011 04:34
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 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
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 out some
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
Hey guys, thanks for all the help so far today.
From within a BEGIN TRANSACTION and END TRANSACTION block, should I not
update the same row in a table more than once? What are the exact
limitations on what I can do during a Transaction?
Thanks,
Ian
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 statement
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 during a
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.
What are the exact
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 update it as many
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 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
won't see
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
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
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 10,100,1000 or 1. . Otherwise, SQLITE by
default
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 10,100,1000
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 same
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 returned,
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
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 do all
.
--
View this message in context:
http://old.nabble.com/SQLITE-transactions-failing-with-multiple-threads-tp30340806p30340806.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http
Quoth cricketfan srtedul...@yahoo.co.in, on 2010-11-30 07:49:36 -0800:
Also not that both threads are
using the same handle passed by main.
No, don't do that. Using the same handle in two threads concurrently
can break depending on the SQLite threading mode, and will gain you no
parallelism in
On 30 Nov 2010, at 3:49pm, cricketfan wrote:
I have 2 threads in my program, 1st thread is doing inserts into a
table and 2nd thread is trying to update the already inserted columns.
1. I have bundled the 1000 inserts per transaction in 1st thread.
2. When I try to start a
it works. Aside from that, transaction
state is bound to a handle; you're starting a transaction and then
trying to start another one inside it.
Open two handles instead.
--
View this message in context:
http://old.nabble.com/SQLITE-transactions-failing-with-multiple-threads
Quoth cricketfan srtedul...@yahoo.co.in, on 2010-11-30 12:11:52 -0800:
Drake, I am using SQLITE in threadsafe mode. Transaction inside another
transaction isnt that equivalent of nested transactions? Should that be
allowed?
SQLite has named savepoints, but not nested BEGIN transactions. It's
-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
View this message in context:
http://old.nabble.com/SQLITE-transactions-failing-with-multiple-threads-tp30340806p30343885.html
Sent from the SQLite mailing list archive at Nabble.com
On 30 Nov 2010, at 8:11pm, cricketfan wrote:
Drake, I am using SQLITE in threadsafe mode. Transaction inside another
transaction isnt that equivalent of nested transactions? Should that be
allowed? I have no problem opening another handle but just trying to
understand the intricacies,
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 to put the
BEGIN/END TRANSACTION statements in the sql file
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 to put
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 wanting
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 in your
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 file
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 file, read
a
I need to be able to see the committed version of a database while a new
transaction is in the works. I would like to open the same database file twice
in a single thread, start a transaction on one of the database connections,
make a few writes to that database, then on the other database
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 say:
If the
Hello,
If I attach one or more databases and wrap a series of operations
which affect some/all of them, would ROLLBACK or COMMIT treat these
operations atomically? For example:
Open database 'foo';
Attach database 'bar' as a1;
BEGIN TRANSACTION;
INSERT INTO main.some_table ... ;
-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
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 say:
If the
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 INTO destdb.table2 SELECT * FROM
main.table2, ...);
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 INTO
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.
At
Hey all,
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? Or is it the case that each thread
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?
Yes.
With SQLite 3.5.1, when a connection is shared between two threads, if
the second thread attempts to begin a transaction while a transaction is
in progress on the first thread, sqlite3_exec( begin transaction )
returns SQLITE_ERROR with a status message cannot start a transaction
within a
Could someone definitively answer the following question about version
2.8 of SQLite and transactions on an attached database?
Following is pseudo code' for the type of transactions we issue. The
statement to the table in the attached database sometimes fails with a
busy error; the busy wait
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 finds a bug in
:)
/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 PROTECTED] wrote:
However
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-mechanism, so
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 been
Hi,
I have this problem - that keeps hunting me.
I use SQLite 3.3.5 - with all queries, updates and inserts in
transactions. I use my own global locking-mechanism, so only one
connection can be opened at one time.
However, I keep getting 'library routine called out of sequence' at
random
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
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
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
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.
The last insert id
[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
I'm trying to figure out how sqlite3_last_insert_rowid() interacts with
transactions.
I've read the thread at
http://www.mail-archive.com/sqlite-users@sqlite.org/msg13393.html
However, the concern raised in the last message of that thread is
exactly my concern, and was never addressed in the
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 can
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.
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 rollback, some additional
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 is
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
semms to have done this. Could i speed up my writes this way?
Thanks,
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. Could i speed up my writes this way?
Thanks,
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. Could i
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
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 transaction finishes?
--
Cory Nelson
http://www.int64.org
--- 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 transaction
--- 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 :memory:
88 matches
Mail list logo
