Professional Web Applications Themes

Problem with transaction - Microsoft SQL / MS SQL Server

Within a procedure i create two connection RConnection for readonly operation WConnection for write operation on a DB. Associated with the WConnection i create a Transaction with isolationlevel ReadUncommitted; All three object are stored in Session variables. I use a class to perform operation on a DB and i need to have all operation that could modify the DB within the transaction Here is the code : string StrConn = "User ID="+UID+";Password="+PWD+";Initial Catalog=Media;Data Source=(local)"; WConnection.ConnectionString = StrConn; RConnection.ConnectionString = StrConn; RConnection.Open(); WConnection.Open(); WTransaction = WConnection.BeginTransaction(IsolationLevel.ReadUn committed); Session["WConnection"] = WConnection; Session["RConnection"] = RConnection; Session["WTransaction"] = WTransaction; In another procedure (within another ...

  1. #1

    Default Problem with transaction

    Within a procedure i create two connection

    RConnection for readonly operation
    WConnection for write operation on a DB.

    Associated with the WConnection i create a Transaction with
    isolationlevel ReadUncommitted;

    All three object are stored in Session variables.

    I use a class to perform operation on a DB and i need to have all
    operation that could
    modify the DB within the transaction

    Here is the code :


    string StrConn = "User ID="+UID+";Password="+PWD+";Initial
    Catalog=Media;Data Source=(local)";

    WConnection.ConnectionString = StrConn;
    RConnection.ConnectionString = StrConn;

    RConnection.Open();
    WConnection.Open();

    WTransaction =
    WConnection.BeginTransaction(IsolationLevel.ReadUn committed);


    Session["WConnection"] = WConnection;
    Session["RConnection"] = RConnection;
    Session["WTransaction"] = WTransaction;


    In another procedure (within another asp.net page) i create an istance
    of my object
    and associate the connection


    Doent newDoc = new Doent();
    newDoc.Connection = WConnection;
    newDoc.Transaction = WTransaction;

    newDoc.Create(); // this insert a record in a table

    in a second procedure i read the doent list stored on the DB
    Doent Doc = new Doent();
    Doc.Connection = RConnection;
    Doc.List();

    Here is the code of the metod List()

    SqlDataAdapter myCommand = new SqlDataAdapter("Doent_List",
    myConnection);
    myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
    ... // code for parameter of the storedprocedure
    myCommand.ExecuteNonQuery();


    At this point the program interrupts and this message is shown :
    Timeout expired. The timeout period elapsed prior to completion of
    the operation or the server is not responding.


    If i was clear, can someone help me to understand what happens?
    Does the transaction block the execution of the storedprocedure?




    --
    Direct access to this group with [url]http://web2news.com[/url]
    [url]http://web2news.com/?microsoft.public.sqlserver.programming[/url]
    basidati Guest

  2. #2

    Default Re: Problem with transaction


    "basidati" <basidati.news.invalidweb2news.net> wrote in message
    news:48677N665web2news.com...
    > Within a procedure i create two connection
    >
    > RConnection for readonly operation
    > WConnection for write operation on a DB.
    >
    > Associated with the WConnection i create a Transaction with
    > isolationlevel ReadUncommitted;
    >
    > All three object are stored in Session variables.
    >
    > I use a class to perform operation on a DB and i need to have all
    > operation that could
    > modify the DB within the transaction
    >
    > Here is the code :
    >
    >
    > string StrConn = "User ID="+UID+";Password="+PWD+";Initial
    > Catalog=Media;Data Source=(local)";
    >
    > WConnection.ConnectionString = StrConn;
    > RConnection.ConnectionString = StrConn;
    >
    > RConnection.Open();
    > WConnection.Open();
    >
    > WTransaction =
    > WConnection.BeginTransaction(IsolationLevel.ReadUn committed);
    >
    >
    > Session["WConnection"] = WConnection;
    > Session["RConnection"] = RConnection;
    > Session["WTransaction"] = WTransaction;
    >
    >
    > In another procedure (within another asp.net page) i create an istance
    > of my object
    > and associate the connection
    >
    >
    > Doent newDoc = new Doent();
    > newDoc.Connection = WConnection;
    > newDoc.Transaction = WTransaction;
    >
    > newDoc.Create(); // this insert a record in a table
    >
    > in a second procedure i read the doent list stored on the DB
    > Doent Doc = new Doent();
    > Doc.Connection = RConnection;
    > Doc.List();
    >
    > Here is the code of the metod List()
    >
    > SqlDataAdapter myCommand = new SqlDataAdapter("Doent_List",
    > myConnection);
    > myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
    > ... // code for parameter of the storedprocedure
    > myCommand.ExecuteNonQuery();
    >
    >
    > At this point the program interrupts and this message is shown :
    > Timeout expired. The timeout period elapsed prior to completion of
    > the operation or the server is not responding.
    >
    >
    > If i was clear, can someone help me to understand what happens?
    > Does the transaction block the execution of the storedprocedure?
    >
    Congradulations on discovering why you shouldn't put connections in session
    variables.
    And why you can't use long-running transactions in web applications.

    Abandon this folly and use ASP.NET and the SQLClient like everyone else.
    Open and close connections immediately, don't use transactions that last
    over one request, and don't put connections or transactions in session or
    application scope.

    See the following for a usefull best-practices example of how to use the
    SQLClient in dotnet.

    [url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp[/url]

    More generally see the .NET Data Access Architecture Guide

    [url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daag.asp[/url]

    David


    David Browne Guest

Similar Threads

  1. WS-Transaction
    By moko in forum ASP.NET Web Services
    Replies: 1
    Last Post: August 13th, 12:25 AM
  2. Transaction Log
    By Shamim in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: August 22nd, 06:25 AM
  3. Transaction
    By basidati in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: August 8th, 03:41 PM
  4. Transaction id and transaction isolation
    By Alex in forum Informix
    Replies: 3
    Last Post: July 23rd, 09:33 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139