[ Pobierz całość w formacie PDF ]
.LOCKPostgreSQL User's GuidePrevNextLOCKNameLOCK — Explicit lock of a table inside a transactionLOCK [ TABLE ] tableInputstable The name of an existing table to lock.Outputs
DELETE 0Message returned on a successful lock.LOCK is implemented as aDELETE FROM tablewhich is guaranteed to not delete any rows.
ERROR table: Table does not exist.Message returned if tabledoes not exist.
Description LOCK locks in exclusive mode a table insidea transaction.The classic use for this isthe case where you want to select some data, thenupdate it inside a transaction.If you don't explicit lock a table using LOCK statement, it will beimplicit locked only at the firstUPDATE, INSERT,or DELETE operation.If you don't exclusive lock the table before the select, someother user may also read the selected data, and try and dotheir own update, causing a deadlock while you both waitfor the other to release the select-induced shared lock soyou can get an exclusive lock to do the update.
Another example of deadlock is where one user locks onetable, and another user locks a second table.While bothkeep their existing locks, the first user tries to lockthe second user's table, and the second user tries to lockthe first user's table.Both users deadlock waiting forthe tables to become available.The only solution to thisis for both users to lock tables in the same order, souser's lock acquisitions and requests to not form a deadlock.Note: Postgres does detect deadlocks and willrollback transactions to resolve the deadlock.Usually, at least oneof the deadlocked transactions will complete successfully.
NotesLOCK is a Postgreslanguage extension.LOCK works only inside transactions.Bug: If the locked table is dropped then it will be automaticallyunlocked even if a transaction is still in progress.
Usage--Explicit locking to prevent deadlock:--BEGIN WORK;LOCK films;SELECT * FROM films;UPDATE films SET len = INTERVAL '100 minute'WHERE len = INTERVAL '117 minute';COMMIT WORK;CompatibilitySQL92 There is no LOCK TABLE in SQL92,which instead uses SET TRANSACTION to specifyconcurrency level on transactions.
PrevHomeNextLOADUpMOVE
[ Pobierz całość w formacie PDF ]