SQL for Update

Welcome, everybody, spit the groove.

Oracle row-Level shared lock

This is usually done through the SELECT ... from UPDATE statement, which is also the primary method we use to manually lock some records. For example, when we query some records, we do not want other users to update the records of the query, you can issue such a statement. When the data is used, the direct issue of the rollback command unlocks the lock (if we have the query and modified, then the commit can release the lock). When RS lock is added to the table, no other transactions are allowed to add exclusive locks to the same table, but other transactions are allowed to lock other rows of data in the same tables through the DML statement or the lock command.

Select for update

Example:

Select *from goods where Goods_code = ' 9990300100000030001 ' for update;

When a for update is used in a SELECT statement, if the current select finds one or more of the data in its own result set that is being modified (if there are other statements in advance for update, etc.), then executing the statement waits:

Execute at this time:

Update/delete and other statements appear waiting. A new for update lock or UPDATE, delete is only possible if the corresponding lock-in data is submitted Commi (or rollback) T.

Select for Update nowait

The for update nowait and for update will lock the current result set that is queried. The difference is that for update nowait discovers that the result set to be locked is being modified by another operation (already locking), the direct feedback resource is consumed and does not wait for execution like for update.

These two operations wait and pop windows are not related to that form of locking SQL, as long as the result set to be processed is locked with a direct feedback pop-up window. (It should be nonsense)

Select for update wait

The difference from the for update nowait is that the for update wait can be configured for wait time, in seconds. When this time has elapsed, the lock resource has not been released, and as for update nowait, the direct popup feedback resource consumption. Otherwise, execute this statement.

Skip Gathing to lock other rows that meet the criteria

Steps:

Window 1 (each window represents a session) executes

Select *from goods where Goods_code = ' 9990300100000030001 ' for update;

In Windows 2, execute:

Because a row in the SQL collection of window 2 is locked by a collection of Windows 1, window two waits for the lock to be released.

Window 2 modifies the statement as follows:

Select *from goods for update skip locked;

association table Lock 1. The associated tables are lock-locked

Select *   from  Innerjoin  goods g    on=where  = ' qwe002n3 '    for update;

It can be concluded that the products of goods_code= ' qwe002n3 ' in the two tables have been locked, so that there is no wait for the statement to be locked when the two whole locks are added.

Lock success when additional data is locked in a newly opened window:

2. Lock a table in the associated table

Select *   from  Innerjoin  goods g    on=where  = ' qwe002n3 '    for Update  of Sg.goods_code;

Oracle Shared Lock

Syntax: Lock table Stat_dic_goods in share mode;

After executing the lock statement and then executing the UPDATE statement, there is a wait, because the lock statement already locks the table, allowing only read to disallow modification.

Add the S lock by using the lock table in share mode command. In this lockdown mode, no user is allowed to update the table. But allow other users to issue a select ... from the FOR UPDATE command to add RS locks to the table.

Oracle only table-level shared locks have no row-level shared locks, row-level exclusive locks, and multiple users can share locks on the same table at the same time. It can be seen through the following statement.

Oracle Exclusive Lock

Example statement: Lock table Stat_dic_goods in exclusive mode;

The same table cannot perform both shared and exclusive locks.

The x lock is added via the lock table in exclusive mode command. In this lockdown mode, other users cannot perform any DML and DDL operations on the table, which can only be queried.

Oracle Row-Level exclusive lock

When we do DML, we automatically add the RX lock on the table being updated, or you can explicitly add the RX lock on the table by executing the lock command. In this lock mode, other transactions are allowed to modify other rows of data in the same table through DML statements, or the lock command adds RX locks to the same tables, but does not allow other transactions to add exclusive locks (x locks) to the same table.

Reference here: http://www.cnblogs.com/hsz1124/p/7409981.html

Find the relevant information to understand is not understand

Oracle shared Row-level exclusive lock

Add the SRX lock via the lock table in share row exclusive mode command. This locking mode is higher than the level of row-level exclusive and shared locks, and you cannot perform DML operations on the same table or add shared locks.

Find the relevant information to understand is not understand

TM Lock, table-level lock;

TX (transaction) lock;

Lock only locks the table (tm Lock), and select for Update,insert,delete,update both the table lock (TM Lock) and the line Lock (TX lock). The Select for update gives the table an upstream shared lock, and insert,delete,update gives the table an upstream exclusive lock.

Isolation level of the database

Look back at the isolation level of the database:

The isolation level is the level of concurrency control for transactions. Ansi/iso SQL divides it into serialization (SERIALIZABLE), repeatable read (repeatable read), read-committed (read commited), read-uncommitted (read uncommited) four levels. In order to achieve the isolation level, the database is typically locked (lock). Generally in the programming time only need to set the isolation level, as to the specific use of what the lock is set by the database.

Oracle:read commited

Mysql:repeatable Read

Oracle By default, read data is not locked, but the rollback segment prevents dirty reads and guarantees repeatable reads.

Oracle has a deadlock check feature that periodically checks the system for deadlocks and, if there is a deadlock, revokes the transaction with the least number of update operations.

InnoDB row locks are implemented by locking the index entries on the index, which MySQL with the Oracle , the latter is achieved by locking the corresponding data rows in the data block. InnoDB This type of row lock implementation is characterized by the factthat InnoDB uses row-level locksonly if the data is retrieved by index criteria, otherwise,InnoDB The table lock will be used! If the corresponding SQL statement does not go through the index, the entire table will be locked out.

Mysql

MySQL supports row and table locks only under the InnoDB engine. The default engine for MySQL is InnoDB.

The MySQL default mode of operation is autocommit auto-commit mode (value 1). This means that unless a transaction is explicitly started, each query is executed automatically as a separate transaction. We can change whether the autocommit mode is auto-committed by setting the value of autocommit.

Can be set manually: Set autocommit = 0; then auto commit is turned off.

Example:

Pessimistic Lock:

Begin

SELECT * from goods where id = 1 for update;

Update goods Set stock = stock-1 where id = 1;

Commit

Optimistic Lock: (modified)

#不加锁获取 Id=1 's Product object

SELECT * from goods where id = 1

Begin

#更新 the stock value, it is important to note where the "stock = Cur_stock" is available, and only the amount of inventory obtained in the program is equal to the inventory in the database to perform the update

Update goods Set stock = stock-1 WHERE id = 1 and stock = Cur_stock;

Commit

Most of the programs used are optimistic lock, pessimistic lock

Differences with Oracle:

MySQL Support: SELECT * from the orders for update wait 6– syntax error

SELECT * FROM Orders for update nowait 6; – Syntax errors

Not finished yet, follow-up supplement

The JPA used by the project team. Thanks for the JPA configuration.

@Repository Public InterfaceJobinfodaoextendsJparepository<jobinfo, long>{@Query (value= "Select J from Jobinfo j where J.jobname =: JobName")  PublicJobinfo getjobforupdate (@Param ("JobName") String JobName); @Lock (Value=lockmodetype.pessimistic_write) @Query (value= "Select J from Jobinfo j where j.id =: id")  Public voidGetjobbyidforupdate (@Param ("id") Long ID); @Lock (Value= Lockmodetype.pessimistic_write)//TODO, don't use this! @Query (Value= "Select J from Jobinfo j where J.jobname =: JobName")  Public voidGetjobbynameforupdate (@Param ("JobName") String JobName); } 

. JPA How to make a select for update.

Also drunk, on the DAO layer method, you want to configure the annotation for lock. And to add Lockmodetype.pessimistic_write, this is equivalent to the for update. You can also see in the SQL that is printed out when the program runs.

SQL for update