Brief analysis of Oracle data integrity and lock mechanism _oracle

The content of this lesson belongs to Oracle Advanced Course category, the content is slightly biased in theory, but it is closely related to the development and management of database program, optimization, and other parts of this lesson in the previous chapters have been covered, please pay attention to theoretical practice.

Transaction

Transaction (Transaction) from the perspective of Communication : is a user-defined sequence of database operations, these operations are either full, or all do not, is an integral unit of work. Transaction control statements, called TCL, generally include commits 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.

Transactions are the 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;

ACID properties and end mode of transactions

ACID properties and end mode of transactions

Factors that disrupt the acid properties of a transaction include:

1. When multiple transactions run in parallel, the operations of different transactions cross execution

2. The transaction is forced to terminate during operation

The end of a transaction includes:

Parallelism and consistency

Parallelism and consistency are for multi-user, multiple transactions, rather than Single-user, single transaction database environments, meaning that in a multiuser, transactional environment, data for the same database table is updated at the same time (with update and insert, Delete).

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

Consistency means that the data that each user sees is consistent.

In order to ensure the consistency of data, the transaction isolation mechanism (transaction isolation model) is commonly used, which is also called transaction serialization, which is used to ensure that transactions are carried out in a serial manner.

The execution of parallel transactions prevents three scenarios:

1. Dirty reads : Transaction reads another uncommitted transaction data (dirty data);

2. Non-repeat reading : The transaction re-read the previously read data and found that another already committed transaction has modified the data;

3. Phantom reads : A transaction is rerun, returning the rowset data that satisfies the condition, and the result finds that another transaction that has been committed inserts data from another row that satisfies the condition.

Isolation Layer UNCOMMITTED read mode read mode of submission Repeat read mode serialization Mode Dirty read may not be possible impossible not to repeat read could possibly impossible Phantom read could possibly possibly impossible

The circumstances in which parallelism applies

The prerequisite is that it must be performed on a multiple-CPU server, at which point the benefits of parallelism can be displayed, and the experimental parallelism on a single CPU server can degrade performance.

• Processing large data queries on large tables (at least 1 million rows above)

• Handle table queries with very large connections

• Processing the establishment of large index, bulk data loading, summary calculation

• Work with large volumes of data copies between Oracle objects

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

• Process data queries that are stored in multiple data files distributed across different disks

• Process queries that require a large amount of secondary memory, such as group by, order by, and so on

Statement-level read consistency and transaction-level read consistency

Oracle Lock

What is a database lock

A lock is a mechanism used to prevent harmful interactions between transactions (save and fetch) when accessing the same resources, including user objects, system objects, memory, shared data structures in the Oralce data dictionary, and most commonly database table objects.

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

Lock is a very important technology to realize concurrency control of database. When a transaction is operating on a data object, the system is asked to lock it before it is made. After the lock transaction has some control over the data object, the other transaction cannot update the data object until the transaction releases the lock.

Two kinds of locking mechanisms

shared locks (Share Lock): The S lock is achieved through 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 of the same resources at the same time. Database objects with exclusive locks cannot be read and modified by other transactions.

• Locks are maintained during transaction retention to prevent interaction such as dirty reads, lost updates, and destructive DLLs. Modifications made to a SQL statement in a transaction can only be used by other transactions when the transaction is committed or rolled back.

The lock used by the transaction is released after commit or rollback is executed.

Dead lock

Type of lock

1. Data lock (DML lock).

Used to ensure the integrity of concurrent access to data. The destructive interaction of DML and DDL operations that can prevent synchronization conflicts. is the primary lock in Oracle, and includes a table-level lock (TM Lock) and row-level locks (TX locks, also known as transaction locks).

(1). TM Lock

1. Data lock (DML lock).

(2). TX Lock and DML lock working mechanism

The TX lock is the transaction eXclusive lock row-level exclusive lock, with a TX lock on a record that cannot be modified or deleted by other users.

• When Oracle executes a DML statement, the system automatically requests a TM type of lock on the table to be manipulated. When the TM lock is obtained, the system automatically requests the TX type of lock and resets the lock flag bit of the actual locked data row. In this way, check the TX lock compatibility before the transaction is locked without further checking the lock flag, and only check the compatibility of TM lock mode, which can greatly improve the efficiency of the system. TM locks include SS, SX, S, X, and many other modes, represented in the database in 0-6. Different SQL operations produce different types of TM locks.

1. Data lock (DML lock)

(2). TX Lock and DML lock working mechanism

• 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 a DML statement on the same record on the table, the first session is locked on the record, and the other session is in the waiting state. When the first session is committed, the TX lock is freed and the other sessions can be locked.

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

2. Dictionary lock (DDL Lock)

When the DDL command is emitted, Oracle automatically adds a DDL lock to the object being processed, preventing the object from being modified by another user. When the DDL command is finished, the DDL lock is freed. DDL locks are not explicitly requested, and DDL locks are added to the object only if the object structure is modified or referenced. For example, when a stored procedure is created or compiled, a DDL lock is added to the referenced object. When you create a view, you also add DDL locks to the referenced tables.

Before executing the DDL command, Oracle automatically adds an implicit commit command and then executes the specific DDL command, and an implicit commit command is added automatically after the DDL command has finished executing. In fact, Oracle converts the DDL command into a DML operation on the data dictionary table. For example, when we issue a DDL command to create a table, Oracle

2. Dictionary lock (DDL Lock)

Insert the name of the table into the Data dictionary table tab$, and insert the column name and the column type into the col$. Therefore, you need to add an implicit commit command in the DDL command to submit those DML operations to the data dictionary table. Even if the DDL command fails, it also issues a submit command. The DDL lock consists of three types:

• 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 another user during the execution of the DDL command. After an exclusive DDL lock has been added to an object, no additional DDL locks can be added to the object. If you are making DDL commands on tables, other processes cannot modify the data in the table.

2. Dictionary lock (DDL Lock)

• Shared DDL lock (Shared DDL Lock)
The object used to protect the DDL is not updated by other user processes, but allows other processes to add shared DDL locks on the object. If you are making DDL commands on tables, other processes can modify the data in the table at the same time. Like when we issue the CREATE VIEW command. When creating a view, the Shared DDL command is added to the table referenced in the view (this table 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 internal database structures, such as data files, that are not visible to users.

2. Dictionary lock (DDL Lock)

• Can break the resolution lock (breakable parsed Lock)

The cached SQL cursor or PL/SQL program code in the shared pool gets the parse lock on the referenced object. If we emit 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 you execute the same SQL statement, you need to parse it again, which is the reload of the so-called SQL statement. Broken parsing locks do not prevent other DDL locks, and parsing locks are broken if DDL locks occur that conflict with the resolution lock

Deadlock resolution

1. Find lock

3.Kill Operating System process

Orakill instance name operating system process ID

Orakill Oralearn 2444

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

Data integrity

Common data integrity constraint rules include:

1.NOT NULL

2. Unique keywords

3. Main key word

4. Foreign key

5. Checking Check

As this section has been interspersed in the previous chapters, the Department will not repeat the following

Key points and exercises

Exercises

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

2. What are the four characteristics of a transaction? What is the main use of concurrent transactions, please cite four kinds of cases.

3. Transactional-level read consistency consists of the three types, and the list shows the same and different points.

4. What is a database lock, including the two major 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 is the procedure for deadlock resolution?

7. Why does database design not recommend using a large number of foreign keys to ensure data integrity?