Help getting a lock to work properly

Discussion in 'ASP .Net' started by BEwebdev@gmail.com, Aug 9, 2007.

  1. Guest

    I have had my head spinning for two days on this one. I have what i
    thought was a simple enough locking issue, but havnt been able to get
    it working.

    Here is my setup:
    App Server is a .net 2.0
    dB is Sql Server 2005

    I have a aspx page that creates an object, lets call it an asset
    object for now. Each time this page is viewed, i create the object.
    I then call a method of that object. In that method, i create a new
    object (a data access layer object). I call a method in that new data
    object. In that data object, i have two stored procs being fired:

    SqlConnection thisConn = new
    SqlConnection(ConfigurationManager.ConnectionStrings["connString"].ConnectionString);

    SqlCommand thisCmd3;

    //get a new id for the table
    thisCmd3 = new SqlCommand("uspIdentityIncrement",
    thisConn);
    thisCmd3.CommandType = CommandType.StoredProcedure;
    thisCmd3.Parameters.Add("@tableName",
    SqlDbType.VarChar, 255);
    thisCmd3.Parameters["@tableName"].Value =
    "uAssetView";
    thisCmd3.Parameters.Add("@newKey", SqlDbType.Int);
    thisCmd3.Parameters["@newKey"].Direction =
    ParameterDirection.Output;

    thisConn.Open();
    int newKey;
    thisCmd3.ExecuteNonQuery();
    newKey =
    Convert.ToInt32(thisCmd3.Parameters["@newKey"].Value);
    //thisConn.Close();

    SqlCommand thisCmd = new
    SqlCommand("uspAssetViewRecord", thisConn);
    thisCmd.CommandType = CommandType.StoredProcedure;

    thisCmd.Parameters.Add("@assetViewId", SqlDbType.Int);
    thisCmd.Parameters["@assetViewId"].Value = newKey;
    thisCmd.Parameters.Add("@assetId", SqlDbType.Int);
    thisCmd.Parameters["@assetId"].Value = assetId;
    thisCmd.Parameters.Add("@userId", SqlDbType.Int);
    thisCmd.Parameters["@userId"].Value = userId;

    //thisConn.Open();
    thisCmd.ExecuteNonQuery();
    thisConn.Close();

    The first sp does an insert, and the second does another insert based
    on the first ones results. They have to stay seperate like that, i
    cannot combine the stored procs.

    The issue i am having is threads are crossing, resulting in the first
    sp to get called two times in a row, before the second has a chance to
    fire.

    I have tried all that I can think of. I have placed locks in every
    place I can think of, i have tried transactions, transactionScopes. I
    am completely stumped. Any ideas would be great!

    Thanks,
    Brian
    , Aug 9, 2007
    #1
    1. Advertising

  2. When you say "they have to stay separate" is this because of some decree from
    the heavens above, or just that you aren't sure how to get what you need all
    in one stored proc? Certainly if you put all the logic in a single stored
    proc, wrap it in a transaction, and use the ROWLOCK or table locking hints in
    your sproc, you can prevent multiple access to the sproc until the two -
    table operation is complete. Your sproc can also return the identity value as
    either an output parameter or via a scalar. In fact, if you generate a GUID,
    you can supply the identity value from the outside and not have to rely on
    getting it out of the database at all.
    --Peter
    Recursion: see Recursion
    site: http://www.eggheadcafe.com
    unBlog: http://petesbloggerama.blogspot.com
    BlogMetaFinder: http://www.blogmetafinder.com



    "" wrote:

    > I have had my head spinning for two days on this one. I have what i
    > thought was a simple enough locking issue, but havnt been able to get
    > it working.
    >
    > Here is my setup:
    > App Server is a .net 2.0
    > dB is Sql Server 2005
    >
    > I have a aspx page that creates an object, lets call it an asset
    > object for now. Each time this page is viewed, i create the object.
    > I then call a method of that object. In that method, i create a new
    > object (a data access layer object). I call a method in that new data
    > object. In that data object, i have two stored procs being fired:
    >
    > SqlConnection thisConn = new
    > SqlConnection(ConfigurationManager.ConnectionStrings["connString"].ConnectionString);
    >
    > SqlCommand thisCmd3;
    >
    > //get a new id for the table
    > thisCmd3 = new SqlCommand("uspIdentityIncrement",
    > thisConn);
    > thisCmd3.CommandType = CommandType.StoredProcedure;
    > thisCmd3.Parameters.Add("@tableName",
    > SqlDbType.VarChar, 255);
    > thisCmd3.Parameters["@tableName"].Value =
    > "uAssetView";
    > thisCmd3.Parameters.Add("@newKey", SqlDbType.Int);
    > thisCmd3.Parameters["@newKey"].Direction =
    > ParameterDirection.Output;
    >
    > thisConn.Open();
    > int newKey;
    > thisCmd3.ExecuteNonQuery();
    > newKey =
    > Convert.ToInt32(thisCmd3.Parameters["@newKey"].Value);
    > //thisConn.Close();
    >
    > SqlCommand thisCmd = new
    > SqlCommand("uspAssetViewRecord", thisConn);
    > thisCmd.CommandType = CommandType.StoredProcedure;
    >
    > thisCmd.Parameters.Add("@assetViewId", SqlDbType.Int);
    > thisCmd.Parameters["@assetViewId"].Value = newKey;
    > thisCmd.Parameters.Add("@assetId", SqlDbType.Int);
    > thisCmd.Parameters["@assetId"].Value = assetId;
    > thisCmd.Parameters.Add("@userId", SqlDbType.Int);
    > thisCmd.Parameters["@userId"].Value = userId;
    >
    > //thisConn.Open();
    > thisCmd.ExecuteNonQuery();
    > thisConn.Close();
    >
    > The first sp does an insert, and the second does another insert based
    > on the first ones results. They have to stay seperate like that, i
    > cannot combine the stored procs.
    >
    > The issue i am having is threads are crossing, resulting in the first
    > sp to get called two times in a row, before the second has a chance to
    > fire.
    >
    > I have tried all that I can think of. I have placed locks in every
    > place I can think of, i have tried transactions, transactionScopes. I
    > am completely stumped. Any ideas would be great!
    >
    > Thanks,
    > Brian
    >
    >
    =?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=, Aug 9, 2007
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Fuzzyman
    Replies:
    3
    Views:
    486
    Andrew MacIntyre
    Dec 5, 2003
  2. Robert Brewer
    Replies:
    0
    Views:
    485
    Robert Brewer
    Dec 5, 2003
  3. k3xji
    Replies:
    7
    Views:
    804
    Gabriel Genellina
    Dec 30, 2008
  4. nano2k

    Application.Lock()/UnLock() or lock(Application)

    nano2k, Jul 23, 2007, in forum: ASP .Net Web Services
    Replies:
    2
    Views:
    275
    nano2k
    Aug 9, 2007
  5. Replies:
    2
    Views:
    121
Loading...

Share This Page