| Author |
Locking while updating
|
|
|
| We are currently using VB to create order numbers. Each user requests an
order number as and when required. The problem is that the next in the
sequence seems to be allocated on the odd occasion to more than one user.
This being due to the requests coming at almost the same time but not as yet
assigned to the user. Is there a way to lock a request to a table until a
previous process is completed before continuing to the next request. This
will help eliminate our issue. If someone could point me in the right
direction then that would be great.
Thank you ever so much
| |
| Andrew J. Kelly 2006-01-05, 1:23 pm |
| You would have to provide the DDL and samples of the code you are using for
us to even begin to suggest something that might be appropriate to you. Do
you have a seperate table that holds the Order numbers? How do you generate
the next order number etc?
--
Andrew J. Kelly SQL MVP
"niel" <niel@fabs.com> wrote in message
news:OvOAblhEGHA.2912@tk2msftngp13.phx.gbl...
> We are currently using VB to create order numbers. Each user requests an
> order number as and when required. The problem is that the next in the
> sequence seems to be allocated on the odd occasion to more than one user.
> This being due to the requests coming at almost the same time but not as
> yet assigned to the user. Is there a way to lock a request to a table
> until a previous process is completed before continuing to the next
> request. This will help eliminate our issue. If someone could point me in
> the right direction then that would be great.
>
> Thank you ever so much
>
| |
| David Gugick 2006-01-05, 8:23 pm |
| niel wrote:
> We are currently using VB to create order numbers. Each user requests
> an order number as and when required. The problem is that the next in
> the sequence seems to be allocated on the odd occasion to more than
> one user. This being due to the requests coming at almost the same
> time but not as yet assigned to the user. Is there a way to lock a
> request to a table until a previous process is completed before
> continuing to the next request. This will help eliminate our issue.
> If someone could point me in the right direction then that would be
> great.
> Thank you ever so much
You need to hold a lock on the id table until the new value is retrieved
and updated. You can retrieve the value and update it all in one step
using code that Andrew Kelly posted a while back:
Update dbo.MyKeyTable Set @NewID = NextID = (NextID + 1) Where
MyKeyTable = @MyKeyTable
http://groups.google.com/group/micr...6d8c3
099
--
David Gugick
Quest Software
www.quest.com
| |
| jxstern 2006-01-05, 8:23 pm |
| On Thu, 5 Jan 2006 17:34:48 -0500, "David Gugick"
<david.gugick-nospam@quest.com> wrote:
>You need to hold a lock on the id table until the new value is retrieved
>and updated. You can retrieve the value and update it all in one step
>using code that Andrew Kelly posted a while back:
>
>Update dbo.MyKeyTable Set @NewID = NextID = (NextID + 1) Where
>MyKeyTable = @MyKeyTable
>
>http://groups.google.com/group/micr...6d8c3
099
That's very nice.
The vanilla way of doing it might involve something like:
set isolation level serializable
begin transaction
select @lastid = lastid from mylocktable where pk = 'idkey'
set @nextid = @lastid + 1
update mylocktable set lastid = @nextid where pk = 'idkey'
select @nextid as nextid
commit transaction
J.
| |
| Gregor Stefka 2006-01-08, 9:23 am |
| jxstern schrieb:
> On Thu, 5 Jan 2006 17:34:48 -0500, "David Gugick"
> <david.gugick-nospam@quest.com> wrote:
>
>
>
>
> That's very nice.
>
> The vanilla way of doing it might involve something like:
>
> set isolation level serializable
> begin transaction
> select @lastid = lastid from mylocktable where pk = 'idkey'
> set @nextid = @lastid + 1
> update mylocktable set lastid = @nextid where pk = 'idkey'
> select @nextid as nextid
> commit transaction
>
> J.
>
....and if you continue in this session dont forget to set your Isolation
level back to its old value...
HTH
Stefka
| |
| Bazili Swioklo 2006-01-10, 8:23 pm |
|
"Gregor Stefka" wrote:
> jxstern schrieb:
>
> ....and if you continue in this session dont forget to set your Isolation
> level back to its old value...
>
> HTH
> Stefka
to avoid the set and unset the isolation level use the hints holdlock and
updlock on the select clause. They will lock during the select and release
the lock after the commit.
begin transaction
select @lastid = lastid from mylocktable (holdlock updlock) where pk = 'idkey'
set @nextid = @lastid + 1
update mylocktable set lastid = @nextid where pk = 'idkey'
select @nextid as nextid
commit transaction
|
|
|
|