Link to home
Start Free TrialLog in
Avatar of Mike Eghtebas
Mike EghtebasFlag for United States of America

asked on

Transaction Isolation Error Handling

I have two sessions executed in step orders as noted noted in the following codes.

Question: How can I complete and make Step 4: (Second Session) function?

First Session:
-- Step 1: (First Session)
use TSQL2012
BEGIN TRAN;

-- Step 3: (First Session)
Update hr.Employees
Set postalcode = N'10004' 
Where empid =1;

-- Step: not sure if it is executed before or after step 4 of the other session.
Commit tran;

Open in new window

Second Session:
-- Step 2: (Second Session)
use TSQL2012
SET TRANSACTION ISOLATED LEVEL UNCOMITTED

-- Step 4: (Second Session)
-- Add TRY/CATCH or IF/ ELSE to determine whether
-- the following select is comiited or still uncomitted
-- or perhaps it is rolled back.
IF @@TRANCOUNT <of First Session> >0
   SELECT 'Committed Result: ' As Status, lastname, firstname, region FROM HR.Employees
   --<commited result returned: region=1004 for empid=1>
ELSE
   SELECT 'Uncommitted Result: ' As Status, lastname, firstname, region FROM HR.Employees
   --<uncommited result returned: region=1004 for empid=1>

Open in new window

Note: I know that perhaps a better method will be to use RCSI or some other isolation method. Here the idea is to discover if there is a way to add the requested IF/Else using SET TRANSACTION ISOLATED LEVEL UNCOMITTED.

I also have the following few lines from the book. I have not made a good sense of it yet but I am including it FYI:
This step uses a table hint to implement READ UNCOMMITTED in a single command. Instead of applying this isolation level to an entire session, you can apply it at the individual command, per table, by using the READUNCOMMITTED table hint. Replace the SELECT command in the code for the previous step with the following, which has the WITH (READUNCOMMITTED) table hint. Then modify the statement to use WITH
(NOLOCK).
Avatar of Peter Chan
Peter Chan
Flag of Hong Kong image

It means you can still read the uncommitted records, within one transaction.
ASKER CERTIFIED SOLUTION
Avatar of Qlemo
Qlemo
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mike Eghtebas

ASKER

Hi Qlemo,

When we execute Step 4:
SELECT '??? Result: ' As Status, lastname, firstname, region FROM HR.Employees

The possibilities are:
1. The first session has already committed or rolled back. We want to know what replaces ??? portion to say 1) "rolled back, now is original committed", or 2) "committed, now is new committed".

2. The first session has not committed/ rolled back yet. We want to know about that and replace ??? portion to say "not committed or rolled back possibly a dirty read."

Note change at
 -- Step: not sure if it is executed before or after step 4 of the other session.
Commit tran;  
-- or
-- Rollback;
"The second session will always see the change of session 1."