locked
How to set isolation level at connecting with SQL Server RRS feed

  • Question

  • User1092566934 posted

    Frequently I am finding locks in the database due to the execution of a procedure from asp.net and some maintenance work in the database at the same time, this forces me to shut the lock down, checking on SQL Server Profiler I see an event class called "Audit Login" generated by ".Net SqlClient Data Provider" which sets the transaction isolation level read to committed, despite of it is defined it as read uncommitted in the sql procedure.

    I tried to change the database's transaction isolation but there is no way and I do not know how to implement this characteristic in the SqlDataSource.

    What can I do?

    Thursday, December 31, 2015 10:08 AM

All replies

  • User753101303 posted

    Hi,

    AFAIK, what you defined in the SP should take over what is done at connection time?

    But it looks like finding a workaround for an issue rather than really fixing the source issue and it could have adverse effects. I would rather investigate a bit more the source issue and would try to fix the problem rather than changing the isolation level to mitigate the effects of this problem.

    Thursday, December 31, 2015 10:26 AM
  • User-2001205625 posted

    You can read the following article .

    http://stackoverflow.com/questions/7684477/is-it-possible-to-set-transaction-isolation-level-snapshot-automatically

    And then you can run the following query 

    ALTER DATABASE [Database on] SET READ_COMMITTED_SNAPSHOT ON;

    Hope that will help . Anything other please reply.

    Thursday, December 31, 2015 10:37 AM
  • User1092566934 posted

    Hello Patrice, 

    AFAIK, what you defined in the SP should take over what is done at connection time?

    The set is already assigned in the procedure but does not take effect

    But it looks like finding a workaround for an issue rather than really fixing the source issue and it could have adverse effects. I would rather investigate a bit more the source issue and would try to fix the problem rather than changing the isolation level to mitigate the effects of this problem.

    To fix the problem I would need to define a parameter in the connection from asp.net to sql server, some place to indicate that all connections are uncommitted either the web.config, the sqldatasource...... I don't know

    Thursday, December 31, 2015 10:39 AM