Are database / sql transaction objects safe for simultaneous access?

Question

I need to execute several SQL queries ( select , update , delete ) at the same time and rollback if they have goroutine errors. So the question is: are DBs safe for concurrent access

Answer 1

DB is safe to access from multiple goroutines:

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

It is safe to use multiple goroutines at the same time.

It is also safe to use Stmt from several goroutines:

Stmt is a prepared expression. Stmt is safe for multiple goroutines to use at the same time.

You should use only one sql.Tx per goroutine:

After calling DB.Begin, the returned Tx is associated with one connection

Answer 2

In general, yes, but you must determine the level of security that you need. Three standard phenomena that can occur in a transaction:

 - 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) 

Depending on what behavior is accepted, you can use different levels of isolation:

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

Typically, you use a higher isolation level, the worse concurrency. It is poorer in the sense that more locks are used and blocks simultaneous requests from other transactions. If you know that you must update the selected row, you can choose ... to update.

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