What is the usage of for update in oracle

In Oracle, "for update" is a row-level lock, which is used to impose a row-level lock on a row. The locked user can query and update the data row, but other users cannot query and update it. The syntax for locked rows is "SELECT * FROM table name WHERE column name FOR UPDATE;".

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

What is the usage of for update in oracle

1. For update definition

for update is a row-level lock, also called exclusive Lock, once a user applies a row-level lock to a row, the user can query and update the locked data rows, while other users can only query but not update the locked data rows. If other users want to update data rows in the table, they must also apply row-level locks to the table. Even if multiple users use shared updates on a table, two transactions are not allowed to update a table at the same time. When the table is actually updated, the table is locked exclusively until the transaction is committed or restored. Row locks are always exclusive locks.

The shared update lock will be released only when one of the following conditions occurs:

1. Execute the commit (COMMIT) statement

2. Exit the database (LOG OFF)

3. The program stops running

2. Concept and usage

Normally, the select statement will not lock the data and hinder the impact Other DML and DDL operations. At the same time, with the support of the multi-version consistent reading mechanism, the select statement will not be blocked by other types of statements.

The select ... for update statement is a manual locking statement we often use. When you execute select ... for update in the database, you will find that the table or certain row data in the database will be locked. In mysql, if the query condition contains a primary key, the row data will be locked. If not, the table will be locked.

Since InnoDB defaults to Row-Level Lock, MySQL will execute Row lock (only locking the selected data) only if the primary key is "clearly" specified. Otherwise, MySQL will execute Table Lock ( Lock the entire data form).

For example: Suppose there is a table user with two columns, id and name, and id is the primary key.

Example 1: (Explicitly specify the primary key, and the data does exist, row lock)

SELECT * FROM user WHERE id=3 FOR UPDATE;
SELECT * FROM user WHERE id=3 and name='Tom' FOR UPDATE;

Example 2: (Explicitly specify the primary key, but the data does not exist, no lock)

SELECT * FROM user WHERE id=0 FOR UPDATE;

Example 3: (Unclear primary key, table lock)

SELECT * FROM user WHERE id<>3 FOR UPDATE;
SELECT * FROM user WHERE id LIKE &#39;%3%&#39; FOR UPDATE;

Example 4: (No primary key, table lock)

SELECT * FROM user WHERE name='Tom' FOR UPDATE;

Note:

1. FOR UPDATE only Applicable to InnoDB and must be in the transaction processing module (BEGIN/COMMIT) to take effect.

2. To test the locking situation, you can use MySQL's Command Mode to open two windows for testing.

3. Myisam only supports table-level locks, while InnerDB supports row-level locks. Data with added (row-level locks/table-level locks) locks cannot be locked by other transactions or modified by other transactions. When it is a table-level lock, the table will be locked regardless of whether the record is queried.

3. When do you need to use for update?

With the for update statement, we can manually implement data locking and protection operations at the application level. When you need exclusive business-level data, you can consider using for update.

In scenarios, such as train ticket booking, the ticket is displayed on the screen, but when the ticket is actually issued, it is necessary to re-confirm that the data has not been modified by other clients. Therefore, during this confirmation process, you can use for update.

4. for update pessimistic lock

Pessimistic lock: always assume the worst case scenario, and think that others will modify it every time you get the data, so every time It will be locked every time the data is retrieved, so that if others want to retrieve the data, they will be blocked until it is unlocked. Many such lock mechanisms are used in traditional relational databases, such as row locks, table locks, read locks, write locks, etc., which are all locked before operations. Just like for update, the implementation of the synchronized keyword in Java is also a pessimistic lock.

Optimistic lock: As the name suggests, it is very optimistic. Every time you go to get the data, you think that others will not modify it, so you will not lock it. However, when updating, you will judge whether others have gone there during this period. To update this data, you can use mechanisms such as version numbers. Optimistic locking is suitable for multi-read application types, which can improve throughput. The write_condition mechanism provided by the database is actually an optimistic lock.

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of What is the usage of for update in oracle. For more information, please follow other related articles on the PHP Chinese website!