将事务用于选择语句?
我不经常使用存储过程，我想知道将我的选择查询包装在事务中是否有意义.
I don't use Stored procedures very often and was wondering if it made sense to wrap my select queries in a transaction.
我的程序有三个简单的选择查询，其中两个使用第一个的返回值.
My procedure has three simple select queries, two of which use the returned value of the first.
推荐答案
在高度并发的应用程序中，(理论上)可能会发生在第一个选择中读取的数据在其他选择执行之前被修改的情况.
In a highly concurrent application it could (theoretically) happen that data you've read in the first select is modified before the other selects are executed.
如果您的应用程序中可能发生这种情况，您应该使用事务来包装您的选择.请确保选择正确的隔离级别，但并非所有事务类型都能保证一致的读取.
If that is a situation that could occur in your application you should use a transaction to wrap your selects. Make sure you pick the correct isolation level though, not all transaction types guarantee consistent reads.
更新:您还可以找到关于并发更新/插入解决方案(又名upsert)有趣.它把几种常用的 upsert 方法放到测试中，看看是什么方法实际上保证数据在 select 和 next 语句之间没有被修改.结果是，嗯，令人震惊.
Update : You may also find this article on concurrent update/insert solutions (aka upsert) interesting. It puts several common methods of upsert to the test to see what method actually guarantees data is not modified between a select and the next statement. The results are, well, shocking I'd say.
相关文章