Help getting a lock to work properly

[BEwebdev]

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

 

[Guest]

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

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