Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2003
    Posts
    5

    Unanswered: Transactions with InnoDB tables

    Hi,

    I'm currently writing a small application which has a VB front-end to communicate with a MySQL database. I am using the latest V4.0.10. All the tables in my database are of type InnoDB (and must be to have the transaction control I require).

    The problem I am having is this:
    I connect to the database and start a new transaction. I then insert records into table1, and without commiting, try to do a select on the records from table1. The problem is that the new records are not being picked up by my query. I was under the impression that as I was still in the same transaction I would be able to see the recently inserted records.

    Notes: if I commit the data after doing the insert to table1 and then run my query, it works fine, but I don' t want to have to commit the data at that point.
    By default the transaction isolation level is READ_REPEATABLE. I've even tried changing this to READ_UNCOMMITTED, but it makes no difference.
    Previously table1 was of type MyISAM and it all worked well, but it is no longer appropriate to keep the table as type MyISAM.

    Has anyone come across a similar issue? Any help would be much appreciated

    Catriona

  2. #2
    Join Date
    Jan 2003
    Location
    Duncan BC Canada
    Posts
    80
    You might try repeating the same SQL statements on the MySQL client command line and see if you get the same results. If you find that the Select then returns the inserted rows, then the problem is likely that you have two connections happening in VB: one in the part that does the Insert and Commit, and a different connection in the part that does the Select.

  3. #3
    Join Date
    Feb 2003
    Posts
    5
    I tried what you suggested, and it works as I would have expected, but I'm still confused as I control the database connection, and there is definately only one being used within my VB code.

    What is really strange is that I can do a 'select count(*)...' on the table from within my code, and it returns the correct number of rows, so it does see the inserts, but when I try to execute the query:
    SELECT IFNULL(SUM(col1), 0) FROM table1
    it always returns 0, when it should not.

    This really has me stumped...

  4. #4
    Join Date
    Jan 2003
    Location
    Duncan BC Canada
    Posts
    80
    I tried what you suggested, and it works as I would have expected, but I'm still confused as I control the database connection, and there is definately only one being used within my VB code.
    OK. You could always try messageboxing the connection ID at the Insert, and again at the Commit, just to be sure.

    a 'select count(*)...' on the table from within my code, and it returns the correct number of rows
    How about if you do that from the MySQL command prompt after your VB code has done the insert, and again after your VB code has done the Commit?

    SELECT IFNULL(SUM(col1), 0) FROM table1 it always returns 0, when it should not.
    It sure does seem confusing.

    Please feel free to email me your VB code.

  5. #5
    Join Date
    Feb 2003
    Posts
    5
    Hi. Thanks for taking the time to help me!

    OK, I know why it doesn't work now, only I'm not sure how to deal with it. Basically, I set up a database connection which I had made private to a module. I execute code in other modules which require this connection (there should only ever be one).

    The following is an extract of my code...

    Private dbConn As ADODB.Connection

    Public Sub openDBConnection()
    On Error GoTo err_openDB
    Set dbConn = New ADODB.Connection
    dbConn.Open "DSN=MySQLLMRDB"
    runSql "Set autocommit = 0"
    Exit Sub
    err_openDB:
    MsgBox "Error opening Database" & Err.Description
    Err.Raise Err.Number
    Exit Sub
    End Sub

    Public Sub startNewTransaction()
    dbConn.BeginTrans
    End Sub

    Public Sub commitTransaction()
    dbConn.CommitTrans
    End Sub

    Public Sub rollbackTransaction()
    dbConn.RollbackTrans
    End Sub

    Public Sub closeDBConnection()
    On Error GoTo err_closeDB
    dbConn.Close
    Exit Sub
    err_closeDB:
    MsgBox "Error closing Database" & Err.Description
    rollbackTransaction
    Exit Sub
    End Sub

    Public Function getDBConn()
    getDBConn = dbConn
    End Function

    Public Sub countRows(strSql As String)
    Dim rs As New ADODB.Recordset
    rs.Open strSql, getDBConn, adOpenStatic, adLockReadOnly
    With rs
    If Not .EOF And Not .BOF Then
    .MoveFirst
    Debug.Print "record count: " & rs.Fields(0)
    End If
    End With
    rs.Close
    End Sub

    I, 1) open the connection, 2) start a transaction, 3) do a select on the table by calling countRows, 4) insert data into my InnoDB table, 5) do a select on the table by calling countRows(), 6) commit or rollback

    OK, in countRows(), when I call getDBConn() to get the database connection I have opened, it returns the wrong count. If I use the dbConn variable directly, i.e.
    rs.Open strSql, dbConn, adOpenStatic, adLockReadOnly
    it works fine and picks up the inserted rows. So it seems the issue is with whatever getDBConn() is returning.

    Now, the really confusing thing is that the id for whatever is returned in getDBConn is the same as the initial dbConn opened (I have checked the value of dbConn in every part of the code that uses it, and it returns the same value each time). I assume the way to check the connection ID is simply to do something like 'Debug.print dbConn'.

    My 'quick-fix' solution is to make dbConn public, and then access it directly in whatever other modules make use of it to talk to the database, as opposed to calling an accessor method getDBConn(), but I really don't like this solution.

    I think getDBConn isn't returning what I would expect because of 'passing by reference/value' rules, but I really am not sure.

    If I do a 'select count()...' from the mySQL command prompt after I do the insert, it does not pick up my changes until a commit is done (which is expected).

    Any help you can give me is really appreciated

    Catriona

  6. #6
    Join Date
    Jan 2003
    Location
    Duncan BC Canada
    Posts
    80
    Sorry, I have yet to attempt transactions spread across separate modules. I think that you may be right about the passing by reference rules.

    You might try the SQLTransaction object, although it may only work with SQL Server:

    http://msdn.microsoft.com/library/de...classtopic.asp

    Bradley

  7. #7
    Join Date
    Feb 2003
    Posts
    5
    Thanks for trying anyway Bradley!

    Catriona

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •