Re: [sqlite] Transactions

[Jens Alfke]

> 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 streaming audio data from the 
filesystem. (And Final Cut was just streaming video frames to the filesystem.)

My point is that the disk-controller flush invoked by SQLite’s commit caused 
the entire filesystem to become unavailable for tens-to-hundreds of 
milliseconds at a time, and when I started doing that multiple times a second, 
it would sometimes starve iTunes’ audio threads of data, causing dropouts.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transactions

[James K. Lowden]

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 frames. 

You should turn that into a war story for "Coders at Work" or
something.  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.  

--jkl
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transactions

[Nathan Bossett]

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 not interdependent statements for performance reason? and if yes do
> you think there's a trade-off about the number of query number in each
> transaction? 
> 
> I'm think about 1000 INSERT query to execute, is transaction works
> better because you do not have to change index at any insert but just
> one time on commit?

Anecdotally, I can say that I've experimented and see huge speedups in 
building up a database on both HDD's with a few tens of megabytes of cache 
and on SSD's by batching up individual INSERTs and UPDATEs into groups 
through a transaction.

I've experimented with batching various numbers of INSERTS when 
building up a new database from another local data source and wound 
up with 100 or 1000, which may not be optimal but was fast enough.

-Nathan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transactions

[Jens Alfke]

> 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 flush at the end of the commit can cause the hardware disk 
controller to block for “a long time” (tens or hundreds of ms) while it writes 
all the blocks from its internal cache to the physical storage medium. This can 
be bad for real-time threads that are dependent on disk I/O.

Ten years ago, back when I worked at Apple and was first using SQLite, I was 
too eager about running transactions. 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 frames. Fortunately this was caught early on by 
internal testers, and I tweaked my insertion code to batch up changes into 
larger more widely-spaced transactions before release.

[Disclaimer: Some of this may be not be true anymore. SSDs have different 
performance characteristics than hard disks, and OS kernels have advanced. But 
it’s still true that achieving durability is expensive and has trade-offs.]

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transactions

[Hick Gunter]

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 setup and 
who else needs access to CPU and I/O resources.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Michele Pradella
Gesendet: Sonntag, 05. Februar 2017 09:42
An: SQLite mailing list 
Betreff: [sqlite] Transactions

Hi all, I have a question about transactions and SQLite:

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 not 
interdependent statements for performance reason? and if yes do you think 
there's a trade-off about the number of query number in each transaction?

I'm think about 1000 INSERT query to execute, is transaction works better 
because you do not have to change index at any insert but just one time on 
commit?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transactions

[Clemens Ladisch]

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
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transactions for read operations

[Richard Warburton]

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, I'm using a pool.  I've been thinking about using WAL, but I had
reservations as it is not enabled by default.  I'll have another look at it.

Darren: I hadn't been considering transaction time.  Now it occurs to me
that writing data back to the user could indeed take time before the page
code returns.  Rather than add complexity to the page objects, I'm thinking
about buffering their writes, so the transaction can be closed before any
data goes over a network.  This has other implications, so I'll think about
this further, but I'd rather keep such considerations in one place rather
than in each page object.

Just to be clear, each page is stateless and many pages could be called at
the same time.  The question revolves around wrapping each page request in
its own transaction - so each of the many page objects need not worry about
such things and can focus on extracting and updating data.

Your tangent conflicts with Simon, so I'm not sure who's right - I see both
points - I'll have to think about this more.  My database library does not
support read-only transactions, which does simplify things (for better or
worse).  I could (if deemed beneficial) automate a rollback if no exec were
called, instead of commit.

Thanks again.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transactions for read operations

[Keith Medcalf]

No.  Apache uses a worker process pool where each forked worker process 
contains one or more service threads.  Each concurrent http operation is 
dispatched to a separate thread (which may only comprise one thread per 
process).  The worker service model is serially entrant.  So you would need one 
connection per service thread.  Processes are not created and destroyed around 
each http operation, now are threads created and destoryed.  When a new worker 
process is needed, it is forked and then does its long initialization process 
including starting and initializing however many service threads are allocated 
per process.  When the number of workers in the pool needs to be decreased 
(because it is no longer needed for the past several minutes) then the 
expensive close operations are performed which spins down all the threads and 
releases all the long held resources needed by that worker process.  Neither 
processes nor threads are created and destroyed willy nilly per request.


>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Simon Slavin
>Sent: Saturday, 6 September, 2014 21:13
>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 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 which case you will have significant multi-leaving of
>operations. Or is your server single threaded and can only answer one
>HTTP operation at a time?
>
>Apache creates a separate process to reply to each page request.  If you
>have Apache replying to half-a-billion concurrent HTTP requests, you will
>have half-a-billion processes.  (Presumably on many different computers
>in a web farm.)  If you're using PHP to access your SQLite database file,
>each process would open its own connection to the database.  That's how
>it works, and it works fine on small and medium-power web hosts.
>
>That many connections would be impossible for SQLite, of course.  But if
>your web host has half-a-billion concurrent HTTP operations your multiple
>web hosts will be using Postgres or some other client/server DBMS, not
>SQLite.
>
>Simon.
>___
>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] Transactions for read operations

[Simon Slavin]

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, in which 
> case you will have significant multi-leaving of operations. Or is your server 
> single threaded and can only answer one HTTP operation at a time?

Apache creates a separate process to reply to each page request.  If you have 
Apache replying to half-a-billion concurrent HTTP requests, you will have 
half-a-billion processes.  (Presumably on many different computers in a web 
farm.)  If you're using PHP to access your SQLite database file, each process 
would open its own connection to the database.  That's how it works, and it 
works fine on small and medium-power web hosts.

That many connections would be impossible for SQLite, of course.  But if your 
web host has half-a-billion concurrent HTTP operations your multiple web hosts 
will be using Postgres or some other client/server DBMS, not SQLite.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transactions for read operations

[Darren Duncan]

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 fraction of a second in 
typical cases.


If you are wanting to do something that involves waiting for users, say, 
especially remote or web users, you should not be holding a transaction open 
while waiting for a user; doing so is generally a design problem with your 
application and you should change it so you use some other method for 
longer-term consistency.


