Stored Procedure Locks 
Author Message
 Stored Procedure Locks

I'm trying to create a stored procedure that pulls out the
last serialized number in order to get the next serialized
number in sequence.  I've already created a working stored
procedure, but my question is about the locks.  The only
way that I will ever be getting a new number is by calling
this stored procedure, but there are multiple computers
that are used to get new numbers.  Does SQL Server run
stored procudures concurrently or are they always run in a
serialized fashion.  If they can be run at the same time,
is it possible to create locks in a stored procedure to
prevent another instance of that stored procedure from
getting a new number before the first procedure writes the
new number to the table?  Any help would be greatly
appreciated!  Thank you for your time!

-Brian



Sat, 22 Oct 2005 17:29:58 GMT
 Stored Procedure Locks

Although stored procs will run concurrently, they will block depending on what the underlying SQL is doing.  In your case, you want to get the "next" number.  If this comes about by updating a row in a key table, then the first one in will block all others until it is finished.  Then, the second one will be able to proceed, blocking all others and so on.  Expect this to be a bottleneck.

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional

www.pinnaclepublishing.com/sql

I'm trying to create a stored procedure that pulls out the
last serialized number in order to get the next serialized
number in sequence.  I've already created a working stored
procedure, but my question is about the locks.  The only
way that I will ever be getting a new number is by calling
this stored procedure, but there are multiple computers
that are used to get new numbers.  Does SQL Server run
stored procudures concurrently or are they always run in a
serialized fashion.  If they can be run at the same time,
is it possible to create locks in a stored procedure to
prevent another instance of that stored procedure from
getting a new number before the first procedure writes the
new number to the table?  Any help would be greatly
appreciated!  Thank you for your time!

-Brian



Sat, 22 Oct 2005 17:36:40 GMT
 Stored Procedure Locks
It comes about by reading a row, getting the current
number and updating that number.  Unless there is another
way to do this, it requires a read and then an update.  I
want to ensure that "concurrent" procudures cannot do this
read before the first does it's update.  Does the read
create the exclusive lock that I'm looking for?  Would it
be best to include "SET TRANSACTION ISOLATION LEVEL
SERIALIZABLE" in the stored procedure?  Thank you for your
help!

-Brian

Quote:
>-----Original Message-----
>Although stored procs will run concurrently, they will

block depending on what the underlying SQL is doing.  In
your case, you want to get the "next" number.  If this
comes about by updating a row in a key table, then the
first one in will block all others until it is finished.  
Then, the second one will be able to proceed, blocking all
others and so on.  Expect this to be a bottleneck.
Quote:

>--
>Tom

>----------------------------------------------------------
-----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional

>www.pinnaclepublishing.com/sql




Quote:
>I'm trying to create a stored procedure that pulls out
the
>last serialized number in order to get the next
serialized
>number in sequence.  I've already created a working
stored
>procedure, but my question is about the locks.  The only
>way that I will ever be getting a new number is by
calling
>this stored procedure, but there are multiple computers
>that are used to get new numbers.  Does SQL Server run
>stored procudures concurrently or are they always run in
a
>serialized fashion.  If they can be run at the same time,
>is it possible to create locks in a stored procedure to
>prevent another instance of that stored procedure from
>getting a new number before the first procedure writes
the
>new number to the table?  Any help would be greatly
>appreciated!  Thank you for your time!

>-Brian



Sat, 22 Oct 2005 17:54:01 GMT
 Stored Procedure Locks

Consider the following:

create proc MyProc
(

)
as
select

,    TheKey = TheKey + 1
from
    MyTable
go




-- do an insert here

This will pick up the key and increment it automatically.  At that point, you can do what you want with the key.  However, if you really want sequenced numbers (i.e. no gaps), then you will have to put the exec and the insert into a transaction.  If the insert fails, then you want to roll back the transaction. This is where you can get a bottleneck.  If you can have gaps, then don't use the transaction.

--
Tom

---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional

www.pinnaclepublishing.com/sql

