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 - 2003-07-29 : 07:41:33
|
| aric writes "my name is aric and i am working in RAFAEL Israel, wich is an aero space and defense company (about 5000 workers).i wrote a vb6 ocx that takes an a MSProject file and writes its data into 4 tables in sql 2000, then i call five different sp that manipulates the data and creates four new tables from the data in the former tables, all of this is wrapped inside a transaction using ado in vb6. i am using the default values of sql and ado accept from cursor type in the ado that is defined as useclient. when i run multiple interfaces of the application that uses the ocx there is no problem, but when i try to run a simple query (from the query analyzer or a query from powerbuilder) all the 8 tables are locked until the transaction finishes (the transaction can take up to 3 minutes). the tables are locked even when i run the ocx on one computer and query the tables on another computer (while the transaction is opened). when i use a simple ado connection (another different process in vb6) and execute the query (using cnn.execute “select * from….) while the transaction is running there is no problem it returns the data in a sec' (may be its a problem of priority).i want to know why is it happening and how can i overcome this.i overcame this problem (for now) by creating temp tables (#mytable) which raises a new question: if i use a temp table should i create it with the indexes used in the real table, because i use insert of unique data to the table and then inserts that data to the real tables.i got an answer that it could be because of deleteing a large amount of records all of the table is locked, but that is not an answer because as i wrote the table is locked for a simple query comming from the query analyzer but if i use the same query from an ado process there is no table lock.may be at has some thing to do with priorety and assigning resources." |
|
|
|
|
|
sql table locks during transaction but only in some cases