In a web context, web applications are supposed to be stateless, and you should 
not have a database transaction shared between multiple web client requests.


The only common situation where its reasonable to have a transaction open for 
more than a split second is if that involves a single database-bound operation, 
such as a batch insert or a complicated report.  Typical database activity does 
not involve this.


On a tangent, if you know a database operation is only going to read, you should 
be using a read-only transaction; commit/rollback is only meaningful if you 
actually make a change.  Barring that you did this, if you don't make a change, 
probably a rollback is the correct way to end it, as in theory that's just 
saying, I didn't intend to make any changes, and I want the db to ensure nothing 
actually changed by accident.


-- Darren Duncan

On 2014-09-06, 7:22 PM, Richard Warburton wrote:

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 url path.  Not all pages will
write data, but nearly all do multiple queries, which should be consistent
with eachother.  I was thinking that to simplify each page object's code, I
would pass a transaction reference instead, and then call rollback if the
page object returns an error, commit otherwise.

However, given that the page will read many times more often than it
writes, I'm wondering if this is a bad idea.

Thoughts?



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transactions for read operations

[Keith Medcalf]

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 using SQLite for a web service.  The database reference is passed to
  ^^
>Page objects, which handle their specific url path.  Not all pages will
>write data, but nearly all do multiple queries, which should be consistent
>with eachother.  I was thinking that to simplify each page object's code,
>I would pass a transaction reference instead, and then call rollback if 
>the page object returns an error, commit otherwise.

>However, given that the page will read many times more often than it
>writes, I'm wondering if this is a bad idea.

>Thoughts?

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 which 
case you will have significant multi-leaving of operations.  Or is your server 
single threaded and can only answer one HTTP operation at a time?

The answer to this question is significant because transactions affect database 
connections only -- you may have millions of cursors running millions of 
queries in millions of threads all on the same connection, but the transaction 
context belongs to the connection, not the cursor/statement.  Isolation is at 
the connection level, not the statement.  If you do not have one connection per 
HTTP operation (or a pool of connections where each connection is only used for 
one HTTP operation at a time) then wrapping transactions around HTTP operations 
will not have the result you intend.

Furthermore, making each HTTP operation use a separate transaction on separate 
connections may have a significant effect on concurrency, unless you are using 
WAL.

But yes, if you need database consistency within the processing of each HTTP 
operation, then you need a connection pool and assign a separate connection per 
HTTP operation with transactions around it (ie, start a transaction when you 
get a connection from the pool and commit it when you return it, if it is not 
already rolled back).  You will probably also want to use WAL to increase 
concurrency.





___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transactions for read operations

[Simon Slavin]

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 transaction some way.  I can see why ROLLBACK would 
appeal as a guard against accidental writing, but it's not neat.  COMMIT is 
neater.

A bunch of my web pages need to look up a bunch of things to fill in details on 
the page.  Your idea would make sure that everything was definitely consistent, 
even if it was outdated almost immediately the page was finished being drawn.

I see no reason why you shouldn't do it but you should test to find out what 
happens if someone closes the page before it is finished drawing.  If your code 
is PHP the database will be unlocked because PHP would hold a separate 
connection for each page.  But if your code is JavaScript the lock might 
persist, and you may have to learn how to release it.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] transactions do not respect delete

[Jim Carroll]

> 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, 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 INSERT have
> succeeded.

You are exactly correct (sound of palm slapping my own face).

I was working on a proof of concept using the sqlite command-line shell, and
completely forget the fact that in code, we'd get an exception when we
attempted the second INSERT, at which point it would be up to us to either
invoke COMMIT or ROLLBACK.

Sorry for the foolishness

Jim




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] transactions do not respect delete

[Dominique Devienne]

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
> instead.

But that's only possible for interactive SQL sessions. You cannot
"branch" in SQL "scripts" (successions of SQL commands) one executes
via sqlite3[.exe] at the command line. Using the ROLLBACK conflict
resolution on the insert is the only way to achieve what he wants as
described, short of switching to a "programmatic" environment to run
his "scripts" for full branching, etc... --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] transactions do not respect delete

[Clemens Ladisch]

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 not ROLLBACK the database.

Why should it?  The statement failed, but this does not imply that the
transaction failed.  (Depending on the application, it might be possible
to recover by updating that record, or by ignoring the error.)

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.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] transactions do not respect delete

[Mark Lawrence]

> 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 INSERT have
succeeded.

Typically an application would explicitly call ROLLBACK after a
statement failure if it didn't want the transaction to commit. The
following for example works the way you probably want it to.

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");  
ROLLBACK;

SELECT * FROM A;

It appears your expectation is that if a statement fails then the
transaction is invalid (thereby ignoring the COMMIT). SQLite treats
that situation differently.

-- 
Mark Lawrence
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] transactions do not respect delete

[Dominique Devienne]

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 want ON CONFLICT ROLLBACK. --DD

http://www.sqlite.org/lang_conflict.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transactions

[Igor Tandetnik]

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, 
_step() it, _reset() it.

Repeat 3) many times.

4) Create a third non-parameter "END TRANSACTION" prepared statement, execute 
it with _step(), and finalize it.
5) Finalize the original "INSERT" prepared statement.


Looks perfectly fine to me. That's pretty much the standard operating 
procedure.



Could these prepared statements conflict with each other?   



I don't see why they would.
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] transactions and locking

[Lars Hansen]

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

[Simon Slavin]

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 first answer.  For instance, the 
clearest and most useful answer is to your question 4.

> 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 operations … complete? In PERL DBI context, I 
> may prepare and execute as many statements as I like. When does SQLite commit?

I feel that that text is poorly worded.  It seems to mean that the changes are 
committed when the connection to the database is closed, i.e. when _close() is 
used.  This is not what happens.  What it actually means is that the changes 
are committed when SQLite returns to autocommit mode.

> 2. "The SQL command "COMMIT" does not actually commit the changes to disk. It 
> just turns autocommit back on."
> 
> Is AutoCommit enabled after every commit? Do I have to disable AutoCommit 
> again after a commit to have it be disabled?

