setting transaction isolation level on connection

setting transaction isolation level on connection

Post by Michael Jon En » Tue, 02 Feb 1999 04:00:00



This is probably posted in the wrong group and is a simple question, but:

For report purposes I'd like to have the isolation level set to
READ UNCOMMITTED or "dirty reads" or "no lock". In our application that
would be acceptable.  I can't count on our users to set this when
logging in.

Is there any way to configure SQL server for this default?
OR
Is there a way to run a stored procedure upon login where I can
place the SQL command to set the isolation level?

Thanks for any help

Michael Eno

 
 
 

1. transaction isolation level setting serializable - deadlocks

Hello -

I am using transaction isolation level serializable in one of the stored
procedure and consistently getting deadlock if I have to more than three
application instances running pointing to the same database. The stored
procedure that I am executing is a simple one. It tries to check whether the
record exists in the table; if exists update the value in one column
otherwise inserts the record in the table. I can't the use the default
isolation level of read committed because it generate duplicate key errors.
(which I know why). Can anyone look the skeleton stored procedure below and
point out why it generates deadlocks? The stored procedure is operating on
only one table.

create procedure
...
begin
    set no count on
    set transaction isolation level serializable
    begin tran

        select col1 from tableA where <conditionA>

        beign
            insert <record> into tableA
        end
        else
        begin
            update tableA set col1 = col1 + <some value> where <conditionA>
        end

    commit tran
    set transation isolation level read committed  -- revert back to default
setting
    set nocount off
end

thanks
uday

2. ODBC Driver for Syb 10

3. set transaction isolation level

4. message when execute an SP in Query Analyzer

5. setting transaction isolation level programatically

6. Date Format From DTS

7. Where should I put SET TRANSACTION ISOLATION LEVEL?

8. help fuction on fields

9. SET TRANSACTION ISOLATION LEVEL

10. Setting Transaction Isolation Level through ODBC

11. How to set Transaction Isolation Level?

12. Set Transaction ISOLATION LEVEL Serializable

13. SET TRANSACTION ISOLATION LEVEL BUG?