Transaction locks the database using SQL Express

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hello,

I have a strange problem in a application I'm working with.
I'm working with Delphi, but I think that the problem occurs for any
programming language. I'll try to explain it as language independent as
possible. I use ADO (Win32) with the most recent MDAC drivers.


My application consists of one ADO Connection, connected to a SQL
Express 2005 database (conn1). This connection is connected when the
application starts.

In the User Interface, there's two buttons. The first button starts a
secondary thread which does the following:

1) Creates a new ADO connection (conn2) to the same SQL Express database
2) Creates a ADODataSet
3) Starts a transaction against conn2
4) Adds 10000 records using the dataset created in 2) to table 'tableX'
5) Commits the transaction

The second button performs a simple 'SELECT * FROM tableX' and iterates
all the returned records. For this select query the first connection
(conn1) is used.


Now to the problem:
I click the first button that starts the thread (which starts the
transaction in conn2). Directly after that I click the second button
which will return all records using conn1.
The dataset used for the SELECT command will NOT be opened until the
thread is terminated and the transaction is either rollbacked or
committed. Therefore, the User Interface will be blocked until the
thread is completed, which is what I tried to avoid by using a
secondary thread.
Even worse, if there are many records stored in 'tableX', the SELECT
command may fail with a 'Command Timeout error'.


If I use a Access database and a JET driver with the exact same code it
will behave just as expected.


Does anyone have an idea what's may be wrong with my code/logic or can
it be a limitation of SQL Express?


Thanks in advance for any hints!
Mikael Stalvik
.

Relevant Pages

  • Re: Problems with transactions on SQL Express 2005
    ... After setting the isolationlevel, ... When the secondary thread (containing the connection with the active ... transaction) was busy posting new records and I try to access the ... After that the secondary thread continues to run as it should. ...
    (borland.public.delphi.database.ado)
  • Re: Working Transactions somehow started not to work
    ... Transaction and locking are not properties of recordsets but are properties ... Opening a served-based cursor will put a lock on ... If a connection is closed, ... SL> and are not associated necessarily with server-based cursors. ...
    (microsoft.public.access.adp.sqlserver)
  • Setting Locks on Rows in MSSQL Database (Currenlty Unsolvable Problem and Trying)
    ... I am beginning a transaction from PHP and setting a lock on a record ... I then commit the transaction I started when the user clicked ... There is no way in PHP where I can start a persistent ms sql connection ...
    (alt.php)
  • TransactionScope Transactions not commiting
    ... be) using the same SQL Connection. ... and dtermines the Connection String to use to create the connection to ... MyClass obj = new MyClass; ... not involved in a Transaction. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Driver AutoCommit issue
    ... Isn't the XA driver for the distributed transaction, I need to install the path sqljdbc_xa.dll and xa_install.sql before using the XA driver. ... I created a TX aware datasource using com.microsoft.sqlserver.jdbc.SQLServerDriver and tried to get the connection from the TX aware data source. ... I set the auto commit to false on connection as soon as I get the connection from the datasoruce. ...
    (microsoft.public.sqlserver.jdbcdriver)