Autocommit is usually on.  You turn it off by explicitly opening a transaction 
yourself.  Commmitting the transaction doesn't actually do any of the hard 
work, it just turns autocommit back on.  autocommit immediately notices that 
there are pending operations to commit, and does the associated work.

> 3. "If the SQL COMMIT command turns autocommit on and the autocommit logic 
> then tries to commit change but fails because some other process is holding a 
> SHARED lock, then autocommit is turned back off automatically. This allows 
> the user to retry the COMMIT at a later time after the SHARED lock has had an 
> opportunity to clear."
> 
> If I had AutoCommit disabled and the commit fails, I have to retry to commit 
> myself. Is it possible (AutoCommit enabled or disabled) to have commits get 
> queued and executed when possible by SQLite so that I don't have to write 
> code myself but can rely on the order of commits (per connection)?

No.  Because if your commit is going to fail, which it might do after a long 
delay, your application will need to know that it has failed before it is able 
to proceed with other operations.  In other words, if commits got queued up and 
executed later, you might have to undo any number of subsequent transactions.  
And that would require your application to keep track of them all, which would 
be difficult and annoying to program.

However this normally does all come out in the wash.  If you think about which 
database changes you want in the same transaction, you will find that your 
natural programming style leads to you wanting to know, when you issue your 
"COMMIT" to close the transaction, whether the commit succeeded or failed.

> 4. "By default, SQLite version 3 operates in autocommit mode."
> 
> Is SQLite transactional by default? Do I have to use transactions? I'd like 
> to. Anything besides begin and commit I have to consider?

You should generally be using transactions with any dependent set of changes.  
However, if you have not declared your own transaction, and do an UPDATE or 
INSERT or DELETE, SQLite automatically wraps your operation in a 'BEGIN … op … 
COMMIT' for you.  If the COMMIT it inserts after your operation results in an 
error, it returns that error for the operation rather than looking for a COMMIT 
you never issued.

> 5. Does a "SELECT …" require a transaction begin and commit to transfer data?

Although SELECT makes no changes to the database, in respect of database 
locking SELECT operates as described in my answer to question 4.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] transactions and locking

[Jay A. Kreibich]

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 associated with the current database connection
> complete."
> 
> How does SQLite know when ALL operations … complete? In PERL DBI context,
> I may prepare and execute as many statements as I like. When does
> SQLite commit?

  When the last active statement finishes execution.  Prepared
  statements don't count, only active ones.

  Many programs only have one active statement at a time, but it can
  get more complex.

  Basically, SQLite keeps a reference count of how many statements are
  actually running.  Any time that reference count hits zero, the
  database state goes through a commit.

> 2. "The SQL command "COMMIT" does not actually commit the changes to
> disk. It just turns autocommit back on."
> 
> Is AutoCommit enabled after every commit?

  Yes.

  Any time there is not an explicit transaction open, autocommit is
  enabled.

> Do I have to disable AutoCommit again after a commit to
> have it be disabled?

  BEGIN.  Start a new explicit transaction and autocommit will be
  disabled.

> 3. "If the SQL COMMIT command turns autocommit on and the autocommit
> logic then tries to commit change but fails because some other process
> is holding a SHARED lock, then autocommit is turned back off
> automatically. This allows the user to retry the COMMIT at a later
> time after the SHARED lock has had an opportunity to clear."
> 
> If I had AutoCommit disabled and the commit fails, I have to retry
> to commit myself.

  Having and open transaction (e.g. a BEGIN) and having autocommit
  disabled are the same thing.  You can't have one without the other.

  And yes, if COMMIT fails, the transaction is (usually) still open
  and if you want to proceed you have to manually retry.

> Is it possible (AutoCommit enabled or disabled) to have commits get
> queued and executed when possible by SQLite so that I don't have
> to write code myself but can rely on the order of commits (per connection)?

  Again, if you're inside an explicit transaction (BEGIN/COMMIT) then
  autocommit is always disabled.

  SQLite can, to a degree, retry COMMIT statements.  But since there is
  no server to coordinate or queue requests, it isn't fully automatic,
  nor does it always work.

  See http://sqlite.org/c3ref/busy_handler.html

> 4. "By default, SQLite version 3 operates in autocommit mode."
> 
> Is SQLite transactional by default?

  Yes.  Every command is run in the context of a transaction-- either
  an explicit one using the BEGIN/COMMIT commands, or an implicit,
  automatic autocommit transaction.

> Do I have to use transactions?

  Explicit BEGIN/END transactions?  No.  If you don't, SQLite will wrap
  your commands in an automatic transaction.

> I'd like to. Anything besides begin and commit I have to consider?

  Locking.  Read: http://sqlite.org/lockingv3.html

> 5. Does a "SELECT …" require a transaction begin and commit to transfer data?

  SELECT requires a transaction (for locking) but not an explicit
  BEGIN/COMMIT transaction.  If you do not wrap SELECT in a BEGIN/COMMIT
  block, SQLite will-- as with any command-- wrap it in an automatic
  transaction.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transactions for SELECT

[Dan Kennedy]

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 any type, in an
explicit transaction.





On Tue, Nov 22, 2011 at 11:41 AM, Dan Kennedy  wrote:


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 times.

Best to experiment to find out if this is significant for your
app.

__**_
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] Transactions for SELECT

[Simon Slavin]

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, then an unlock.  So you're only out 
the time of two very short operations.

Slow SELECTs are most probably the result of the lack of an index which is good 
for the SELECT.  I see many examples where people have indexed each column 
individually, and that isn't a good way to do it.

Another thing that can do it is using "SELECT * ..." when you could specify 
individual columns.  And if you're expecting 10,000 records, then another is 
poor memory handling, by reading the entire results of a SELECT into memory 
rather than reading a row, processing it, then reading the next row, etc..

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transactions for SELECT

[Baruch Burstein]

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 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 times.
>
> Best to experiment to find out if this is significant for your
> app.
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>



-- 
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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transactions for SELECT

[Dan Kennedy]

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 times.

Best to experiment to find out if this is significant for your
app.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TRANSACTIONs

