Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

All About Transactions - Part 1 Expand / Collapse
Author
Message
Posted Wednesday, October 27, 2004 6:40 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 13, 2014 10:40 AM
Points: 1,035, Visits: 408
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dpet


/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek



*****************/
Post #143793
Posted Monday, November 15, 2004 7:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 22, 2008 2:03 PM
Points: 180, Visits: 35

This is a wonderful article I wish to see for a long time. I'd like to see the coming series covering the following issues:

error handling on severity level 17,18 and 19. especially handling constraints like duplicate record violation.

transaction handling xp_cmdshell like bcp...

there are too many issues to be considered in sqlserver2000, seems sqlserver2005 has a good exception handler by try... catch...

locking is another weakness of sqlserver, no question Oracle is doing much better in isolation level control, locking and error handling.




Post #146256
Posted Monday, November 15, 2004 9:08 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 13, 2014 10:40 AM
Points: 1,035, Visits: 408

Thank you.  The next article (scheduled for publication on Nov 24th will deal pretty extensively with error handling.  The third (submitted but no publish date yet) deals with Locks and Isolation levels.

I have to disagree with you about Oracle's superiority though.  Having worked extensively with Oracle, I would say that there is very little advantage with its locking and transaction isolation support.  It's true that in Oracle readers don't block writers and vise versa, but that locking strategy comes at a cost.  Both SQL Server and DB2 use very similar locking strategies to great success.  The key is to know the DBMS you use, understand its strengths and weaknesses and know how to get the most out of it.

Oracle's error handling is more consistent and easier to use, but from what I've seen, SQL Server 2005 will change that. 




/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek



*****************/
Post #146307
Posted Monday, November 15, 2004 9:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 22, 2008 2:03 PM
Points: 180, Visits: 35

When I mention Oracle's advantage, I actually want to say that select statement in TSQL put a lock on row/page/table, and potentially cause more deadlock than Oracle, because pl/sql select do not put lock unless using "select ... for update".

anyway, I look forward to see your next article, it's really inspiring and I never see one before talking about the whole thing.

thank you very much.

solute.

Jiulu




Post #146317
Posted Monday, November 15, 2004 9:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 22, 2008 2:03 PM
Points: 180, Visits: 35

By the way, when you mentioned that "locking strategy comes at a cost", did you mean Oracle rollback segment? I'd like to see your comments on the tradeoff of Oracle's less locking and saving before-image in rollback segment. I think Oracle still lead some top TPCC records.

thanks.




Post #146319
Posted Monday, November 15, 2004 10:18 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 13, 2014 10:40 AM
Points: 1,035, Visits: 408

That is exactly what I meant, but I did not mean to imply that the cost was entirely paid in performance or that it couldn't be mitigated by other factors.  The cost of using rollback segments includes additional disk space and memory used, which impact performance.  There tends to be a minimal overall performance hit because of reduced blocking so that tends to be a wash.  Yes, Oracle does well on the TPC benchmarks (as do DB2 and SQL Server) but the biggest "cost" I see with the Oracle approach is management.  Our Oracle DBA's spent considerable time managing and monitoring the rollback segments.  Our SQL Server DBA's spent much less time dealing with blocking problems.




/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek



*****************/
Post #146323
Posted Monday, November 15, 2004 8:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 04, 2012 12:02 PM
Points: 4, Visits: 4

Nice article. Hope to see next in series soon.

Post #146402
Posted Tuesday, November 16, 2004 12:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 23, 2009 2:00 AM
Points: 26, Visits: 8

Great article.

I know this is probably a bit off topic, but I noticed that you didn't mention COM+ transactions. I think there's quite a lot of developers out there using COM+ and it would really be helpful if you will have a part on how transactions work with COM+, especially the tricky parts such as:

1. Behaviour when COM+ transactional object that calls non COM+ object and vice versa.

2. How to execute statements in a COM+ transactional object that's not enlisted in the COM+ transaction (for example when error/audit logging, it just wouldn't do to have it being rolled back right)?

Post #146420
Posted Tuesday, November 16, 2004 7:35 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 13, 2014 10:40 AM
Points: 1,035, Visits: 408

I'm not a programmer and don't pretend to be one so I won't be the one to effectively address your COM+ transaction questions.  I could address the issue at a very general level, but I am not familiar with all the details of how COM+ or .Net handle transactions.

Generally speaking though, I think it is best to control transactions at the DBMS rather than in the application, that's one of the major functions of the DBMS and ultimately that is where the rubber meets the road so to speak when it comes to transactions.  In my experience, controlling transactions at the application layer results in more network traffic and less reliable results.




/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek



*****************/
Post #146484
Posted Tuesday, November 16, 2004 12:22 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 01, 2011 2:05 PM
Points: 32, Visits: 47
Hi, good article. Just something you may like to consider mentioning. I have seen this problem several times now and it allows potential for system misuse. The problem is where a screen of data has been entered, has updated the database but this does not get committed until the user accepts the entries from the screen. Consider this theoretical scenario to do with receipting. The user logs into the receipting system and enters receipt data for a cash transaction, but does not commit this. On a second session the user goes into a print routine and prints details of the uncommitted data (as a receipt) and then goes back to the first session and cancels the transaction, rolling out any record of the details now printed on the receipt. The user now pockets the cash with no record of this existing in the system. This is a trap that programmers often don’t consider when using commit processing, and can be mitigated by writing the data to a temporary table until accepted and only then updating the live table.


Post #146557
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse