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 |
|
DrorMaor
Starting Member
4 Posts |
 Posted - 2008-10-06 : 08:16:58
|
| Hi there,I'm developing an VB6 app that uses SQL occasionally, and I have this question: I have a table that one if its fields increments with each update. Once the new value is updated (incremented), I "select" it to the VB6 app.What we're afraid of is, what will happen if inbetween the "update" and "select", a different user will "select" it, and the two users will get the identical value. How should I go about locking this record for the duration of the two DB calls?Thanks a lot |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-06 : 08:21:42
|
| do you mean to say value is incremented and returned on selecting rather than changing in db itself? |
 |
|
|
DrorMaor
Starting Member
4 Posts |
Posted - 2008-10-06 : 08:39:47
|
| Thanks so much for your reply.Yes, the lines of code in VB are (I shortened them here for clarity):------------------------------------update drafts set counter = counter + 1 where id =...select counter from drafts where id =...------------------------------------I want to lock that record before the "update", and unlock it right after the "select" |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-06 : 09:55:53
|
| is that what you're looking for:-http://sqlblog.com/blogs/louis_davidson/archive/2007/05/07/using-application-locks-to-implement-a-critical-section-in-t-sql-code.aspx |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-10-06 : 10:47:53
|
Why not use IDENTITY columns instead?
E 12°55'05.63"N 56°04'39.26" |
|
|
|
DrorMaor
Starting Member
4 Posts |
Posted - 2008-10-06 : 11:18:51
|
| Thanks visakh16, and that article touches the points. I'm wondering how I can do all this from Visual Basic itself, and not in a Stored Procedure, as our department head ruled against writing external code. |
|
|
|
DrorMaor
Starting Member
4 Posts |
Posted - 2008-10-06 : 11:21:16
|
| Peso, we thought of doing something like that, but each "draft" (as we call it) gets its own row, and the "counter" field increments - rather than having a unique row for each time the row gets accessed. Thanks. |
|
|
|
|
|
|
|
|
Record Locking