[Simon Slavin]

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 UPDATE modifies a field in your ORDER BY clause of one row.  It could move 
the row you're currently on, moving it back to the beginning of the sort order. 
 Or it could move it to the end of the sort order.  Or it could move a row you 
haven't got to yet, to a position you've already gone past.  When the next 
_step() moves to the 'next' record it might end up somewhere completely 
different to where you expected.

In all these cases, without knowing undocumented internal details about how 
that version of SQLite works, you can't predict what will happen.  Your SELECT 
might skip one or more rows: not just a row you modified but other untouched 
rows.  Or it might read some rows twice: not just a row you modified but other 
untouched rows.  Or it might be completely immune to the problem having fetched 
and stored row indexes somewhere.  So don't do that.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TRANSACTIONs

[Jan Hudec]

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 details to make things simple.
> 
> Suppose you do a SELECT ... WHERE ... that returns ten rows.  You retrieve
> three rows, then make a change that would mean you should have retrieved
> eleven rows, not ten.  You can't predict what SQLite will do without
> knowing the internal workings of SQLite, right ?  So don't do that.  The
> same is true even if the only thing you change is values to be returned.
> Does SQLite copy the all values when you execute the SELECT, or row-by-row
> as you step through the results ?  Again, you don't know unless you know
> the internal workings of SQLite.  So don't do that.

Oh, right. There are actually two distinct problems:

 - Inside one transaction, running select may or may not see data inserted or
   updated in parallel. For selects on other handles it's however well
   defined that they will not read the data and no operations will fail.

 - Dropping or modifying table will fail (with "table is locked" status) if
   there are any running statements involving that table.

-- 
 Jan 'Bulb' Hudec 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TRANSACTIONs

[Simon Slavin]

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 'A': Atomic.  A 
SELECT should really be a single operation which gathers all the data and 
stores it somewhere.  The problems with this are when the SELECT gathers a lot 
of data: first you get one command taking a long time, which can make your GUI 
jerky, and second you have to store the result set, so you need as much spare 
memory as the entire result set takes up.  Which can be gigabytes.

So the solution is to implement SELECT atomically in your programming.  Don't 
do any other SQL commands until you've done the last _step().

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TRANSACTIONs

[Pavel Ivanov]

> 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 uncommitted data in the same transaction.


Pavel


On Wed, Jun 1, 2011 at 2:57 PM, Robert Myers  wrote:
> 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 any data committed in other transactions. But it doesn't
>> impose any restrictions on how statements should behave within the
>> same transaction. And in SQLite it's pretty unpredictable and so even
>> SQLite's documentation prohibits updating some table while select
>> statement on it is active (I can find a link on sqlite.org for you
>> when I have some spare time).
> What about the I of ACID? The select should have an implicit transaction
> around it.
>
> ___
> 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] TRANSACTIONs

[Robert Myers]

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 any data committed in other transactions. But it doesn't
> impose any restrictions on how statements should behave within the
> same transaction. And in SQLite it's pretty unpredictable and so even
> SQLite's documentation prohibits updating some table while select
> statement on it is active (I can find a link on sqlite.org for you
> when I have some spare time).
What about the I of ACID? The select should have an implicit transaction
around it.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TRANSACTIONs

[Pavel Ivanov]

> 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. But it doesn't
impose any restrictions on how statements should behave within the
same transaction. And in SQLite it's pretty unpredictable and so even
SQLite's documentation prohibits updating some table while select
statement on it is active (I can find a link on sqlite.org for you
when I have some spare time).


Pavel


On Wed, Jun 1, 2011 at 2:41 PM, Robert Myers  wrote:
> 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 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 currently
>>> Actually insert, update and delete are OK. Drop and alter table are
>>> a problem.
>> Pavel is right.  He left out some details to make things simple.
>>
>> Suppose you do a SELECT ... WHERE ... that returns ten rows.  You retrieve 
>> three rows, then make a change that would mean you should have retrieved 
>> eleven rows, not ten.  You can't predict what SQLite will do without knowing 
>> the internal workings of SQLite, right ?  So don't do that.  The same is 
>> true even if the only thing you change is values to be returned.  Does 
>> SQLite copy the all values when you execute the SELECT, or row-by-row as you 
>> step through the results ?  Again, you don't know unless you know the 
>> internal workings of SQLite.  So don't do that.
>>
>> Simon.
>> ___
>
> 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.
> ___
> 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] TRANSACTIONs

[Robert Myers]

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 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 currently
>> Actually insert, update and delete are OK. Drop and alter table are
>> a problem.
> Pavel is right.  He left out some details to make things simple.
>
> Suppose you do a SELECT ... WHERE ... that returns ten rows.  You retrieve 
> three rows, then make a change that would mean you should have retrieved 
> eleven rows, not ten.  You can't predict what SQLite will do without knowing 
> the internal workings of SQLite, right ?  So don't do that.  The same is true 
> even if the only thing you change is values to be returned.  Does SQLite copy 
> the all values when you execute the SELECT, or row-by-row as you step through 
> the results ?  Again, you don't know unless you know the internal workings of 
> SQLite.  So don't do that.
>
> Simon.
> ___

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.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TRANSACTIONs

[Simon Slavin]

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 limitations on what I can do during a Transaction?
>> 
>> 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 details to make things simple.

Suppose you do a SELECT ... WHERE ... that returns ten rows.  You retrieve 
three rows, then make a change that would mean you should have retrieved eleven 
rows, not ten.  You can't predict what SQLite will do without knowing the 
internal workings of SQLite, right ?  So don't do that.  The same is true even 
if the only thing you change is values to be returned.  Does SQLite copy the 
all values when you execute the SELECT, or row-by-row as you step through the 
results ?  Again, you don't know unless you know the internal workings of 
SQLite.  So don't do that.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TRANSACTIONs

[Jan Hudec]

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 Transaction?
> 
> 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.

> active and using it. Active means you made at least one call to
> sqlite3_step and last call to sqlite3_step returned SQLITE_ROW.

-- 
 Jan 'Bulb' Hudec 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TRANSACTIONs

[Pavel Ivanov]

>  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 currently
active and using it. Active means you made at least one call to
sqlite3_step and last call to sqlite3_step returned SQLITE_ROW.


