(Les09 managing Concurrent Data Processing) [20180319], les0920180319

(Les09 managing Concurrent Data Processing) [20180319], les0920180319
Objective Description locking mechanism and how to manage concurrent data processing monitoring and lock resolution conflict locks in Oracle-prevent multiple sessions from changing the same data at the same time-automatically obtained at the lowest possible level of a specified statement- transaction Processing without upgrading can lock a single data row, multiple data rows, or even the entire table. Oracle DB supports manual locks and automatic locks. The automatically obtained lock always selects the lowest possible lock level to minimize potential conflicts with other transactions. Lock Mechanism advanced data concurrent processing-Execute insert, use row-Level Lock when updating and deleting-do not require any lock automatic queue management during Transaction Processing (use COMMIT or ROLLBACK operations) keep locking before. Note: Row-level locks are obtained when transactions modify data, rather than block-level or table-level locks. When you modify an object (such as moving a table), the object lock is obtained instead of the entire database lock or schema lock. SQL> LOCK TABLE employees IN EXCLUSIVE MODE; ROW SHARE: allow concurrent access to the locked TABLE, but prohibit EXCLUSIVE access to the entire TABLE from being locked IN the session. Row exclusive: Same as row share, but cannot be locked in SHARE mode at the same time. The row exclusive lock is automatically obtained when data is inserted or deleted. The row exclusive lock allows multiple read operations, but only one process is allowed to write data. SHARE: Allows concurrent queries, but prohibits update of locked tables. You must have a SHARE lock to create an index for the table. This lock is automatically requested during table creation. However, creating an online index requires a row share lock when creating an index. The shared lock allows multiple processes to read data, but does not allow write operations. When deleting or updating rows in a parent table and its child tables have foreign key constraints, the shared lock is also used transparently. Share row exclusive: Used to query the entire table, allowing others to query the rows in the table, but prohibiting others from locking the table or updating rows in SHARE mode. EXCLUSIVE: allows you to query locked tables. You cannot execute any other activities on locked tables. You must have an EXCLUSIVE lock to delete the table. DML lock each DML transaction must obtain two locks: the exclusive row lock for one or more rows being updated for the row exclusive (RX) of the table being updated) the table (TM) in the mode. This prevents the entire table from being locked by another session (the table may be deleted or truncated ). This mode is also called the sub-Row Table lock (SX ). The queue locking mechanism automatically queues requests. As soon as the transaction holding a lock is processed completely, the next session in the queue receives the lock. The queuing mechanism tracks the request lock sequence and the request lock mode. Sessions that already hold the lock can request the Switch Lock, without having to be placed at the end of the team. There are two types of waiting processes: Waiting processes without shared ownership, and waiting processes with shared ownership/but not with the upgraded lock level. The second type of waiting process is called a conversion process. The priority of such processes is always higher than that of the normal waiting process, even if the waiting time is short. The queuing mechanism is used to track-sessions waiting for locks-lock modes of requests-possible causes of lock conflicts of Session Request locks-uncommitted changes-long run transaction processing: lock conflicts often occur when both transactions are executed and in batch. -Unnecessary high lock Level Lock conflict detection-commit or roll back the session holding the lock-terminate the session holding the lock (in an emergency) Note: if the session has idle timeout, the PMON session detection program automatically terminates the session, which can be completed using the profile or resource manager. Use SQL to solve the lock conflict SELECT SID, SERIAL #, USERNAME FROM V $ SESSION WHERE SID IN (SELECT BLOCKING_SESSION FROM V $ SESSION); ALTER SYSTEM KILL SESSION '<SID>, <SERIAL #> 'immeidate; alter system disconnect session '<SID>, <SERIAL #> 'immediate; deadlock is a special case of lock conflict. When two or more sessions wait for data that has been locked by another session, a deadlock occurs. Because each session is waiting for another session to release the lock, any session cannot complete transaction processing and cannot resolve the conflict. Oracle DB automatically detects deadlocks and terminates statements with errors.