官术网_书友最值得收藏!

Understanding transaction isolation levels

Up to now, you have seen how to handle locking as well as some basic concurrency. In this section, you will learn about transaction isolation. To me, this is one of the most neglected topics in modern software development. Only a small fraction of software developers are actually aware of this issue, which in turn leads to disgusting and mind-boggling bugs.

Here is an example of what can happen:

Most users would actually expect the left transaction to always return 300 regardless of the second transaction. However, this is not true. By default, PostgreSQL runs in READ COMMITTED transaction isolation mode. This means that every statement inside a transaction will get a new snapshot of the data, which will be constant throughout the query.

An SQL statement will operate on the same snapshot and will ignore changes by concurrent transactions while it is running.

If you want to avoid that, you can use TRANSACTION ISOLATION LEVEL REPEATABLE READ. In this transaction isolation level, a transaction will use the same snapshot through the entire transactions. Here is what will happen:

As just outlined, the first transaction will freeze its snapshot of the data and provide us with constant results throughout the entire transaction. This feature is especially important if you want to run reports. The first and the last page of a report should always be consistent and operate on the same data. Therefore, repeatable read is key to consistent reports.

Note that isolation-related errors won't always pop up instantly. It can happen that trouble is noticed years after an application has been moved to production.

Repeatable read is not more expensive than read committed. There is no need to worry about performance penalties.
主站蜘蛛池模板: 南昌县| 于都县| 伊宁市| 德兴市| 涿鹿县| 和龙市| 西乌| 潼南县| 宁津县| 侯马市| 康乐县| 乐陵市| 邢台市| 远安县| 米泉市| 磐安县| 改则县| 城口县| 甘谷县| 明水县| 鄄城县| 桐柏县| 小金县| 资讯 | 威宁| 天柱县| 读书| 太仆寺旗| 平舆县| 江西省| 长寿区| 屯门区| 新化县| 沙坪坝区| 清丰县| 新丰县| 大冶市| 苍山县| 昌图县| 河曲县| 哈密市|