Shulou(Shulou.com)06/01 Report--

As mentioned earlier, PostgreSQL uses a method called Snapshot isolation Snapshot Isolation (SI) for concurrency control, and here is a brief introduction to basic concepts such as snapshots and isolation levels associated with PostgreSQL.

Note:

1. For simplicity, this section will not discuss transaction rollback for the time being.

two。 If not specified, the default transaction isolation level is READ COMMITTED

I. transaction snapshot

Transaction Snapshot (Snapshot) stores information about whether all transactions are active (in progress or not yet started) at a certain point in time.

The snapshot information of the current transaction can be obtained through the txid_current_snapshot () function.

Testdb=# select txid_current_snapshot (); txid_current_snapshot-2315 row 2315

The format of the snapshot is xmin: xmax: xip_list

Where:

Xmin: the earliest still active transaction txid. Transactions earlier than this txid are either committed and visible, or rolled back and discarded.

Xmax: ShmemVariableCache- > latestCompletedXid + 1, that is, txid + 1 of the last completed transaction (COMMITTED/ABORTED). At the time of taking a snapshot, all transactions greater than or equal to this txid have not been started and are therefore not visible.

Xip_list: the transaction txid that was active when the snapshot was taken. The list contains the active txid between xmin and xmax.

To sum up, simply put, for a given transaction txid:

Txid ∈, which is a past transaction, is visible to this snapshot

Txid ∈ [xmin,xmax), txid is not visible if it is still in IN_PROGRESS state, COMMITED status is visible, and ABORTED transaction is not visible either

Txid ∈ [xmax, ∞), which is a future transaction and is not visible to this snapshot

Start three clients (psql) and execute the begin startup transaction:

Session 1

11:36:04 (xdb@ [local]: 5432) testdb=# begin;BEGIN11:36:09 (xdb@ [local]: 5432) testdb=#* select 1;? column?-1 (1 row) 11:36:09 (xdb@ [local]: 5432) testdb=#* select txid_current () Txid_current-2327 (1 row) 11:36:09 (xdb@ [local]: 5432) testdb=#* select txid_current_snapshot (); txid_current_snapshot-2327 testdb=#* select txid_current_snapshot: (1 row)

Session 2

11:36:02 (xdb@ [local]: 5432) testdb=# begin;BEGIN11:36:17 (xdb@ [local]: 5432) testdb=#* select 1;? column?-1 (1 row) 11:36:17 (xdb@ [local]: 5432) testdb=#* select txid_current () Txid_current-2328 (1 row) 11:36:17 (xdb@ [local]: 5432) testdb=#* select txid_current_snapshot (); txid_current_snapshot-2327 testdb=#* select txid_current_snapshot: (1 row)

Session 3

11:35:59 (xdb@ [local]: 5432) testdb=# begin;BEGIN11:36:18 (xdb@ [local]: 5432) testdb=#* select 1;? column?-1 (1 row) 11:36:18 (xdb@ [local]: 5432) testdb=#* select txid_current () Txid_current-2329 (1 row) 11:36:18 (xdb@ [local]: 5432) testdb=#* select txid_current_snapshot (); txid_current_snapshot-2327 testdb=#* select txid_current_snapshot: (1 row)

The txid of session on 1-2-3 is 2327, 2328, and the snapshots are all 2327, that is, transactions less than 2327 are visible, and those greater than or equal to 2327 are not visible.

Session 2 creates data tables, inserts data, commits, restarts transactions, and inserts data.

11:36:17 (xdb@ [local]: 5432) testdb=#* 11:37:24 (xdb@ [local]: 5432) testdb=#* create table t_session2 (id int); CREATE TABLE11:37:28 (xdb@ [local]: 5432) testdb=#* insert into t_session2 values (1); INSERT 0 111 testdb=#* insert into t_session2 values 37 28 (xdb@ [local]: 5432) testdb=#* commit;COMMIT11:37:28 (xdb@ [local]: 5432) testdb=# begin BEGIN11:38:25 (xdb@ [local]: 5432) testdb=#* insert into t_session2 values (2); INSERT 0 111 INSERT 38 31 (xdb@ [local]: 5432) testdb=#*

View the changes in session snapshot information on 1-2-3.

Session 1

11:38:03 (xdb@ [local]: 5432) testdb=#* select txid_current_snapshot (); txid_current_snapshot-2327 testdb=#* select txid_current_snapshot (1 row)

The snapshot information of session 1 is changed to 2327, 2329, that is, transactions less than 2327 are visible, and transactions greater than or equal to 2329 are not visible.

Session 2

11:39:02 (xdb@ [local]: 5432) testdb=#* select txid_current (); txid_current-2330 (1 row) 11:39:14 (xdb@ [local]: 5432) testdb=#* select txid_current_snapshot (); txid_current_snapshot-2327 testdb=#* select txid_current 2329 (1 row)

The snapshot information of session 2 is changed to 2327, which means that transactions less than 2327 are visible, transactions greater than or equal to 2329 are not visible, and transactions of 2327 are not visible.

Session 3

