|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
How to program around row locksHow would you write a stored procedure to look at possible row locks prior
to execution ? For example, we will be periodically executing a command that updates a column in every row of a table. If any users are editing the table, we would have a potential timeout situation. How can you test for this within a stored proc ? Rob wrote:
> How would you write a stored procedure to look at possible row locks prior You typically don't "test" for row locking, you just let SQL deal with > to execution ? > > For example, we will be periodically executing a command that updates a > column in every row of a table. If any users are editing the table, we > would have a potential timeout situation. How can you test for this > within a stored proc ? > > > it. This column that you're updating, can you provide more info? You might benefit from moving this column into a seperate table, depending on how the column is used. Thanks Tracy,
These columns are in a table used by an ERP system (cannot move the column or ERP would crash). The ERP system appears to lock the tables during certain processes which result in a time out error while attempting to run an update. So I wanted to test for the condition, and not run the update if the table was in use. Show quote "Tracy McKibben" <tr***@realsqlguy.com> wrote in message news:44EDA8F9.80209@realsqlguy.com... > Rob wrote: >> How would you write a stored procedure to look at possible row locks >> prior to execution ? >> >> For example, we will be periodically executing a command that updates a >> column in every row of a table. If any users are editing the table, we >> would have a potential timeout situation. How can you test for this >> within a stored proc ? >> >> >> > > You typically don't "test" for row locking, you just let SQL deal with it. > This column that you're updating, can you provide more info? You might > benefit from moving this column into a seperate table, depending on how > the column is used. > > > -- > Tracy McKibben > MCDBA > http://www.realsqlguy.com Rob wrote:
> Thanks Tracy, I doubt the ERP system is explicitly locking tables, it's probably > > These columns are in a table used by an ERP system (cannot move the column > or ERP would crash). The ERP system appears to lock the tables during > certain processes which result in a time out error while attempting to run > an update. So I wanted to test for the condition, and not run the update > if the table was in use. > locking pages or rows (normal behavior) within the table. Multiply that by X users, and you have multiple locks scattered throughout a table. Then along comes your update, that you said is updating every row. That update will likely get escalated (again, normal behavior) to a table lock, but since there are other locks already in place, it has to wait for those to be released. How are you issuing this update command? You shouldn't see a "timeout error" in Query Analyzer, it will just sit there and spin until the locks are released. Actually, I was using Access as a client tool (so Access caused the
time-out). Are you saying that if I run a stored proc it should work ok ? I do not want the ERP to get "held up" in any way due to this process... so, if the sp does not execute successfully within a certain time period, I would like to stop the process. Thanks, Rob Show quote "Tracy McKibben" <tr***@realsqlguy.com> wrote in message news:44EDDC5C.3060502@realsqlguy.com... > Rob wrote: >> Thanks Tracy, >> >> These columns are in a table used by an ERP system (cannot move the >> column or ERP would crash). The ERP system appears to lock the tables >> during certain processes which result in a time out error while >> attempting to run an update. So I wanted to test for the condition, and >> not run the update if the table was in use. >> > > I doubt the ERP system is explicitly locking tables, it's probably locking > pages or rows (normal behavior) within the table. Multiply that by X > users, and you have multiple locks scattered throughout a table. Then > along comes your update, that you said is updating every row. That update > will likely get escalated (again, normal behavior) to a table lock, but > since there are other locks already in place, it has to wait for those to > be released. > > How are you issuing this update command? You shouldn't see a "timeout > error" in Query Analyzer, it will just sit there and spin until the locks > are released. > > > -- > Tracy McKibben > MCDBA > http://www.realsqlguy.com Rob wrote:
> Actually, I was using Access as a client tool (so Access caused the Sorry for the delay, I took a long weekend... I'm not saying > time-out). Are you saying that if I run a stored proc it should work ok ? > I do not want the ERP to get "held up" in any way due to this process... so, > if the sp does not execute successfully within a certain time period, I > would like to stop the process. > > Thanks, > Rob > definitively that moving your update to a stored proc will "just work", but it will be more likely to. I would encourage you to give it a try, and then post back here if you need more help. It will be easier to diagnose as a stored proc.  |
|||||||||||||||||||||||
Other interesting topics