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 |
|
HG
Starting Member
2 Posts |
 Posted - 2011-01-05 : 11:36:23
|
Hi,following problem:I've got two stored procedures A and B. A calls B several Time and B is making an update to a table:Begin TranUPDATE Tablename SET Columnname=@value1 WHERE PrimaryColumn=@ID;commit tran when procedure B is called the first time the Table Tablename is locked until procedure A has finished. How can I prevent this? I want to read the written value from an other process. But this is not possible because the table is locked. What can I do so that the table is not locked anymore after procedure B finished? (SQL Server 2000, 2005 and 2008)Thanks for help |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-01-05 : 12:03:51
|
| the begin and commit tran don't do anything here so you might as well get rid of rhem.When B is called it is in a transaction - this could be controlled by A or started before A is called - in any case and updates by B will hold locks until the transaction is committed. Nothing you can do about that other htan not hold the transaction in which case you will commit for ever call to B. It sounds like a dubius design - why not accumulate the updates and execute them all in a single statement.Another option is dirty (uncommitted) reads from the reading connection - possibly worse than the underlying issue.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
HG
Starting Member
2 Posts |
Posted - 2011-01-06 : 08:21:32
|
| Thank you |
|
|
|
|
|
|
stored procedure locks table