Oracle Lock table row-level lock table-level lock row-level lock

Oracle Lock table row-level lock table-level lock

----row is locked exclusively

----Other users cannot modify this line until a row's lock is released----use the commit or the rollback command to release the lock

----Oracle by using INSERT, UPDATE, and SELECT ... The for UPDATE statement automatically acquires row-level locks

SELECT ... FOR UPDATE clause-place an exclusive lock on one or more rows of a table-to prevent other users from updating the row

-Can perform actions other than updates

―select * from goods where gid=1001―for update of gname;

-Other users can update gname only if the user commits the transaction

What's new in the for UPDATE wait clause ―oracle9i--prevent waiting indefinitely for locked rows--The wait interval must be specified as a numeric literal

-The wait interval cannot be an expression, an assignment variable, or a PL/SQL variable

―select * from goods where gid=1001 for update of gname wait 3― waits for the user to release the update lock for 3 seconds, otherwise time out. Table-level lock

― Protect the data of the table

-Ensure data integrity when multiple users are accessing data at the same time-can be set to three modes: shared, shared update, and exclusive

Syntax: Lock table <table_name>in <mode>; Shared Lock-Lock table

― Allow only other users to perform query operations-cannot insert, update, and delete

-Multiple users can place this lock in the same table at the same time ―lock table table_name―in share mode [nowait];

―rollback and Commit commands release the lock ―nowait keyword to tell other users not to wait for a shared update lock

― Lock the row to be updated

― Allow other users to query, insert, update unlocked rows at the same time

-Use the "for Update" clause in the SELECT statement to force the use of a shared update lock--allows multiple users to lock the table at the same time

Two ways to lock

Lock table Tab_name in share update mode; Select Column1,column2 from goods where goods where gid=1001

For update of Column1,column2 exclusive locks

-Exclusive lock is the most restrictive table lock compared to the other two locks-allows only other users to query the data

― Do not allow insert, delete, and update operations

― Allow only one user to place an exclusive lock on the table at the same time-share lock is the opposite

Lock table Tab_name in exclusive mode; Lock table < table name >[< table name] ... in share mode [nowait]

Lock table < table name >[< table name;] ... in exclusive mode [nowait] lock table < table name >[< table name;] ... In Share update mode[nowait]

-----------------------------------------------------------------------------------------------

LOCK Name

lock-explicitly locks a table in a transaction lock [table] Name

LOCK [TABLE] name in [ROW |   ACCESS] {SHARE | EXCLUSIVE} MODE

LOCK [TABLE] name in SHARE ROW EXCLUSIVE MODE input

Name

The existing table to be locked.

ACCESS SHARE MODE

Note: This lock mode automatically takes effect on the table being queried.

This is the least restrictive lock mode, only conflicts with ACCESS EXCLUSIVE mode. It is used to protect the queried table from being modified by parallel ALTER table, DROP table, and VACUUM for the same table operation.

ROW SHARE MODE

Note: any SELECT ...   Automatically takes effect when the for UPDATE statement executes. Because it is a shared lock, it may later be updated to a ROW EXCLUSIVE lock.

Conflicts with EXCLUSIVE and ACCESS EXCLUSIVE lock mode.

ROW EXCLUSIVE MODE

Note: Any UPDATE, delete, and INSERT statements take effect automatically when they are executed.

Conflicts with SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE mode.

SHARE MODE

Note: Any CREATE INDEX statement is automatically attached when executed. Sharing locks the entire table.

Conflicts with row Exclusive,share row exclusive,exclusive and ACCESS EXCLUSIVE mode. This mode prevents a table from being updated in parallel.

SHARE ROW EXCLUSIVE MODE

Note: This mode is similar to EXCLUSIVE mode, but allows the SHARE ROW lock for other transactions.

-----------------------------------------------------------------------------------------------

Conflicts with row Exclusive,share,share row exclusive,exclusive and ACCESS EXCLUSIVE mode.

EXCLUSIVE MODE

Note: This mode is also more binding than SHARE ROW EXCLUSIVE.   It blocks all parallel ROW share/select ... For UPDATE query.

Conflicts with row Exclusive,share,share row exclusive,exclusive and ACCESS EXCLUSIVE mode.

ACCESS EXCLUSIVE MODE

Note: By the statement ALTER TABLE, the DROP table,vacuum executes automatically. This is the strictest constraint lock, which conflicts with all other lock modes and protects a locked table from any other parallel operation changes.

Note: An unqualified lock TABLE also requires this lock mode (for example, a command that does not have an explicit lock mode option).

Output

Lock TABLE returns after a successful lock.

ERROR name:table does not exist. If name does not exist, this information is returned.

Describe

LOCK table controls the concurrent access to a table for the lifetime of a transaction.   Postgres Use the minimum constrained lock mode whenever possible. Lock TABLE provides a more binding lock when you need it.

RDBMS locking uses the following terminology:

EXCLUSIVE

Lock to prevent other (transaction) locks from being generated.

SHARE

Allow other (transactional) shared locks. Avoid EXCLUSIVE locks.

ACCESS

-----------------------------------------------------------------------------------------------

Locks the table structure.

ROW

Locks a separate row.

Note: If EXCLUSIVE or SHARE is not declared, it is assumed to be EXCLUSIVE. Locks exist within the transaction cycle.

For example, an app runs a transaction at the READ commited isolation level, and it needs to ensure that the data in the table exists during the operation of the transaction. To implement this you can lock the table using SHARE lock mode before querying.   This will protect the data from being modified in parallel and provide the actual state of data for any further read operations on the table, because the SHARE lock mode conflicts with the ROW EXCLUSIVE mode required for any write operation, and your lock TABLE name The in SHARE MODE statement is not executed until all parallel writes are committed or rolled back.

Note: When you run a transaction at the SERIALIZABLE isolation level, and you need to read the data in the real state, you must run a LOCK TAB before executing any DML statement (at which point the transaction defines what parallel modification is visible to it itself) LE statement.

In addition to the above requirements, if a transaction is prepared to modify data in a table, then the SHARE ROW EXCLUSIVE lock mode should be used to avoid a deadlock condition (when two parallel transactions attempt to lock the table in SHARE mode and then attempt to change the data in the table, A ROW EXCLUSIVE lock mode is required for two transactions (implied), and this mode conflicts with a parallel SHARE lock.

To continue with the deadlock above (two transactions waiting for each other), you should follow the two general rules to avoid the deadlock condition:

Transactions should request locks on the same object in the same order.

For example, if an app updates the row R1 and then updates the row R2 (in the same transaction), then the second app should not update the row R2 (in the same transaction) if you want to update the row R1 later. Instead, it should update the rows R1 and R2 in the same order as the first app.

The premise of a transaction request two conflicting lock modes: one of the lock patterns is self-conflicting (that is, one transaction can only be held at a time). If multiple lock modes are involved, then the transaction should always be the first to request the most restrictive lock mode.

An example of this rule is given in the previous discussion about replacing SHARE mode with the SHARE ROW EXCLUSIVE mode. -----------------------------------------------------------------------------------------------

Note: Postgres does detect deadlocks and will roll back at least one waiting transaction to resolve deadlocks.

Attention

LOCK is a Postgres language extension.

In addition to access share/exclusive lock mode, all other Postgres lock modes and lock TABLE statements are compatible with those in Oracle.

LOCK is used only inside a transaction.

Usage

Demonstrates the use of a SHARE lock on a table with a primary key when inserting on a foreign key table:

BEGIN work;

LOCK TABLE Films in SHARE MODE; SELECT ID from films

WHERE name = ' Star wars:episode i-the Phantom menace ';

--If the record does not return the volume back

INSERT into Films_user_comments VALUES

(_id_, ' great!      I was waiting for it to so long! '); COMMIT work;

SHARE a ROW EXCLUSIVE lock on a table with a primary key when performing a 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; Compatibility SQL92

There is no lock TABLE in SQL92, you can use SET TRANSACTION to declare the level of the current transaction. We also support this, see SET TRANSACTION for more information.

Oracle Lock table row-level lock table-level lock row-level lock