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

Understanding basic locking

In this section, you will learn basic locking mechanisms. The goal is to understand how locking works in general and how to get simple applications right.

To show how things work, a simple table can be created. For demonstration purposes, I will add one row to the table:

test=# CREATE TABLE  t_test (id int);  
CREATE TABLE 
test=# INSERT INTO t_test VALUES (1); INSERT 0 1

The first important thing is that tables can be read concurrently. Many users reading the same data at the same time won't block each other. This allows PostgreSQL to handle thousands of users without problems.

Multiple users can read the same data at the same time without blocking each other.

The question now is: what happens if reads and writes occur at the same time? Here is an example. Let us assume that the table contains one row and its id = 0:

Two transactions are opened. The first one will change a row. However, this is not a problem as the second transaction can proceed. It will return the old row as it was before the UPDATE. This behavior is called Multi-Version Concurrency Control (MVCC).

A transaction will see data only if it has been committed by the writing transaction prior to the initiation of the read transaction. One transaction cannot inspect the changes made by another active connection. A transaction can see only those changes that have already been committed.

There is also a second important aspect: many commercial or open source databases are still (as of 2017) unable to handle concurrent reads and writes. In PostgreSQL, this is absolutely not a problem. Reads and writes can coexist.

Writing transactions won't block reading transactions.

After the transaction has been committed, the table will contain 2.

What will happen if two people change data at the same time? Here is an example:

Suppose you want to count the number of hits on a website. If you run the code as outlined just now, no hit can be lost because PostgreSQL guarantees that one UPDATE is performed after the other.

PostgreSQL will only lock rows affected by  UPDATE. So, if you have 1,000 rows, you can theoretically run 1,000 concurrent changes on the same table.

It is also noteworthy that you can always run concurrent reads. Our two writes will not block reads.

主站蜘蛛池模板: 涿鹿县| 沈丘县| 渭源县| 临澧县| 海盐县| 阳朔县| 图木舒克市| 都昌县| 宁津县| 民丰县| 富川| 克拉玛依市| 西畴县| 桑日县| 石柱| 崇左市| 沈丘县| 察哈| 利川市| 黄龙县| 淮滨县| 航空| 东兰县| 抚顺市| 康保县| 清镇市| 怀仁县| 仪陇县| 桃园市| 阿克苏市| 苍梧县| 如皋市| 东阿县| 乃东县| 大关县| 青冈县| 林甸县| 兰溪市| 城步| 大方县| 策勒县|