Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Locking hints

Author  Topic 

obscuregirl
Starting Member

41 Posts

Posted - 2007-05-01 : 07:02:44
Hi

My VB.Net (with SQL Server backend) application currently allows more than one user to look at a particular record at the same time. This is not a problem unless both those users also try to update that record as well. One user's changes then overwrite the other's.

I've been reading up on locking hints but my database knowledge is a little scant and I'm also rather dense and need things spelling out for me!! So I have a few questions that I hope someone can help with:

If I add an updlock to my update SQL statement, this would allow both users to view the record but would only allow one user's changes through. Is that correct?

For the other user, would SQL Server return an error message that I can use to tell the user that their update has not worked?

Would I have to get my VB.NET application to re-get the record information so that the user who's update failed can see the changes made by the other user and reapply their own changes?

Does the updlock become unlocked once the record is updated or do I need to specifically unlock it somehow?

Thanks!

NeilG
Aged Yak Warrior

530 Posts

Posted - 2007-05-01 : 08:14:18
I personally prefer setting transaction isolation levels have a look in BOL see what you think, it is another path that you could look at.
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-01 : 08:38:09
see:

http://www.codeproject.com/useritems/concurrentupdates.asp

for a good technique, which is what I (basically) use and recommend.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

obscuregirl
Starting Member

41 Posts

Posted - 2007-05-02 : 04:37:23
quote:
Originally posted by NeilG

I personally prefer setting transaction isolation levels have a look in BOL see what you think, it is another path that you could look at.



I'm afraid I'm a real newbie! What is BOL?
Go to Top of Page

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-02 : 04:39:39
BOL is book online, SQL SERVER help file, when you press f1 from the query analyser the help will be opened

Chirag

http://chirikworld.blogspot.com/
Go to Top of Page

obscuregirl
Starting Member

41 Posts

Posted - 2007-05-02 : 04:41:51
quote:
Originally posted by chiragkhabaria

BOL is book online, SQL SERVER help file, when you press f1 from the query analyser the help will be opened

Chirag

http://chirikworld.blogspot.com/



Ah! I see! Thanks!
Go to Top of Page

obscuregirl
Starting Member

41 Posts

Posted - 2007-05-03 : 07:06:16
quote:
Originally posted by jsmith8858

see:
http://www.codeproject.com/useritems/concurrentupdates.asp
for a good technique, which is what I (basically) use and recommend.
- Jeff
http://weblogs.sqlteam.com/JeffS



Thanks this link has been really useful. However I haven't quite managed to get it working. I've got it checking the timestamp and that all works correctly.

When there has been an update, I get the user to re-get the information and re-apply their changes.
On the save, the timestamps match and the save goes through but my check to see if it's worked is based on the sql statement returning a row count
(like the example) but it doesn't return anything even when the update has been successful.
What am I doing wrong? A snippet of the code appears below:



str = "If Exists (Select * from ResearchGrant where ResearchGrantID
= " & gvar_ResGrantID & ") UPDATE ResearchGrant SET " & strTitle & ", " & strPropID & ", " & strFundCode & ", " & strExtBodyID
& ", " & strStartDate & ", " & strEndDate & ", " & strDuration & ", " & strHoD & ", " & strResOff & ", " & strSubmission & ", " &
strReturn & ", " & strTotAppFor & ", " & strTotAwd & ", " &
strFinCode & ", " & strSuccess & " FROM ResearchGrant WHERE ResearchGrant.ResearchGrantID = "
& gvar_ResGrantID & " and RGTimestamp = " & ToHexString(gvar_timestamp)

