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 |
|
pwcphoto
Yak Posting Veteran
69 Posts |
 Posted - 2005-05-28 : 17:35:43
|
| This is related to my previous post.In my stored procedure I create a table using select into, then delete the active table and rename the newly created table to the active table.Here is the code SELECT UF_rt_images.image_ID, UF_rt_images.user_ID INTO UF2_Donor_Images_1 FROM (UF_rt_Images INNER JOIN UF2_rt_Users ON (UF_rt_Images.user_id = UF2_rt_users.user_id)) WHERE ((UF_rt_Images.Image_Active = 1) AND (UF_rt_Images.Image_Section LIKE 'critique') AND (UF2_rt_users.user_Donor = 1))DROP TABLE UF2_Donor_ImagesEXEC sp_rename 'UF2_Donor_Images_1', 'UF2_Donor_Images'My question is, suppose that multiple instances of this procedure are executed at the same time or very close together so there is an overlap. It would be possible for either the second instance of the sp to get an error in that it could not run the select into because the table already exists. If I drop the table before doing the select into, I stand the possibility of dropping the table prior to it getting renamed in the other instance etc...Is there some sort of locking mechanism, or wait loop that can be imposed so if the second instance of the sp sees the table it would wait until it no longer sees it before creating it. (it only exists for a short time prior to being renamed)Does the table get created the instance the select into has started, or does it wait for all of the query to be completed before it actually creates the table? In other words the moment the sp runs is there an empty table created that is filled as the sp runs which if 2 or more instances of the sp running at the same time only the first one could perform the select into because the table did not exist when it was run, and all the other instances would fail because the table was already there.I realize the window of conflict is very small, but with my luck it will happen and things will get messed up.Thanks,Phil-----------------------I used to be a rocket scientist. Now I'm just a space cadet... |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-05-28 : 18:26:01
|
| Yes this can give an errorThe table gets created at the begining of the statement.This is a bad idea - permanent tables should be permanent.You can create another table and take an exclusive lok on it in a transaction before running thos [rocess - that will block the same process running twide from two spids.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
pwcphoto
Yak Posting Veteran
69 Posts |
Posted - 2005-05-28 : 21:27:25
|
| Well actually because the table is created at the beginning of the proc this may be to my advantage. I can look and loop while the table is there. Once it is no longer there meaning it got renamed to the active table I can then go through with the proc and let it create the table etc...The proc is not something that would run all the time, maybe 500 times a day or so. The main concern is that 2 people by chance will press the button at the same time or with in a second of each other causing overlap in execution.Granted this may not be the most desireable way to go, it is kind of like a semi-permanent temp table. Without it I get a query time of 8 seconds with it I get one of 2 seconds, barring any problems which I am trying to address here, the speed increase may be worth it. People tend not to stick around if a site takes too long to load.Phil-----------------------I used to be a rocket scientist. Now I'm just a space cadet... |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-05-29 : 06:43:06
|
| Can you usea temp table?Maybe if you post what you are trying to do someone will suggest a better way.>> Once it is no longer there meaning it got renamed to the active table I can then go through with the procWhat if it gets created between the check and create? You could say that the occasional error is ok.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
Stored Procedure Contention?