DB transactions discussion

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
User avatar
BDKR
DevNet Resident
Posts: 1207
Joined: Sat Jun 08, 2002 1:24 pm
Location: Florida
Contact:

DB transactions discussion

Post by BDKR »

I've been shocked recently to encounter a bit of resistance to using database level transactions. You know... Begin, Rollback, Commit.

Instead, I hear the bloke saying things like, "Since you already have the ID's, you could just delete the record". Now while that may sound simple enough, how is that easier then just saying "Rollback" to
the db?

I've used them where appropriate for some time now ('02 was the first) and can't begin to understand why someone would take such a stance. Is there something that I don't understand?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: DB transactions discussion

Post by Christopher »

I get the impression that some programmers, especially those that are self-taught or learned on very simple websites, have very little understanding of what databases are capable of. This is very different from old school style of offloading complex operations to the database with features like transactions, triggers, stored procedures, etc. This is both good and bad. There is a modern trend to simplicity in database use. NoSQL database usage is the most obvious examples of this.

Also, if you consider you Model to be an interface to some abstract datasource out there somewhere -- maybe a database / maybe a webservice -- then it might make sense to not assume transaction capability and do your own rollback on an error.

That being said, and unlike triggers or stored procedures, transactions are a very standard, portable part of SQL. They really should be used for cases where multiple SQL statements are a related group and must be undone if there is a failure at any point. So I think there may be a little DIY ignorance when transactions are not used.

PS - welcome back BDKR!
(#10850)
User avatar
BDKR
DevNet Resident
Posts: 1207
Joined: Sat Jun 08, 2002 1:24 pm
Location: Florida
Contact:

Re: DB transactions discussion

Post by BDKR »

Christopher wrote: I get the impression that some programmers, especially those that are self-taught or learned on very simple websites, have very little understanding of what databases are capable of. This is very different from old school style of offloading complex operations to the database with features like transactions, triggers, stored procedures, etc. This is both good and bad. There is a modern trend to simplicity in database use. NoSQL database usage is the most obvious examples of this.
Honestly, I suspect this also has something to do with a growing trend (even among guys coming out of Comp-Sci programs) to view everything they do through the capabilities of a given framework. It's alarming and maddening at the same time!

Not down on frameworks, but frameworks aren't Swiss Army Knives.
Christopher wrote: PS - welcome back BDKR!
Thanks! :D :drunk:
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: DB transactions discussion

Post by josh »

Well why are you inserting rows just to delete them or roll them back, might as well use a temp table if I understand right. Or maybe even a sqllite in memory database that you delete after you're done. Or just don't use a database at all if you don't need persistence.
User avatar
BDKR
DevNet Resident
Posts: 1207
Joined: Sat Jun 08, 2002 1:24 pm
Location: Florida
Contact:

Re: DB transactions discussion

Post by BDKR »

josh wrote:Well why are you inserting rows just to delete them or roll them back, might as well use a temp table if I understand right. Or maybe even a sqllite in memory database that you delete after you're done. Or just don't use a database at all if you don't need persistence.
:roll:

Either you're not comprehending what I wrote or you don't understand the reasoning behind transactions. It's simple really. If there is an operation that touches the database in multiple places (updates, deletes, whatever...) and one of them fails, you can't just pop up an error somewhere and say, "oh well". The db is NOW IN A CORRUPT STATE!

That said, RDBMS vendors for a real long time now have built in the ability for the database to undo all the changes by issuing a single command: ROLLBACK.

If you understand that, then you know the only reason a ROLLBACK occurs is because one of the series of operations failed.

Now let me ask you a question. If I had an operation that needed to make 7 inserts and 10 updates (purely hypothetical), would it be easier to just issue a ROLLBACK command to the db, or jump through all kinds of other BS hoops like temp tables, heap tables, or whatever. What is more computationally expensive, critical, and takes more time from the developer?

Sure, you could keep track of the id's for all the newly created rows, but in an auto commit environment (which obviously is what you are accustomed to dealing with), how do you un-phuck yourself after you've made some of those updates only to have something fail?
matthijs
DevNet Master
Posts: 3360
Joined: Thu Oct 06, 2005 3:57 pm

Re: DB transactions discussion

Post by matthijs »

Agreed. I think that whenever you run multiple queries and a problem half way could leave the db in a corrupted state, you should use transactions.

Maybe the problem is also that many examples, articles and tutorials are pretty basic, with very simple examples. Insert this, update that and done. Very little thought on possible problems, security, etc
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: DB transactions discussion

Post by josh »

BDKR wrote:
josh wrote:Well why are you inserting rows just to delete them or roll them back, might as well use a temp table if I understand right. Or maybe even a sqllite in memory database that you delete after you're done. Or just don't use a database at all if you don't need persistence.
:roll:

Either you're not comprehending what I wrote or you don't understand the reasoning behind transactions.
I understand transactions. For some reason when I originally read your post, it sounded like you were trying to insert a bunch of rows, perform some calculation, and then remove the rows [unconditionally]. That's what temp tables are for.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: DB transactions discussion

Post by VladSun »

Highly related to discussion is the row locking:
A shared (S) lock permits the transaction that holds the lock to read a row.
An exclusive (X) lock permits the transaction that holds the lock to update or delete a row.
That ensures "records with IDs you have" are in the DB, and optionally access to them is denied during your transaction.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply