Possible to lock a row within a stored procedure in SQL Server 2000?

[M Wells]

Hi All,

I have a table that holds pregenerated member IDs.

This table is used to assign an available member id to web site
visitors who choose to register with the site

So, conceptually the process has been, from the site (in ASP), to:

- select the top record from the members table where the assigned flag
= 0

- update the row with details about the new member and change the
assigned flag to 1

- return the selected member id to the web page

Now I'm dealing with the idea that there may be brief, high traffic
periods of registration, so I'm trying to build a method (stored
procedure?) that will ensure the same member id isn't returned by the
select statement if more than 1 request to register happens at the
same instant.

So, my question is, is there a way, once a record has been selected,
to exclude that record from other select requests, within the bounds
of a stored procedure?

ie:

- select statement is executed and row is instantly locked; any other
select statement running at that exact moment will receive a different
row returned and sill similarly lock it, ad nauseum for as many
simultaneous select statements as take place
- row is updated with details and flag is updated to indicate the
member id is no longer unassigned
- row is released for general purposes etc

If what I'm suggesting above isn't practical, can anyone help me
identify a different way of achieving the same result?

Any help immensely, immensely appreciated!

Much warmth,

Murray

[Simon Hayes]

M Wells <pl**********@planetthoughtful.org> wrote in message news:<j1********************************@4ax.com>. ..

Hi All,

I have a table that holds pregenerated member IDs.

This table is used to assign an available member id to web site
visitors who choose to register with the site

So, conceptually the process has been, from the site (in ASP), to:

- select the top record from the members table where the assigned flag
= 0

- update the row with details about the new member and change the
assigned flag to 1

- return the selected member id to the web page

Now I'm dealing with the idea that there may be brief, high traffic
periods of registration, so I'm trying to build a method (stored
procedure?) that will ensure the same member id isn't returned by the
select statement if more than 1 request to register happens at the
same instant.

So, my question is, is there a way, once a record has been selected,
to exclude that record from other select requests, within the bounds
of a stored procedure?

ie:

- select statement is executed and row is instantly locked; any other
select statement running at that exact moment will receive a different
row returned and sill similarly lock it, ad nauseum for as many
simultaneous select statements as take place
- row is updated with details and flag is updated to indicate the
member id is no longer unassigned
- row is released for general purposes etc

If what I'm suggesting above isn't practical, can anyone help me
identify a different way of achieving the same result?

Any help immensely, immensely appreciated!

Much warmth,

Murray

It's not clear from your description why you need to generate the IDs
in advance - a simpler approach might be to use an IDENTITY column,
and insert directly into the members table for a new registration. You
can then return the system-generated value to the client using
scope_identity(). This assumes, of course, that the membership ID is
simply a number, with no other meaning:

create table dbo.Members (
MemberID int identity(1,1) primary key,
FirstName varchar(50) not null,
LastName varchar(50) not null
)
go

insert into dbo.Members (FirstName, LastName)
values ('Murray', 'Wells')

select 'Your ID is: ' + cast(scope_identity() as char(2))
go

If you do need some control over the value of the membership ID, then
one of these approaches might be suitable:

http://groups.google.com/groups?q=sq...ewin.ch&rnum=1

If this doesn't help, you may want to post the structure (CREATE
TABLE) of your members table, along with some sample data, and explain
exactly what you want to return to the client.

Simon

[M Wells]

On 4 Feb 2004 23:44:05 -0800, sq*@hayes.ch (Simon Hayes) wrote:

M Wells <pl**********@planetthoughtful.org> wrote in message news:<j1********************************@4ax.com>. ..

Hi All,

[ here there be snippage ]

It's not clear from your description why you need to generate the IDs
in advance - a simpler approach might be to use an IDENTITY column,
and insert directly into the members table for a new registration. You
can then return the system-generated value to the client using
scope_identity(). This assumes, of course, that the membership ID is
simply a number, with no other meaning:

create table dbo.Members (
MemberID int identity(1,1) primary key,
FirstName varchar(50) not null,
LastName varchar(50) not null
)
go

insert into dbo.Members (FirstName, LastName)
values ('Murray', 'Wells')

select 'Your ID is: ' + cast(scope_identity() as char(2))
go

If you do need some control over the value of the membership ID, then
one of these approaches might be suitable:

http://groups.google.com/groups?q=sq...ewin.ch&rnum=1

If this doesn't help, you may want to post the structure (CREATE
TABLE) of your members table, along with some sample data, and explain
exactly what you want to return to the client.

Hi Simon,

Thank you very much for your help with this!

Unfortunately, the need to use pre-generated member ids is a foregone
issue; it's not something I have any control over.

I looked at the two examples in the link you provided. The first makes
sense to me, but I can't formulate a query that makes it do exactly
what I want. And I don't yet know enough about locking in SQL Server
to know if the locking hint (UPDLOCK) does what I hope it will do.

To give you a little extra detail, my test table definition is:

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

CREATE TABLE [dbo].[tblmembers] (
[recid] [int] IDENTITY (1, 1) NOT NULL ,
[memid] [numeric](18, 0) NULL ,
[memname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[mememail] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[activated] [bit] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblmembers] WITH NOCHECK ADD
CONSTRAINT [PK_tblmembers] PRIMARY KEY CLUSTERED
(
[recid]
) ON [PRIMARY]
GO

CREATE UNIQUE INDEX [IX_tblmembers_memid] ON
[dbo].[tblmembers]([memid]) ON [PRIMARY]
GO
INSERT tblmembers(recid,memid,memname,mememail,activated)
VALUES('1','1000001','John Smith','j***@smith.com','1')
INSERT tblmembers(recid,memid,memname,mememail,activated)
VALUES('2','1000002','Jen Smith','s****@jen.com','1')
INSERT tblmembers(recid,memid,memname,mememail,activated)
VALUES('3','1000003','','','0')
INSERT tblmembers(recid,memid,memname,mememail,activated)
VALUES('4','1000004','','','0')
INSERT tblmembers(recid,memid,memname,mememail,activated)
VALUES('5','1000005','','','0')

So, the basic concept is that when a membership registration form is
submitted, I want to go and get the next unassigned member id
(activated=0) change the activated flag to 1 and update memname and
mememail fields with parameters passed from the form. I then need to
return the value in that record's memid field back to the confirmation
page.

I can do all of this, but I haven't been able to satisfy myself that
there won't be a problem if I get two simultaneous registration
requests.

In essence, I'm trying to figure a way, given the above sample data,
that if there are two simultaneous registration requests, one of them
is returned the row with the member id of 1000003 and one of them is
returned the row with the member id of 1000004. And, it probably goes
without saying, that if there are 3 simultaneous requests, the third
one would be returned the row with the member id of 1000005, and so
on. In other words, no two registrations should return the same member
id.

Given the examples you provided in the link, I was trying to do do
something similar to:

declare @rowid numeric

UPDATE tblmembers set @rowid=selmem.recid, activated = 1 where recid =
(SELECT TOP 1 recid from tblmembers where activated = 0) as selmem

return @rowid

But, of course, this is invalid syntax, since it seems you can't
provide an alias to a subquery in an UPDATE statement as I have
attempoted to do above.

However, the concept was to perform the select and update in the same
statement, return the recid of the selected / updated row, and then
use that recid value to perform another update query etc to provide
the member name and email details.

I'm sorry if this only confuses matters, but my hope is that it
explains a little better what I'm hoping to achieve...

Thank you, again, for your help!

Much warmth,

Murray

[Simon Hayes]

"M Wells" <pl**********@planetthoughtful.org> wrote in message
news:un********************************@4ax.com...

On 4 Feb 2004 23:44:05 -0800, sq*@hayes.ch (Simon Hayes) wrote:

M Wells <pl**********@planetthoughtful.org> wrote in message news:<j1********************************@4ax.com>. ..

Hi All,
[ here there be snippage ]

It's not clear from your description why you need to generate the IDs
in advance - a simpler approach might be to use an IDENTITY column,
and insert directly into the members table for a new registration. You
can then return the system-generated value to the client using
scope_identity(). This assumes, of course, that the membership ID is
simply a number, with no other meaning:

create table dbo.Members (
MemberID int identity(1,1) primary key,
FirstName varchar(50) not null,
LastName varchar(50) not null
)
go

insert into dbo.Members (FirstName, LastName)
values ('Murray', 'Wells')

select 'Your ID is: ' + cast(scope_identity() as char(2))
go

If you do need some control over the value of the membership ID, then
one of these approaches might be suitable:

http://groups.google.com/groups?q=sq...=en&lr=&ie=UTF

-8&oe=UTF-8&selm=40044e65%241_2%40news.bluewin.ch&rnum=1
If this doesn't help, you may want to post the structure (CREATE
TABLE) of your members table, along with some sample data, and explain
exactly what you want to return to the client.

Hi Simon,

Thank you very much for your help with this!

Unfortunately, the need to use pre-generated member ids is a foregone
issue; it's not something I have any control over.

I looked at the two examples in the link you provided. The first makes
sense to me, but I can't formulate a query that makes it do exactly
what I want. And I don't yet know enough about locking in SQL Server
to know if the locking hint (UPDLOCK) does what I hope it will do.

To give you a little extra detail, my test table definition is:

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

CREATE TABLE [dbo].[tblmembers] (
[recid] [int] IDENTITY (1, 1) NOT NULL ,
[memid] [numeric](18, 0) NULL ,
[memname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[mememail] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[activated] [bit] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblmembers] WITH NOCHECK ADD
CONSTRAINT [PK_tblmembers] PRIMARY KEY CLUSTERED
(
[recid]
) ON [PRIMARY]
GO

CREATE UNIQUE INDEX [IX_tblmembers_memid] ON
[dbo].[tblmembers]([memid]) ON [PRIMARY]
GO
INSERT tblmembers(recid,memid,memname,mememail,activated)
VALUES('1','1000001','John Smith','j***@smith.com','1')
INSERT tblmembers(recid,memid,memname,mememail,activated)
VALUES('2','1000002','Jen Smith','s****@jen.com','1')
INSERT tblmembers(recid,memid,memname,mememail,activated)
VALUES('3','1000003','','','0')
INSERT tblmembers(recid,memid,memname,mememail,activated)
VALUES('4','1000004','','','0')
INSERT tblmembers(recid,memid,memname,mememail,activated)
VALUES('5','1000005','','','0')

So, the basic concept is that when a membership registration form is
submitted, I want to go and get the next unassigned member id
(activated=0) change the activated flag to 1 and update memname and
mememail fields with parameters passed from the form. I then need to
return the value in that record's memid field back to the confirmation
page.

I can do all of this, but I haven't been able to satisfy myself that
there won't be a problem if I get two simultaneous registration
requests.

In essence, I'm trying to figure a way, given the above sample data,
that if there are two simultaneous registration requests, one of them
is returned the row with the member id of 1000003 and one of them is
returned the row with the member id of 1000004. And, it probably goes
without saying, that if there are 3 simultaneous requests, the third
one would be returned the row with the member id of 1000005, and so
on. In other words, no two registrations should return the same member
id.

Given the examples you provided in the link, I was trying to do do
something similar to:

declare @rowid numeric

UPDATE tblmembers set @rowid=selmem.recid, activated = 1 where recid =
(SELECT TOP 1 recid from tblmembers where activated = 0) as selmem

return @rowid

But, of course, this is invalid syntax, since it seems you can't
provide an alias to a subquery in an UPDATE statement as I have
attempoted to do above.

However, the concept was to perform the select and update in the same
statement, return the recid of the selected / updated row, and then
use that recid value to perform another update query etc to provide
the member name and email details.

I'm sorry if this only confuses matters, but my hope is that it
explains a little better what I'm hoping to achieve...

Thank you, again, for your help!

Much warmth,

Murray

I think this is what you're looking for, assuming that your definition of
the 'next' row is the one with the lowest value for memid within the set of
rows which have an activated value of 0:

declare @memid int,
@memname varchar(50),
@mememail varchar(255)

set @memname = 'John Doe'
set @mememail = 'j***@doe.com'

begin tran

select @memid = min(memid)
from tblmembers with(updlock)
where activated = 0

update tblmembers
set activated = 1, memname = @memname, mememail = @mememail
where memid = @memid

commit

select @memid
Since you're returning the memid, but updating the other columns, you can't
use the UPDATE syntax I suggested in the link - you'll have to use the
locking hint, which needs to be inside a transaction. Note that I haven't
put any error handling in the code above, but you should definitely put it
in your real code. Here is a helpful resource:

http://www.sommarskog.se/error-handling-II.html

One other point is that having both the recid and memid columns seems to be
redundant - the natural primary key of the table appears to be memid (and
mememail may also be a candidate key), so it's not clear what purpose recid
serves, although I appreciate that you may not have complete control over
the schema, and that you may have simplified your real data here.

Simon

[M Wells]

[ here there be snippage ]

I think this is what you're looking for, assuming that your definition of
the 'next' row is the one with the lowest value for memid within the set of
rows which have an activated value of 0:

declare @memid int,
@memname varchar(50),
@mememail varchar(255)

set @memname = 'John Doe'
set @mememail = 'j***@doe.com'

begin tran

select @memid = min(memid)
from tblmembers with(updlock)
where activated = 0

update tblmembers
set activated = 1, memname = @memname, mememail = @mememail
where memid = @memid

commit

select @memid
Since you're returning the memid, but updating the other columns, you can't
use the UPDATE syntax I suggested in the link - you'll have to use the
locking hint, which needs to be inside a transaction. Note that I haven't
put any error handling in the code above, but you should definitely put it
in your real code. Here is a helpful resource:

http://www.sommarskog.se/error-handling-II.html

One other point is that having both the recid and memid columns seems to be
redundant - the natural primary key of the table appears to be memid (and
mememail may also be a candidate key), so it's not clear what purpose recid
serves, although I appreciate that you may not have complete control over
the schema, and that you may have simplified your real data here.

Hi Simon,

Thank you, thank you, thank you!

This seems to be doing exactly what I need it to do!

One question, though -- the min() function in the select statement
seems to slow the execution of the stored procedure considerably.

Is there anything wrong with simply using:

select Top 1 @memid = memid from tblmembers with(updlock)
where activated = 0

My own testing _seems_ to establish this as being somewhat faster...

Again, thank you, thank you, thank you!

Much warmth,

Murray

[Erland Sommarskog]

M Wells (pl**********@planetthoughtful.org) writes:

This seems to be doing exactly what I need it to do!

One question, though -- the min() function in the select statement
seems to slow the execution of the stored procedure considerably.

Is there anything wrong with simply using:

select Top 1 @memid = memid from tblmembers with(updlock)
where activated = 0

My own testing _seems_ to establish this as being somewhat faster...

Permit me to bump in here. I was about to suggest something last night
that was akin to what Simon proposed. But then I was struck of a sense
of doubt whether it would work or not. You see, the standard question
is about get the next value to insert. This pre-generated thing changes
the playing rules a bit. So I left your message unanswered and went to
bed.

Problem is that it is now about 24 hourse since I saw your question the
first time, which means that also today the bed is waiting for me. But
I can aleast provide a tip on how to test this. Add this statement to the
batch between the SELECT and the UPDATE:

WAITFOR DELAY '00:00:10'

Then run the batch from two different windows in Query Analyzer, and
see if you get the expected result.

Also, change the design of the table, so that the clustered index is
on memberid (you would do best in dropping recid; it serves no purpose),
and add a non-clustered index on activated. This may be good for
performance.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[M Wells]

On Thu, 5 Feb 2004 23:46:45 +0000 (UTC), Erland Sommarskog
<so****@algonet.se> wrote:

M Wells (pl**********@planetthoughtful.org) writes:
[ here there be snippage ]
Permit me to bump in here. I was about to suggest something last night
that was akin to what Simon proposed. But then I was struck of a sense
of doubt whether it would work or not. You see, the standard question
is about get the next value to insert. This pre-generated thing changes
the playing rules a bit. So I left your message unanswered and went to
bed.
(Laugh) Hi Erland -- you're more than welcome to bump in! Thank you
for adding your thoughts...

Problem is that it is now about 24 hourse since I saw your question the
first time, which means that also today the bed is waiting for me. But
I can aleast provide a tip on how to test this. Add this statement to the
batch between the SELECT and the UPDATE:

WAITFOR DELAY '00:00:10'

Then run the batch from two different windows in Query Analyzer, and
see if you get the expected result.

Thank you for this suggestion -- I was wondering how to test if it
works, and it seems to do just that.

Also, change the design of the table, so that the clustered index is
on memberid (you would do best in dropping recid; it serves no purpose),
and add a non-clustered index on activated. This may be good for
performance.

The suggestion re the memberid field makes a lot of sense, but the
activated field is currently a BIT type. I was under the impression
you can't put indexes on BIT fields?

I've been wondering if there would be any benefit in changing this to
a TINYINT type so I can put an index on it. Storage isn't an issue,
however speed is.

Thanks, again, for your input -- and hope you got some good sleep.

Much warmth,

Murray

[Erland Sommarskog]

M Wells (pl**********@planetthoughtful.org) writes:

The suggestion re the memberid field makes a lot of sense, but the
activated field is currently a BIT type. I was under the impression
you can't put indexes on BIT fields?

Did you say which version of SQL Server you are using? It's correct that
in SQL7 and earlier, you cannot have bit column in indexes. However, in
SQL2000 this restriction is lifted.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[M Wells]

On Fri, 6 Feb 2004 09:14:36 +0000 (UTC), Erland Sommarskog
<so****@algonet.se> wrote:

M Wells (pl**********@planetthoughtful.org) writes:

The suggestion re the memberid field makes a lot of sense, but the
activated field is currently a BIT type. I was under the impression
you can't put indexes on BIT fields?

Did you say which version of SQL Server you are using? It's correct that
in SQL7 and earlier, you cannot have bit column in indexes. However, in
SQL2000 this restriction is lifted.

Hi Erland,

Hmmm. I'm using SQL2000, but I don't see bit columns when I create
indexes using the Manage Indexes / Keys dialogue...

Do I need to create indexes for bit columns via SQL statements?

Much warmth,

Murray

[M Wells]

On Fri, 06 Feb 2004 14:09:45 GMT, M Wells
<pl**********@planetthoughtful.org> wrote:

On Fri, 6 Feb 2004 09:14:36 +0000 (UTC), Erland Sommarskog
<so****@algonet.se> wrote:

[ here there be snippage ]

Hi Erland,

Hmmm. I'm using SQL2000, but I don't see bit columns when I create
indexes using the Manage Indexes / Keys dialogue...

Do I need to create indexes for bit columns via SQL statements?

Hi Erland,

No need to reply to the above -- I discovered that, yes, I can create
indexes on bit columns via SQL...

However, if I can be forgiven for one last question: given one or more
bit columns in the WHERE clause of a select statement, should I
explicitly CAST the value I'm looking for as a bit?

I came across this tip while surfing for information on bit columns,
but wasn't certain if this remained relevant fro SQL2K...

Many thanks to both you and Simon for all of your help!!

Much warmth,

Murray

[Simon Hayes]

"M Wells" <pl**********@planetthoughtful.org> wrote in message
news:3s********************************@4ax.com...

On Fri, 06 Feb 2004 14:09:45 GMT, M Wells
<pl**********@planetthoughtful.org> wrote:

On Fri, 6 Feb 2004 09:14:36 +0000 (UTC), Erland Sommarskog
<so****@algonet.se> wrote:

[ here there be snippage ]

Hi Erland,

Hmmm. I'm using SQL2000, but I don't see bit columns when I create
indexes using the Manage Indexes / Keys dialogue...

Do I need to create indexes for bit columns via SQL statements?

Hi Erland,

No need to reply to the above -- I discovered that, yes, I can create
indexes on bit columns via SQL...

However, if I can be forgiven for one last question: given one or more
bit columns in the WHERE clause of a select statement, should I
explicitly CAST the value I'm looking for as a bit?

I came across this tip while surfing for information on bit columns,
but wasn't certain if this remained relevant fro SQL2K...

Many thanks to both you and Simon for all of your help!!

Much warmth,

Murray

As far as I'm aware, there shouldn't be any need to explicitly CAST the
value to a bit, but I haven't used bit columns much, so I can't say for
sure.

To answer your previous question:

"Is there anything wrong with simply using:

select Top 1 @memid = memid from tblmembers with(updlock)
where activated = 0"

The issue here is that TOP without ORDER BY doesn't really mean anything -
you will get one row returned, but in theory you could get any row where
activated = 0. In practice, if you have a clustered index on the table, then
you'll probably get rows back in the order of that index, but there's no
guarantee.

So if your business rule requires you to get the lowest possible memid, then
you would need to do this:

select Top 1 @memid = memid
from tblmembers with(updlock)
where activated = 0
order by memid asc -- this defines what TOP means

This is effectively the same as my query, of course.

Simon

[Erland Sommarskog]

M Wells (pl**********@planetthoughtful.org) writes:

However, if I can be forgiven for one last question: given one or more
bit columns in the WHERE clause of a select statement, should I
explicitly CAST the value I'm looking for as a bit?

Yes.

This is because of the conversion rules in SQL Server. If you don't use
cast(), SQL Server will convert the bit column to integer. And whenever
a column is not used in its original shape, SQL Server can no longer
seek an index with this column.

It could still opt to scan the index, but it would have to scan all
pages for the index. However, for you repro, I got a table scan when
I did not use cast(). This may be due to the small size of the table.
However, when I used cast(), SQL Server used an Index Seek.

I think that even with a large members table, this method should be
really fast, although there is a cost for updating the index when you
activate the customer.

I also did some concurrency studies, and I feel fairly confident that
the method with UPDLOCK is safe.

Here is a repro, so you can see exactly what I ran:

CREATE TABLE [dbo].[tblmembers] (
[memid] [numeric](18, 0) NOT NULL ,
[memname] [varchar] (50) NULL,
[mememail] [varchar] (255) NULL ,
[activated] [bit] NOT NULL,
CONSTRAINT PK_tblmembers PRIMARY KEY CLUSTERED (memid)
)
GO

CREATE INDEX isactivated ON tblmembers(activated)
GO

INSERT tblmembers(memid,memname,mememail,activated)
VALUES('1000001','John Smith','j***@smith.com','1')
INSERT tblmembers(memid,memname,mememail,activated)
VALUES('1000002','Jen Smith','s****@jen.com','1')
INSERT tblmembers(memid,memname,mememail,activated)
VALUES('1000003','','','0')
INSERT tblmembers(memid,memname,mememail,activated)
VALUES('1000004','','','0')
INSERT tblmembers(memid,memname,mememail,activated)
VALUES('1000005','','','0')
go
declare @memid int,
@memname varchar(50),
@mememail varchar(255)

set @memname = 'John Doe'
set @mememail = 'j***@doe.com'

begin tran

select @memid = min(memid)
from tblmembers with(updlock)
where activated = convert(bit, 0)

update tblmembers
set activated = 1, memname = @memname, mememail = @mememail
where memid = @memid

commit

select @memid



--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[M Wells]

On Fri, 6 Feb 2004 23:06:17 +0000 (UTC), Erland Sommarskog
<so****@algonet.se> wrote:

[ here there be snippage ]

I just wanted to give a heartfelt thanks to both of you (Simon and
Erland) for all of your help with this.

I can't imagine a better testimony for the internet than the fact that
people like you two (and many others) go out of your way to share your
knowledge and expertise with a complete stranger who is struggling to
get something done.

Thank you both, again!

Much warmth,

Murray
http://www.planetthoughtful.org