11:36:18 (xdb@ [local]: 5432) testdb=#* select txid_current_snapshot (); txid_current_snapshot-2327 txid_current_snapshot 2329 row 2327 (1 row) 11:39:57 (xdb@ [local]: 5432) testdb=#*

The snapshot information of session 3 is consistent with that of session 2.

Session 1 creates a datasheet and inserts data

11:40:36 (xdb@ [local]: 5432) testdb=#* create table t_session1 (id int); CREATE TABLE11:45:10 (xdb@ [local]: 5432) testdb=#* insert into t_session1 values (1); INSERT 0 111 CREATE TABLE11:45:10 45 testdb=#* 19 (xdb@ [local]: 5432) testdb=#*

Session 2 query data Table t_session1

11:39:17 (xdb@ [local]: 5432) testdb=#* select * from tweets session 1 error: relation "t_session1" does not existLINE 1: select * from tweets session 1 TestDB session 1: select * from tours session 1 TestDB session 58 (xdb@ [local]: 5432) TestDB session! Select txid_current_snapshot ();-- > if something goes wrong, roll back the transaction: ERROR: current transaction is aborted, commands ignored until end of transaction block11:50:28 (xdb@ [local]: 5432) test database transactions! Commit;-- > although it is commit, when prompted, it is actually rollback.ROLLBACK

Report an error, indicating that the relationship does not exist.

Note: in the event of an error during a transaction, Oracle can still commit the previously successful operation, but PG requires the transaction to be rolled back

In the above case, the transaction snapshot changes when a new SQL is executed in the same transaction, because of the default isolation level READ COMMITTED mechanism. If the isolation level is REPEATABLE READ, the transaction snapshot is taken only once and the status of the transaction when the snapshot is taken is recorded.

II. Isolation level

In PostgreSQL, there are four isolation levels, namely READ UNCOMMITTED/READ COMMITTED/REPEATABLE READ/SERIALIZABLE.

There is no difference between READ UNCOMMITTED and READ COMMITTED; the SERIALIZABLE level is less used and is not described here.

READ COMMITTED

Continue to use section 1 of the session 1 Universe 2.

Session 2 restart the transaction

12:06:17 (xdb@ [local]: 5432) testdb=#* select txid_current (); txid_current-2331 (1 row) 12:06:23 (xdb@ [local]: 5432) testdb=#* select txid_current_snapshot (); txid_current_snapshot-2327 testdb=#* select txid_current (1 row)

Session 1 commit transaction

11:40:36 (xdb@ [local]: 5432) testdb=#* create table t_session1 (id int); CREATE TABLE11:45:10 (xdb@ [local]: 5432) testdb=#* insert into t_session1 values (1); INSERT 0 111 CREATE TABLE11:45:10 45 testdb=#* commit;COMMIT12:07:21 19 (xdb@ [local]: 5432) testdb=#* commit;COMMIT12:07:21 (xdb@ [local]: 5432) testdb=#

At this time, session 2 can read the t_session1 data table in session 1.

12:08:21 (xdb@ [local]: 5432) testdb=#* select * from tours session 1; id-1 (1 row)

REPEATABLE READ

Session 1, isolation level is READ COMMITTED

12:12:14 (xdb@ [local]: 5432) testdb=# START TRANSACTION ISOLATION LEVEL READ COMMITTED; START TRANSACTION

Session 2, isolation level is REPEATABLE READ

12:12:41 (xdb@ [local]: 5432) testdb=# START TRANSACTION ISOLATION LEVEL REPEATABLE READ; START TRANSACTION12:12:43 (xdb@ [local]: 5432) testdb=#* select * from tession1; id-1 (1 row)

Still using the above scenario, session 1 inserts the data and commits the transaction, while the newly inserted data is still not visible to session 2.

Session 1

12:12:18 (xdb@ [local]: 5432) testdb=#* insert into t_session1 values (2); INSERT 0 112 testdb=#* insert into t_session1 values 13 INSERT 22 (xdb@ [local]: 5432) testdb=#* commit;COMMIT12:13:25 (xdb@ [local]: 5432) testdb=#

Session 2

12:13:38 (xdb@ [local]: 5432) testdb=#* select * from tweets session 1; id-1-- > newly inserted data 2 is not visible (1 row) 12:13:58 (xdb@ [local]: 5432) testdb=#* select txid_current_snapshot (); txid_current_snapshot-2329 232332 row

The transaction snapshot of session 2 is taken only once.

The logic of taking a snapshot of a transaction and how to determine whether the heap tuple is visible will be parsed later in the source code interpretation.

III. Reference materials

Concurrency Control

IV. Extra

Typos, PG also asks to roll back the transaction?

12:06:25 (xdb@ [local]: 5432) testdb=#* selet * from tweets session 1: error: syntax error at or near "selet" LINE 1: selet * from tweets session 1; ^ 12:07:56 (xdb@ [local]: 5432) testdbsessions! Select * from tweets session 1 session 1 / error: current transaction is aborted, commands ignored until end of transaction block12:08:02 (xdb@ [local]: 5432) testdatabase sessions! Commit;ROLLBACK

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.