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 |
|
olily
Starting Member
37 Posts |
 Posted - 2002-10-16 : 02:58:22
|
| I have a system implemented 3 months ago. Somehow in these few days, one of the tables in my database is locked by a sleeping connection. Whenever this happens, I won't be able to do SELECT through my program or Query Analyzer. I have to kill the connection then only I can do SELECT. I have checked my coding and I have error handling set properly where when there is an error, my program will roll back by using RollbackTrans. If there is no error, I will commit the transaction by using CommitTrans. Please advice. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-10-16 : 06:29:20
|
| Check the connection that is doing the locking.dbcc inputbuffer (spid) will give you the last command issued.Putting the transaftion control in the application rather than stored procedures makes things a lot more complicated and prone to blocking like this.On error I would always close and re-open the connection to make sure any nested transactions are cleared.If you always open and close the connection on every database access then this sort of problem can't occur.You may have to resort to running the profiler or tracing the application to find out what is happenning.==========================================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. |
 |
|
|
|
|
|
Record Locked