lock -exclusive lock a table

lock -Explicit lockofa table inside a transaction

.nf

\fBlock\fR [\fBtable\fR] classname

[\fBin\fR [\fBrow\fR|\fBaccess\fR][\fBshare\fR|\fBexclusive\fR] |

\fBlock\fR [\fBtable\fR] classname\fBin\fR [\fBrow\fR|\fBaccess\fR]{\fBshare\fR|\fBexclusive\fR}\fBmode\fR

.fi

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

\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

\fBALTERTABLE\fR,\fBDROPTABLE\fR and

\fBVACUUM\fR statements over the same table.

\fBNote\fR: Automatically acquired by SELECT FOR UPDATE statement.

Conflicts with EXCLUSIVE and ACCESS EXCLUSIVE lock modes.

\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.

\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.

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.

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.

\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).

.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:

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.

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.

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.

.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;

begin(l),

commit(l),

set(l),

select(l).

set - set run-time parameters for session

.IR TIMEZONE

sets your timezone.

.PP

.ITRANSACTIONISOLATIONLEVEL

sets the current transaction's isolation level to

.IR SERIALIZABLE

or

.IR READCOMMITTED .

.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 READCOMMITTED

means that the current transaction reads only committed rows.

.IR READCOMMITTED

is the default.

\fITRANSACTIONISOLATIONLEVEL\fR sets the current transaction's isolation

level to\fISERIALIZABLE\fR or\fIREADCOMMITTED\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.\fIREADCOMMITTED\fR means that the current

transaction queries will read only rows committed before a query began.

\fIREADCOMMITTED\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