英文:
Are database/sql transaction objects safe for concurrent access?
问题
我需要同时执行多个SQL查询（select，update，delete），如果任何goroutine出错，需要回滚。因此问题是：数据库事务在并发访问时安全吗？
英文:
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?
答案1
得分: 21
DB 可以安全地从多个 goroutine 中访问：
> DB 是一个表示零个或多个底层连接池的数据库句柄。
>
> 它可以被多个 goroutine 并发使用。
Stmt 也可以安全地从多个 goroutine 中使用：
> Stmt 是一个预处理语句。Stmt 可以被多个 goroutine 并发使用。
每个 goroutine 应该只使用一个 sql.Tx：
> 一旦调用了 DB.Begin，返回的 Tx 就绑定到一个单独的连接上。
英文:
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
答案2
得分: 1
一般来说是的，但你需要定义所需的安全级别。事务中可能发生的三种标准现象是：
- 脏读（读取未提交的数据）
- 不可重复读（一行数据被检索两次，在两次读取之间，行内的值发生了变化）
- 幻读（执行两个相同的查询，第二个查询返回的行集与第一个查询不同）
根据所接受的行为，可以使用不同的隔离级别：
- 读取未提交（所有现象都可能发生）
- 读取已提交（防止脏读）
- 可重复读（可能发生幻读）
- 可串行化（不可能发生以上任何现象）
一般来说，使用较高的隔离级别会导致并发性较差。并发性较差意味着使用更多的锁，并阻止其他事务的并发查询。如果你知道要更新一个被选中的行，可以使用"select ... for update"语句。
你可以参考例如http://en.wikipedia.org/wiki/Isolation_%28database_systems%29，了解更详细的解释。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论