Wednesday, January 31, 2007

Understanding Transaction Isolation

自从开始作为一个developer, 就可以是接触和了解事务隔离级别(transaction isolation level) , 但是坦白说我还没有真正的理解过这个概念, 总是停留再一种似懂非懂的境界. 期间也去网上找过很多资料, 但是都是搜索中文网页, 老实说, 国内的网站技术文章太少了 ,而且都是一篇文章所有网站抄来抄去, 很悲哀到了现在还是似懂非懂, 难道要糊涂的生, 然后糊涂的死么?
昨天, 当我现在在这里写这些文字的时候,我一直在回想, 昨天是什么促使我突然又开始关注事务隔离级别这个话题. 想不起来了, 昨天晚上公司年夜饭, 喝了不少红酒, 还喝了一杯白酒, 回家之后倒头就睡,过去的记忆仿佛都在昨天被抛弃了. 就是说想不起来了, 想不起来也无所谓, 这件事情还是要做的, 所以今天又google了一把, 关键字是transaction isolation level, 嘿嘿,找到了一些资源, 先看看. 写这些文字的时候, 我还根本没看那些找到的文章, 有点颠倒是非. 先写道这里, 看了之后, 然后再说我是否已经真正理解了事务隔离级别这个概念. 理解了我便要在这里写下我的理解.
下面这篇文章不错, 我决定直接copy过来.

Understanding Transaction Isolation

Article summary

This is a topic related to SQL Server. However, the concept is applicable to any RDBMS. While auditing many applications, I have found that incomplete or no understanding of ‘Isolation Level’ leads to a lot of real life problems. These include performance degradation, blocking, locking as well as major deadlocks.

This article provides an easy-to-understand view of what ‘Isolation Levels’ really mean and when to use which level.

‘Isolation Level’ is a setting that decides how data which is a part of an ongoing transaction is made visible to other transactions.

Transactions are well understood. So what is the problem?

As all of us know, it is a unit of work. The work may contain many steps but either all steps happen or all steps don’t happen. This is fairly well known. Nothing new here. But consider how transactions occur in real life.

  • I am updating one row in a Begin Tran and Commit section. I have issued Begin Tran as well as the Update statement. Commit is not yet issued – because I want to perform some more actions in the same transactions on some other tables. If some other user wants to read this row that I have updated but not yet committed, what should happen?
  • Consider another scenario. I start a transaction. I calculate a total of a field based upon all rows in a table. Now, I need to add a new record in another table which contains this total. Now, can the original table be changed by some other user after I calculate the total? In which case, there could be a mismatch. Do you want to take such chances?
  • Another scenario. I am working on some transaction table between a range of keys within a transaction—say 10 and 20. There were only 5 records when I read the range – 10, 12, 14, 16, 20. Now I am working on other things in the transaction. Before I could commit the transaction, someone added another row with a key value of 11. Now, my base assumption about what records I read between 10 and 20 and further work upon them itself is wrong. Problem!!! Is it not?
  • I start reading a long table. It is not a transaction at all. But other users want to refer to that table for updating some fields in specific rows. The query takes 20 minutes to read all the rows. What happens to other users who are trying to update the rows? Do they wait for 20 minutes? Or they are allowed to update the rows even when the rows are being read in a large query? What if the query was used to generate a summary report containing grand totals? The total would be wrong because after the summation started, some rows have changed. Some of these rows could have changed after the summation occurred. What’s to be done now?

As you can see all these situations are confusing and prone to inaccuracies. To avoid such problems we have a feature called “Isolation Levels”.

Isolation Levels are applicable to transactions. These decide the visibility of information which is a part of an ongoing transaction.

In very simple terms Isolation Levels decide “What happens when some data being referred to (for reading or writing) within an incomplete transaction is also being referred to (for reading or writing) from another connection (or user – actually it is called another ‘transaction lock space’)?”

To make things sound technical, all these problems have been given nice and complex sounding names.

Data visibility problems that can occur during a transaction

Let us understand some jargon.

  • Transaction Lock Space:

Each transaction performs certain operations (select / insert / update / delete) on one or more rows of one or more tables. Transaction starts with Begin Tran command and ends with Commit. If unsuccessful, it ends with Rollback command. Now, after Begin Tran is issues and before Commit is issued, multiple tables may participate in the transaction related commands. Specific parts of these tables need to be locked during this phase to ensure that other users do not interfere with this transaction. This is called Transaction Lock Space.

  • Uncommitted (dirty) data:

Consider this code snippet (Blue text is code; green indicates comments):

1. Begin Transaction

—we want to change the customer status from active = “yes” to active = “no”