It comes about by reading a row, getting the current
number and updating that number.  Unless there is another
way to do this, it requires a read and then an update.  I
want to ensure that "concurrent" procudures cannot do this
read before the first does it's update.  Does the read
create the exclusive lock that I'm looking for?  Would it
be best to include "SET TRANSACTION ISOLATION LEVEL
SERIALIZABLE" in the stored procedure?  Thank you for your
help!

-Brian

Quote:
>-----Original Message-----
>Although stored procs will run concurrently, they will

block depending on what the underlying SQL is doing.  In
your case, you want to get the "next" number.  If this
comes about by updating a row in a key table, then the
first one in will block all others until it is finished.  
Then, the second one will be able to proceed, blocking all
others and so on.  Expect this to be a bottleneck.
Quote:

>--
>Tom

>----------------------------------------------------------
-----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional

>www.pinnaclepublishing.com/sql




Quote:
>I'm trying to create a stored procedure that pulls out
the
>last serialized number in order to get the next
serialized
>number in sequence.  I've already created a working
stored
>procedure, but my question is about the locks.  The only
>way that I will ever be getting a new number is by
calling
>this stored procedure, but there are multiple computers
>that are used to get new numbers.  Does SQL Server run
>stored procudures concurrently or are they always run in
a
>serialized fashion.  If they can be run at the same time,
>is it possible to create locks in a stored procedure to
>prevent another instance of that stored procedure from
>getting a new number before the first procedure writes
the
>new number to the table?  Any help would be greatly
>appreciated!  Thank you for your time!

>-Brian



Sat, 22 Oct 2005 18:01:06 GMT
 Stored Procedure Locks
With Tom's response specific on a new (key) value, I had similar, but more
general situation.  For general situation, you can at the top of the stored
proce SET TRANSACTION ISOLATION LEVEL
SERIALIZABLE and then put your whole content of the stored proc code into a
transaction.  The transaction isolation level applies only to the individual
transactions, that can be many within your stored proc.

Quentin


Quote:
> It comes about by reading a row, getting the current
> number and updating that number.  Unless there is another
> way to do this, it requires a read and then an update.  I
> want to ensure that "concurrent" procudures cannot do this
> read before the first does it's update.  Does the read
> create the exclusive lock that I'm looking for?  Would it
> be best to include "SET TRANSACTION ISOLATION LEVEL
> SERIALIZABLE" in the stored procedure?  Thank you for your
> help!

> -Brian

> >-----Original Message-----
> >Although stored procs will run concurrently, they will
> block depending on what the underlying SQL is doing.  In
> your case, you want to get the "next" number.  If this
> comes about by updating a row in a key table, then the
> first one in will block all others until it is finished.
> Then, the second one will be able to proceed, blocking all
> others and so on.  Expect this to be a bottleneck.

> >--
> >Tom

> >----------------------------------------------------------
> -----
> >Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >SQL Server MVP
> >Columnist, SQL Server Professional

> >www.pinnaclepublishing.com/sql



> >I'm trying to create a stored procedure that pulls out
> the
> >last serialized number in order to get the next
> serialized
> >number in sequence.  I've already created a working
> stored
> >procedure, but my question is about the locks.  The only
> >way that I will ever be getting a new number is by
> calling
> >this stored procedure, but there are multiple computers
> >that are used to get new numbers.  Does SQL Server run
> >stored procudures concurrently or are they always run in
> a
> >serialized fashion.  If they can be run at the same time,
> >is it possible to create locks in a stored procedure to
> >prevent another instance of that stored procedure from
> >getting a new number before the first procedure writes
> the
> >new number to the table?  Any help would be greatly
> >appreciated!  Thank you for your time!

> >-Brian



Sat, 22 Oct 2005 18:17:37 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. ODBC Stored Procedure Locks in TEMPDB ???

2. Extended Stored Procedure locking up inside transaction

3. Stored procedure locks

4. STORED PROCEDURE / LOCKING

5. Stored procedure LOCK UP: BUG?

6. Stored procedures locking behaviour

7. Stored Procedure locks tempdb

8. Stored Procedure Locks

9. Calling a Java Stored Procedure from another Java Stored Stored Procedure

10. locks, stored procedure, update row

11. Locking a record across two stored procedures

12. Row Locking In a Stored Procedure


 
Powered by phpBB® Forum Software