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?
2 Answers
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
4 Comments
Peter
"You should use only one sql.Tx per goroutine". You probably meant to say one gorouting per Tx, although I still don't see how that follows from the documentation you quoted.
Ashford Nichols
From a database perspective, individual connections are generally not thread-safe. So by extension, things that are bound to a single connection would, also, not be safe for concurrent use.
Abhijit
Can you please clarify? I still don't understand if I can access a
sql.Tx made in the main goroutine inside other goroutines.
OneOfOne
You can not, you can access a Stmt.
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.
answered Aug 15, 2014 at 11:04
Lennart - Slava Ukraini
7,2111 gold badge23 silver badges37 bronze badges
database/sqllibrary types which mediate between the program and database engine transactions. These are separate concepts (though related) and you really have to properly explain which one you're interested in. Do you meanMySQLtransactions of instances ofsql.Txtype?