Oracle Transaction Lock mechanism

This lesson is part of Oracle's advanced curriculum, which is slightly biased in theory, but closely related to the development and management of database programs, as well as the part of this lesson that has been covered in previous chapters.

Transactions  

Transactions (Transaction) from the point of view of communication : A user-defined sequence of database operations, which are either full-or no-do, is an inseparable unit of work. Transaction control statements, called TCL, typically include commit and rollback.

A transaction is not a program, and a transaction and a program belong to two concepts. In an RDBMS, a transaction can have an SQL statement, a set of SQL statements, or an entire program, and an application typically contains multiple transactions.

A transaction is a basic unit of recovery and concurrency control.

Explicit and implicit transactions

Begin

Insert into classes_2 (BJBH,BJMC,BJMS,BZR,SSXB,BJRS,BZ)

VALUES (' 888 ', ' Test class ', ' Test class ', ' Xiaofeng ', ' 003 ', ' 38 ', ');

Commit/rollback;

End;

Insert into classes_2 (BJBH,BJMC,BJMS,BZR,SSXB,BJRS,BZ)

VALUES (' 888 ', ' Test class ', ' Test class ', ' Xiaofeng ', ' 003 ', ' 38 ', ');

Commit/rollback;

of the transaction ACID features and end-of-way

of the transaction ACID features and end-of-way

Disruptive Transactions ACID factors of the characteristics include:

1. When multiple transactions run concurrently, the operations of different transactions cross-execute

2. The transaction is forcibly terminated during operation

The end-of-transaction methods include:

Parallelism and consistency

Parallelism and consistency is for multi-user, multi-transaction, rather than single-user, single-transaction database environment, it means that in multi-user, multi-transaction environment, the data for the same database table has been updated (including update and insert, Delete).

Parallelism means that multiple users can access data at the same time;

Consistency means that the data each user sees is consistent.

In order to ensure the consistency of data, a transaction isolation mechanism (transaction isolation model), also known as transaction serialization, is used to ensure that transactions are executed as often as possible in a serial manner.

There are three cases to be prevented from executing parallel transactions:

1. Dirty Read : The transaction reads the data of another uncommitted transaction (dirty data);

2. non-repeatable read : The transaction re-reads the previously read data, and found that another committed transaction has modified the data;

3. Phantom reads : One transaction is re-executed, returning the rowset data that satisfies the condition, and the result is that another committed transaction has inserted data for the other rows that meet the criteria.

Isolation layer	UNCOMMITTED read mode	Read mode of submission	Repeat read mode	Serialization mode
Dirty Read	possible	No way	No way	No way
Non-REPEATABLE READ	possible	possible	No way	No way
Phantom read	possible	possible	possible	No way

where parallelism is applicable

The precondition is that it must be executed on a multi-CPU server, at which point the benefits of parallelism can be shown, while experimental parallelism on a single-CPU server degrades performance.

• Handle large data queries on large tables (at least 1 million rows of records above)

• Processing of very large table queries

• Handle building large indexes, bulk data loading, summary calculations

• Handles large volumes of data copies between Oracle objects

• Processing queries on machines with SMP (symmetric multiprocessor) or MPP (massively parallel processing) clusters and aggregations (multiple machines accessing the same set of disks and primary databases at the same time)

• Processing of data queries stored in multiple data files distributed across different disks

• Processing queries that require large amounts of secondary memory, such as group by, Order by, etc.

Statement-level read consistency and transactional-level read consistency

Oracle Lock

What is a database lock

Locks are a mechanism used to prevent harmful interactions (deposits, fetching) between transactions when accessing the same resources (including user objects, system objects, memory, shared data structures in Oralce data dictionaries, most commonly, database tables table objects).

Different types of locks represent whether the current user allows or prevents other users from accessing the same resource simultaneously, ensuring that the integrity, consistency, and parallelism of the system data are not compromised.

Locking is a very important technology to realize concurrency control of database. When a transaction makes a request to the system before it operates on a data object, it locks it. After locking, the transaction has some control over the data object, and other transactions cannot update the data object until the transaction releases the lock.

Two kinds of locking mechanism

Shared Locks ( Share Lock ): s lock, which is achieved by the high parallelism of data access. database objects with shared locks can be read by other transactions, but cannot be modified by other transactions.

Exclusive Lock ( Exclusive Lock ): an X lock, also known as an exclusive lock, is a lock used to prevent sharing the same resource at the same time. Database objects with an exclusive lock cannot be read and modified by other transactions.

• Locks are maintained during transaction retention to prevent interactions such as dirty reads, lost updates, and destructive DLLs. Modifications made to an SQL statement in a transaction can only be used by other firms after the transaction commits or rolls back.

After commit or rollback is executed, the lock used by the firm is released.

Dead lock

Types of Locks

1. Data lock (DML lock).

Used to guarantee the integrity of parallel access data. A disruptive interaction that prevents DML and DDL operations from synchronizing conflicts. is the primary lock in Oracle, including table-level locks (tm locks) and row-level locks (TX locks, also known as transaction locks).

(1). TM Lock

1. Data lock (DML lock).

(2). Operation Mechanism of TX lock and DML lock

The TX lock is a transaction eXclusive lock row-level row lock, and when a record is added to a TX lock, no other user can modify or delete the record.

• When Oracle executes DML statements, the system automatically requests a TM-type lock on the table to be operated on. When the TM lock is obtained, the system automatically requests the TX type of lock and resets the lock flag bit of the data row that is actually locked. In this way, checking the compatibility of the TX lock before the transaction is locked, it is necessary to check the compatibility of the TM lock mode and greatly improve the efficiency of the system. TM locks include SS, SX, S, X and other modes, which are represented in the database by 0-6. Different SQL operations produce different types of TM locks.

1. Data lock (DML lock)

(2). Operation Mechanism of TX lock and DML lock

• Only x locks (exclusive locks) on the data line. In an Oracle database, a TX lock is obtained when a transaction initiates a DML statement for the first time, and the lock remains until the transaction is committed or rolled back. When two or more sessions execute DML statements on the same record on a table, the first session is locked on that record, and the other sessions are waiting. When the first session is committed, the TX lock is released and other sessions can be locked.

• When a TX lock wait occurs on an Oracle database, failure to handle it often causes the Oracle database to hang or cause a deadlock to occur, resulting in a ORA-60 error. These phenomena can cause great harm to the actual application, such as long time not responding, large number of transaction failures, etc.

2. Dictionary lock (DDL Lock)

When a DDL command is issued, Oracle automatically adds a DDL lock on the object being processed, preventing the object from being modified by another user. When the DDL command finishes, the DDL lock is freed. DDL locking cannot be explicitly requested, and DDL locking is added to an object only if the structure of the object is modified or referenced. For example, a DDL lock is added to the referenced object when a stored procedure is created or compiled. When you create a view, DDL locks are added to the referenced table, and so on.

Before executing the DDL command, Oracle automatically adds an implicit commit command, then executes the specific DDL command, and automatically adds an implicit commit command after the execution of the DDL command is complete. In fact, when Oracle executes the DDL command, it converts it to a DML operation on the data dictionary table. For example, when we issue a DDL command that creates a table, Oracle inserts the name of the table into the Data dictionary table tab$, inserting the column name and the type of the column into the col$ table, and so on. Therefore, you need to add an implicit commit command in the DDL command to commit the DML operations on the Data dictionary table. Even if the DDL command fails, it also issues a commit command. There are three types of DDL locks:

• Exclusive DDL lock (Exclusive DDL Lock)
Most DDL operations add an exclusive DDL lock on the object being manipulated, preventing the object from being modified by other users during the execution of the DDL command. When an exclusive DDL lock is added on an object, no additional DDL locks can be added to the object. If you are making DDL commands on a table, other processes cannot modify the data in the tables.

2. Dictionary lock (DDL Lock)

• Shared DDL lock (sharable DDL Lock)
Used to protect the DDL object from being updated by other user processes, but allows other processes to add shared DDL locks on the object. If you are making DDL commands on a table, other processes can modify the data in the tables at the same time. For example, when we issue the CREATE VIEW command, the Shared DDL command is added to the table referenced by the view (which is also called the base table). That is, when you create a view, other users cannot modify the structure of the base table , but you can update the data in the base tables.

3. Internal lock

Internal locks protect the internal database structure, such as data files, that are not visible to the user.

2. Dictionary lock (DDL Lock)

• Broken resolution lock (breakable parsed Lock)

The SQL cursor or the PL + + program code cached in the shared pool gets the parsing lock on the referenced object. If we issue a DDL command that modifies the structure of an object, the parsing lock associated with that object in the shared pool is broken, causing the SQL cursor referencing the object or the PL/SQL program code to fail. The next time the same SQL statement is executed again, it needs to be re-parsed, which is the reload of the so-called SQL statement. A broken resolution lock does not prevent other DDL locks, and if a DDL lock is in conflict with the resolution lock, the resolution lock will be broken

Deadlock resolution

1. Find the Lock

3.Kill Operating system processes

Orakill instance name operating system process ID

Orakill Oralearn 2444

Where Oralearn is the database sid,244 is the second step to isolate the SPID

Data integrity

Common data integrity constraint rules include the following:

1.NOT NULL

2. Unique keywords

3. Primary keywords

4. Foreign key

5. Checking Check

Since the contents of this section have been explained in the previous chapters, the Department will not repeat

Key points and exercises

Exercises

1. What is a transaction, please explain what is an explicit transaction and an implicit transaction.

2. What are the four characteristics of a transaction? What are the main uses of parallel transactions, please cite four scenarios.

3. Transaction-level read consistency consists of those three types, and the list describes its similarities and differences.

4. What are database locks, including those two large types? What types of TM locks are included?

5. Combine table-level and row-level locks to illustrate the working mechanism of data locks.

6. What is a deadlock? What are the steps for deadlock resolution?

7. Why is database design not recommended to use a large number of foreign keys to ensure data integrity?

Original address: http://www.cnblogs.com/quanweiru/archive/2013/05/24/3097367.html

Oracle Transaction Lock mechanism