2. Update customer Set active = “No”

where CustomerID = 2324

—some more commandss

3. Select * from customer where CustomerID = 2324

4. Commit Transaction

Now, if the transaction commits, the value will be “No”. If it does not commit (for whatever reason), the value will remain “Yes”.

Consider that the value has already been changed to “No”. But there are more commands to be executed before the entire transaction commits. These commands are time consuming and take, say, 3 minutes. During these three minutes, if some user outside the transaction lock space reads the value of Active field in the Customer table for ID 2324, what should they see? “Yes” or “No”? The answer is simple. The value is changed but not committed. Therefore, external queries should still show “Yes”.

Now what would happen if a Select active from Customer where CustomerID – 2324 returns “No” to another user? What happens if the transaction rolls back? This is called Uncommitted Data. Ideally this should not be visible outside the transaction.

However, consider the same command executed within the transaction (line 4). It should – and it will return “No”.

Now let us consider various problems that can occur. The problems can be of three types:

1. Dirty Read

2. Non-repeatable read

3. Phantom rows

- Dirty reads

This is when connections outside the transaction space can read the uncommitted data. You don’t want this to happen in most cases. The only reason when you may want to allow this is when you are creating a report which assumes a certain amount of inaccuracy. You think this situation is rare? Not really. Suppose the report compares the current month sale with last month sale and returns a percentage difference. Consider that the report takes 5 minutes to generate. During these 5 minutes, more transactions may be getting added to the sales data. Typically 5 transactions would get added to the sales table. The average transaction value is Rs. 1000. The total sale for the month is typically 30-40 lacs. In such cases, you really don’t need to worry about absolute accuracy. You can intentionally allow dirty reads because the impact is really not significant from a business perspective.

- Non-repeatable read

Consider the above code. We change the value of Active to “No” in line 2. Now in line 4 you expect the value to be “No” because we are querying the row ‘within’ the transaction.

Now what would happen if some other transaction was allowed to change the same value to, say, “Maybe”? In line 4, we would expect value of “No” but actually get value of “Maybe”. This problem is called non-repeatable read. The idea is that within a transaction, the same data read any number of times should yield consistent results. If it is not same then the reads are ‘non-repeatable’.

- Phantom Rows

This was explained in the introduction (range 10 to 20 example). The concept is simple. If you have already read a range of data based upon a key value, another transaction inserts a new row which happens to have a value between this range. The original range which was referred to would now become invalid. This is because a “Phantom row” was added. This is also a major problem.

Important learning

Although the above three issues are listed as problems, they may not always be considered as problems! Paradoxical? Not really. As explained for Dirty Read, whether it is a problem or not depends entirely upon the business context. Technology exists to allow these problems to occurs as well as prevent them. The choice is yours. I know this complicates matters. But if these matters were not complicated, what are IT professionals paid for!

Now let us see how to prevent these problems? The answer is “using Isolation Levels”.

Types of Isolation Levels

To solve the problem of … The Isolation Level should be…
Dirty Read Read Committed (Default of SQL Server)

Dirty Read and Non-Repeatable Read Repeatable Read Non-Repeatable Read

Dirty Read and Non-Repeatable

Read and Phantom Rows

To retain all three problems Read Uncommitted

Before we discuss each isolation level, let us understand how to set or change it in T-SQL.




- This setting applies to all transactions which are happening within a single connection.

- Multiple connections can have different Isolation Levels.

- Within a single connection, you can change the Isolation Level for specific transactions.

- You must know the kind of transaction you are performing and its business requirements before you can decide the right isolation level.

- Isolation level does not just affect the current transactions in a given connection. It also affects how other transactions issued within other connections will behave.

- Therefore, you should have a good idea of what kind of concurrent transactions occur in your application when the system is live and many users are active.

This is the single biggest problem which leads to low performance, locking and blocking, as well as deadlocks

- Most developers think of individual transactions as though they are occurring in a single user system.

- It is difficult to envisage all permutations of possible transactions which can occur together. But it is very much possible to plan for it in a structured and informed manner. This requires additional effort, monitoring live systems and tweaking of specific transactions and so on.

- Unfortunately, this additional effort is rarely a part of system deployment! Such problems surface only after the system load increases in such a way that small, unnoticed issues become amplified due to large volume of data and / or large number of concurrent users.

- Only one of the options can be set at a time.

- It remains in effect till you explicitly change the option by issuing another command. This is an important aspect to consider.

Best Practice : When you change the Isolation Level for a transaction, remember to set it back to the original level after the transaction is completed.

Isolation levels

  • Read Uncommitted