Dim cmdSP1 As New SqlClient.SqlCommand(str, conn)
cmdSP1.CommandType = CommandType.Text
Try
Dim drSearchDetails As SqlClient.SqlDataReader = Nothing
conn.Open()
drSearchDetails = cmdSP1.ExecuteReader
If drSearchDetails.HasRows = False Then
MessageBox.Show("Another user has changed this record since you opened it.
Click OK to update the information and re-apply your changes.",
"Update Error", MessageBoxButtons.OK,
MessageBoxIcon.Exclamation)
populateFields()
conn.Close()
Exit Sub
End If
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-03 : 08:31:47
First off, and this is important, USE PARAMETERS -- never, never, ever concatenate data together with a SQL statement and execute it. Parameters are cleaner, faster, safer, shorter, more accurrate -- there is not a single reason to avoid using them. Please read this carefully: http://weblogs.sqlteam.com/jeffs/archive/2006/07/21/10728.aspx

and also read this about sql injection:

http://en.wikipedia.org/wiki/SQL_injection

Once you re-write your code using best practices (i.e., parameters) and post it here, I'll be happy to help you. I'll give you a hint, however: An Update() doesn't return rows.


- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-03 : 08:32:51
Also, and this is just as important: Please format your code so that we can read it! Add line breaks so that the huge long lines wrap; this whole web page gets really screwed up and impossible to read when lines of code make the page about 4-5 screens wide. You can edit previous posts by clicking the little pencil icon at the top of it.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

obscuregirl
Starting Member

41 Posts

Posted - 2007-05-03 : 10:29:36
Thanks for that. I've edited the post so hopefully it'll display ok for you now. The link for parameters was very useful, thanks.

I've re-written the code. I must confess it isn't working yet, I'm getting type conversion issues that I'm in the process of sorting out (all my fields are text fields - long story!), but am I on the right lines with checking the return value of the Execute.NonQuery statement to see if my update has worked?

Dim conn As New SqlConnection(DBConnection.strConnection)
Dim str As String
Dim paramInt As Integer
Dim ret As Integer

Dim cmdSP1 As New SqlClient.SqlCommand("", conn)
cmdSP1.CommandType = CommandType.Text
cmdSP1.CommandText = "If Exists (Select * from ResearchGrant where
ResearchGrantID = @ID) UPDATE ResearchGrant SET Title = @Title,
StaffProposerID = @StaffID, FundersCode = @FCode, ExternalBodyID =
@ExBodID, StartDate = @SDate, EndDate = @EDate, [Duration(Months)] =
@Dur, DateSubmitted = @HoD, DateRecievedByResearchOffice = @ResOff,
DateSubmittedToExternalBody = @Subm, DateReturnedByExternalBody =
@Return, AmountAppliedFor = @TotAppFor, AmountAwarded = @TotAwd,
FinanceCode = @FinCode, Successful = @Success FROM ResearchGrant
WHERE ResearchGrant.ResearchGrantID = @ID And RGTimestamp = @TStamp"

cmdSP1.Parameters.Add("@ID", SqlDbType.Int).Value = (paramInt =
Convert.ToInt32(gvar_ResGrantID))
cmdSP1.Parameters.Add("@Title", SqlDbType.NVarChar).Value =
txtResTitle.Text
cmdSP1.Parameters.Add("@StaffID", SqlDbType.Int).Value = coPropID.Text
cmdSP1.Parameters.Add("@FCode", SqlDbType.Int).Value =
coFundersCode.Text
cmdSP1.Parameters.Add("@ExBodID", SqlDbType.Int).Value = coExtBodyID.Text
cmdSP1.Parameters.Add("@SDate", SqlDbType.SmallDateTime).Value = txtRGStDt.Text
cmdSP1.Parameters.Add("@EDate", SqlDbType.SmallDateTime).Value = txtRGEndDt.Text
cmdSP1.Parameters.Add("@Dur", SqlDbType.Int).Value =
nuDuration.Value
cmdSP1.Parameters.Add("@HoD", SqlDbType.SmallDateTime).Value =
txtHOD.Text
cmdSP1.Parameters.Add("@ResOff", SqlDbType.SmallDateTime).Value = txtReOff.Text
cmdSP1.Parameters.Add("@Subm", SqlDbType.SmallDateTime).Value = txtSubmission.Text
cmdSP1.Parameters.Add("@Return", SqlDbType.SmallDateTime).Value = txtReturn.Text
cmdSP1.Parameters.Add("@TotAppFor", SqlDbType.SmallDateTime).Value = nuTotAppFor.Value
cmdSP1.Parameters.Add("@TotAwd", SqlDbType.SmallDateTime).Value =
nuTotAwd.Value
cmdSP1.Parameters.Add("@FinCode", SqlDbType.Int).Value =
coFinanceCode.Text
cmdSP1.Parameters.Add("@Success", SqlDbType.Int).Value = coSuccessful.Text
cmdSP1.Parameters.Add("@TStamp", SqlDbType.Timestamp).Value = ToHexString(gvar_timestamp)

