Reqiured Transaction and Required New Transaction
Hello All,
I am developing a COM+ application with VB and SQL Sever 2000
I have written two component, one called "Confirm" with transaction level
"Required Transaction" while the other one called "LockRecord" with
transaction level
set to "Required New Transaction".
For the component "Confirm", it will select records from database and then
do some checking
before insert into database. The "LockRecord" component use to insert the
selected records of
"Confirm" into database and so other user can't know that these records have
been locked by
another user so that they can't select these records by the query.
Now, I have a big problem with this two component. When the "LockRecord"
component called
by the "Confirm" component, it will hold on the part when connect to the
database. I have changed
the type to connect the database like command object, connection object and
recordset directly, but
all of them also suffer from the "hold" situation with database and at last
will go to the Error Handling
with error "ODBC connection timeout". I have analysis the connection of the
database from SQL Server
Server Profiler, it show that when the "LockRecord" component with
transaction level set to "Required New Transaction",
the duration of the execution of the query is about 1000 times with the
duration when the "LockRecord" component
with transaction level set to "Required Transaction".
Maybe you can said I can set the transaction level to "Required Transaction"
so that I can have faster execution time.
However, coz I want to let other user can "see" the record insert by the
"LockRecord" component, so I can only use
"Required New Transaction", otherwise, other can "see" the records.
As result, do anyone have any idea to solve it? thx !
Br,
Rayman
