Transaction Isolation in SP's

Transaction Isolation in SP's

Post by Bob Castlema » Thu, 24 Jul 2003 16:47:50



I've been looking at how transaction isolation levels effect parts of our
application and noticed that it appears that the isolation level set by the
connection only applies to queries submitted through the connection but not
queries within stored procedures called over the same connection.  Is this
the case?

Thanks,

Bob Castleman
SuccessWare Software

 
 
 

Transaction Isolation in SP's

Post by Narayana Vyas Kondredd » Thu, 24 Jul 2003 18:36:55


A connection's transaction isolation level applies to all the queries
executed in that connection, unless you specify some locking hints (eg:
NOLOCK) for some SELECT statements.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm


I've been looking at how transaction isolation levels effect parts of our
application and noticed that it appears that the isolation level set by the
connection only applies to queries submitted through the connection but not
queries within stored procedures called over the same connection.  Is this
the case?

Thanks,

Bob Castleman
SuccessWare Software

 
 
 

1. Isolation Level Changing in SP's

 Hi,
   I have got an sql script which starts a Serializable transaction,
In this script I am calling a stored procedure, I don't want this stored
procedure to be part of this transaction.
How will I make this work????

I tried the sample given below.. but it din't work.

eg..
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
  EXEC SP2
  .....
  ....
COMMIT TRANSACTION

PROCEDURE SP2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRANSACTION
 UPDATE SOME TABLE
COMMIT TRANSACTION.

Thanks & Regards,
Ashish

2. List Users

3. Transaction Isolation Levels don't work

4. pg_largeobject is a security hole

5. Call an SP within Transaction, Doesn't Work

6. ADO connection string for DBF III

7. Help on Transactions and SP's

8. The REMOTE BOOKING SYSTEM

9. Nested SP's with Transactions - Help!

10. SP's in Transactions

11. Transaction Replication - Added SP's

12. Transaction and SP calling SP

13. SP isolation level with COM+