есть скрипт:
declare @RecordID as identifiers
declare @InvoiceNumber as identifiers
set @recordID = xxxx
set @InvoiceNumber = null
select top 1 @InvoiceNumber = a.InvoiceNumber
from MDSInvoiceNumbers a
left outer join MDSCustomerBalance b
on a.InvoiceNumber = b.InvoiceNumber
where b.BalanceID is null
order by a.InvoiceNumber
if isnull(@InvoiceNumber,0) = 0
begin
insert into MDSInvoiceNumbers (null_field) values(null)
set @InvoiceNumber = @@identity
end
update MDSCustomerBalance set InvoiceNumber = @InvoiceNumber where RecordID = @RecordID
Этот скприпт может одновременно вызываться во многих сессиях из процедур и триггеров, необходимо чтобы в первом селекте выбиралась одна запись и лочилась на время пока InvoiceNumber не попадет в MDSCustomerBalance таблицу.
Как это лучше всего сделать?
Это описание таблиц если надо:
CREATE TABLE [dbo].[MDSInvoiceNumbers](
[InvoiceNumber] [dbo].[identifiers] IDENTITY(10001,1) NOT NULL,
[null_field] [char](1) NULL,
CONSTRAINT [PK_MDSInvoiceNumbers] PRIMARY KEY CLUSTERED ([InvoiceNumber] ASC)
) ON [PRIMARY]
CREATE TABLE [dbo].[MDSCustomerBalance](
[BalanceID] [dbo].[identifiers] IDENTITY(1,1) NOT NULL,
[InvoiceNumber] [dbo].[identifiers] NULL,
...,
CONSTRAINT [PK_MDSCustomerBalance] PRIMARY KEY CLUSTERED ([BalanceID] ASC)
) ON [PRIMARY]
ALTER TABLE [dbo].[MDSCustomerBalance] WITH NOCHECK ADD CONSTRAINT [FK_MDSCustomerBalance_MDSInvoiceNumbers] FOREIGN KEY([InvoiceNumber])
REFERENCES [dbo].[MDSInvoiceNumbers] ([InvoiceNumber])
GO
Здравствуйте, gwg-605, Вы писали:
G6>есть скрипт:
G6>G6>declare @RecordID as identifiers
G6>declare @InvoiceNumber as identifiers
G6>set @recordID = xxxx
G6>set @InvoiceNumber = null
G6>select top 1 @InvoiceNumber = a.InvoiceNumber
G6>from MDSInvoiceNumbers a
G6> left outer join MDSCustomerBalance b
G6> on a.InvoiceNumber = b.InvoiceNumber
G6>where b.BalanceID is null
G6>order by a.InvoiceNumber
G6>if isnull(@InvoiceNumber,0) = 0
G6> begin
G6> insert into MDSInvoiceNumbers (null_field) values(null)
G6> set @InvoiceNumber = @@identity
G6> end
G6>update MDSCustomerBalance set InvoiceNumber = @InvoiceNumber where RecordID = @RecordID
G6>
G6>Этот скприпт может одновременно вызываться во многих сессиях из процедур и триггеров, необходимо чтобы в первом селекте выбиралась одна запись и лочилась на время пока InvoiceNumber не попадет в MDSCustomerBalance таблицу.
G6>Как это лучше всего сделать?
BEGIN TRAN
select top 1 @InvoiceNumber = a.InvoiceNumber
from MDSInvoiceNumbers a (ROWLOCK, UPDLOCK, READPAST)
left outer join MDSCustomerBalance b (ROWLOCK, UPDLOCK, READPAST)
on a.InvoiceNumber = b.InvoiceNumber
where b.BalanceID is null
order by a.InvoiceNumber
if isnull(@InvoiceNumber,0) = 0
begin
insert into MDSInvoiceNumbers WITH (ROWLOCK, HOLDLOCK)(null_field) values(null)
set @InvoiceNumber = SCOPE_IDENTITY()
end
update MDSCustomerBalance WITH (ROWLOCK) set InvoiceNumber = @InvoiceNumber where RecordID = @RecordID
COMMIT TRAN
