When does Oracle need a commit

Today, in Oracle's SQL plus run the delete and query operations, and then run the query in PL/SQL, the same, the result is not the same, let me feel depressed, and then suddenly thought may be on both sides of the data inconsistency caused, but why inconsistent, is useless commit,

I looked it up on the internet, which is probably what I said:

DML language, such as Update,delete,insert and other changes to the data in the table need to commit;
DDL language, such as create,drop changes the table structure, there is no need to write a commit (because the internal hidden commit);

DDL Data Definition Language:
CREATE table Creating tables
ALTER TABLE change tables
drop TABLE Delete Tables
Truncate TABLE to delete all rows in tables
CREATE index
Drop INDEX to delete indexes
When you run a DDL statement, Oracle commits the current transaction before and after each statement. Assuming that the user inserts a record into the database using the Insert command, a DDL statement (such as Create
table), data from the Insert command is submitted to the database. When the DDL statement finishes running, the DDL statement is self-committed and cannot be rolled back.

DML Data Manipulation Language:
Insert inserts a record into the database
Update records for database changes
Delete Deletes a database record
When the DML command is run, it is assumed that no commits will be seen by other sessions. Unless a DDL command or DCL command is run after the DML command, or the user exits the session or terminates the instance, the system will voluntarily
Issue a COMMIT command to make the uncommitted DML command commit.


Ddl:
Data Definition Language (DDL) statements is 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 is removed

Comment-add comments to the data dictionary

Rename-rename an Object

Dml:

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

Select-retrieve data from the A 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 is used to manage the changes made by DML statements. It allows statements to being grouped together into logical transactions.

Commit-save work done

Savepoint-identify a point in a transaction to which 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, the database generates multiple transactions that access the same data at the same time. If the concurrency operation is not controlled, it is possible to read and store the wrong data and destroy the consistency of the database.

Locking is a very important technique 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.

There are two main types of locks in the database: Exclusive lock (Exclusive Locks, i.e. x Lock) and shared lock (Share Locks, S lock). When a data object is added to an exclusive lock, other transactions cannot read and change it. Data objects with shared locks can be read by other transactions, but cannot be changed. The database uses these two main types of locks to control the concurrency of the database transactions.



Types of locks for Oracle databases

Depending on the object being protected, the Oracle database lock can be divided into the following categories: DML lock (data locks, lock) to protect the integrity of the data, DDL locks (Dictionary locks, Dictionary locks) to protect the structure of database objects, such as tables, Structure definitions for indexes, internal locks and latches (internal locks and latches), protect the internal structure of the database.

The purpose of a DML lock is to ensure data integrity in the case of concurrency. In the Oracle database, DML locks mainly contain TM and TX locks, in which TM locks are called table-level locks, and TX locks are called transaction or row-level locks.

When Oracle runs DML statements, the system itself proactively requests a TM-type lock on the table to be manipulated. When the TM lock is obtained, the system then voluntarily applies for 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 not necessary to check the lock flag by line, but only check the compatibility of TM lock mode, which greatly improves the efficiency of the system. TM Lock contains the SS, SX, S, X and other modes, in the database with 0-6 to represent. Different SQL operations produce different types of TM locks.

There is only an X lock (exclusive lock) 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 run 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 are capable of locking.

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