Analysis of Oracle data integrity and lock mechanism

Transactions are not programs. Transactions and programs belong to two concepts. transaction control statements are called TCL and generally include Commit and Rollback. For details, refer

Transactions are not programs. Transactions and programs belong to two concepts. transaction control statements are called TCL and generally include Commit and Rollback. For details, refer

This course is a part of the Oracle Advanced Course, which is slightly theoretical, but closely related to database program development, management, and optimization. In addition, part of this course has been covered in the previous chapter, pay attention to the theory and practice.

Transaction (Transaction) fromCommunicationFrom a user-defined database operation sequence, these operations are either all done or all done, and are an inseparable unit of work. Transaction control statements are called TCL and generally include Commit and Rollback.

Transactions are not programs. Transactions and programs belong to two concepts. In RDBMS, a transaction can have an SQL statement, a group of SQL statements, or the entire program. An application usually contains multiple transactions.

Transactions are the basic unit of recovery and concurrency control.

Begin

Insert into classes_2 (bjbh, bjmc, bjms, bzr, ssxb, bjrs, bz)

Values ('000000', 'test class', 'test class', 'xiao fengbin', '003 ', '38 ','');

End;

Insert into classes_2 (bjbh, bjmc, bjms, bzr, ssxb, bjrs, bz)

Values ('000000', 'test class', 'test class', 'xiao fengbin', '003 ', '38 ','');

ACID properties and termination methods of transactions

ACID properties and termination methods of transactions

Factors that undermine the ACID properties of transactions include:

1. When multiple transactions run in parallel, the operations of different transactions are performed in parallel.

2. The transaction is forcibly terminated during operation.

The transaction end methods include:

Concurrency and consistency are for multiple users and transactions, rather than single users and single transaction database environments. The meaning is that in multi-user and multi-transaction environments, data in the same database table is updated at the same time (including Update, Insert, and Delete.

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

Consistency means that the data displayed by each user is consistent.

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

To execute parallel transactions, you must prevent the following three situations:

1. Dirty read: The transaction reads data (dirty data) from another uncommitted transaction );

2. Non-repeated read: The transaction re-reads the previously read data and finds that another committed transaction has modified the data;

3.Phantom read: Re-execute a transaction and return the row set data that meets the condition. The result shows that another committed transaction has inserted the data of other rows that meet the condition.

Isolation layerUncommitted read modeRead mode submittedRepeated read modeSerialization ModeDirty read may not be possible, non-repeated read may not be possible, phantom read may not be possible

Parallelism Applicability

PrerequisitesIt must be executed on a multi-CPU server. The benefits of parallelism can be displayed. The experimental parallelism on a single CPU server reduces the performance.

• Process large data volume queries for large tables (at least 1 million rows of Records)

• Process Table queries with very large connections

• Process the creation of large indexes, large data loading, and summary computing

• Process massive data copies among Oracle Objects

• Queries on SMP (symmetric multi-processor) or MPP (large-scale parallel processing) clusters and aggregation (multiple machines simultaneously access the same group of disks and Primary databases)

• Process Data Queries stored in multiple data files distributed on different Disks

• Process queries that require a large amount of auxiliary memory, such as Group by and Order

Statement-level read consistency and transaction-level read consistency

What is a database lock?

Locks are used to prevent access to the same resources (including user objects, system objects, memory, and shared data structures in the Oralce data dictionary, the most common is database Table objects, A mechanism for harmful interaction (storage and fetch) between transactions.

Different types of locks indicate whether the current user permits or prevents other users from simultaneously accessing the same resource, so as to ensure that the system data integrity, consistency, and parallelism are not damaged.

Locking is a very important technology for implementing database concurrency control. Before a transaction operates on a data object, it first sends a request to the system to lock it. After the lock, the transaction has certain control over the data object. Before the transaction releases the lock, other transactions cannot update the data object.

Two lock mechanisms

Share Lock ):That is, the S lock is implemented through high concurrency of data access. Database objects with shared locks can be read by other transactions, but cannot be modified by other transactions.

Exclusive Lock ):The X lock, also known as the exclusive lock, is used to prevent the lock that shares the same resource at the same time. Database objects with exclusive locks cannot be read or modified by other transactions.

• Locks are maintained during transaction persistence to prevent interactions including dirty reads, loss of updates, and destructive DLL. Modifications made to SQL statements in a transaction can only be used by other transactions after the transaction is committed or rolled back.

• After the Commit or Rollback operation, the lock used by the transaction is released.

Deadlock

Lock type

1. Data lock (DML lock ).

It is used to ensure the integrity of data accessed in parallel. This prevents destructive interactions between DML and DDL operations that conflict with synchronization. It is the main lock in Oracle, and also includes table-level locks (tmlocks) and row-level locks (TX locks, also known as transaction locks ).

