From 0e41fd57df6760f8978045cd27fd8de94e085964 Mon Sep 17 00:00:00 2001 From: "Vadim B. Mikheev" Date: Wed, 9 Jun 1999 03:51:40 +0000 Subject: [PATCH] MVCC updates. --- src/man/lock.l | 167 +++++++++++++++++++++++++++++++++++++++++++++++---------- src/man/set.l | 23 ++++---- 2 files changed, 147 insertions(+), 43 deletions(-) diff --git a/src/man/lock.l b/src/man/lock.l index 6da514ad2a..24defb0e51 100644 --- a/src/man/lock.l +++ b/src/man/lock.l @@ -1,47 +1,156 @@ .\" This is -*-nroff-*- .\" XXX standard disclaimer belongs here.... -.\" $Header: /cvsroot/pgsql/src/man/Attic/lock.l,v 1.8 1999/06/04 04:28:54 momjian Exp $ -.TH FETCH SQL 01/23/93 PostgreSQL PostgreSQL +.\" $Header: /cvsroot/pgsql/src/man/Attic/lock.l,v 1.9 1999/06/09 03:51:40 vadim Exp $ +.TH LOCK SQL 01/23/93 PostgreSQL PostgreSQL .SH NAME -lock - exclusive lock a table +lock - Explicit lock of a table inside a transaction .SH SYNOPSIS .nf \fBlock\fR [\fBtable\fR] classname -[\fBin\fR [\fBrow\fR|\fBaccess\fR] [\fBshare\fR|\fBexclusive\fR] | -[\fBshare row exclusive\fR] \fBmode\fR] +\fBlock\fR [\fBtable\fR] classname \fBin\fR [\fBrow\fR|\fBaccess\fR] {\fBshare\fR|\fBexclusive\fR} \fBmode\fR +\fBlock\fR [\fBtable\fR] classname \fBin\fR \fBshare row exclusive\fR \fBmode\fR .fi .SH DESCRIPTION -By default, -.BR lock -exclusive locks an entire table inside a transaction. -Various options allow shared access, or row-level locking control. +Available lock modes from least restrictive to most restrictive: .PP -The classic use for this -is the case where you want to \fBselect\fP some data, then update it -inside a transaction. If you don't exclusive lock the table before the -\fBselect\fP, some other user may also read the selected data, and try -and do their own \fBupdate\fP, causing a deadlock while you both wait -for the other to release the \fBselect\fP-induced shared lock so you can -get an exclusive lock to do the \fBupdate.\fP +\fBACCESS SHARE MODE\fR + +\fBNote\fR: this lock mode is acquired automatically over tables being +\queried. \fBPostgres\fR releases automatically acquired +ACCESS SHARE locks after statement is done. + +This is the least restrictive lock mode which conflicts with ACCESS EXCLUSIVE +mode only. It's intended to protect table being queried from concurrent +\fBALTER TABLE\fR, \fBDROP TABLE\fR and +\fBVACUUM\fR statements over the same table. + +\fBROW SHARE MODE\fR + +\fBNote\fR: Automatically acquired by SELECT FOR UPDATE statement. + +Conflicts with EXCLUSIVE and ACCESS EXCLUSIVE lock modes. + +\fBROW EXCLUSIVE MODE\fR + +\fBNote\fR: Automatically acquired by UPDATE, DELETE, INSERT statements. + +Conflicts with SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE and ACCESS EXCLUSIVE +modes. Generally means that a transaction updated/inserted some tuples in a +table. + +\fBSHARE MODE\fR + +\fBNote\fR: Automatically acquired by CREATE INDEX statement. + +Conflicts with ROW EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE and ACCESS +EXCLUSIVE modes. This mode protects a table against concurrent updates. + +\fBSHARE ROW EXCLUSIVE MODE\fR + +Conflicts with ROW EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE and +ACCESS EXCLUSIVE modes. This mode is more restrictive than SHARE mode +because of only one transaction at time can hold this lock. + +\fBEXCLUSIVE MODE\fR + +Conflicts with ROW SHARE, ROW EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, +EXCLUSIVE and ACCESS EXCLUSIVE modes. This mode is yet more restrictive than +SHARE ROW EXCLUSIVE one - it blocks concurrent SELECT FOR UPDATE queries. + +\fBACCESS EXCLUSIVE MODE\fR + +\fBNote\fR: Automatically acquired by ALTER TABLE, DROP TABLE, VACUUM +statements. + +This is the most restrictive lock mode which conflicts with all other +lock modes and protects locked table from any concurrent operations. + +\fBNote\fR: This lock mode is also acquired by first form of LOCK TABLE +(i.e. without explicit lock mode option). + +.SH USAGE +.BR Postgres +always uses less restrictive lock modes ever possible. LOCK TABLE statement +provided for cases when you might need in more restrictive locking. .PP -Another example of deadlock is where one user locks one table, and -another user locks a second table. While both keep their existing -locks, the first user tries to lock the second user's table, and the -second user tries to lock the first user's table. Both users deadlock -waiting for the tables to become available. The only solution to this -is for both users to lock tables in the same order, so user's lock -aquisitions and requests to not form a deadlock. +For example, application run transaction at READ COMMITTED isolation level +and need to ensure existance data in a table for duration of transaction. To +achieve this you could use SHARE lock mode over table before querying. This +will protect data from concurrent changes and provide your further read +operations over table with data in their real current state, because of +SHARE lock mode conflicts with ROW EXCLUSIVE one, acquired by writers, and +your LOCK TABLE table IN SHARE MODE statement will wait untill concurrent +write operations (if any) commit/rollback. (Note that to read data in their +real current state running transaction at SERIALIZABLE isolation level you +have to execute LOCK TABLE statement before execution any DML statement, +when transaction defines what concurrent changes will be visible to +herself). + +If, in addition to requirements above, transaction is going to change data +in a table then SHARE ROW EXCLUSIVE lock mode should be acquired to prevent +deadlock conditions when two concurrent transactions would lock table in +SHARE mode and than would try to change data in this table, both +(implicitly) acquiring ROW EXCLUSIVE lock mode that conflicts with +concurrent SHARE lock. + +Following deadlock issue (when two transaction wait one another) +touched above, you should follow two general rules to prevent +deadlock conditions: + +\fB1. Transactions have to acquire locks on the same objects in the same order.\fR + +For example, if one application updates row R1 and than updates row R2 (in +the same transaction) then second application shouldn't update row R2 if +it's going update row R1 later (in single transaction). Instead, it should +update R1 and R2 rows in the same order as first application. + +\fB2. Transactions should acquire two conflicting lock modes only if one of +them is self-conflicting (i.e. may be held by one transaction at time only) +and should acquire most restrictive mode first.\fR + +Example for this rule is described above when told about using +SHARE ROW EXCLUSIVE mode instead of SHARE one. + +\fBNote\fR: \fBPostgres\fR does detect deadlocks and will rollback one of +waiting transactions to resolve the deadlock. + +.SH COMPATIBILITY +LOCK TABLE statement is a \fBPostgres\fR language extension. + +Except for ACCESS SHARE/EXCLUSIVE lock modes, all other \fBPostgres\fR lock +modes and LOCK TABLE statement syntax are compatible with \fBOracle\fR +ones. + .SH EXAMPLES .nf -- --- Proper locking to prevent deadlock +-- SHARE lock primary key table when going to perform +-- insert into foreign key table. -- -begin work; -lock table mytable; -select * from mytable; -update mytable set (x = 100); -commit; +BEGIN WORK; +LOCK TABLE films IN SHARE MODE; +SELECT id FROM films + WHERE name = 'Star Wars: Episode I - The Phantom Menace'; +-- +-- Do ROLLBACK if record was not returned +-- +INSERT INTO films_user_comments VALUES + (_id_, 'GREAT! I was waiting it so long!'); +COMMIT WORK; + +-- +-- SHARE ROW EXCLUSIVE lock primary key table when going to perform +-- delete operation. +-- +BEGIN WORK; +LOCK TABLE films IN SHARE ROW EXCLUSIVE MODE; +DELETE FROM films_user_comments WHERE id IN + (SELECT id FROM films WHERE rating < 5); +DELETE FROM films WHERE rating < 5; +COMMIT WORK; + .SH "SEE ALSO" begin(l), commit(l), +set(l), select(l). diff --git a/src/man/set.l b/src/man/set.l index 15c64baca7..23ecae07e7 100644 --- a/src/man/set.l +++ b/src/man/set.l @@ -1,6 +1,6 @@ .\" This is -*-nroff-*- .\" XXX standard disclaimer belongs here.... -.\" $Header: /cvsroot/pgsql/src/man/Attic/set.l,v 1.21 1999/06/04 03:44:42 momjian Exp $ +.\" $Header: /cvsroot/pgsql/src/man/Attic/set.l,v 1.22 1999/06/09 03:51:40 vadim Exp $ .TH SET SQL 05/14/97 PostgreSQL PostgreSQL .SH NAME set - set run-time parameters for session @@ -81,19 +81,14 @@ The default is unlimited. .IR TIMEZONE sets your timezone. .PP -.I TRANSACTION ISOLATION LEVEL -sets the current transaction's isolation level to -.IR SERIALIZABLE -or -.IR READ COMMITTED . -.IR SERIALIZABLE -means that the current transaction will place a lock on every row read, -so later reads in that transaction see the rows unmodified by -other transactions. -.IR READ COMMITTED -means that the current transaction reads only committed rows. -.IR READ COMMITTED -is the default. +\fITRANSACTION ISOLATION LEVEL\fR sets the current transaction's isolation +level to \fI SERIALIZABLE\fR or \fIREAD COMMITTED\fR. \fISERIALIZABLE\fR +means that the current transaction queries will read only rows committed +before first DML statement (SELECT/INSERT/DELETE/UPDATE/FETCH/COPY_TO) was +executed in this transaction. \fIREAD COMMITTED\fR means that the current +transaction queries will read only rows committed before a query began. +\fIREAD COMMITTED\fR is the default. \fBNote\fR: SQL92 standard requires +\fISERIALIZABLE\fR to be the default isolation level. .PP .IR CLIENT_ENCODING|NAMES sets the character set encoding of the client. Only available if multi-byte -- 2.11.0