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.
Author |
Topic |
obscuregirl
Starting Member
41 Posts |
Posted - 2007-05-01 : 07:02:44
|
HiMy 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. |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
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? |
|
|
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 openedChiraghttp://chirikworld.blogspot.com/ |
|
|
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 openedChiraghttp://chirikworld.blogspot.com/
Ah! I see! Thanks! |
|
|
obscuregirl
Starting Member
41 Posts |
Posted - 2007-05-03 : 07:06:16
|
quote: Originally posted by jsmith8858 see: http://www.codeproject.com/useritems/concurrentupdates.aspfor a good technique, which is what I (basically) use and recommend.- Jeffhttp://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.TextTryDim drSearchDetails As SqlClient.SqlDataReader = Nothingconn.Open()drSearchDetails = cmdSP1.ExecuteReaderIf drSearchDetails.HasRows = False ThenMessageBox.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 SubEnd If |
|
|
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.aspxand also read this about sql injection:http://en.wikipedia.org/wiki/SQL_injectionOnce 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.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
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.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
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 StringDim paramInt As IntegerDim ret As IntegerDim cmdSP1 As New SqlClient.SqlCommand("", conn)cmdSP1.CommandType = CommandType.TextcmdSP1.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.TextcmdSP1.Parameters.Add("@StaffID", SqlDbType.Int).Value = coPropID.TextcmdSP1.Parameters.Add("@FCode", SqlDbType.Int).Value = coFundersCode.TextcmdSP1.Parameters.Add("@ExBodID", SqlDbType.Int).Value = coExtBodyID.TextcmdSP1.Parameters.Add("@SDate", SqlDbType.SmallDateTime).Value = txtRGStDt.TextcmdSP1.Parameters.Add("@EDate", SqlDbType.SmallDateTime).Value = txtRGEndDt.TextcmdSP1.Parameters.Add("@Dur", SqlDbType.Int).Value = nuDuration.ValuecmdSP1.Parameters.Add("@HoD", SqlDbType.SmallDateTime).Value = txtHOD.TextcmdSP1.Parameters.Add("@ResOff", SqlDbType.SmallDateTime).Value = txtReOff.TextcmdSP1.Parameters.Add("@Subm", SqlDbType.SmallDateTime).Value = txtSubmission.TextcmdSP1.Parameters.Add("@Return", SqlDbType.SmallDateTime).Value = txtReturn.TextcmdSP1.Parameters.Add("@TotAppFor", SqlDbType.SmallDateTime).Value = nuTotAppFor.ValuecmdSP1.Parameters.Add("@TotAwd", SqlDbType.SmallDateTime).Value = nuTotAwd.ValuecmdSP1.Parameters.Add("@FinCode", SqlDbType.Int).Value = coFinanceCode.TextcmdSP1.Parameters.Add("@Success", SqlDbType.Int).Value = coSuccessful.TextcmdSP1.Parameters.Add("@TStamp", SqlDbType.Timestamp).Value = ToHexString(gvar_timestamp)Tryconn.Open()ret = cmdSP1.ExecuteNonQuery()If ret = 0 ThenMessageBox.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 SubEnd IfCatch ex As SqlExceptionDim str_mess As Stringstr_mess = ex.MessageMessageBox.Show(ex.Message, "SQL Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)Finallyconn.Close()End Try |
|
|
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?- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
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. |
|
|
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 againFist off, use Stored ProceduresSecond off, make sure all of your tables have at least an Updated TimstampAnd to prevent data collisions, make sure you and to the predicateWHERE UPD_dt = @Up_Dt from the original selectIf the update fails, the do an existance check without the datetime...if it does exists, then data was modified..if not then it was deletedBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
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 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
X002548
Not Just a Number
15586 Posts |
|
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.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
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 thisBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
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 againFist off, use Stored ProceduresSecond off, make sure all of your tables have at least an Updated TimstampAnd to prevent data collisions, make sure you and to the predicateWHERE UPD_dt = @Up_Dt from the original selectIf the update fails, the do an existance check without the datetime...if it does exists, then data was modified..if not then it was deletedBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd 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. |
|
|
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 NethiSQL Server MVP************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2007-05-03 : 13:59:08
|
quote: Originally posted by NeilGremember 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 thatAnd yes, we do see alot of different views here...oh, and please feel free to disreagard anything I sayIs it 5:00 yet?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
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 |
|
|
Next Page
|
|
|
|
|