Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Help getting a lock to work properly

Reply
Thread Tools

Help getting a lock to work properly

 
 
BEwebdev@gmail.com
Guest
Posts: n/a
 
      08-09-2007
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.ConnectionStrin gs["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

 
Reply With Quote
 
 
 
 
=?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=
Guest
Posts: n/a
 
      08-09-2007
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



"(E-Mail Removed)" 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.ConnectionStrin gs["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
>
>

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Read-Write Lock vs primitive Lock() k3xji Python 7 12-30-2008 10:19 PM
Application.Lock()/UnLock() or lock(Application) nano2k ASP .Net Web Services 2 08-09-2007 09:31 AM
Threading - Why Not Lock Objects Rather than lock the interpreter Fuzzyman Python 3 12-05-2003 10:43 PM
RE: Threading - Why Not Lock Objects Rather than lock theinterpreter Robert Brewer Python 0 12-05-2003 05:33 PM
More American Graffiti: Properly Framed, Properly Scored? Scot Gardner DVD Video 0 09-02-2003 02:28 AM



Advertisments