Today, in Oracle's SQL plus execution of the delete and query operations, and then execute the query in PL/SQL, the same results are not the same, let me feel depressed, and then suddenly think of the two sides of the data inconsistency caused, but why inconsistent, is useless commit, Looked up on the internet, probably said: DML language, such as Update,delete,insert, such as the need to modify the table data Commit;ddl language, such as create,drop, such as changing the table structure, There is no need to write a commit (because a commit is hidden internally);DD L Data Definition language: CREATE TABLE creates tables ALTER TABLE modify tables drop table Delete tables truncate TABLE Delete all rows in the CREATE index To create an index drop index to delete indexes when executing DDL statements, Oracle commits the current transaction before and after each statement. If the user uses the Insert command to insert a record into the database and executes a DDL statement (such as CreateTable), the data from the Insert command is committed to the database. When the DDL statement execution completes, the DDL statement is automatically committed and cannot be rolled back. DML Data Manipulation Language: Insert records inserted into database update modify database record delete delete database records when the Execute DML command is not committed, it will not be seen by other sessions. Unless a DDL command or DCL command is executed after the DML command, or the user exits the session or terminates the instance, a commit command is automatically issued 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 databasealter-alters the structure of the Databasedrop-delete objects From the Databasetruncate-remove all records to a table, including all spaces allocated for the records is removedcom Ment-add comments toThe data dictionaryrename-rename an objectdml:data manipulation Language (DML) statements is used for managing data wit Hin schema objects. Some Examples:select-retrieve data from the a Databaseinsert-insert data into a tableupdate-updates existing data wi Thin a Tabledelete-deletes all records from a table, the space for the Records Remainmerge-upsert operation (INSERT or UPDATE) Call-call a PL/SQL or Java subprogramexplain plan-explain access path to DataLock Table-control Concurrencyd Cldata Control Language (DCL) statements. Some examples:grant-gives user ' s access privileges to Databaserevoke-withdraw access privileges given with the GRANT C Ommandtcltransaction 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 donesavepoint-identify a point in a transaction to which can later roll Backrollback-restore da Tabase to original since the last COmmitset Transaction-change TRANSACTION options like isolation level and what rollback segment to use lock concept base database is a multi-user shared Resources. When multiple users access data concurrently, in the database, multiple transactions are generated concurrently to access the same data. If the concurrency operation is not controlled, it is possible to read and store incorrect data and compromise the consistency of the database. 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. There are two basic types of locks in the database: Exclusive lock (Exclusive Locks, X Lock) and shared lock (Share Locks, S lock). When a data object is added to an exclusive lock, other transactions cannot read and modify it. Data objects with shared locks can be read by other transactions, but cannot be modified. The database uses these two basic types of locks to control the concurrency of a database's transactions. The type of lock for an Oracle database depends on the object being protected, the Oracle database lock can be divided into the following categories: DML lock (data locks, lock), which is used to protect the integrity of the data; DDL locks (Dictionary locks, Dictionary locks), The structure used to protect database objects, such as the structure definition of tables, indexes, and internal locks and latches (internal locks and latches), protecting the internal structure of the database. The purpose of a DML lock is to ensure data integrity in the case of concurrency. In Oracle databases, DML locks mainly include TM and TX locks, where TM locks are called table-level locks, and TX locks are called transaction or row-level locks. 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. 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 execute a DML statement on the same record on the table, the first session is locked on that record, and the other oneThe conversation is in a waiting state. 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 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.
When Oracle requires commit
