Blocking problem with DSN-less connection


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Blocking problem with DSN-less connection

Hybrid View

  1. #1
    Adam Dawes Guest

    Blocking problem with DSN-less connection

    I have been working on an application for some time written in VB running
    against SQL Server 7 SP1.

    Recently I have had to change the connection string used to connect to the
    database so that it no longer uses a DSN. After having made this change, I
    am experiencing constant blocking problems on the database.

    I have managed to isolate the piece of code and the database structure
    required to reproduce this problem, and this is all included below. I would
    very much appreciate any assistance that anyone might be able to give me!

    The cause of the problem appears to be executing INSERT statements when a
    have an open recordset. When the INSERT is executed, a second connection
    appears to my database, and this connection is blocked by the original
    connection. I am totally puzzled as to why this is happening. When I modify
    the connection string to use a DSN as before, this problem does not occur
    and the code executes without problem.

    Can anyone explain what is going on?

    To create the structure required to recreate this problem, create a new
    database and execute the following in Query Analyser:


    CREATE TABLE [dbo].[PlansEvents] (
    [Name] [varchar] (64) NOT NULL ,
    [Id] [float] NOT NULL ,
    [Description] [varchar] (255) NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[Tracks] (
    [Ref] [varchar] (64) NOT NULL ,
    [Description] [varchar] (255) NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[TracksEvents] (
    [Ref] [varchar] (64) NOT NULL ,
    [ID] [float] NOT NULL ,
    [Description] [varchar] (255) NULL ,
    [Format] [varchar] (64) NULL
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[Tracks] WITH NOCHECK ADD
    CONSTRAINT [PK_Tracks] PRIMARY KEY NONCLUSTERED
    (
    [Ref]
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[TracksEvents] ADD
    CONSTRAINT [TracksTracksEvents] FOREIGN KEY
    (
    [Ref]
    ) REFERENCES [dbo].[Tracks] (
    [Ref]
    )
    GO

    INSERT INTO [PlansEvents] ([Name], [ID]) VALUES ('Test',1)
    INSERT INTO [PlansEvents] ([Name], [ID]) VALUES ('Test',2)
    INSERT INTO [PlansEvents] ([Name], [ID]) VALUES ('Test',3)


    Once this is done, create a new VB project, reference ADO (I'm using the ADO
    2.5 library but I think this occurs with all versions) and paste the
    following code into the Form_Load event:


    Dim adoConnection As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sql As String

    Set adoConnection = New ADODB.Connection
    adoConnection.CommandTimeout = 10
    adoConnection.Open "Provider=SQLOLEDB;Data Source=Victim1;Initial
    Catalog=BlockTest;UID=sa;PWD=nothing"

    'Begin the transaction
    adoConnection.Execute "BEGIN TRANSACTION MyTrans"

    'Create a record in the Tracks table
    sql = "INSERT INTO [Tracks] (Ref, Description)"
    sql = sql & " VALUES ('TestRef', 'Description')"
    adoConnection.Execute sql

    'Get a list of records in the PlansEvents table
    Set rs = New ADODB.Recordset
    rs.Open "SELECT * FROM [PlansEvents] WHERE Name = 'Test'", _
    adoConnection, adOpenStatic, adLockReadOnly

    'For each record retrieved, insert a record into TracksEvents
    Do Until rs.EOF
    sql = "INSERT INTO [TracksEvents] (Ref, ID, Description)"
    sql = sql & " VALUES ('TestRef', " & _
    rs("ID") & ", '" & rs("Description") & "')"

    'The following line fails to execute
    adoConnection.Execute sql

    rs.MoveNext
    Loop

    rs.Close
    Set rs = Nothing

    adoConnection.Execute "COMMIT TRANSACTION MyTrans"

    Set adoConnection = Nothing


    (You'll obviously need to modify the connection string to refer to the
    database and server that you have set up, rather than those shown in the
    code above).

    When the code is executed on my system, it runs through to the Execute
    method call in the loop, and then fails after the 10 second timeout has
    elapsed ("Method 'Execute' of object '_Connection' failed"). It I execute an
    sp_who call in Query Analyser before the timeout elapses (increase the
    timeout period to make this easier), a second connection has appeared at
    this point, and is blocked by the first.

    Removing the transaction also stops the problem occurring, but I need the
    transaction to be in place.

    I would very much like to know:

    1. Why is this happening? Why has a second connection appeared? What is
    causing it to be blocked?

    2. Why does this only occur when using a DSN-less connection string? Is
    there something special about a DSN that changes the way the database
    behaves? How can I make the DSN-less connection behave in the same way as
    that provided by the DSN?

    Many thanks in advance,

    --

    Adam.





  2. #2
    Tom Wilson Guest

    Re: Blocking problem with DSN-less connection


    I'm not totally sure about this, but I think it may be because you are opening
    a recordset in the middle of the transaction.
    Try opening rs before you start the transaction. You could also build one
    sql statement and then execute it all at once. Your sql statement would
    have the begin trans, all of the insert calls, and then the end trans.

    Hope this helps

    Open the recordset first
    > Dim adoConnection As ADODB.Connection
    > Dim rs As ADODB.Recordset
    > Dim sql As String
    >
    > Set adoConnection = New ADODB.Connection
    > adoConnection.CommandTimeout = 10
    > adoConnection.Open "Provider=SQLOLEDB;Data Source=Victim1;Initial
    >Catalog=BlockTest;UID=sa;PWD=nothing"
    >
    > 'Get a list of records in the PlansEvents table
    > Set rs = New ADODB.Recordset
    > rs.Open "SELECT * FROM [PlansEvents] WHERE Name = 'Test'", _
    > adoConnection, adOpenStatic, adLockReadOnly


    > 'Begin the transaction
    > adoConnection.Execute "BEGIN TRANSACTION MyTrans"
    >
    > 'Create a record in the Tracks table
    > sql = "INSERT INTO [Tracks] (Ref, Description)"
    > sql = sql & " VALUES ('TestRef', 'Description')"
    > adoConnection.Execute sql
    >
    >
    > 'For each record retrieved, Insert a record
    > Do Until rs.EOF
    > sql = "INSERT INTO [TracksEvents] (Ref, ID, Description)"
    > sql = sql & " VALUES ('TestRef', " & _
    > rs("ID") & ", '" & rs("Description") & "')"
    >

    'The following line fails to execute
    adoConnection.Execute sql
    >
    > rs.MoveNext
    > Loop
    >
    > rs.Close
    > Set rs = Nothing
    >

    adoConnection.Execute "COMMIT TRANSACTION MyTrans">
    > Set adoConnection = Nothing
    >


    Build the sql string completely before executing
    > Dim adoConnection As ADODB.Connection
    > Dim rs As ADODB.Recordset
    > Dim sql As String
    >
    > Set adoConnection = New ADODB.Connection
    > adoConnection.CommandTimeout = 10
    > adoConnection.Open "Provider=SQLOLEDB;Data Source=Victim1;Initial
    >Catalog=BlockTest;UID=sa;PWD=nothing"
    >
    > 'Begin the transaction

    sql = "BEGIN TRANSACTION MyTrans"
    > 'adoConnection.Execute "BEGIN TRANSACTION MyTrans"
    >
    > 'Create a record in the Tracks table
    > sql = sql & " INSERT INTO [Tracks] (Ref, Description)"
    > sql = sql & " VALUES ('TestRef', 'Description')"
    > 'adoConnection.Execute sql
    >
    > 'Get a list of records in the PlansEvents table
    > Set rs = New ADODB.Recordset
    > rs.Open "SELECT * FROM [PlansEvents] WHERE Name = 'Test'", _
    > adoConnection, adOpenStatic, adLockReadOnly
    >
    > 'For each record retrieved, use the record to build the sql string
    > Do Until rs.EOF
    > sql = sql & " INSERT INTO [TracksEvents] (Ref, ID, Description)"
    > sql = sql & " VALUES ('TestRef', " & _
    > rs("ID") & ", '" & rs("Description") & "')"
    >

    'The following line fails to execute
    'adoConnection.Execute sql
    >
    > rs.MoveNext
    > Loop
    >
    > rs.Close
    > Set rs = Nothing
    >

    sql = sql & " COMMIT TRANSACTION MyTrans"
    adoConnection.Execute sql
    >
    > Set adoConnection = Nothing
    >



  3. #3
    Tom Wilson Guest

    Re: Blocking problem with DSN-less connection


    Adam,

    I have a better suggestion than the ones in my previous post.

    Why not just create a stored procedure?

    You could use a fetch cursor to loop through the records that you want to
    extract data from. Inside the loop, you would have an INSERT INTO statement.
    After each insert, you could check the error status and roll back your transaction
    if there's a problem.

    Always use stored procedures whenever you can. If you don't absolutely have
    to use a VB do loop in this case, the stored procedure is your best option.

    Hope this helps.

  4. #4
    Mike Beadles Guest

    Re: Blocking problem with DSN-less connection


    Adam - did you get a satisfactory response or workaround?

    I set up this problem and simulated it. In the database, there is an open
    transaction shown - which would be putting an exlusive lock in the table.
    It would locks out all other activity, including simple select statements.

    In fact, SELECT statements issued from a separate query window do not complete
    - they are blocked and time out.

    The open transaction goes away when the debugger is stopped.

    Pursue this further? (I'm dealing with a similar issue and will carry on
    even if I don't hear from you).

    Normally, stored procedures would be used as a best practice, but the app
    I work with is supported by a number of database engines so the code has
    to be as generic and common as possible.



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center