This is as good (or bad) as not having any isolation. All data which is uncommitted is readable from any connection. This should not be used unless you have a very good reason to do so.

  • Read Committed

This prevents dirty reads. This does not prevent phantoms or non-repeatable reads. This is the default. Although it is the default, it does not mean that this isolation level is ideal for all transactions. One of the regular tendencies amongst techies is to simply use default values without giving it a second thought! I cannot imagine the number of phantom and non-repeatable reads that must be occurring in the world because someone simply used the ‘default’ value. It is a scary thought to say the least.

This level is obviously more restrictive than the ‘Read Uncommitted’ level.

  • Repeatable read

This prevents dirty reads as well as non-repeatable reads. It does not prevent phantom rows. This is more restrictive than Read Committed level. When I say restrictive what does it mean? It means that the chances of other transactions having to wait for this one to finish are INCREASED. Another way of saying this is – Repeatable Read reduces concurrency compared to Read Committed level.

  • Serializable

This is the most restrictive of the options. This should never be used as the default level. If you use this one as the default, it will most probably create a single user system!

This prevents all three problems.

How do Isolation Levels work?

You will notice that we have not yet addressed this issue at all in this article. The answer to this question is another Pandora’s box. The answer is simple. It uses the appropriate types of locks to achieve the desired effects. We will see what locks are and how they are implemented using Isolation Levels in the next article.

In the meantime, please go through your code and see if you are simply using the default level or are there some transactions which merit a different isolation level. If you feel like changing a level, never do it in production system. It can create havoc. Try it out in a test environment first, satisfy yourself that it has no side effects and then implement it in production system.

Don’t stop there. Monitor the production system. Some problems are never detected in test systems. They may occur in production. Handle them as required.

If you have a packaged product which handles large amount of data and many concurrent users, you must analyse the appropriate usage of Isolation Levels.


Anonymous said...

强化一下我的理解吧。 一般在一个应用程序中事务隔离级别设置为Read Commited就可以了,而且通常所有事务的隔离级别是一样的,但是我们需要弄清楚为什么设置成ReadCommited而不是RepeatableRead或者Serializable。
1)ReadUncommitted 这个基本表示事务之间是透明的,没有任何隔离。假设事务A读取了一行数据R1,并且修改了R1,然后事务A也来读取R1,那么事务A看到了更新后的R1,事务A开始基于更新的R1执行后续的逻辑。但是事务B并没有提交更新后的R1,而是执行了回滚。这样事务A读到的数据就是脏的,也就是脏读的问题。 当然在这个事务隔离级别下,也就会出现不可重复读的问题,因为事务A在事务B提交前和提交后读取的数据肯定不一样。 更不用说幻影读取了。
2)ReadCommitted,这个隔离级别可以避免脏读问题。事务B虽然更新了R1,但由于没有提交,所以事务A是看不到的,事务A看到的仍然是数据库中保存的最初的R1。 但是事务B如果么有回滚,而是提交了事务,那么事务A在事务B提交前后读取的R1是不一样的,这样还是无法解决不可重复读的问题。
结合sqlserver具体说说,所有的数据库隔离级别最终是由数据库通过锁的机制来实现的。 ReadCommitted在sqlserver中应该是共享锁,也可以叫只读锁,或者乐观锁, 比如事务A读取R1,那么它在R1上设置了共享锁,这个时候事务B也要读取R1,因为R1已经被设置了锁,但是由于是共享锁,所以事务B也可以得到这个锁,从而读取R1。假设这个时候,事务B要更新R1,那么事务B必须获得更新锁(更新锁是一种意图锁,当一个事务已经请求共享琐后并试图请求一个独占锁的时候发生更新琐,它和独占锁不一样。如果两个事务在几行数据行上都使用了共享锁,并同时试图获取独占锁以执行更新操作时,就发生了死锁:都在等待对方释放共享锁而实现独占锁。更新锁的目的是只让一个事务获得更新锁,防止这种情况的发生。),事务B获得了更新锁,就可以更新数据,这个时候事务A再次读取的话,R1的值就变了。
3)RepeatableRead 这个事务隔离级别 可以防止不可重复读的问题。假设事务A读取了R1,那么数据库在R1上会放置独占锁,也可以叫排他锁,悲观锁,这个时候如果事务B要读取R1,它必须等待直到事务A提交了事务。 但是这个隔离级别无法阻止事务B表R1所在的表中插入其他数据,从而事务A两次读取获得记录行数可能不一样,还是无法解决欢迎读取的问题

脚踏实地 said...