Pavel


On Wed, Jun 1, 2011 at 10:00 AM, Ian Hardingham  wrote:
> 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
> ___
> 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] Transactions while using prepare and step

[Simon Slavin]

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 the dating with a single UPDATE command that has a less specific 
WHERE clause ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transactions while using prepare and step

[Pavel Ivanov]

> 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 wrong. First of all LIMIT ... OFFSET ... clauses have undefined
results if you don't have ORDER BY clause. And second although you are
selecting only one row SQLite should scan and count all i rows to
return the (i + 1)th. So you better select everything, remember it and
then update it one-by-one (preferably in smallest number of
transactions possible).

And it's not understandable from your pseudo code what is "step" and
does thread 1 execute 1 insert statement or several in a loop (which I
suppose you intended to show).


Pavel

On Thu, Dec 2, 2010 at 2:38 PM, cricketfan  wrote:
>
> 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;
> while(step)
> {
>     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
> }
> }
>
> If I run these 2 threads individually the tps is acceptable but if I run
> them together the performance is woeful. I am running in serialized mode.
> Both threads use different handles to DB. Please advice what I can do in
> order to improve performance.
>
> 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.
>
>
> 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 or 1. . Otherwise, SQLITE by
>> default commits for every single insert,update,delete.
>> I have a prepare statement(using limit clause so will get only 10 rows
>> back) followed by step(during which I use an update clause) so how would
>> the transaction concept behave in this case? If I wrap the loop I use for
>> stepping with a begin transaction(followed by an end transaction  when the
>> loop ends) would it be sufficient?
>>
>
> --
> View this message in context: 
> http://old.nabble.com/Transactions-while-using-prepare-and-step-tp30359695p30361890.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> 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] Transactions while using prepare and step

[cricketfan]

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;
while(step)
{
 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
}
}

If I run these 2 threads individually the tps is acceptable but if I run
them together the performance is woeful. I am running in serialized mode.
Both threads use different handles to DB. Please advice what I can do in
order to improve performance.

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.


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 or 1. . Otherwise, SQLITE by
> default commits for every single insert,update,delete.
> I have a prepare statement(using limit clause so will get only 10 rows
> back) followed by step(during which I use an update clause) so how would
> the transaction concept behave in this case? If I wrap the loop I use for
> stepping with a begin transaction(followed by an end transaction  when the
> loop ends) would it be sufficient?
> 

-- 
View this message in context: 
http://old.nabble.com/Transactions-while-using-prepare-and-step-tp30359695p30361890.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transactions while using prepare and step

[Simon Slavin]

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, or could be returned
> unless/after you updated them. Other than that it's not a big deal.

Yep, fair point.  But I read his question as strongly suggesting that he's 
updating the rows that are returned.  I could be wrong, though.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transactions while using prepare and step

[Pavel Ivanov]

> 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 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, or could be returned
unless/after you updated them. Other than that it's not a big deal.

Answering original question depending on particular select/update
statements SQLite can behave the same way when you wrap the whole loop
in transaction and when you don't. But if you use "BEGIN TRANSACTION"
or don't use transactions at all be ready that updates can return
SQLITE_BUSY in such situation because a deadlock can be detected. To
avoid such situation start transaction before the loop using "BEGIN
IMMEDIATE".


Pavel

On Thu, Dec 2, 2010 at 11:12 AM, Simon Slavin  wrote:
>
> 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 or 1. . Otherwise, SQLITE by
>> default commits for every single insert,update,delete.
>
> So far, very good.
>
>> I have a prepare statement(using limit clause so will get only 10 rows back)
>> followed by step(during which I use an update clause) so how would the
>> transaction concept behave in this case?
>
> It doesn't.  You will get some results from SQLite by doing
>
> SELECT
> step to record 1
> UPDATE record 1
> step to record 2
> UPDATE record 2
> step to record 3
> UPDATE record 3
> ...
>
> but it means nothing in terms of SQL and precisely what it does may change 
> between SQL engines and different versions of SQL engines.  I might even 
> argue that SQL engines should produce an error message when people try to do 
> things like this.  There are two possible solutions:
>
> A) The UPDATE command has a WHERE clause so put the SELECT criteria in that 
> WHERE clause.  This will enable you to roll both commands into one single 
> UPDATE command.  This is the simplest solution, and will execute the most 
> quickly and efficiently.
>
> B) If that solution is not available because the calculations cannot be 
> completed inside SQL, do the SELECT first, read the entire list of results 
> into an array (list ?) in your chosen programming language, and only when the 
> SELECT is finished, start looking through the results and doing UPDATE 
> commands.  You can, of course, issue a BEGIN TRANSACTION before the SELECT, 
> and an END TRANSACTION after the last UPDATE.
>
> Both (A) and (B) are sound procedures and their results are clearly defined 
> and will give the same results across all SQL engines and all versions of SQL 
> engines (unless they're buggy !).
>
>> If I wrap the loop I use for
>> stepping with a begin transaction(followed by an end transaction  when the
>> loop ends) would it be sufficient?
>
> 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.
>
> Simon.
> ___
> 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] Transactions while using prepare and step

[Simon Slavin]

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 or 1. . Otherwise, SQLITE by
> default commits for every single insert,update,delete.

So far, very good.

> I have a prepare statement(using limit clause so will get only 10 rows back)
> followed by step(during which I use an update clause) so how would the
> transaction concept behave in this case?

It doesn't.  You will get some results from SQLite by doing

SELECT
step to record 1
UPDATE record 1
step to record 2
UPDATE record 2
step to record 3
UPDATE record 3
...

but it means nothing in terms of SQL and precisely what it does may change 
between SQL engines and different versions of SQL engines.  I might even argue 
that SQL engines should produce an error message when people try to do things 
like this.  There are two possible solutions:

A) The UPDATE command has a WHERE clause so put the SELECT criteria in that 
WHERE clause.  This will enable you to roll both commands into one single 
UPDATE command.  This is the simplest solution, and will execute the most 
quickly and efficiently.

B) If that solution is not available because the calculations cannot be 
completed inside SQL, do the SELECT first, read the entire list of results into 
an array (list ?) in your chosen programming language, and only when the SELECT 
is finished, start looking through the results and doing UPDATE commands.  You 
can, of course, issue a BEGIN TRANSACTION before the SELECT, and an END 
TRANSACTION after the last UPDATE.

Both (A) and (B) are sound procedures and their results are clearly defined and 
will give the same results across all SQL engines and all versions of SQL 
engines (unless they're buggy !).

> If I wrap the loop I use for
> stepping with a begin transaction(followed by an end transaction  when the
> loop ends) would it be sufficient?

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.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transactions + .read

[Simon Slavin]

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 line, execute it using sqlite3_exec(), read the next line, execute that, 
>> etc..  It shouldn't be much more than a five line loop.  Don't forget to 
>> trap errors.
> 
> Guess I'll adapt some existing code then to wrap all the lines sent in one 
> big transaction.

Just a note that the SQL command files produced by the command-line tool itself 
already have all necessary transaction commands in.  And they have other 
characteristics which may look strange, but actually speed up the execution of 
the SQL commands.  For instance, indices are created after the data is entered 
into the tables.  Where this does have any effect it does lead to smaller 
runtimes.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transactions + .read

[Kevin M.]

>> 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 manually and query one 
>> line at a time?

> No, sorry.  That's what programming languages are for.  Open the file, read a 
> line, execute it using sqlite3_exec(), read the next line, execute that, 
> etc..  It shouldn't be much more than a five line loop.  Don't forget to trap 
> errors.

Guess I'll adapt some existing code then to wrap all the lines sent in one big 
transaction.

> Of course, sqlite3_exec() can execute multiple commands in one go.  So you 
> could open the file, read the whole thing into one huge string, close the 
> file, then execute the string.  That will work perfectly if your have enough 
> memory and disk space.  But if you aren't confident about the size of the 
> text file before you start, it's probably better to do it line by line.

Most of the sql files probably are ok with that approach, but others not so 
much.  Maybe a hybrid approach ... small files all in one go, big ones broken 
up.

> Simon.


  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transactions + .read

[Simon Slavin]

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 program.
>> 
>> Simon.
> 
> 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.

> 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 
line, execute it using sqlite3_exec(), read the next line, execute that, etc..  
It shouldn't be much more than a five line loop.  Don't forget to trap errors.

Of course, sqlite3_exec() can execute multiple commands in one go.  So you 
could open the file, read the whole thing into one huge string, close the file, 
then execute the string.  That will work perfectly if your have enough memory 
and disk space.  But if you aren't confident about the size of the text file 
before you start, it's probably better to do it line by line.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transactions + .read

[Kevin M.]

> 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 to put that into a program.  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?

-- Kevin


  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transactions + .read

[Simon Slavin]

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 the 
> BEGIN/END TRANSACTION statements in the sql file itself?

Either way will work.  Actually I don't know how '.read' works; the code may 
already wrap it inside a transaction, which would make both pointless.  But 
they won't do any harm.

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.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transactions and attached databases

[Tito Ciuro]

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 main database is ":memory:" then transactions continue to be
> atomic within each individual database file. But if the host computer
> crashes in the middle of a COMMIT where two or more database files are
> updated, some of those files might get the changes where others might
> not.

No, I didn't look at that page... sorry about that. I was looking at  
the SQLite list instead for answers. Thank you for the help!

Regards,

-- Tito
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transactions and attached databases

[Tito Ciuro]

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 main database is ":memory:" then transactions continue to be
> atomic within each individual database file. But if the host computer
> crashes in the middle of a COMMIT where two or more database files are
> updated, some of those files might get the changes where others might
> not.

No, I didn't look at that page... sorry about that. I was looking at  
the SQLite list instead for answers. Thank you for the help!

Regards,

-- Tito

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transactions and attached databases

[Griggs, Donald]

 
-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 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 ... ;
  DELETE FROM main.some_table WHERE ... ;
  INSERT INTO a1.another_table ... ; COMMIT/ROLLBACK TRANSACTION;

Would the insertions and deletions be either committed or rolled back
atomically? Is this supported?

Thanks,

-- Tito


===
===

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 main database is ":memory:" then transactions continue to be
atomic within each individual database file. But if the host computer
crashes in the middle of a COMMIT where two or more database files are
updated, some of those files might get the changes where others might
not. 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transactions on attached databases

[Marco Bambini]

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 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", ...);
>> sqlite3_exec(db1, "COMMIT", ...);
>>
>> so, I am actually reading from db1 and writing to db2, but since the
>> two db are attached and the transaction is started in db1, I wonder  
>> if
>> locking on db1 is marked as RESERVED or EXCLUSIVE at some point. (I
>> just would like to know if db1 seems a db with write operations from
>> sqlite's point of view)... and should the transaction be started on
>> db2 or it doesn't matter when the two dbs are attached?
>>
>
> Recompile with -DSQLITE_DEBUG=1.  Then call "PRAGMA lock_status" prior
> to the COMMIT (or any other place when you want to know what the
> status of the locks is) and it will tell you.
>
> Or, from C, call sqlite3_file_control() with the
> SQLITE_FCNTL_LOCKSTATE option for each attached database and you will
> get back the lock status for that database.  See the implementation of
> the "lock_status" pragma for an example.
>
> D. Richard Hipp
> d...@hwaci.com

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transactions on attached databases

[D. Richard Hipp]

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 destdb.table2 SELECT * FROM
> main.table2", ...);
> sqlite3_exec(db1, "COMMIT", ...);
>
> so, I am actually reading from db1 and writing to db2, but since the
> two db are attached and the transaction is started in db1, I wonder if
> locking on db1 is marked as RESERVED or EXCLUSIVE at some point. (I
> just would like to know if db1 seems a db with write operations from
> sqlite's point of view)... and should the transaction be started on
> db2 or it doesn't matter when the two dbs are attached?
>

Recompile with -DSQLITE_DEBUG=1.  Then call "PRAGMA lock_status" prior  
to the COMMIT (or any other place when you want to know what the  
status of the locks is) and it will tell you.

Or, from C, call sqlite3_file_control() with the  
SQLITE_FCNTL_LOCKSTATE option for each attached database and you will  
get back the lock status for that database.  See the implementation of  
the "lock_status" pragma for an example.

D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transactions and Threads

[Igor Tandetnik]

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. Transactions are per-connection, not per-thread.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transactions across attached databases

[drh]

"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 2.8 that can cause database corruption
or which is a realistic security vulnerability, then I'll look into
fixing it.  But beyond that, 2.8 is what it is.  I have no intent
of doing further development on 2.8.  It is maintenance only.
Any strange or goofy behavior is defined to be a feature, not a 
bug.

3.3.8 is current.  Look for 3.3.9 soon.  The world has moved on...

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

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

[Michael B. Hansen]

Hi D. Richard,

Your comments / suggestions made me think and widen my exception
tracking - and it does indeed seem that it is caused by statement that
fails with a NULL-pointer exception when sqlite3_prepare is called. Why
this is I need to figure out - but I suspect that the Finisar.SQLite
wrapper maybe has a weird bug somewhere which causes sqlite3_prepare to
be called with a NULL-pointer (propably the string containing the sql
statement). Why thousands of sql-statements can be run before the
exception is thrown I do not know - but at least I got a bit further.

Thank you for you help :)


/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, 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 finalized or by trying to use the same database handle at
the same time in two different threads.

--
D. Richard Hipp   <[EMAIL PROTECTED]>

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

[drh]

"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
finalized or by trying to use the same database handle
at the same time in two different threads.

--
D. Richard Hipp   <[EMAIL PROTECTED]>

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

[Michael B. Hansen]

Hi Dennis,

Thank you for your reply :)

I'm using SQLite in .NET through Finisar.SQLite wrapper.
The locking mechanism is a static object which I use as a mutex by the
.NET 'lock'-method.
And finally - yes, I open and close the database in each transaction: 

Regarding the API that returns the error - then it's sqlite3_exec
(followed by a call to sqlite3_errcode which actually detects the
problem).

I have used the .NET library and the locking mechanism before - but
without the transactions - without any serious problems. However, then
some of the INSERTs / UPDATEs wasn't committed correctly, and
transactions was lost.

The problem also seems to appear primarely when the machine is heavily
loaded with other jobs, especially I/O - however this is not always the
case. We have recently double the RAM in the machine which almost
eliminated the disk queue - but the problem with SQLite 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 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 code can be very difficult.

The way you have worded the above it sounds like you are opening a
database connection for each transaction (as opposed to using a global
connection to execute each transaction). Is that true?

Which API function is returning the error?

Dennis Cote

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

[Dennis Cote]

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 code can be very difficult.


The way you have worded the above it sounds like you are opening a 
database connection for each transaction (as opposed to using a global 
connection to execute each transaction). Is that true?


Which API function is returning the error?

Dennis Cote

Re: [sqlite] Transactions and sqlite3_last_insert_rowid

[Michael Ekstrand]

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 C1 rolled back, there's no 4 in the database, just 5 
and whatever else, correct?


No, this can't happen. As soon as C1 does its insert, it acquires an 
exclusive lock on the database. C2 can't do an insert until C1 either 
commits or rolls back and releases the lock. If C1 committed, then C2 
will get 5, if C1 rolled back, then C2 will get 4.


All is clear now. Thank you :-).

