Topic: SQL server 中，怎么让stored procedure在同一个时间只能允许一个用户运行？ @华人论坛:枫下论坛 The Rolia Forum of

工作学习 / 专业知识杂谈 / SQL server 中，怎么让stored procedure在同一个时间只能允许一个用户运行？ -luking(luking); 2006-3-29 (#2871348@0)

dedicate username/password and transaction? -canadiantire(轮胎-M.I.N.K.); 2006-3-29 (#2871459@0)

i created a SP, of which only one execution is allowed at a time, whatever the username, password is, how to accomplish it? , thx! -luking(luking); 2006-3-29 (#2871494@0)

Give username and password to only one person. -canadiantire(轮胎-M.I.N.K.); 2006-3-29 (#2871526@0)

No!, I still need other people to run SP other time, but one instance at one time -luking(luking); 2006-3-29 (#2871541@0)

Sorry, i am just kidding you, lock the table or row will work. -canadiantire(轮胎-M.I.N.K.); 2006-3-29 (#2871552@0)

then what's the benifit of using stored procedure? you can ask the user to run the sql script manually every time he needs to run and lock every resource the job demands -buma(buma); 2006-3-29 (#2871549@0)

The scenario is: I have a sp doing complicated calculation which is a time-consuming process on SQL server and update some tables on completion, and this sp is called from client application,I have a sp doing complicated calculation which is a time-consuming process on SQL server and update some tables on completion, and this sp is called from client application, client application is multiple users. to save sql server cpu time and avoid duplicated calculation, what I want is: if the sp is already executing, other attempt running it will be ignored -luking(luking); 2006-3-29 {363} (#2871625@0)

I guess using SET TRANSACTION ISOLATION LEVEL SERIALIZABLE could be a solution, but seriously i think there might be some design issue here as well. -canadiantire(轮胎-M.I.N.K.); 2006-3-29 (#2871683@0)

is there a way to check the instance of current sp in process list at the begining of sp, if the number of instance >=2, then quit? -luking(luking); 2006-3-29 (#2871703@0)

If you want to check # of instances, the operation must be atomic, how you can guarantee that? no way. -canadiantire(轮胎-M.I.N.K.); 2006-3-29 (#2871719@0)

There is no solution on SQL Server end??? The calculation in one sp takes 10 minutes and during which I don't want other users to call this sp again thru client application -luking(luking); 2006-3-29 (#2871807@0)

I don't think it's a good idea to put a 10minutes calculation into a stored procedure, as i said, i seriously doubt there are some design issues here. -canadiantire(轮胎-M.I.N.K.); 2006-3-29 (#2871815@0)

then where else is better? move it form a high performace server with 2 3000MHz CPUs to a 450MHz win98? do you think it will be faster??? -luking(luking); 2006-3-29 (#2871847@0)

This sp will severely deteriorate the performance of the whole db server. -canadiantire(轮胎-M.I.N.K.); 2006-3-29 (#2871831@0)

if you insist, do u have middle ware for your application or this is just client/server type.if you have middleware, add restrictions then.
if you use client/server mode, setup a flag-checking mechanism to lock this sp from client application's conflictions.
the solution should not be done inside ur stored-procedure at db server level in my opinion. -buma(buma); 2006-3-29 {261} (#2872032@0)

my current solution: At the begining of sp, check existence of a global temporary table, quit if true, else create temporary table and proceed -luking(luking); 2006-3-29 (#2872064@0)

if u insist to put this in the same stored procedure, just check a field in a table contains all the flasg, it is convenient and looks like prouse a single table for this purpose is waste -buma(buma); 2006-3-29 {44} (#2872116@0)

I only need one single flag, why bother using a table plus a field, just for pro. look? -luking(luking); 2006-3-29 (#2872321@0)

你的问题会让发明SP的人跳楼的 -elecskunk(elecskunk); 2006-3-29 (#2873032@0)
create a table, one column with unique index. at beginning of the SP, insert a the procedure name to the table, if success, go future, if not, return. before the SP finish, delete the line inserted at begining of the SP. -647i(-); 2006-3-29 (#2873057@0)

Correct...... -hard20(hard20); 2006-3-29 (#2873207@0)
thx, this is what i am doing now, I use a global temporary table instead. but i am looking for a way to check system processes or something else, and query sysprocesses does not work. -luking(luking); 2006-3-29 (#2873269@0)

You can check if there is any process running your SP by querying sysprocesses or dbcc inputbuffer or another undocumented query. -wdw2130833(二拖二); 2006-3-29 (#2873127@0)

@