Database session values and the design of database/sql

[Daniel Eloff]

I keep running into a (perceived?) problem with the go database/sql package. A lot (all?) databases have a concept of a database session. But the database/sql package makes no guarantee that two consecutive DB.Exec/Query will be executed in the same database session. I don't see how they can make that guarantee either, e.g. if the connection is lost between the two queries.

sql.Tx solves a lot of that by giving you a transaction which must be inside of a session, but it doesn't solve all cases. A real example:

MySQL SET TRANSACTION, can set the isolation level for the session, or the next transaction. There seems to be no way to use this with anything other than changing the global isolation level. Ideally one would use it to set the next transaction isolation level, and then call Begin(). I'm guessing I can do this in one Exec("set transaction ... ; begin") but then I have the same problem that I just started a transaction (changed session state) without a sql.Tx object or other reference to the session, and thus no way to know if subsequent queries will be executed in the same transaction or not.

Am I correct that this is an issue? Is there a way to workaround it?

Thanks,

Dan

[Tamás Gulácsi]

Sorry, I don't know MySQL, but can't you just use "set transaction" inside SQL.Tx?

[Carlos Castillo]

Since you intend on using mysql-specific features, why don't you use the mysql driver directly? Most DB drivers have their own API, and don't incorporate a connection pool by default.

[Daniel Eloff]

Sadly, MySQL doesn't permit changing the current transaction with set transaction. I can probably fall back to use the MySQL specific API, but it kind of sucks since the application is meant to work with postgres as well.

I'm more concerned by the more general failing of the Go SQL API, that it doesn't seem to allow sessions outside of sql.Tx.

[egon]

On Saturday, 6 September 2014 21:59:38 UTC+3, Daniel Eloff wrote:

I keep running into a (perceived?) problem with the go database/sql package. A lot (all?) databases have a concept of a database session. But the database/sql package makes no guarantee that two consecutive DB.Exec/Query will be executed in the same database session. I don't see how they can make that guarantee either, e.g. if the connection is lost between the two queries.

Why do you need such sessions?

[Daniel Eloff]

On Sunday, September 7, 2014 12:21:47 AM UTC-5, egon wrote:

Why do you need such sessions?

I gave one example of SET TRANSACTION in MySQL which unlike Postgres doesn't affect the current transaction, but rather affects either the session or the next transaction. Anytime one needs to modify session variables or settings outside of a transaction, you'd run into the situation. I guess most times you can set the session variables in the transaction and then roll them back afterward.

Actually I think this might work, correct me if I'm wrong: 

tx := db.Begin()

tx.Exec("set transaction ...; rollback; begin")

// use tx like normal

It's a little messy, since you have to start two transactions for every one, but It should work because a transaction does use a single database connection.

[Harald Weidner]

Hello,

On Sat, Sep 06, 2014 at 11:59:37AM -0700, Daniel Eloff wrote:

> I keep running into a (perceived?) problem with the go database/sql
> package. A lot (all?) databases have a concept of a database session. But
> the database/sql package makes no guarantee that two consecutive
> DB.Exec/Query will be executed in the same database session. I don't see
> how they can make that guarantee either, e.g. if the connection is lost
> between the two queries.

> MySQL SET TRANSACTION, can set the isolation level for the session, or the
> next transaction. There seems to be no way to use this with anything other
> than changing the global isolation level.

If you need to have an other transaction isolation level than the MySQL
server default, you should specify that directly in the Data Source Name.
The same holds for any other MySQL session variable.

E.g. with github.com/go-sql-driver/mysql, you could use
"username:password@tcp(hostname)/dbname?tx_isolation=REPEATABLE-READ"
as DSN. See https://github.com/go-sql-driver/mysql#system-variables .
Other MySQL drivers should offer similar mechanisms.

If your application needs MySQL sessions with different isolation
levels, you should consider using several connection pools.

> Am I correct that this is an issue? Is there a way to workaround it?

Hope that helps.

Harald

[Daniel Theophanes]

Hi Daniel,

Would you mind reviewing the API for rdb: http://godoc.org/bitbucket.org/kardianos/rdb ? Specifically, would it's API solve your current problem, or is it also missing features needed for your use case?

I'm mostly happy with the API as it stands, but I'm continuing to refine it and I'm trying to find corner cases in it's API.

Thanks,

-Daniel

[Daniel Eloff]

On Monday, September 8, 2014 6:45:28 AM UTC-5, Harald Weidner wrote:

 

If you need to have an other transaction isolation level than the MySQL
server default, you should specify that directly in the Data Source Name.
The same holds for any other MySQL session variable.

E.g. with github.com/go-sql-driver/mysql, you could use
"username:password@tcp(hostname)/dbname?tx_isolation=REPEATABLE-READ"
as DSN. See https://github.com/go-sql-driver/mysql#system-variables .
Other MySQL drivers should offer similar mechanisms.

I didn't realize that, it's a good solution to the problem. Thanks!