lock, transaction mode

transaction mode

The isolation level of a transaction

determines what data the transaction can see when other transactions are running concurrently:

READ UNCOMMITTED

In PostgreSQL READ UNCOMMITTED is treated as READ COMMITTED.

READ COMMITTED

A statement can only see rows committed before it began. This is the default.

REPEATABLE READ

All statements of the current transaction can only see rows committed before the first query or data-modification statement was executed in this transaction.

SERIALIZABLE

any concurrent execution of a set of Serializable transactions is guaranteed to produce the same effect as running them one at a time in some order.

All statements of the current transaction can only see rows committed before the first query or data-modification statement was executed in this transaction. If a pattern of reads and writes among concurrent serializable transactions would create a situation which could not have occurred for any serial (one-at-a-time) execution of those transactions, one of them will be rolled back with a serialization_failure error.

The transaction access mode

determines whether the transaction is read/write or read-only

The DEFERRABLE transaction

property has no effect unless the transaction is also SERIALIZABLE and READ ONLY.

When all three of these properties are selected for a transaction, the transaction may block when first acquiring its snapshot, after which it is able to run without the normal overhead of a SERIALIZABLE transaction and without any risk of contributing to or being canceled by a serialization failure.

---

lock

LOCK TABLE : postgresql/oracle dialect임. 표준은 SET TRANSACTION

완전히 같은 의미를 두 가지 다로 표현할 수는 없음

postgresql은 mvcc를 지원한다.

table lock을 얻는 방법

• BEGIN
• LOCK TABLE IN SHARE MODE
• COMMIT

---

row lock을 얻는 방법

• BEGIN
• SELECT FOR UPDATE
• COMMIT

FOR lock_strength [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ]

where lock_strength can be one of

• UPDATE
• NO KEY UPDATE
• SHARE
• KEY SHARE

---

알게 된 사실

• SELECT FOR UPDATE SKIP LOCKED를 이용하면 queue를 postgresql로 구현할 수 있네...
• note that rows skipped over by OFFSET will get locked

• postgresql 기본 isolation level은 read commited이고 query가 시작된 후에 commit된 데이터는 볼 수 없다.

  (트랜잭션이 시작된 후에 commit된 데이터는 볼 수 있다!!)

• locking clause는 aggregation과 같이 쓸 수 없다.
• serializable isolation mode와 SQLSTATE 40001에서의 재시도 조합이 explicit lock과 locking clause 조합보다 특정 환경에서는 더 성능이 좋을 수 있다고 한다.
• CREATE INDEX CONCURRENTLY는 update, delete, insert, select를 block하지 않는다.
• 일부 ALTER TABLE은 SHARE UPDATE EXCLUSIVE이므로 update/delete/insert/select를 block하지 않는다.
• 테이블에 대한 select 를 block하려면 access exclusive lock(테이블 락)을 걸어야 한다.

---

참고

https://www.postgresql.org/docs/10/sql-lock.htm

https://www.postgresql.org/docs/10/sql-set-transaction.html

https://www.postgresql.org/docs/10/sql-select.html#SQL-FOR-UPDATE-SHARE