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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-06-27 : 09:16:39
|
| Marko Logar writes "Hello,I have the following problem:I'm using MDAC 2.6 on MSSQLServer 2000 SP2 running on Windows 2000 ServerIn VB code I start a transaction on opened connection. Then I call a stored procedure, which returns inserts a record in a table and returns a parameter as return value, which I need later in VB program. After recieving return parameter from procedure I have some work to do in VB code (some records are added into some other tables (not the one, stored procedure wrote record into !!!) on SQLServer's database). After everything is completed I commit transaction on opened connection. So far, so good, but how comes, that the table, which has inserted record from stored procedure, remains locked until I commit or rollback transaction from my VB code ? It means, that no one else can call stored procedure, until first one has finished his job. That's frustrating, because in some cases, there are many records to be added into other tables and whole transaction take some time.Best regards,Marko Logar" |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-06-27 : 11:39:09
|
| Marko,Post the DDL for the table(s) involved, as well as the relevant sections of the stored procedure, and we can help you with your specific problem.Generally speaking locks will be created and held for the duration of the transaction. This is expected and desired behavior. It might be the granularity of the locks that's producing the adverse symptoms, or perhaps your access methods in the stored procedure, or the length of time you hold the transaction open, etc. etc.Jonathan Boott, MCDBA |
 |
|
|
|
|
|
|
|
locking problem