BEGIN and COMMIT

[Cubical]

Should BEGIN and COMMIT only be used when lots of data is written to a table or should it be used whenever a query is sent to the database? I'm not fully understanding it. If someone could give me some examples and explain when and when not to use it that would be awesome.

edit: I now understand that every time a you do a query that it begins a transaction and commits it automatically however I still do not understand the proper use of BEGIN and COMMIT if that's the case. The only thing I can think of is if you are adding a bunch of rows to a table and you would want to keep the transaction open so it doesnt have to BEGIN and COMMIT over and over. I still lack full understanding of it.

[fowlplay4]

My understanding of it is:

BEGIN;
.. a bunch of statements, that should all fail if one doesn't go through ..
COMMIT;

After reading: http://sqlite.org/lang_transaction.html

It leads me to believe if u pass a formatted SQL query that contains 10 SQL statements it will automatically BEGIN and COMMIT for you. However if you do something like..

PHP Code:

sql("BEGIN");
sql("UPDATE stuff");
sql("UPDATE stuff");
sql("COMMIT"); 


You might have to use it, really depends how it's implemented behind-the-scenes.

[cbk1994]

Originally Posted by fowlplay4

My understanding of it is:

BEGIN;
.. a bunch of statements, that should all fail if one doesn't go through ..
COMMIT;

That's also my understanding of it. Note also that you can get greatly improved write speeds with INSERT/UPDATE if executing many queries if you do them in a transaction.

[Admins]

I never use begin/commits, it just adds more hassle. What can speed up stuff is to do several inserts with the same query. For optimization it's good to not store data which you don't need to store, e.g. don't store if the value is empty anyway and you don't have previous entries for this key (I've seen bad examples on several bigger servers), and you need to add indices to make the lookup fast. One thing that is often speeding up queries is to only update the columns which you want to update, because updating a key is much much slower because it needs to update the index too.