January 31, 2003 at 7:28 am
jpipes,
I appologize for my remarks yerteday. I overreacted a bit and I know you were just trying to help and I mistunderstood some of the comments. On second thought they were not so off-line.
Brfandon
February 3, 2003 at 6:13 am
jpipes request for seeing some errorhandling code is actually a very good tip, because the errorhandling code is essential for your problem, as your script will generate a conversion deadlock if two processes tries to get the new number concurrently.
The 'problem' is that HOLDLOCK will only hold a share lock to prevent others from updating the read row. And this does not serialize. But the Update issued afterward cannot update (the share lock prevents) and so will go on hold. But the other process cannot update either because of the first process' share lock. So you get a very classic conversion deadlock.
Just run below two scripts within five seconds after each other to see the problem:
User 1:
declare @RegNumber numeric(10,0)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Begin Transaction
select @RegNumber = RegistrationKey
from dbo.zMaster
-- with (HOLDLOCK)
waitfor delay '000:00:10'
update dbo.zMaster
set RegistrationKey = RegistrationKey + 1
commit transaction
User 2:
declare @RegNumber numeric(10,0)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Begin Transaction
select @RegNumber = RegistrationKey
from dbo.zMaster
-- with (HOLDLOCK)
waitfor delay '000:00:05'
update dbo.zMaster
set RegistrationKey = RegistrationKey + 1
commit transaction
With below tables:
create table zMaster( RegistrationKey numeric(10,1) primary key )
go
insert into zMaster( RegistrationKey ) values ( 1.0 )
go
So unless your error handling code deals nicely with the conversion deadlock you will get exactly the problem you mention.
Whether you specify HOLDLOCK or not should make only neglible difference because the SERIALIZABLE transaction already causes locks to be held. The fact that you see a difference must be due to different timing when you specify HOLDLOCK.
Antares' tip of doing the update and select in one go is the way to go. Antares example:
update dbo.zMaster
set @RegNumber = RegistrationKey, RegistrationKey = RegistrationKey + 1
will fetch the old number into @RegNumber; if you ever need the new value do:
update dbo.zMaster
set @RegNumber = RegistrationKey = RegistrationKey + 1
regards
jensk
February 3, 2003 at 11:15 am
I don't know if this is a by-product of submitting a simplified schema or not but the UniqueField column is defined as being NOT NULL but doesn't have a default or explicit value supplied. An error handler would have caught this.
HTH
Steve Hendricks
MCSD, MCDBA
AFS Consulting Group
(949) 588-9800 x15
Steve Hendricks
MCSD, MCDBA
Data Matrix
shendricks@afsconsulting.com
(949) 588-9800 x15
February 3, 2003 at 3:36 pm
I simplified the code to just the gist of it and the unique field is an identity column so the value should be generated from the database. The unique column is there so I will never have duplicate fields. I never have received any error message either...
The holdlock, although the document said was not needed since I requested the stored procedures to run serializeable in a prior statement. Maybe the question should be, how do I make SQL server provide separation between each user running a stored procedure, ie.. only one user at a time can run the stored procedure.
Thanks,
Brandon
February 4, 2003 at 3:32 am
You cannot, unfortunately, lock the SP from running in limited fashion. That is the reason for transactions and locking to prevent access until freed.
February 4, 2003 at 7:01 am
I needed the "next" number, not the current one, so I did some testing on Jensk2's adaptation of Antares' one line idea (see below).
if you ever need the new value do:
update dbo.zMaster
set @RegNumber = RegistrationKey = RegistrationKey + 1
It works great, I couldn't make it fail, but it scares me. Is this double equals an accepted method, just something that works currently (my setup is SQL7 SP4), or something Jensk2 dreamed up in a caffeine induced haze and has never been seen before? I'd really like to use this.
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither
February 4, 2003 at 7:48 am
It was actually pretty common back in the early eighties.......but I haven't seen it myself in many many years....
February 4, 2003 at 10:32 am
Still not uncommon but the only issue I ever saw was readability.
Consider
SET this = this + 1, @that = this + 1
as opposed to
SET @that = this = this + 1
Although the same, anyone who sees this may not understand it works right off.
In fact I hadn't even thought about that in years myself and had pretty much forgotten it could be done that way.
February 5, 2003 at 1:52 pm
quote:
it is worth noticing that an UPDLOCK ratherthan a HOLDLOCK may save the show. An UPDLOCK is a shared lock, so it
does not block readers. However, only one process at a time can have
an UPDLOCK, so if thw two processes are running this code simultaneously,
one of them will be held up here.
The kind of deadlock you are seeing is probably a conversion deadlock;
both processes wants to convert their shared lock to an exclusive lock,
and both are blocked by the other process holding a shared lock.
--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se
February 5, 2003 at 2:12 pm
"if you ever need the new value do:
update dbo.zMaster
set @RegNumber = RegistrationKey = RegistrationKey + 1
It works great, I couldn't make it fail, but it scares me. Is this double equals an accepted method, just something that works currently (my setup is SQL7 SP4), or something Jensk2 dreamed up in a caffeine induced haze and has never been seen before? I'd really like to use this."
Tsk, tsk, tsk ! Ofcourse its and accepted method and for sure it works great!
Please read BOL on UPDATE:
UPDATE
Changes existing data in a table.
Syntax
UPDATE
.........
SET
{ column_name = { expression | DEFAULT | NULL }
| @variable = expression
| @variable = column = expression } [ ,...n ]
BOL further explains loud and clear:
"@variable
Is a declared variable that is set to the value returned by expression.
SET @variable = column = expression sets the variable to the same value as the column.
This differs from SET @variable = column, column = expression, which sets the variable to the pre-update value of the column."
So:
SET @variable = column = expression
will put the new value in @variable, whereas:
SET @variable = column, column = expression
will put the old value in @variable
This is documented behaviour!!!!!
Ofcourse this only works logically/usable if the update only hits one row! Which i assume is why BOL states:
"Setting Variables and Columns
Variable names can be used in UPDATE statements to show the old and new values affected. This should only be used when the UPDATE statement affects a single record; if the UPDATE statement affects multiple records, the variables only contain the values for one of the updated rows."
No caffeine here!
/jensk2
February 5, 2003 at 2:23 pm
Jensk2: My hat is off to you. Not only do you find the best of all methods, you are able (and willing) to find the chapter and verse that supports it! I looked right at the Update topic in BOL and missed it. Thanks a bunch, this is now our official method for getting the next in a series of numbers. It would be quite simple to add an error handler later in the SP that resets the @NewNum to 0 or Null in the event of an error. Thanks again,
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither
February 5, 2003 at 2:30 pm
quote:
--------------------------------------------------------------------------------
"it is worth noticing that an UPDLOCK rather
than a HOLDLOCK may save the show. An UPDLOCK is a shared lock, so it
does not block readers. However, only one process at a time can have
an UPDLOCK, so if thw two processes are running this code simultaneously,
one of them will be held up here."
Yes, UPDLOCK will CURE the problem and should be considered if you need to support other SQL Backends.
But using two trips on such a hot-spot shared ressource as a Number Generator (on which you want true serialization) potentially will half the performance.
Hot-spot functions like number generators always needs special optimizations.
And other SQL Backends supports similar logic. On Oracle you would use an output parameter for the same job.
Actually, Oracle True Committed/Versioning reads can be mimmic'ed nicely with SQL Server READPAST, if one needs to maintain a 'Drawn but not used' number sequence, when one is not sure whether a transaction will in fact use the number given.
regards
jensk
February 6, 2003 at 5:50 am
We have the same situtation. As stated in the previous post we do use the UPDLOCK as show below :
Select @SerialKey = SerialKey
From SerialKey WITH ( ROWLOCK, UPDLOCK )
We have not had any problems with it to date and we have been using this code for over a year.
February 6, 2003 at 10:47 am
Great topic. Always tried to find good way of incrementing id numbers. I still use non identity int columns (primary key) for my id's and set by using
select @id = ISNULL(MAX(id),0)+1 from table1
IF @@ERROR ...
insert into tablea (...) values (id,...)
IF @@ERROR ...
I only have a small number of users to worry about and as yet not had any problems.
I suppose dumb luck has its rewards eh!!
Are identity columns the best and only way to store incremental rows. I have a natural aversion of using non int primary keys in tables. Are there an cons to this other then what is mentioned here?
Edited by - davidburrows on 02/06/2003 10:49:53 AM
Far away is close at hand in the images of elsewhere.
Anon.
February 6, 2003 at 12:42 pm
quote:
You cannot, unfortunately, lock the SP from running in limited fashion. That is the reason for transactions and locking to prevent access until freed.
You can, see sp_applock.
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply