PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Sun Jun 16, 2019 3:03 pm

All times are UTC - 5 hours




Post new topic Reply to topic  [ 8 posts ] 
Author Message
PostPosted: Wed Oct 23, 2013 3:31 pm 
Offline
DevNet Resident
User avatar

Joined: Sat Jun 08, 2002 1:24 pm
Posts: 1207
Location: Florida
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?


Top
 Profile  
 
PostPosted: Wed Oct 23, 2013 5:17 pm 
Offline
Site Administrator
User avatar

Joined: Wed Aug 25, 2004 7:54 pm
Posts: 13590
Location: New York, NY, US
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)


Top
 Profile  
 
PostPosted: Wed Oct 30, 2013 3:22 pm 
Offline
DevNet Resident
User avatar

Joined: Sat Jun 08, 2002 1:24 pm
Posts: 1207
Location: Florida
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:


Top
 Profile  
 
PostPosted: Sat Nov 02, 2013 12:30 am 
Offline
DevNet Master

Joined: Wed Feb 11, 2004 4:23 pm
Posts: 4872
Location: Palm beach, Florida
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.


Top
 Profile  
 
PostPosted: Fri Nov 08, 2013 11:46 am 
Offline
DevNet Resident
User avatar

Joined: Sat Jun 08, 2002 1:24 pm
Posts: 1207
Location: Florida
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?


Top
 Profile  
 
PostPosted: Tue Nov 12, 2013 12:30 pm 
Offline
DevNet Master

Joined: Thu Oct 06, 2005 3:57 pm
Posts: 3360
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


Top
 Profile  
 
PostPosted: Fri Nov 29, 2013 9:53 pm 
Offline
DevNet Master

Joined: Wed Feb 11, 2004 4:23 pm
Posts: 4872
Location: Palm beach, Florida
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.


Top
 Profile  
 
PostPosted: Mon Dec 23, 2013 12:04 pm 
Offline
DevNet Master
User avatar

Joined: Wed Jun 27, 2007 9:44 am
Posts: 4313
Location: Sofia, Bulgaria
Highly related to discussion is the row locking:
Quote:
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


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 8 posts ] 

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 2 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group