html5-img
1 / 27

Transaction

Transaction. Simon Cho. Who am I?. Simon Cho Blog : Simonsql.com Email : Simon@simonsql.com All Presentation and script will be on My blog. This is my first presentation in Public. Question 1. BEGIN TRAN A INSERT [ Tbl ] values ( 'A' ) BEGIN TRAN B

lew
Download Presentation

Transaction

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Transaction Simon Cho

  2. Who am I? • Simon Cho • Blog : Simonsql.com • Email : Simon@simonsql.com • All Presentation and script will be on My blog. • This is my first presentation in Public.

  3. Question 1. BEGINTRAN A INSERT[Tbl] values('A') BEGINTRAN B INSERT[Tbl] values('B') ROLLBACKTRAN B COMMITTRAN A • Is it working? Obviously Not. Why? Rollback transaction will rollback everything.

  4. Question 2. BEGINTRANAAAA INSERT[Tbl] values('A') BEGINTRANBBBB INSERT[Tbl] values('B') COMMIT TRANAAAA COMMITTRANBBBB • Is it working? No? Answer is Yes. Why? Transaction don’t care the name. Because nested transaction doesn’t support. How? Let’s look at inside transaction log

  5. Question 3 BEGINTRAN TRUNCATETABLE[Tbl] ROLLBACKTRAN • Is it working? Yes it is. Why? Truncate table is no logging for each row deletion. But, it’s fully logged for system table change. Remember, this is RDBMS. Which mean, all DDL statement can be rollbacked.

  6. Demo - Nested Transaction

  7. Question 4. • Which recovery mode is the fastest for this query? Truncate table Table1 GO INSERTINTOTable1 SELECTTOP 1000000 'A' FROMsys.objects a CROSSJOINsys.objects b CROSSJOINsys.objects c CROSSJOINsys.objects d CROSSJOINsys.objects e CROSSJOINsys.objects f Hint : This table doesn’t have any index Definitely Simple?? Actually all same disregarding recovery mode. Why? The Statement itself required full logged. A. SIMPLE Recovery mode B. BULK_LOGGED Recovery mode C. FULL Recovery mode Hug?

  8. Question 4 - Solution • Minimal Logging within Simple or Bulk_Logged Mode. • Minimally Logged, No Logging. Truncate table Table1 GO INSERTINTOTable1 SELECTTOP 1000000 'A' FROMsys.objects a CROSSJOINsys.objects b CROSSJOINsys.objects c CROSSJOINsys.objects d CROSSJOINsys.objects e CROSSJOINsys.objects f WITH(TABLOCK) How much faster? Vary. Depends on system. In my work station, 400 times less IO 10 times faster A. SIMPLE Recovery mode B. BULK_LOGGED Recovery mode C. FULL Recovery mode

  9. Demo – Minimal Logging

  10. What is Recovery Mode? • Full • Log backup required • Full logging everything • Note : Starting SQL 2008, some statement can be small logged even in full recovery mode. • Simple • Automatically reclaims log space. • No log backup • Log chain is broken – Not able to restore Point-in-time • Fully Support minimal logging operation • Bulk_Logged • Log backup required • Note : The log size pretty much same as full recovery mode even the minimal logging operation. • Fully Support minimal logging operation • Log change is NOT broken. • Purpose : Temporary change recovery mode for Minimal logging STMT in Full logging Database. • Note • Unfortunately, Mirrored database can’t changed. • When transactional replication is enabled, most of statement fully logged even Bulk Logged mode

  11. 3 things for TX error handling • 1. @@TranCount • It can determine nested transaction. • It doesn’t know uncommittable transaction. • 2. XACT_STATE() • 1 : Commitableactive TX. • 0 : No active TX. • -1 : Uncommitable active TX. • It can determine uncommittable transaction. • It doesn’t know nested transaction.

  12. 3 things for TX error handling - Cont • 3. SET XACT_ABORT • On : In case of run-time error, the entire TX is terminated and rolled back. • Off(Default) : [in some cases only] the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. • Note *: Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF.

  13. Rollback Transaction • Rollback Tran • Do NOT print any error message • Rolls back all inner transactions • @@TRANCOUNT system function to 0

  14. Question 5. CREATEPROCEDUREUSP_INNER_TRAN @output varchar(255)OUTPUT AS BEGIN SETNOCOUNTON; BEGINTRANSACTION BEGINTRY SELECT 1/0-- Bug COMMITTRANSACTION SELECT@output ='DONE' ENDTRY BEGINCATCH ROLLBACKTRANSACTION SELECT@output ='FAIL' ENDCATCH Return 0 END GO

  15. Question 5. - Cont BEGINTRANSACTION BEGINTRY DECLARE@output VARCHAR(255) EXECUSP_INNER_TRAN@output=@output OUTPUT SELECT'InTry'ASWhereWeAre ,@@TRANCOUNT ,@@ERRORASError ,ERROR_MESSAGE()ASERR_MESSAGE COMMITTRANSACTION ENDTRY BEGINCATCH SELECT'InCatch'ASWhereWeAre ,@@TRANCOUNT ,@@ERRORASError ,ERROR_MESSAGE()AS ERR_MESSAGE ROLLBACKTRANSACTION ENDCATCH • Is it working?

  16. Question 6 CREATE PROCTestTran AS BEGIN DECLARE @Err INT SETXACT_ABORTOFF BEGINTRANSACTION BEGINTRY SELECT*FROM[TypoNoTable] COMMITTRAN ENDTRY BEGINCATCH SET@ERR =@@ERROR IF@Err <> 0 BEGIN RAISERROR('Encountered an error, rollback', 16,1) IF@@TRANCOUNT<>0 BEGIN ROLLBACK END RETURN (@ERR) END ENDCATCH END GO EXECTestTran • Anything wrong this SP?

  17. Demo – Exception Case

  18. Best Practices to Compose SP • Goal • Transaction match (Begin and Rollback/Commit) • It’s not simple • Try catch isn’t perfect. • Avoid Nested transaction for easy handling • Error logging for troubleshooting • Need to save Enough information in case of error • What we need to do? • Define Return code and return it properly • Use Raiserror statement • Transaction Handling • Logging for error message

  19. Best Practices to Compose SP • Try Catch isn’t enough for Error Handling • Basic Rule • Set XACT_Abort on • Set Nocount on • Define return code SP and check the return code. • Avoid nested transaction • Inner SP need to check transaction status first before begin transaction • Transaction should be short enough • Execute plan, Minimal Logging • Commit and Rollback transaction at the end. • Use Goto statement to handle Commit/Rollback

  20. Best Practices to Compose SP • Errors • Expected Error • In case of parameter data wrong • Return is wrong • UnExpectedError • Not expected error • Logical bug • Any Unexpected system error : Server down… • Consider Global Error Logging table • Global • Database should be online all the time. • Consolidated error table • Error handling using GoTo statement. • This is old fashion. But, it’s still best I thought.

  21. Demo - Best Practices SP Structure

  22. Bonus – Demosp_now/sp_now_param

  23. Q & ASimon@simonsql.comWelcome for any SQL question.Please feel free to email me.

  24. Minimal Logging Operation http://technet.microsoft.com/en-us/library/dd425070%28v=sql.100%29.aspx (1) If you are using the INSERT … SELECT method, the ORDER hint does not have to be specified, but the rows must be in the same order as the clustered index. If using BULK INSERT the order hint must be used. (2) Concurrent loads only possible under certain conditions. See “Bulk Loading with the Indexes in Place”. Also, only rows written to newly allocated pages are minimally logged. (3) Depending on the plan chosen by the optimizer, the nonclustered index on the table may either be fully- or minimally logged.

  25. Minimal Logging Reference • Minimal Logging Reference • Operations That Can Be Minimally Logged • http://technet.microsoft.com/en-us/library/ms191244(v=sql.100).aspx • Prerequisites for Minimal Logging in Bulk Import • http://technet.microsoft.com/en-us/library/ms190422%28v=sql.100%29.aspx • Minimal logging operation with Traceflag610 • http://simonsql.com/2011/12/05/minimal-logging-operation-with-traceflag-610/ • The Data Loading Performance Guide • http://msdn.microsoft.com/en-us/library/dd425070.aspx • Minimal Logging • http://www.sqlservercentral.com/articles/Administration/100856/ • Considerations for Switching from the Full or Bulk-Logged Recovery Model • http://technet.microsoft.com/en-us/library/ms190203(v=sql.105).aspx

  26. Bonus II - Distributed Transaction • Distributed Transaction Coordinator(MSDTC) • DCOM service • Port 135(TCP) : Listener port • Default UDP port range 1024-5000 • Netbios • UDP port 137 (name services) • UDP port 138 (datagram services) • TCP port 139 (session services) • SQL port 1433 TCP • Statement • Begin distributed transaction • Rollback transaction • When • Always happen “begin transaction” between 2 servers such as linked server.

  27. Bonus III - Do not use Savepoint • duplicate savepoint names are allowed • SavePoint is not a transaction. • This is mark on LDF file. • We can rollback to SavePoint. • Rollback Tran savepoint_name • Does not decrement @@TRANCOUNT • Not working in case of Distributed transactions • savepoint name rolls back only to the most recent in case of duplicated name of savepoint • Lock escalations are not released and not converted back previous lock mode. • Please do not use it except very special case. It’s NOT a Transaction. • Error handling is really painful and debugging is hard. • Main problem, in case of error, it can make huge amount of blocking due to lock release issue.

More Related