- Michael

--
mouse, n: a device for pointing at the xterm in which you want to type.
-- Fortune
Visit me on the Web: http://www.elehack.net

Re: [sqlite] Transactions and sqlite3_last_insert_rowid

[Dennis Cote]

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 that is committed; when the transaction is rolled back, 
it pretends it didn't happen.


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 C1 rolled back, there's no 4 in the database, just 5 
and whatever else, correct?


No, this can't happen. As soon as C1 does its insert, it acquires an 
exclusive lock on the database. C2 can't do an insert until C1 either 
commits or rolls back and releases the lock. If C1 committed, then C2 
will get 5, if C1 rolled back, then C2 will get 4.


HTH
Dennis Cote

Re: [sqlite] Transactions and sqlite3_last_insert_rowid

[Michael Ekstrand]

[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 transaction is rolled back, 
it pretends it didn't happen.


What happens to the ROWID when the transaction is rolled back? Is it as 
if the record with that ROWID was deleted?


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 C1 rolled back, there's no 4 in the database, just 5 and whatever 
else, correct?


Thanks for enlightening me :-)
- Michael

--
mouse, n: a device for pointing at the xterm in which you want to type.
-- Fortune
Visit me on the Web: http://www.elehack.net

Re: [sqlite] Transactions and sqlite3_last_insert_rowid

[drh]

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 is a property of a connection. So there is no way
> > that one connection can accidently get the last row inserted by
> > another connection.
> 
> But when is the ROWID generated? Is it generated when the INSERT is 
> evaluated/executed, or when it is committed?
> 

The ROWID is generated after the BEFORE triggers fire and
before the row is inserted. 
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] Transactions and sqlite3_last_insert_rowid

[Michael Ekstrand]

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
that one connection can accidently get the last row inserted by
another connection.


But when is the ROWID generated? Is it generated when the INSERT is 
evaluated/executed, or when it is committed?


Take the following scenario:

Conn 1: BEGIN TRANSACTION
Conn 1: INSERT...
Conn 1: sqlite3_last_insert_rowid()
Conn 2: INSERT... (same table as Conn1 just did)
Conn 2: COMMIT
Conn 1: COMMIT

Now, who got the "next" ROWID? Conn 1 or Conn 2? What did 
sqlite3_last_insert_rowid() return to Conn 1?


I'm sorry if I'm being dense; I'm just trying to understand the exact 
behavior in this case.


- Michael

--
mouse, n: a device for pointing at the xterm in which you want to type.
-- Fortune
Visit me on the Web: http://www.elehack.net

Re: [sqlite] Transactions and sqlite3_last_insert_rowid

[Will Leshner]

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 accidently get the last row inserted by
another connection.

Re: [sqlite] Transactions

