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

  • Mastering PostgreSQL 9.6
  • Hans Jurgen Schonig
  • 289字
  • 2021-07-09 19:57:13

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.
主站蜘蛛池模板: 高平市| 林芝县| 大庆市| 昌邑市| 麟游县| 松阳县| 分宜县| 大化| 漠河县| 东平县| 旬邑县| 平顺县| 新竹市| 乌拉特后旗| 九龙坡区| 陇川县| 商水县| 南木林县| 杂多县| 榆社县| 常德市| 扶沟县| 伊吾县| 红桥区| 田东县| 鄂托克旗| 依安县| 永平县| 拜泉县| 赤城县| 航空| 天等县| 名山县| 湘西| 白玉县| 普陀区| 灵寿县| 林西县| 新巴尔虎右旗| 左贡县| 济南市|