> 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 <slav...@bigfraud.org> 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 10000..... . 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
