CODEWITHSUNDEEP
go
hey
hey

Reputation: 7819

Are database/sql transaction objects safe for concurrent access?

I need to execute several SQL queries (select, update, delete) concurrently and roll back if any goroutine errors out. Thus the question: are DB transactions safe for concurrent access?

Upvotes: 16

Views: 14617

Answers (2)

OneOfOne
OneOfOne

Reputation: 99224

DB is safe to be accessed from multiple goroutines:

DB is a database handle representing a pool of zero or more underlying connections.

It's safe for concurrent use by multiple goroutines.

Also Stmt is safe to be used from multiple goroutines:

Stmt is a prepared statement. Stmt is safe for concurrent use by multiple goroutines.

You should use only one sql.Tx per goroutine:

Once DB.Begin is called, the returned Tx is bound to a single connection

Upvotes: 23

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7171

In general yes, but you have to define the level of safety that you require. The three standard phenomena that can occur in a transaction are:

- Dirty reads (read uncommitted data)
- Nonrepeatable reads (a row is retrieved twice and the values within the row differ between reads)
- Phantom reads ( two identical queries are executed, and the collection of rows returned by the second query is different from the first)

Dependent of what behavior that is accepted you can use different isolation levels:

- Read uncommitted (all phenomena possible)
- Read committed (dirty read prevented)
- Repeatable reads (phantom read can occur)
- Serializable (non of the phenomena is possible)

In general the "higher" isolation level you use, the poorer concurrency you get. Poorer in the sense that more locks are used and blocks concurrent queries from other transactions. If you know that you shall update a row that is selected you can select ... for update.

See for example http://en.wikipedia.org/wiki/Isolation_%28database_systems%29 for a more thorough explanation.

Upvotes: 1

Related Questions