以前对事务隔离级别的理解一直在纠缠一个问题,到底这个事务隔离级别使用来影响当前事务本身,还是用来影响和这个事务竞争资源的其他事务。 比如数据库中有表Student(id,version, age,name, primary key(id))(这里都假设是mysql, 不同的数据库对事务隔离级别的实现可以是不一样的),其中一条记录(idj=5, version=1, age=10,name='jerry'),假设现在有事务Ta和事务Tb同时要读取和更新这条记录(id=5),并且事务Ta先更新了记录(name=‘jerry-ta’),但是还没有提交,这个时候Tb也来读取这条记录,这个时候会出现什么情况? 如果隔离级别是用来影响其他竞争事务的,那么在这里Tb就会看Ta的事务隔离级别,比如说serializable,那么Tb会等待Ta提交之后才开始,尽管Tb的隔离级别可能只是read_uncommitted。这种理解方式对于Ta是serializable时还好理解,如果Ta是read_committed的话,逻辑上都很难想的清。。。那个时候我就纠缠这个问题,弄的自己都一脑袋浆糊。
现在澄清一下,隔离级别是用来描述当前事务的,这个时候解释上面的情况就应该是这样了,还是说Ta的事务隔离级别为serializable, Tb的是read_uncommitted。因为隔离级别是影响当前事务的,所以Tb会直接读取Ta中未提交的数据(name='jerry-ta'),根本不管Ta的隔离级别是什么(重申一点,这是在mysql上测试得到的结论)。
1. 数据库已经存在记录Student(id=5, version=1, age=10,name='jerry')





[Ta 是read_repeatful_read,Tb是read_committed]

[Ta 是read_read_uncommitted,Tb是read_serializable]

脚踏实地 said...

step3时Tb得到name=‘jerry’,Step5时Tb得到name=‘jerry-ta’。 因为在read_commit的时候,Ta执行更新会尝试获得更新锁(只能读不能改,并不要求Tb释放共享锁),就算Tb也尝试获得更新锁,但是由于Ta已经获得了更新锁,Tb就需要等到Ta释放更新锁之后才能获得,这样就避免了死锁。


[Ta 是read_repeatful_read,Tb是read_committed]

脚踏实地 said...

结果,如果设置了事务隔离级别为serialize,就会出现死锁,而如果设置为read committed,sp_1和sp_2取得的值是一样的。为什么会这样,应该是事务隔离级别只能影响sqlserver的锁策略。比如sp_1先在流水号记录上加了共享锁,然后sp_2也加了共享锁,这个时候sp_1想要更新,由于是serialize,所以sp_1想要独占锁,同样的sp_2更新的话也要独占锁,sp_1等着sp_2 释放共享锁,sp_2等着sp_1释放共享锁,所以死锁了。而当事务隔离级别为read committed的时候,sp_1想要更新意向锁,sp_2也想要更新锁,sqlserver会保证只有一个事务获得这个锁,从而避免出现死锁,但是也导致sp_1和sp_2获得的流水号是一样的,这样也不对。
后来就在step#1中增加了'with (updlock)'的声明,同时采用默认的read committed的隔离级别,这样就可以了。因为sp_1读的时候就在该流水号记录上加了更新锁,这样sp_2想要读该记录的时候就会被阻塞。



SELECT @errcode = '0000'



--WAITFOR DELAY '00:00:03'

UPDATE AnteSysPara set ParaValue = CONVERT(VARCHAR(50),(CONVERT(BIGINT,ParaValue)+2)) WHERE ParaKey = 'AnteWinUID';

IF (@@ERROR<>0)


SELECT @errcode = '9998'

GOTO error_end


INSERT INTO AnteWin (WinUID,TicketSerialNo,UserID,AnteCode,IssueID,BonusLevel,Price,AnteTime,AnteType,IsBigAward,GameID,CounterNum,AcctID) VALUES(@UID,@TicketSerialNo,@UserID,@AnteCode,@IssueID,@BonusLevel,@Price,@AnteTime,@AnteType,@IsBigAward,@GameID,@CounterNum,@AcctID)

IF (@@ERROR<>0)


SELECT @errcode = '9999'

GOTO error_end



RETURN @errcode



RETURN @errcode


脚踏实地 said...



UPDATE HumanResources.Employee SET BirthDate = '1975-08-25'
WHERE EmployeeID = 25

WAITFOR DELAY '00:00:10'

SELECT * FROM HumanResources.Employee



UPDATE HumanResources.Employee SET BirthDate = '1979-08-25'
WHERE EmployeeID = 5

SELECT * FROM HumanResources.Employee


消息 1205,级别 13,状态 51,第 6 行
Transaction (Process ID 75) was deadlocked on lock | communication buffer resources with
another process and has been chosen as the deadlock victim. Rerun the transaction.