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 |
|
kaff
Starting Member
11 Posts |
 Posted - 2006-01-19 : 20:43:47
|
| Hi,I am using SQL Server 2000 Server. I had one Stored procedure, which basically useing Begin Transation and End Transaction.The Whole Execution process takes 1 hr to complete, INCLUDES INSERTS AND UPDATE AND DELETE.Problem is during the whole period of execution, none of my application feature works, as they are reporting tables are locked.Why? is that DUE to Begin transaction statements?If I take out the Transaction thingy, then it goes fine, but I don't want to loose the feature of roll back the whole transaction in case of any bug.Is it a bUG? or I am using it Wrong?Thanks in AdvanceKaff |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-01-20 : 02:05:44
|
| If you use Begin Transaction then all the objects that are in that block get locked until the transaction is Committed or rollbacked. There is nothing wrong that other part of application doesnt work. This is helpful if your application is used by multi usersMadhivananFailing to plan is Planning to fail |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-01-20 : 02:12:28
|
| >> The Whole Execution process takes 1 hr to completeMaybe you should consider optimizing the stored procedure and also spliting the begin trans .. end trans into smaller block.By any chance you are using cursor in this SP ?-----------------'KH' |
|
|
|
kaff
Starting Member
11 Posts |
Posted - 2006-01-22 : 14:13:53
|
| Thanks all for feedbackYes.. I am using cursors a lot. Infact in every stored procedure..is that causing the issue as well?Thanks againKaff |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-01-23 : 02:43:41
|
| >>Yes.. I am using cursors a lot. Infact in every stored procedure..Cursors perform slowly. It is better to use Set based approachMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
Why Transations are Locking the Tables??