Re: Sql Server Queue?

From: Raterus (raterus_at_hotmail.com)
Date: 01/03/05 

Date: Mon, 3 Jan 2005 13:04:44 -0500

I haven't played with locks much, so I have a question about this. Say I lock down this stored procedure as you suggested.

PersonA runs this procedure, quickly followed by PersonB running the same procedure. Will PersonB be hit with an error since the stored procedure is currently "locked" or will sql server wait until PersonA's procedure finishes, then automatically start PersonB's?

Thanks,
--Michael

"David Browne" <davidbaxterbrowne no potted meat@hotmail.com> wrote in message news:eE28k8a8EHA.2088@TK2MSFTNGP10.phx.gbl...
> >"Raterus" <raterus@hotmail.com> wrote in message
>> >news:ex6rBQa8EHA.1396@tk2msftngp13.phx.gbl...
>>Hello,
>
>>I have a long running stored-procedure. End users can start this procedure
>>anytime >they want (It runs a search on some data). My problem is, if they
>>ever (and they do) >start this sproc a couple of times all at once, they
>>will break the process. (Not so >much the stored procedure execution, just
>>the programming logic within the stored >procedure.
>
>>I'd like to have some sort of queue process that manages the execution of
>>this stored >procedure, instead of starting the sproc directly, they would
>>just add it to a queue, and >it would make sure they stored procedure is
>>not ran concurrently.
>
> The easiest and most reliable way to control concurrency of a stored
> procedure is through locking. If the table locking required by your
> procedure is not sufficient, you can use sp_getapplock to explicity
> serialize invocations of your procedure.
>
> Use
>
> alter procedure foo_once as
> begin transaction
> exec sp_getapplock @Resource = 'foo_once_mutex', @LockMode = 'Exclusive'
> WAITFOR DELAY '00:00:10'
> print 'done'
> commit transaction
>
> If your procedure uses a transaction. This is the most reliable since it
> enlists the applock in your transaction, and it will be automatically
> released when you commit or roll back. If you must run your procedure
> without a transaction, use
>
> alter procedure foo_once as
> exec sp_getapplock @Resource = 'foo_once_mutex',
> @LockMode = 'Exclusive',
> @LockOwner = 'Session'
> WAITFOR DELAY '00:00:10'
> print 'done'
> exec sp_releaseapplock @Resource = 'foo_once_mutex',
>
> but here you are responsable for releasing the app lock manually. If you
> don't, it will last until the session ends.
>
> David
>
>
>

Relevant Pages

  • Re: Tansaction/locking MSSQL2000
    ... >transaction behavior in single statements [not a BEGIN TRANSACTION ... >tables I need to lock so there is not a race later]? ... If the table is already held by an exclusive lock ... When the transaction is finished (due to ROLLBACK/COMMIT, releasing the ...
    (microsoft.public.sqlserver.programming)
  • Re: Tansaction/locking MSSQL2000
    ... Are you saying that ALL select statements acquire a shared TABLE lock on ALL ... with the SET outcome after the SELECT transaction begins. ... > If the table is already held by an exclusive lock ... > When the transaction is finished (due to ROLLBACK/COMMIT, releasing the ...
    (microsoft.public.sqlserver.programming)
  • Re: ADO.NET Transaction Locking
    ... the entire transaction takes 2 seconds, why other clients are timing out, ... enough, so that the other clients can just wait the 2 seconds, and then the ... make sure your updates and anything your stored procedure is ... say only lock the changed or new records do not lock the entire table ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Setting a SQL Server-wide Block timeout for SQL Server 2005
    ... quickily enough or not committing the transaction. ... quickily releasing the lock. ... Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books ... ...
    (comp.databases.ms-sqlserver)
  • Re: Working Transactions somehow started not to work
    ... Transaction and locking are not properties of recordsets but are properties ... Opening a served-based cursor will put a lock on ... If a connection is closed, ... SL> and are not associated necessarily with server-based cursors. ...
    (microsoft.public.access.adp.sqlserver)
Loading