事务、锁、隔离级别
我有几个关于标题主题的问题。首先，假设我们使用 JDBC，并且有 2 个事务 T1 和 T2。在 T1 中，我们对一个特定行执行 select 语句。然后我们对该行执行更新。在事务 T2 中，我们在同一行上执行 select 语句。
以下是问题：
1) 事务 T1 何时获取上述行上的锁？我认为它发生在 select 语句执行期间？
2）事务T1持有锁多久？它会保留它直到事务提交/回滚，还是在此之前释放锁？
3）隔离级别是否控制使用什么类型的锁？例如：
a) 如果我们在事务 T2 上使用已提交读隔离级别，这是否意味着 T2 将为 select 语句使用共享读锁，这样，如果 T1 更新了该行，T2 将不会有访问该行（避免脏读），并且如果 T1 没有更新行，但 T2 将具有对该行的读访问权限？
b) 如果我们在事务 T2 上使用读未提交隔离级别，这是否意味着 T2 将对 select 语句使用无锁，因此即使 T1 正在修改数据，它也可以读取数据（允许脏数据）读）。
所以，最让我烦恼的问题是谁有权决定应用什么类型的锁？是事务的隔离级别，还是有其他方式？
4) 如果问题 3 的答案是肯定的（隔离级别控制使用什么锁），那么如果我们在 mysql 数据库上使用 jdbc，并且在共享模式构造中使用 select for update 或 select lock，会发生什么情况？我记得第一个是独占锁，第二个是共享读锁。它将如何反映我们的事务隔离级别？
5）可重复读隔离级别会获取什么样的锁？假设我们的 T2（具有可重复读隔离级别）在同一行上有两个 select 语句，而 T1 与之前相同。首先在T2中执行一个select语句，然后执行T1并提交，然后执行T2第二个select。这种情况有可能吗？如果事务在提交/回滚之前一直持有锁，我认为在 T2 完成之前 T1 将无法获得更新的独占锁？
编辑：还有一个问题：
6）在多版本并发控制系统中，当我们设置可序列化隔离级别时，事务A尝试更新由另一个事务B更新的某些行（B在A开始）将被回滚。我想问的是，在乐观锁定场景中，这不是发生同样的事情吗？
提前致谢。
I have a few questions regarding subject from the title. First of all, lets assume that we work with JDBC, and there we have 2 transactions T1 and T2. In T1 we execute select statement on one particular row. Then we execute update on that row. In transaction T2 we execute select statement on the same row.
Here are the questions:
1) When does transaction T1 acquire the lock on mentioned row? I assume it happens during select statement execution?
2) How long transaction T1 holds the lock? Does it hold it until the transaction is committed/rolled back, or it release the lock before that?
3) Does isolation level controls what type of lock is used? For example:
a) if we use read committed isolation level on transaction T2, does that means that T2 will use shared read lock for select statement, so that in case T1 updated the row already T2 will not have access to that row (avoiding dirty reads), and in case T1 did not update row yet T2 will have read access to that row?
b) If we use read uncommitted isolation level on transaction T2, does that means T2 will use no-lock for select statement, so it can read data even if it is being modified by T1 (allowing for dirty reads).
So, the question that bugs me most is who is in control of deciding what type of locks are applied? Is it isolation level of transaction, or there is some other way?
4) If answer to question 3 is positive (isolation level controls what locks are used), then what happens if we use jdbc over mysql database for example, and we use select for update or select lock in share mode constructs? As I remember first is exclusive lock, while second is shared read lock. How will it reflect on our transaction isolation level?
5) What kind of lock is acquired in case of repeatable read isolation level? Lets assume our T2 (with repeatable read isolation level) have two select statements on the same row while T1 is same as before. First is executed one select statement in T2, then T1 is executed and committed, and after that T2 second select is executed. Is this scenario even possible? If transactions hold their locks until they are committed/rolled back, I assume T1 will not be able to get exclusive lock for update until T2 is completed?
EDIT: One more question:
6) In multiversion concurrency control system, when we set serializable isolation level, transaction A that tries to update some row that is updated by another transaction B (B updated the row after the A started) will be rolled back. I want to ask isn't that pretty same thing that happens in optimistic locking scenarios?
Thanks in advance.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
你的问题提得很好。了解获取何种类型的锁可以加深对 DBMS 的理解。在 SQL Server 中，在所有隔离级别（未提交读、已提交读（默认）、可重复读、可序列化）下，写操作都会获取独占锁。
无论隔离级别如何，事务结束时都会释放排他锁。
隔离级别之间的差异指的是获取/释放共享（读）锁的方式。
在未提交读隔离级别下，不会获取任何共享锁。在此隔离级别下，可能会发生称为“脏读”的并发问题。
在已提交读隔离级别下，将为相关记录获取共享锁。当前指令结束时共享锁被释放。此隔离级别可防止“脏读”，但由于记录可以由其他并发事务更新，因此可能会发生“不可重复读”或“幻读”。
在可重复读取隔离级别下，在事务持续时间内获取共享锁。 “脏读”和“不可重复读”被阻止，但“幻读”仍然可能发生。
在可序列化隔离级别下，在事务持续时间内获取范围共享锁。上述并发问题都不会发生，但性能会大幅下降，并且存在发生死锁的风险。
Your question is a good one. Understanding what kind of locks are acquired allows a deep understanding of DBMS's. In SQL Server, under all isolation levels (Read Uncommitted, Read Committed (default), Repeatable Reads, Serializable) Exclusive Locks are acquired for Write operations.
Exclusive locks are released when transaction ends, regardless of the isolation level.
The difference between the isolation levels refers to the way in which Shared (Read) Locks are acquired/released.
Under Read Uncommitted isolation level, no Shared locks are acquired. Under this isolation level the concurrency issue known as "Dirty Reads" can occur.
Under Read Committed isolation level, Shared Locks are acquired for the concerned records. The Shared Locks are released when the current instruction ends. This isolation level prevents "Dirty Reads" but, since the record can be updated by other concurrent transactions, "Non-Repeatable Reads" or "Phantom Reads" can occur.
Under Repeatable Reads isolation level, Shared Locks are acquired for the transaction duration. "Dirty Reads" and "Non-Repeatable Reads" are prevented but "Phantom Reads" can still occur.
Under Serializable isolation level, ranged Shared Locks are acquired for the transaction duration. None of the above mentioned concurrency issues occur but performance is drastically reduced and there is the risk of Deadlocks occurrence.