Re: New concurrency error
From: Nick Malik (nickmalik_at_hotmail.nospam.com)
Date: 09/01/04
- Next message: Morten Wennevik: "Re: error sending mail"
- Previous message: Nick Malik: "Re: Process.Start"
- In reply to: Robert Schuldenfrei: "New concurrency error"
- Next in thread: Robert Schuldenfrei: "Re: New concurrency error - resolved"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 01 Sep 2004 05:28:30 GMT
Kevin Yu's response is completely correct. your code will not throw an
exception in the condition you specify.
Here is why:
Person A reads record 1 with timestamp 333
Person B reads record 1 with timestamp 333
Person A updates record 1. Timestamp is change to 444
Person B issues the update statement. The update statement says "Update the
row where the timestamp is 333"
However, there is no matching row, because the timestamp is now 444.
The number of rows affected is zero.
This is not an error. In SQL, it is perfectly legal to issue an update
query that doesn't match anything. So, as Kevin suggests, if you query the
rowcount, you can get an idea if the query failed to match the row as you
expect.
I do think that your update statement is not efficient. Doesn't this table
have a primary key? If it does, you only need to match on the primary key,
not on every field. If it doesn't, you should add one, even if it means
adding a unique column like an Identity or a UniqueIdentifier.
Also: when I used to use timestamp for concurrency (SQL 6.5), I would put
code into a SQL Server trigger that would actually raise an error if the row
was updated and the new timestamp value didn't equal the old timestamp
value... that way the code had something to catch. However, checking the
number of rows affected will work just as well.
Hope this helps,
--- Nick
"Robert Schuldenfrei" <bob@s-i-inc.com> wrote in message
news:By5Zc.101438$mD.24263@attbi_s02...
> Dear NG,
>
> I was about to "improve" concurrency checking with a Timestamp when I
> discovered
> that my current code is not working. After about a day of beating my head
> against the wall, I am turning to the NG in hopes that someone can spot
what
> I am doing wrong.
>
> Key to this technique working is the SQL UPDATE statement. It is designed
> to fail
> if the row on file in SQL has been modified by some other user. User 1
> comes in
> and starts to change the txtPrime.Text. User 2 then makes another change
to
> the
> same text box and her completes the operation by going through the
> ChangeItemLoc()
> method. Now user 1 goes to UPDATE SQL and raises NO exceptions! The only
> trouble is that user 1 changes do not happen. What I expected was that
user
> 1
> would get the concurrency error message in the calling code below.
>
> I am sure I have done something stupid, but I can not spot it. Can you
guys
> see what I have done wrong.
>
> Thank you,
>
> Bob
>
> Robert Schuldenfrei
> bob@s-i-inc.com
>
> {
> //Change code goes here. Get or Add instantiates object
> ItemLoc oldItemLoc = new ItemLoc(); //Concurrency check
> oldItemLoc.Loc_PartNo = itemLoc.Loc_PartNo; //save existing
> oldItemLoc.Loc_location = itemLoc.Loc_location;
> oldItemLoc.Loc_quantity = itemLoc.Loc_quantity;
> oldItemLoc.Loc_prime = itemLoc.Loc_prime;
> LoadItemLoc(); //take the changes out of text boxes and load object
> if (!ItemLocTbl.ChangeItemLoc(oldItemLoc, itemLoc))
> {
> MessageBox.Show("Error: " + ItemLocTbl.excpNumber.ToString() + " Could
not
> change location record. " +
> " Record may be in use by another user.", "Datebase error");
> }
> DisableAddEditMode();
> }
>
>
> private void LoadItemLoc()
> {
> itemLoc.Loc_PartNo = txtPartNo.Text;
> itemLoc.Loc_location = txtLoc.Text;
> itemLoc.Loc_quantity = Convert.ToDecimal(txtQty.Text);
> itemLoc.Loc_prime = txtPrime.Text;
> }
>
>
> public static bool ChangeItemLoc(ItemLoc oldItemLoc, ItemLoc newItemLoc)
> {
> string updateStmt = "UPDATE ItemLoc SET "
> + "loc_PartNo = @loc_PartNo, "
> + "loc_location = @loc_location, "
> + "loc_quantity = @loc_quantity, "
> + "loc_prime = @loc_prime "
> + "WHERE loc_PartNo = @Old_loc_PartNo "
> + "AND loc_location = @Old_loc_location "
> + "AND loc_quantity = @Old_loc_quantity "
> + "AND loc_prime = @Old_loc_prime "
> ; //notice ; on line by itself
>
> //Change to TRANSACTION form 08/30/04. Notice order of statements.
> SqlConnection mcs3Connection = MCS3_DB.GetConnection();
> mcs3Connection.Open(); //TRANSACTION requires this to be here
> SqlCommand updateCommand = new SqlCommand(updateStmt, mcs3Connection);
> SqlTransaction updateTrans = mcs3Connection.BeginTransaction(); //Begin
> trans
> updateCommand.Parameters.Add("@loc_PartNo", newItemLoc.Loc_PartNo);
> updateCommand.Parameters.Add("@loc_location", newItemLoc.Loc_location);
> updateCommand.Parameters.Add("@loc_quantity", newItemLoc.Loc_quantity);
> updateCommand.Parameters.Add("@loc_prime", newItemLoc.Loc_prime);
>
> updateCommand.Parameters.Add("@Old_loc_PartNo", oldItemLoc.Loc_PartNo);
> updateCommand.Parameters.Add("@Old_loc_location",
oldItemLoc.Loc_location);
> updateCommand.Parameters.Add("@Old_loc_quantity",
oldItemLoc.Loc_quantity);
> updateCommand.Parameters.Add("@Old_loc_prime", oldItemLoc.Loc_prime);
> try
> {
> updateCommand.Transaction = updateTrans; //Necessary? Yes
> updateCommand.ExecuteNonQuery();
> updateTrans.Commit();
> mcs3Connection.Close();
> return true;
> }
> catch (SqlException ex)
> {
> updateTrans.Rollback();
> excp = ex.Message;
> excpNumber = ex.Number;
> mcs3Connection.Close();
> return false;
> }
> }
>
>
- Next message: Morten Wennevik: "Re: error sending mail"
- Previous message: Nick Malik: "Re: Process.Start"
- In reply to: Robert Schuldenfrei: "New concurrency error"
- Next in thread: Robert Schuldenfrei: "Re: New concurrency error - resolved"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
