| Author |
Topic |
|
rb112978
Starting Member
11 Posts |
 Posted - 2005-02-19 : 02:39:43
|
| I plan to use a Stored Procedure in updating records,how do I include concurrency checking in my SP?ThanksProud to be Pinoy |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-02-19 : 04:07:55
|
| Can you give me an example of the problem you need to solve pls?Kristen |
 |
|
|
rb112978
Starting Member
11 Posts |
Posted - 2005-02-20 : 22:44:50
|
| This is how to:how to include concurrency checking in an update stored procedureProud to be Pinoy |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-02-21 : 03:32:49
|
| Take a look at the rowversion datatype.When you read the rows to the client you include the rowversion for every column.When you update the row, you check if the rowversion has changed, if it has then another client modified the row, and there is a concurrency issue.There is also binary_checksum() or checksum() functions that can be used in this situation.rockmoose |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-02-21 : 03:44:25
|
you could also do it with status column. kind of the same thing as RowVersion but you have to handle it alone.ie.: status=0 - row ok, status=1 - row in processing (don't do anyything with it until it's not 0 again).Go with the flow & have fun! Else fight the flow  |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-02-21 : 03:53:50
|
| That would be pessimistic concurrency, wouldn't it spirit ?A client locks the row when it acquires it, by setting status = 1 (row locked/in processing).rockmoose |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-02-21 : 04:05:45
|
well i wouldn't call it pessimistic i'm a complete optimist  .but yes. for concurency issues this is acctually not a bad way to do it.seen it done a few times and it worked like a charm.Go with the flow & have fun! Else fight the flow |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-02-21 : 04:47:40
|
If status of a row is set to 1, (row locked/processing), then how can one be sure that it gets set back to 0 ?What if the client just locks the row then bugs out.I rarely use the status approach (only for lengthier processes, and I really want to lock things down for that time), when I do I tend to get paranoid that rows will be locked indefinitely.Has anyone studied the concurrency checking that is generated by .NET framework ?(Last time I looked it checks all the fields to see if any other client modified any, ugly)It's monday, can you tell rockmoose |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-02-21 : 04:54:38
|
| that's what i meant by handling it yourself.it has problems like that yes. true that the app this was used in was a win app and not a net one.so if the user buged out you could still put the status back to zero.i think there was also a timer that put it to 0 after a while.there was a lot of work put into that but that's because it was thought to be the best way from the begining.i was a little green behind the ears to know any better then... but basicaly the problem could be only in updates. so it was done lieke this.sproc:1. check if the desired row's status was 1 or 0.2. if 1 bail out else update3. set update to 14. update.5. set update to 0someone might argue that about the time problem between 3 and 4. but realisticly that's not a problem.i would have to be 10's of updates per second to become one if you ask me...Go with the flow & have fun! Else fight the flow |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-02-21 : 05:12:18
|
| Between 1 & 4 another process can lock the row.I don't see a problem between 3 & 4.I would do it:a. set status = 1 where status = 0b. if @@rowcount = 0 bail out, already lockedc. update thecolumns, status = 0or rather, I would not use status at all for concurrency purposes.rockmoose |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-02-21 : 05:14:46
|
| oh well... 100 people 100 ways to do it...i just saw the stuff didn't even get near it.Go with the flow & have fun! Else fight the flow |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-02-21 : 07:21:23
|
set transaction isolation level really_grudgybegin transaction whinequote:
oh well... 100 people 100 ways to do it...
Ov course, some ways are better/less errorprone than others.I like the conclusion in this paper:[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_vstechart/html/vbtchConcurrencyInADONET.asp[/url]-------------------------------------------------------------------------I read that in SLQ2005, we get snapshot isoplation level as well (  )rockmoose |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-02-21 : 07:47:00
|
| right on moose!! Go with the flow & have fun! Else fight the flow |
|
|
|
|
Concurrency in Stored Procedure