Transactions and concurrency over the same database

[xiam]

Hello @cznic,

I've been experimenting with QL's transactions and it seems like it does not handle concurrent DB.Begin() calls on the same database in a concurrent manner.

In the following example I simulate multiple goroutines opening the same database and beginning transactions within that database.

package main

import (
    "database/sql"
    _ "github.com/cznic/ql/driver"
    //_ "github.com/mattn/go-sqlite3"
    "log"
    "sync"
    "time"
)

func simulateSessionWithTransactions(sessID int) {
    var wg sync.WaitGroup

    sess, err := sql.Open("ql", "file.ql")
    //sess, err := sql.Open("sqlite3", "file.sqlite")

    if err != nil {
        log.Fatalf("%d: Open: %q", sessID, err)
    }

    for i := 0; i < 10; i++ {
        wg.Add(1)
        go func(i int, wg *sync.WaitGroup) {
            tx, err := sess.Begin()
            if err != nil {
                log.Fatalf("%d: Begin(%d): %q", sessID, i, err)
            }

            log.Printf("%d: Tx %d opened, waiting...", sessID, i)
            time.Sleep(time.Second * 3)
            log.Printf("%d: Tx %d OK!", sessID, i)

            if err = tx.Rollback(); err != nil {
                log.Fatalf("%d: Rollback(%d): %q", sessID, i, err)
            }

            wg.Done()
        }(i, &wg)
    }

    wg.Wait()

    if err := sess.Close(); err != nil {
        log.Fatalf("%d: Close: %q", sessID, err)
    }
}

func main() {
    var wg sync.WaitGroup

    for i := 0; i < 10; i++ {
        wg.Add(1)
        go func(i int, wg *sync.WaitGroup) {
            simulateSessionWithTransactions(i)
            wg.Done()
        }(i, &wg)
    }

    wg.Wait()
}

Regardless of the concurrent nature of the program, beginning a transaction is always sequential:

015/08/31 07:11:38 5: Tx 5 opened, waiting...
2015/08/31 07:11:41 5: Tx 5 OK!
2015/08/31 07:11:41 9: Tx 7 opened, waiting...
2015/08/31 07:11:44 9: Tx 7 OK!
2015/08/31 07:11:44 4: Tx 2 opened, waiting...
2015/08/31 07:11:47 4: Tx 2 OK!
2015/08/31 07:11:47 5: Tx 3 opened, waiting...
2015/08/31 07:11:50 5: Tx 3 OK!
2015/08/31 07:11:50 6: Tx 1 opened, waiting...

I also tried with SQLite, MySQL and PostgreSQL and they allow multiple concurrent transactions on the same database (which is what I was expecting):

2015/08/31 07:12:12 3: Tx 6 opened, waiting...
2015/08/31 07:12:12 3: Tx 5 opened, waiting...
2015/08/31 07:12:12 7: Tx 0 opened, waiting...
2015/08/31 07:12:12 7: Tx 3 opened, waiting...
2015/08/31 07:12:12 7: Tx 2 opened, waiting...
2015/08/31 07:12:12 7: Tx 1 opened, waiting...
2015/08/31 07:12:12 7: Tx 5 opened, waiting...
2015/08/31 07:12:12 7: Tx 4 opened, waiting...

The effect of this, besides slower benchmarks, is not being able to benefit a lot from concurrency, because at the end every transaction has to be executed sequentially from start to end; besides if a transaction takes a long time it will delay all the other transactions.

I understand that transactions are essentially sequential anyways and that this lock is required in order to keep consistency, but what about moving the lock to Commit() (which does the actual writing) instead of having it on Begin()?

[cznic]

Still on vacations, will look into this next week...