Manage Transactions

(This is part of the Study Guide series, 70-457)
Microsoft’s Measured Skill description: This objective may include but is not limited to: mark a transaction; understand begin tran, commit, and rollback; implicit vs. explicit transactions; isolation levels; scope and type of locks; trancount
What I see:
·         mark a transaction
·         begin tran, commit tran, rollback tran
·         implicit vs. explicit transactions
·         isolation levels
·         @@trancount
Mark a Transaction
                SQL Server allows us to mark transactions in order to leverage specific point recovery to a particular transaction.  For instance, with the AdventureWorks database say you mark a transaction when you modify particular data:
use AdventureWorks2012;
go
begin tran ProductionUpdate withmark
       update HumanResources.Department
       set name = ‘Production Modified’
       where DepartmentID = 7;
commit tran ProductionUpdate
And then you further modify this same data:
update HumanResources.Department
set name = ‘Production after Mark’
where DepartmentID = 7;
Once the log is subsequently backed up, you now have the option to restore to the committed transaction named “ProductionUpdate”.  You can accomplish this by doing the following (provided you have the correct full recovery model backups available):
restore log AdventureWorks2012
from disk = ‘C:\YourBackupDir\AW_postMT.trn’
with
       recovery,
       stopatmark = ‘ProductionUpdate’;
go
Now by running the following query, you can see that we have restored the database to the committed portion of the marked transaction:
use AdventureWorks2012;
go
select *
from HumanResources.Department
where DepartmentID = 7;
BEGIN TRAN, COMMIT TRAN, and ROLLBACK TRAN
                These three T-SQL statements are used with explicit transactions.  BEGIN TRAN tells SQL Server that an explicit transaction is starting.  It can be a named transaction, and marked (as explained above).  Subsequently, COMMIT TRAN signifies the end of a transaction by doing just that; committing it.  ROLLBACK TRAN will undo the data modification that happened during the transaction.  These explicit transaction statements are used in order to adhere to the ACID principle, particularly atomicity.  You can ensure that transaction integrity leads to data integrity.  Take the following example:
begin tran
       update HumanResources.Department
       set Name = ‘Production’
       where DepartmentID = 7;
       if is_rolemember(‘db_owner’, user_name()) = 1
              commit tran
       else
              rollback tran
The above is a relatively useless example, but it shows through the use of explicit transactions how BEGIN TRAN, COMMIT TRAN, and ROLLBACK TRAN function.  It does an UPDATE of data, and if the current database user isn’t in the db_owner role, it rolls back the modified data.  Otherwise it commits the UPDATE.
Implicit vs. Explicit Transactions
                We have already talked briefly about using explicit transactions (see above), but conversely SQL Server allows us to utilize implicit transactions.  When you are operating with IMPLICIT_TRANSACTIONS ON for a particular connection, there are a handful of statements that automatically start a transaction, and that transaction will be open until either committed or rolled back.  To show an example of implicit transactions, see below:
use AdventureWorks2012;
go
set implicit_transactions on;
go
update HumanResources.Department
set Name = ‘Eng’
where DepartmentID = 1;
— now disconnect this connection
—     (i.e. close the query window)
— open a new query window and execute the below code.
— you will notice that the initial transaction was
— never committed.  This is because with IMPLICIT_TRANSACTIONS ON
— you need to commit the transaction in order for that to reflect
use AdventureWorks2012;
go
select *
from HumanResources.Department;
Isolation Levels
                SQL Server transaction isolation levels are a relatively in depth portion of locking and transactions.  You should have a thorough understanding of all the pessimistic and optimistic isolation levels.  Please see BOL for reference.
@@TRANCOUNT
                The system function @@TRANCOUNT returns the current open transactions.  It will be incremented by one for BEGIN TRAN, decremented by one for COMMIT TRAN, and appropriately set to zero for ROLLBACK TRAN.  See below for an example in order to view the return of @@TRANCOUNT with different variations of explicit transactions:
begin tran
       select @@trancount
       begin tran
              select @@trancount
              begin tran
                     select @@trancount
              commit tran
              select @@trancount
       commit tran
       select @@trancount
       begin tran
              select @@trancount
       commit tran
       select @@trancount
commit tran
select @@trancount
References
·         BOL reference on @@TRANCOUNT
If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at sqlsalt@gmail.com.