(1). tmlock

1. Data lock (DML lock ).

(2). TX lock and DML Lock Mechanism

The TX Lock is a row-level eXclusive Lock of the Transaction eXclusive Lock. After the TX Lock is applied to a record, other users cannot modify or delete the record.

• When Oracle executes a DML statement, the system automatically applies for a TM lock on the table to be operated. After the tmlock is obtained, the system automatically applies for the TX lock and places the lock flag of the actually locked data row. In this way, when the consistency of the TX lock is checked before the transaction locks, the lock mark does not need to be checked row by row. Instead, you only need to check the compatibility of the tmlock mode, which greatly improves the system efficiency. The tmlock includes multiple modes, such as SS, SX, S, and X, which are represented by 0-6 in the database. Different SQL operations generate different types of tmlocks.

1. Data lock (DML lock)

(2). TX lock and DML Lock Mechanism

• There is only an exclusive lock on the Data row ). In the Oracle database, a TX lock is obtained when a transaction initiates a DML statement for the first time. The lock is kept until the transaction is committed or rolled back. When two or more sessions execute DML statements on the same record of the table, the first session locks the record, and other sessions are in the waiting state. After the first session is submitted, the TX lock is released before other sessions can be locked.

• When the Oracle database has a TX lock wait, if not promptly processed, it will often cause the Oracle database to be suspended, or cause a deadlock, resulting in ORA-60 errors. These phenomena will cause great harm to the actual application, such as long-time unresponding and a large number of transaction failures.

2. dictionary lock (DDL lock)

WhenDDLWhen the command is issued, Oracle automatically adds a DDL lock to the processed object to prevent the object from being modified by other users. When the DDL command ends, the DDL lock is released. DDL lock cannot be explicitly requested. Only when the object structure is modified or referenced can DDL lock be added to the object. For example, create or compileStored ProcedureDDL lock will be added to the referenced object. When creating a view, it also adds DDL locks to the referenced table.

Before executing the DDL command, Oracle automatically adds an implicit commit command and then executes the specific DDL command. After the DDL command is executed, an implicit commit command is automatically added. In fact, Oracle converts DDL commands to DML operations on data dictionary tables. For example, when we issue a DDL command to create a table, Oracle will

2. dictionary lock (DDL lock)

Insert the table name to the data dictionary table tab $. Insert the column names and column types in the table to the col $ table. Therefore, you must add implicit commit commands to DDL commands to submit DML operations on data dictionary tables. Even if the DDL command fails, it will issue the submit command. DDL locks include three types:

• Exclusive DDL Lock)
Most DDL operations add exclusive DDL locks to the objects to be operated, so as to prevent objects from being modified by other users during DDL command execution. After exclusive DDL locks are added to an object, no other DDL locks can be added to the object. If you run DDL commands on a table, other processes cannot modify the data in the table.

2. dictionary lock (DDL lock)

• Shared DDL Lock)
It is used to protect the DDL objects from being updated by other user processes, but allows other processes to add shared DDL locks to the objects. If you run DDL commands on a table, other processes can modify the data in the table at the same time. For example, when the create view command is issued to create a view, the shared DDL command is added to the referenced table (such a table is also called a base table) of the view. That is to say, when creating a view, other users cannot modifyBase tableBut can update the data in the base table.

3. Internal lock

Internal locks protect internal database structures, such as data files, which are invisible to users.

2. dictionary lock (DDL lock)

• Broken resolution Lock (Breakable Parsed Lock)

The cached SQL cursor in the shared pool or PL/SQL program code will get the resolution lock on the referenced object. If a DDL command is issued to modify the structure of an object, the resolution lock related to the object located in the shared pool is broken, as a result, the SQL cursor or PL/SQL program code that references this object is invalid. The next time you execute the same SQL statement, you need to re-parse it. This is the reload of the so-called SQL statement. A broken resolution lock does not prevent other DDL locks. If a DDL lock conflicts with the resolution lock, the resolution lock will also be broken.

Deadlock Solution

1. Search for locks

3. Kill Operating System Processes

Orakill Instance name operating system process ID

Orakill oralearn 2444

Here, oralearn is the database sid, and 244 is the second step to find the spid

Common Data integrity constraints include:

1. NOT NULL

2. Unique keywords

3. Primary keywords

4. Foreign key

5. Check items

As this part of content has been explained in the previous sections, I will not repeat it here

Exercise

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

2. What are the four features of a transaction? What are the main use cases of parallel transactions? Let's look at four cases.

3. Transaction-level read consistency includes the three types, and the list shows their similarities and differences.

4. What are database locks, including the two types? What types does the tmlock include?

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

6. What is a deadlock? What are the steps to solve the deadlock?

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