[Christian Smith]

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
>>> writing to disk is surely unaviodable.
>>>
>>
>>
>> You'll have to write your own pager layer, as there is currently no
>> way to
>> disable the rollback journal.
>>
>
>Hmmh, other issues with not having a journal file aside - pager.c has
>the following code in sqlite3pager_open():
>
>   int useJournal = (flags & PAGER_OMIT_JOURNAL)==0;
>
>Looks like you might be able to switch off the rollback journal by
>passing the proper flags to sqlite3pager_open().
>Does anybody have more details/insight about this?


This is only invoked when opening a transient or virtual table, using the
OpenVirtual VDBE opcode. These are used for sorting and unions in selects
or IN expressions. Such transient data is not required to live across
crashes, transactions or even statements, so no journalling is done.

However, it does offer the ability to simply modify the existing pager.c
to turn off journals unconditionally. The attached (untested) patch should
do that.  Just recompile pager.c with -DSQLITE3_DISABLE_JOURNALLING.


>
>
>

Christian

PS. No, I don't recommend anyone does this, BTW:)

-- 
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \? patch
Index: src/pager.c
===
RCS file: /sqlite/sqlite/src/pager.c,v
retrieving revision 1.215
diff -u -r1.215 pager.c
--- src/pager.c 17 Sep 2005 15:20:27 -  1.215
+++ src/pager.c 6 Oct 2005 14:48:13 -
@@ -1585,7 +1585,19 @@
 ** If zFilename is ":memory:" then all information is held in cache.
 ** It is never written to disk.  This can be used to implement an
 ** in-memory database.
+**
+** 
+** Defining the CPP variable SQLITE3_DISABLE_JOURNALLING
+** disables all journalling. This is not part of the original SQLite release.
+** 
 */
+
+#ifdef SQLITE3_DISABLE_JOURNALLING
+static int sqlite3pager_journalling_enabled = 0;
+#else
+static int sqlite3pager_journalling_enabled = 1;
+#endif
+
 int sqlite3pager_open(
   Pager **ppPager, /* Return the Pager structure here */
   const char *zFilename,   /* Name of the database file to open */
@@ -1601,7 +1613,7 @@
   int tempFile = 0;
   int memDb = 0;
   int readOnly = 0;
-  int useJournal = (flags & PAGER_OMIT_JOURNAL)==0;
+  int useJournal = sqlite3pager_journalling_enabled && (flags & 
PAGER_OMIT_JOURNAL)==0;
   int noReadlock = (flags & PAGER_NO_READLOCK)!=0;
   char zTemp[SQLITE_TEMPNAME_SIZE];
 

Re: [sqlite] Transactions

[Jens Miltner]

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.




You'll have to write your own pager layer, as there is currently no  
way to

disable the rollback journal.



Hmmh, other issues with not having a journal file aside - pager.c has  
the following code in sqlite3pager_open():


  int useJournal = (flags & PAGER_OMIT_JOURNAL)==0;

Looks like you might be able to switch off the rollback journal by  
passing the proper flags to sqlite3pager_open().

Does anybody have more details/insight about this?

Re: [sqlite] Transactions

[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.


You'll have to write your own pager layer, as there is currently no way to
disable the rollback journal.

Note, with no rollback journal, you'll not be able to rollback a
transaction. Any updates you make will be final even before a commit.

Another option might be to use a memory database, and write periodic
snapshots to disk in the form of a SQL text file. Look at shell.c in the
SQLite source for an example of how to do this (look for implementation of
the .dump command.) The SQL file then becomes your checkpoint, and can
also be compressed quite nicely which may save disk space. This is how the
likes of HSQL works.

Of course, if this is an embedded application, memory may be a problem
with a memory database. YMMV.


>
>Martin
>
>Christian Smith schrieb:
>
>>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 speed up my writes this way?
>>>
>>>
>>
>>
>>Turn off synchronous writes:
>>http://www.sqlite.org/pragma.html
>>
>>Look for the synchronous pragma. Set it to OFF:
>>sqlite> PRAGMA synchronous = OFF;
>>
>>Updates to transactions will no longer fsync() data to the disk, and
>>instead rely on OS write-back caching, which can significantly improve
>>speed at the cost of chances of corruption in case of system crash.
>>
>>
>>
>>
>>>Thanks,
>>>Martin
>>>
>>>
>>>
>>
>>
>>
>

-- 
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

Re: [sqlite] Transactions

[Martin Engelschalk]

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 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?
   




Turn off synchronous writes:
http://www.sqlite.org/pragma.html

Look for the synchronous pragma. Set it to OFF:
sqlite> PRAGMA synchronous = OFF;

Updates to transactions will no longer fsync() data to the disk, and
instead rely on OS write-back caching, which can significantly improve
speed at the cost of chances of corruption in case of system crash.


 


Thanks,
Martin

   



 

Re: [sqlite] Transactions

[Christian Smith]

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 speed up my writes this way?


Turn off synchronous writes:
http://www.sqlite.org/pragma.html

Look for the synchronous pragma. Set it to OFF:
sqlite> PRAGMA synchronous = OFF;

Updates to transactions will no longer fsync() data to the disk, and
instead rely on OS write-back caching, which can significantly improve
speed at the cost of chances of corruption in case of system crash.


>
>Thanks,
>Martin
>

-- 
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

Re: [sqlite] Transactions and in-memory databases

[Dan Kennedy]

--- 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:
> > database in the other handle until the transaction finishes?
> > 
> > -- 
> > Cory Nelson
> > http://www.int64.org
> 
> No. This will open two completely seperate in-memory databases. Just
> as if you had opened two handles on two different files.
> 
> Dan.

Actually I think I misread the question the first time. The answer is 
still no, but disregard the extra comment. :P

Dan.




__ 
Do you Yahoo!? 
Yahoo! Small Business - Try our new resources site!
http://smallbusiness.yahoo.com/resources/ 

Re: [sqlite] Transactions and in-memory databases

[Dan Kennedy]

--- 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 finishes?
> 
> -- 
> Cory Nelson
> http://www.int64.org

No. This will open two completely seperate in-memory databases. Just
as if you had opened two handles on two different files.

Dan.




__ 
Do you Yahoo!? 
Yahoo! Small Business - Try our new resources site!
http://smallbusiness.yahoo.com/resources/