Selecting Unique Record -- sql server (alternate)

Larry
7/1/2004 5:04:18 PM

Joe,

The stored procedure was written years ago by someone
who is long gone. I inherited it.

For new stored procedures we now know to prefix them with
something other than "sp_"

I know what a transaction is, I am trying to understand what
"serializable isolation level" is.

Someone suggested that I do this:

begin tran
Select @newRegNum = (Select min(Reg) from reg_number with
updlock,rowlock)

I am not sure about how to configure the transaction,
As this stored procedure is being called from another
stored procedure, at a point in the calling stored procedure
when another transaction is active.

Thank You,

Laurence Nuttall
Programmer Analyst III
UCLA - Division of Continuing Education

'-------------------------------

Here is the DDL for the table:

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Reg_Number]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Reg_Number]
GO

CREATE TABLE [dbo].[Reg_Number] (
[Reg_Number_ID] [int] NOT NULL ,
[Reg] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Login] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Modified_date] [smalldatetime] NULL ,
[Concurrent_use] [tinyint] NULL
) ON [PRIMARY]
GO

'---------------------------------------------------------------------------------------------------------------

[quoted text, click to view]

Joe Celko
7/1/2004 5:46:52 PM

The trick is to use a transaction and a serializable isolation level.

That is the first code I have seen with GOTO statements in over 15
years! Did you mean to put a "sp_" prefix on the code?

Could I suggest that instead of deleting the reg numbers as they are
issued, that you update the table to show the date and time they are
issued? No sense losing information and the ability to re-construct a
history. Might want to add the user info, too. Something like this:

CREATE PROCEDURE GetNextRegnum
@newregnum CHAR(6) OUTPUT
AS
BEGIN TRANS
SET @newregnum
=(SELECT MIN(reg)
FROM RegNumbers
WHERE issue_date IS NULL);
UPDATE RegNumbers
SET issue_date = CURRENT_TIMESTAMP
WHERE reg = @newregnum;
<< error handling, commit, rollback here >>
END;

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Devdex http://www.devdex.com ***

Larry
7/1/2004 5:51:56 PM

A fellow worker just pointed at that:

The @@Error will be zero, even if the
delete could not find a record to delete,

That is:

Delete from reg_number where reg = @newRegNum
IF @@Error <> 0

The if statment will never be true, that is
the @@ERROR will never be non zero.

Is this true?, if so then this stored procedure
never worked at all.

Thanks in Advance,

Larry

[quoted text, click to view]

Erland Sommarskog
7/3/2004 5:34:03 PM

Joe Celko (jcelko212@earthlink.net) writes:
[quoted text, click to view]

But since the default on SQL Server is READ COMMITTED, you must specify
that you need serializable. Thus you suggestion does not fly:

[quoted text, click to view]

You must say SET TRANSACTION ISOLATION LEVEL SERIALIZABLE first, or else
two simultaneous callers can get the same @newregnum. Using UPDLOCK as
Larry said in his later posting is also good.

Note that whatever the method, if there are two parallel calls, then
the second caller will be blocked until the first commits this transaction.
(Larry said that this procedure is part of a outside transaction.)

[quoted text, click to view]

Indeed a good suggestion. Just make sure that there is an index on
(index_date, reg).

[quoted text, click to view]

Hey! I use GOTO in my code occasionally. (Mainly for error exits.)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at

Erland Sommarskog
7/3/2004 5:34:35 PM

Larry (Bliff@Bliff.com) writes:
[quoted text, click to view]

Yes. Not find a matching row is not an error, but a perfectly normal thing.

[quoted text, click to view]

Well, @@error could be non-zero for other reasons, for instance a
constraint violation, or a lock timeout.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server (alternate)

sql server (alternate) : Selecting Unique Record