When Does oracle need commit?

Today, oracle SQL plus performed the delete and query operations and then the query operations in PL/SQL. The results of the same statements were different, which made me feel quite depressed, later, I suddenly thought that the data on both sides may be inconsistent, but why is it inconsistent? It means no use of commit,

I checked it on the Internet, which is probably like this:

DML language, such as update, delete, insert, and so on, to modify data in a table, commit;
If the DDL language, such as create or drop, changes the table structure, you do not need to write commit (because commit is hidden internally );

DDL data definition language:
Create table
Alter table
Drop table delete table
Truncate table delete all rows in the table
Create index
Drop index Delete index
When a DDL statement is executed, oracle submits the current transaction before and after each statement. If you use the insert command to insert records into the database, a DDL Statement (such as create
In this case, data from the insert command will be submitted to the database. After a DDL statement is executed, it is automatically submitted and cannot be rolled back.

DML data operation language:
Insert inserts records into the database
Update and modify Database records
Delete database records
If the DML command is not submitted, it will not be seen by other sessions. Unless the DDL or DCL command is executed after the DML command, or the user exits the session or terminates the instance, the system automatically
Issue the commit command to submit unsubmitted DML commands.

DDL:
Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

CREATE-to create objects in the database

ALTER-alters the structure of the database

DROP-delete objects from the database

TRUNCATE-remove all records from a table, including all spaces allocated for the records are removed

COMMENT-add comments to the data dictionary

RENAME-rename an object

DML:

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

SELECT-retrieve data from the database

INSERT-insert data into a table

UPDATE-updates existing data within a table

DELETE-deletes all records from a table, the space for the records remain

MERGE-UPSERT operation (insert or update)

CALL-call a PL/SQL or Java subprogram

Explain plan-explain access path to data

Lock table-control concurrency

DCL

Data Control Language (DCL) statements. Some examples:

GRANT-gives user's access privileges to database

REVOKE-withdraw access privileges given with the GRANT command

TCL

Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

COMMIT-save work done

SAVEPOINT-identify a point in a transaction to which you can later roll back

ROLLBACK-restore database to original since the last COMMIT

Set transaction-Change transaction options like isolation level and what rollback segment to use

Lock concept Basics

A database is a shared resource used by multiple users. When multiple users access data concurrently, multiple transactions can access the same data simultaneously in the database. If concurrent operations are not controlled, incorrect data may be read and stored, compromising Database Consistency.

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.

There are two basic lock types in the database: Exclusive Locks and Share Locks ). When an exclusive lock is applied to a data object, other transactions cannot read or modify it. Data Objects with a shared lock can be read by other transactions, but cannot be modified. The database uses these two basic lock types to control the concurrency of database transactions.

Oracle Database lock type

Oracle Database locks can be divided into the following categories based on different protected objects: DML locks (data locks), used to protect data integrity; DDL locks (dictionary locks, dictionary locks) are used to protect the structure of database objects, such as schema definitions for tables and indexes. internal locks and latches protect the internal structure of the database.

The purpose of the DML lock is to ensure data integrity in the case of concurrency ,. In Oracle databases, DML locks mainly include tmlocks and TX locks. tmlocks are called table-level locks, and TX locks are called transaction locks or row-level locks.

When Oracle executes the 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.

Only the X lock (exclusive lock) exists 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 timely processing will often cause the Oracle database to suspend, or cause the occurrence of a deadlock, produce ORA-60 errors. These phenomena will cause great harm to the actual application, such as long time not responding, a large number of transactions failed, etc.