UDF - Is Transaction Control possible?

Question

UDF - Is Transaction Control possible?

NewbyUser

SSC Rookie

Points: 45
More actions
September 7, 2016 at 8:18 am

#150504

Hi everybody,
i have a problem with a scalar valued function. There are two systems who will use the function to create new "OrderNumbers".
Sometimes the two system use the function at the same time and this leads to duplicated Numbers for different Processes.
The statment in the function delivers the lastNumber + 1 from the Orders-Table:
-> Statement: SELECT @ret = MAX(OrderNumber), 0) +1 FROM Order
Is it possible to lock the number or lock the function for one system?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246986 More actions · Answer 1

NewbyUser (9/7/2016)
Hi everybody,
i have a problem with a scalar valued function. There are two systems who will use the function to create new "OrderNumbers".
Sometimes the two system use the function at the same time and this leads to duplicated Numbers for different Processes.
The statment in the function delivers the lastNumber + 1 from the Orders-Table:
-> Statement: SELECT @ret = MAX(OrderNumber), 0) +1 FROM Order
Is it possible to lock the number or lock the function for one system?

Have you considered using an IDENTITY() column instead? Your concurrency issues would be alleviated.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

NewbyUser SSC Rookie Points: 45 More actions · Answer 2

Yes of course, my first idea was an IDENTITY() column.

But i need two different numbers - one is starting with 1 and the other is starting with 5.

Therefore i adjust my function with one parameter and the query have a where condition.

Query:

SELECT @ret = MAX(OrderNumber), 0) +1 FROM Order

where parameter = @par

Therefore the IDENTITY() column is not the best solution for me.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

http://source.entelect.co.za/why-is-this-upsert-code-broken

While it talks about insert/update pairs, your manual sequence code will have the same problems.

Edit: And if you need an incrementing value that starts at 5,

SomeNumber INT IDENTITY (5,1)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

NewbyUser SSC Rookie Points: 45 More actions · Answer 4

GilaMonster (9/8/2016)[/b]

http://source.entelect.co.za/why-is-this-upsert-code-broken

While it talks about insert/update pairs, your manual sequence code will have the same problems.

Edit: And if you need an incrementing value that starts at 5,

SomeNumber INT IDENTITY (5,1)

I know about this incrementing Value - but in this way i cannot separate between starting with 1 or 5

Phil Parkin SSC Guru Points: 246986 More actions · Answer 5

Maybe you could make use of two or more SEQUENCEs. Take a read of this and see whether it could be of help.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

NewbyUser SSC Rookie Points: 45 More actions · Answer 6

Thanks for your tips.

The Sequenze-Function looks nice - but the function is not available for SQLServer 2005.

I just should explain my problem again.

Basically there is a procedure which creates orders every 20 Minutes.

There are two type for orders and the should have a own number range.

To create the order_id as identifier there is a function which creates the number on the input paramater.

Type A starts with 1000000 - Type B starts with 55000000.

Statement:

SELECT @ret = MAX(OrderNumber), 0) +1 FROM Order where type = @input_type

This works also perfect.

But now there is a "Admin"-System, this could also creates Orders.

Is it possible to block the next number for the Admin-System thats no duplicate OrderIds are possible?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

NewbyUser (9/12/2016)
Is it possible to block the next number for the Admin-System thats no duplicate OrderIds are possible?

GilaMonster (9/8/2016)
http://source.entelect.co.za/why-is-this-upsert-code-broken

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

NewbyUser SSC Rookie Points: 45 More actions · Answer 8

@gilamonster

Nice to know - but this code doesn't work in functions

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

but this code doesn't work in functions

The isolation level and transaction will have to go into whatever calls the function, but the rest should work in scalar UDFs.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass