Transaction not commiting

[ajoses]

Hi,

I'm having an Issue with the commit of transaction.

Normally with Firebird I use one connection object and then I use multiple transactions to do the querys. However in golang with this driver it leaves the transaction open, so after a while of using my program there are a lot of transactions. From the perspective of Firebird is bad because after a while it decrease the performance.

My code for open the connection is the following:

var Conexion *sql.DB

func ConectarDB() {
    var err error
    var tx *sql.Tx

    conn, err := sql.Open("firebirdsql", "SYSDBA:masterkey@localhost/C:/DatosGDB/FACTWEB.FDB")
    tx, err = conn.Begin()

    if err != nil {
        conn, _ := sql.Open("firebirdsql_createdb", "SYSDBA:masterkey@localhost/C:/DatosGDB/FACTWEB.FDB")

        tx, err = conn.Begin()

        if err != nil {
            log.Panic(err)
        }

    }

    Conexion = conn

    fmt.Println("conectado")

    tx.Commit()
}

The variable Conexion is a global variable that I use in all the program, then when I need to do a query I do the following:

    tx, err := models.Conexion.Begin()
    defer tx.Commit()

    err = tx.QueryRow("select count(*) as TOTAL\n"+
        "from FPI_DOCTO_DOCUMENTOS\n"+
        "where RFCEMPRESA = ? and NOSUCURSAL = ? and TIPO = 600 and (FECSYNC is null or FECSYNC = 0)", rfcempresa, nosucursal).Scan(&cuantos)

    if err != nil {
        return nil, err
    }

Normally with each function I start in the same way if the function will be using something of the DB, I started to see the problem when inside a function I call another function that has the same structure.

I don't know if it's something of Go, any idea will be helpful.

Greetings Arnulfo José

[nakagami]

Sorry I don't have any knowledge. I want somebody's help !

[ajoses]

Hi,

I think I found the issue of the transaction open during the time of the connection. It's the first transaction after opening the transaction.

I did some test and I modify the following:

• I review the issues #67 and #42, and tested with changes in the pull request, I think I cover both of the issues. Also I think the issue #79 is resolved with this changes.
• The first transaction doesn't start until some query or exec statement is runned. With this if I open a connection and do a connection.Begin() the driver doesn't leave a transaction open without a way to commit.
• Accordign to the code, the transaction is AutoCommit only if theres is not a begin or the last transaction was commited or rollbacked. But in the autocommit mode, only the Exec was closing the transaction, so I added the autocommit after a statement is closed.
• I change the initialization of the transaction if it has the needBegin flag (use when is autocommit) to tha connection.prepare() function in that way if its a query or an exec it will initialized if necessary
• Also in the connection.Exec() I changed from commitRetaining to Commit, in that way the transaction is closed.
• All the test passed correctly

All this changes is because the transaction open for long time and of type Read/Write affect the performance of the database, even reach the point to the need of the sweeper wich kills the database perfomance.

I think the changes improves a little the transaction management, that is something very important in Firebird.

Anything please inform me.

Pull Request #91

[nakagami]

Thanks!

Pull Request #91 seems good, so I have merged it.

But...

• Firebird 2.5 -> Pass
• Firebird 3.0 -> ?
• Firebird 4.0beta (github master HEAD) -> Fail

$ go test
--- FAIL: TestIssue89 (1.13s)
    transaction_test.go:387: Autocommit in prepare don't work
FAIL
exit status 1
FAIL    _/home/nakagami/firebirdsql 34.124s

I will investigate in detail later

[nakagami]

fix test