Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Heinz23
Yak Posting Veteran
84 Posts |
 Posted - 2012-01-27 : 02:55:17
|
| Hi all,I have a Stored Procedure which is called by multiple users at the same time. But the SP should only execute once. On the 2nd, 3rd etc call it should just wait until the 1st call is completed and then return the data. What is the best way to do it? I thought about having a flag in another table which is checked first. If not set ('Open') it will be set ('In Process') and the SP will be executed. After finishing, the flag will be set to 'Completed'. All other executions also check the flag. If 'In Process' they will sleep until 'Completed'. Problem: If the SP is called 2 times at the same time, both executions see that it's not yet started and they set to 'In Process' at the same time. Then they are executed at the same time. So after setting to 'In Process' I need to check whether this was really set by this execution. Instead of 'In Process' I might also set a session ID or whatever could be necessary to identify the session. Was this understandable? Is there a 'standard' solution for it? I would be grateful for any examples! Many thanks in advance!! |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
|
Heinz23
Yak Posting Veteran
84 Posts |
Posted - 2012-01-27 : 03:18:45
|
| Hi khtan,thanks for fast reply. Looks very interesting, but currently the user is not dbowner. It seems that I could not use applock then, right? For security reasons I've restricted the SP-caller to only execute the SPs, but not be an owner of the DB. |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-01-27 : 03:34:14
|
Can't remember exactly but i don't think you need the user to be a dbowner. Check out the Books On Line for permission required and further details
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
Heinz23
Yak Posting Veteran
84 Posts |
Posted - 2012-01-27 : 03:47:38
|
It's listed in the link you posted: quote:
However to be able to call sp_getapplock a user calling the stored procedure must meet one of these conditions: is dbo is in the db_owner role is the DB Principal ID (e.g. guest) is in the DB Principal ID role (e.g. public)
|
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-01-27 : 04:01:02
|
sorry, that was many many years ago when i first read that article. I was much younger and have better memory  quote:
Originally posted by Heinz23
It's listed in the link you posted: quote:
However to be able to call sp_getapplock a user calling the stored procedure must meet one of these conditions: is dbo is in the db_owner role is the DB Principal ID (e.g. guest) is in the DB Principal ID role (e.g. public)
no harm give it a quick try right ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
|
|
|
|
|
Call SP only once?