可序列化事务与选择更新 [英] Serializable Transactions vs SELECT FOR UPDATE
问题描述
我正在阅读不同的事务隔离级别,并遇到了SERIALIZABLE
隔离级别.我也知道诸如Postgres,Oracle和MySQL之类的数据库支持SELECT .. FOR UPDATE
语法.
I was reading on the different transaction isolation levels, and came up across the SERIALIZABLE
isolation level. I also know that databases such as Postgres, Oracle and MySQL support the SELECT .. FOR UPDATE
syntax.
但是,当我想锁定要对其执行更新的行(或行范围)时,我感到困惑.
I am however confused how these should be used when I would like to lock a row (or a range of rows) which I wish to perform updates on.
过去使用JPA时,我总是在查询中结合使用@Transactional
和LockModeType.PESSIMISTIC_WRITE
.这意味着在SQL中将READ_COMMITTED
隔离级别与SELECT .. FOR UPDATE
一起使用.
When using JPA in the past, I always used @Transactional
coupled with a LockModeType.PESSIMISTIC_WRITE
on the query. This translates to using a READ_COMMITTED
isolation level with a SELECT .. FOR UPDATE
in SQL.
但是现在,在阅读了有关SERIALIZABLE
的内容之后,我想知道如果我将@Transactional(isolation=SERIALIZABLE)
与普通的SELECT
一起使用(例如 em.findById 来获取一个分离的实体)会有什么不同),后跟UPDATE
(实体的合并).
But now, having read about SERIALIZABLE
, I'm wondering what would be different if I used @Transactional(isolation=SERIALIZABLE)
with a normal SELECT
(e.g. em.findById to fetch a detached entity), followed by an UPDATE
(merge of the entity).
行为会一样吗?
例如,我有一个银行系统,我希望在两个帐户之间进行转帐.我要求在转移过程中不要干预这些帐户.因此,假设我用-100借记一个帐户并将其贷记到另一个帐户中.确保这些帐户仅对执行更新的交易可用的最佳方法是什么?
Say for example, I have a Bank system, and I wish to transfer money between two accounts. I require these accounts not to be meddled with, while the transfer is in progress. So, suppose I debit one account with -100 and credit it into the other account. What would be the best way to ensure that these accounts are available only to the transaction performing the update?
假设我正在处理JPA分离的实体,因此在更新之前,我将不得不从数据库中读取它们,例如 findById().
Assume that I'm manipulating JPA detached entities, so prior to updating, I will have to read them from the DB, e.g. findById().
- 使用
@Transactional(isolation=READ_COMMITTED)
, em.findById 和LockModeType.PESSIMISTIC_WRITE
(即SELECT .. FOR UPDATE
),然后使用 em.merge (即UPDATE
)吗? - 还是使用
@Transactional(isolation=SERIALIZABLE)
, em.findById ,然后使用 em.merge (即UPDATE
)?
- Use
@Transactional(isolation=READ_COMMITTED)
, em.findById withLockModeType.PESSIMISTIC_WRITE
(i.e.SELECT .. FOR UPDATE
), and then em.merge (i.e.UPDATE
) ? - OR Use
@Transactional(isolation=SERIALIZABLE)
, em.findById, and then em.merge (i.e.UPDATE
)?
推荐答案
SERIALIZABLE与使用SELECT FOR UPDATE之间的主要区别在于,SERIALIZABLE始终锁定所有内容.与SELECT FOR UPDATE一样,您可以选择锁定的内容和时间.
The main difference between SERIALIZABLE and using SELECT FOR UPDATE is that with SERIALIZABLE everything is always locked. Where as with SELECT FOR UPDATE you get to choose what and when you lock.
因此,如果您只想锁定某些数据(即BankAccount)而不是其他数据(例如Branch,AccountTypes),那么SELECT FOR UPDATE将为您提供更好的控制,因为SERIALIZABLE会阻塞整个系统,因为从ACCOUNT_TYPES表中选择的每个事务.另外,某些交易可能只想检查余额,因此不需要锁定ACCOUNT表.
So if you only want to lock some data, i.e. BankAccount but not other, i.e. Branch, AccountTypes, then SELECT FOR UPDATE gives you much better control, where as SERIALIZABLE would block your entire system because every transaction selected from the ACCOUNT_TYPES table. Also, some transactions may just want to check the balance, so do not need to lock the ACCOUNT table.
看到,
http://en.wikibooks.org/wiki/Java_Persistence/Locking
这篇关于可序列化事务与选择更新的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!