Try
conn.Open()
ret = cmdSP1.ExecuteNonQuery()
If ret = 0 Then
MessageBox.Show("Another user has changed this record since you
opened it. Click OK to update the information and re-apply your
changes.", "Update Error", MessageBoxButtons.OK,
MessageBoxIcon.Exclamation)
populateFields()
conn.Close()
Exit Sub
End If
Catch ex As SqlException
Dim str_mess As String
str_mess = ex.Message
MessageBox.Show(ex.Message, "SQL Error",
MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
Finally
conn.Close()
End Try
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-03 : 11:00:36
Why didn't you follow the technique in the article? You really should be using stored procedures. I was about to write some sample code for you, but the article demonstrates it quite well. By simply using OUTPUT parameters, you can return the results of the update very easily. I really have nothing more to add other than what the article says, I'm afraid. Are there parts of the article you don't understand or disagree with? is there a reason you aren't using stored procedures?


- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

obscuregirl
Starting Member

41 Posts

Posted - 2007-05-03 : 11:18:35
I did follow the technique in the article! Why do you think I haven't?

It's a long story about not using stored procedures, but basically when I started writing the application a year ago, I was told not to use stored procedures, hence the SQL strings I was using. Whether or not this was the best way to do it is clearly debateable but given that this was my first try at a VB.Net application and my first encounter with SQL Server, I did the best that my limited knowledge and experience allowed. I am now having to fix a problem with concurrency because two users could update the same record at the same time - again this oversight was down to a lack of experience. I would love to be able to go back and re-write the code to comply with best practice but unfortunately deadlines do not allow at present, so I'm limited to getting it working as best as possible.

I appreciate your help but I find your tone a little patronising. You clearly have a wealth of knowledge and experience, not everyone has that luxury and some of us are still learning. Treating someone as if they are stupid just because they don't know or understand something is a little unfair. Thank you for your help. I'll look elsewhere for the rest.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2007-05-03 : 11:21:06
quote:
Originally posted by NeilG

I personally prefer setting transaction isolation levels have a look in BOL see what you think, it is another path that you could look at.




No, No, No...and No again

Fist off, use Stored Procedures

Second off, make sure all of your tables have at least an Updated Timstamp

And to prevent data collisions, make sure you and to the predicate

WHERE UPD_dt = @Up_Dt from the original select

If the update fails, the do an existance check without the datetime...if it does exists, then data was modified..if not then it was deleted



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-03 : 11:28:59
so why do you dislike setting the transaction isolation levels, brett?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2007-05-03 : 11:59:54
Because...it's dangerous, and a bad piece of advice for someone new to sql server



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-03 : 12:00:44
quote:
Originally posted by obscuregirl

I did follow the technique in the article! Why do you think I haven't?

It's a long story about not using stored procedures, but basically when I started writing the application a year ago, I was told not to use stored procedures, hence the SQL strings I was using. Whether or not this was the best way to do it is clearly debateable but given that this was my first try at a VB.Net application and my first encounter with SQL Server, I did the best that my limited knowledge and experience allowed. I am now having to fix a problem with concurrency because two users could update the same record at the same time - again this oversight was down to a lack of experience. I would love to be able to go back and re-write the code to comply with best practice but unfortunately deadlines do not allow at present, so I'm limited to getting it working as best as possible.

I appreciate your help but I find your tone a little patronising. You clearly have a wealth of knowledge and experience, not everyone has that luxury and some of us are still learning. Treating someone as if they are stupid just because they don't know or understand something is a little unfair. Thank you for your help. I'll look elsewhere for the rest.



I apologize, I didn't mean to come off that way. I just couldn't add anything more than what the article wrote, and I was wondering why you weren't using stored procedures, since the entire premise of the article relies on using a stored procedure. it's as simple as that. We all interpret the tone of an email differently, so it is hard to tell if someone is yelling at you and insulting you or just trying to strongly recommend avoiding bad practices, it is tough to know for sure. Anyway, like I said, I really wanted to help you, but everything I started to type would be pretty much the same as what the article said, so all I can really do is refer you to it and ask why you weren't following it to see if we can address that. if you didn't understand the point or value of stored procedures previously, now you do, so instead of saying "it's too late! nothing can be done" it may be worthwhile to simply create the stored procedure and learn how to use them. YOu might be surprised at how much easier it is to write and debug and test and optimise, and even if you end up not liking it, you have learned a new skill.

so,again, I apologize if you felt that I am treating you stupid, but here's my advice for you: don't let ego or feelings get in the way of learning and using best practices. If I posted 100 lines of bad code, someone called me "clueless" and demonstrated how to do it cleanly in only 2 lines, while I might be slightly insulted by the remark, I would be much, much more grateful for the knowledge gained.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2007-05-03 : 13:09:38
quote:
Originally posted by obscuregirl

a long story about not using stored procedures, but basically when I started writing the application a year ago, I was told not to use stored procedures



I like stories, and I'd love to hear this one...and if you are in the NY Metro area, I'd even say I'd talk to the person who told you this



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

NeilG
Aged Yak Warrior

530 Posts

Posted - 2007-05-03 : 13:40:12
quote:
Originally posted by X002548

quote:
Originally posted by NeilG

I personally prefer setting transaction isolation levels have a look in BOL see what you think, it is another path that you could look at.




No, No, No...and No again

Fist off, use Stored Procedures

Second off, make sure all of your tables have at least an Updated Timstamp

And to prevent data collisions, make sure you and to the predicate

WHERE UPD_dt = @Up_Dt from the original select

If the update fails, the do an existance check without the datetime...if it does exists, then data was modified..if not then it was deleted



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam







Well it was only a suggestion and WOULD work, however I will agree it might not be the perfect way but is another option to look at.

remember everyone has there own opinions on different methods in which to sort a problem, which means that yours is not always the right one, that's the point of a forum isn't it, to get different peoples views on your current problem.
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-03 : 13:46:52
Using a timestamp column is a good approach as compared to using transaction isolation levels. Using transactions explicitly can slow down the performance and also lead to lot of wait resources and eventually blocking/spin locks etc.

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2007-05-03 : 13:59:08
quote:
Originally posted by NeilG
remember everyone has there own opinions on different methods in which to sort a problem, which means that yours is not always the right one, that's the point of a forum isn't it, to get different peoples views on your current problem.



Perhaps, and you are free to take or not take advice...I try to present best practice type advice.

If someone tells someone new to look at isolation levels, then it is also my right to disagree with that

And yes, we do see alot of different views here...oh, and please feel free to disreagard anything I say

Is it 5:00 yet?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

NeilG
Aged Yak Warrior

530 Posts

Posted - 2007-05-03 : 14:20:07
Is there any articles on here about using the timestamp method for updates that I can look at as I would be intrested in this, as a new person to sql I can only learn from the masters
Go to Top of Page
    Next Page

- Advertisement -