The @oracle of Oracle's 5--oracle and the transaction processing @ Case Study ____oracle

Change and deletion in 1.Oracle

DML (Data manipulation language– ) can be performed under the following conditions: Inserting data into a table, modifying existing data, and deleting existing data.

A transaction consists of a DML statement that completes several tasks.

2. Inserting Data operations

Lists the values for each column in the default order of the columns. The column names and their values are listed randomly in the INSERT clause. Character and date type data should be enclosed in single quotes.

Use the & variable to specify the column value in the SQL statement. & variables are placed in the values clause. & variable is a placeholder, which is to enter a value in the console

INSERT into Departments

(department_id, Department_name, location_id)

VALUES (&department_id, ' &department_name ', &location);

3. Update Data operations

Add only one piece of data at a time, but updates can be multiple data at once.

If the WHERE clause is omitted, all data in the table is updated

4. Delete Data operation

5. Database transactions consist of the following components:

One or more DML statements

A DDL (data definition language– language) statement

A DCL (Data Control language– language) statement

Begins with the execution of the first DML statement, ending with one of the following:

Commit or ROLLBACK statement; DDL or DCL statement (autocommit); user session ended gracefully; system exception ended.

With the commit and rollback statements, we can: ensure data integrity.

Data changes are submitted before the preview. Group logically related operations.

State of data before committing or rolling back

Autocommit executes in the following cases: DDL statement; DCL statement.

End a session normally without committing or rolling back with a commit or ROLLBACK statement.

End of Session exception or system exception causes automatic rollback

State of data after submission

The state of the data before the change can be restored

A user who performs a DML operation can query the previous correction through a SELECT statement

Other users cannot see the changes made by the current user until the current user ends the transaction.

The rows involved in the DML statement are locked and cannot be manipulated by other users.

status after data rollback

Use the ROLLBACK statement to invalidate data changes:

Data changes were canceled. The data state before the modification is restored. The lock is released.

6. Isolation level of the database

For multiple transactions running concurrently, when these transactions access the same data in the database, if the necessary isolation mechanism is not taken, it can cause various concurrency problems:

Dirty reads: For two things T1, T2, T1 read fields that have been T2 updated but have not yet been committed. After that, if the T2 is rolled back, the contents of the T1 read are temporary and invalid.

Non-repeatable reads: For two things T1, T2, T1 read a field, and T2 updated the field. After that, T1 reads the same field again, and the value is different.

Phantom reads: For two things T1, T2, T1 reads a field from a table, and then T2 inserts some new rows into the table. After that, if T1 reads the same table again, there will be a few more lines.

The isolation of database transactions: The database system must have the ability to isolate and run transactions concurrently, so that they do not interact with each other and avoid various concurrency problems.

The degree to which a transaction is isolated from other transactions is called the isolation level. The database provides a variety of transaction isolation levels, different isolation levels for different levels of interference, the higher the isolation level, the better the data consistency, but the less concurrency

7. Conceptual characteristics of the transaction

concept: A transaction is a logical unit of work in a database, consisting of one or more SQL statements that complete a set of related behaviors, ensuring that the operations of this set of SQL statements are executed completely successfully, complete the unit operation, or do nothing.

Main features: Ensure the integrity of the database.

acid Properties of transactions:

A set of SQL statement operations constitutes a transaction, and the database operating system must ensure the atomicity, consistency, isolation, and durability of these operations.

Atomic Sex (atomicity)

The atomicity of a transaction means that all operations contained in a transaction are either wholly or not, that is, all activities are reflected in the database, or all are not reflected, to ensure consistency of the database.

Consistency (consistency)

Transactional consistency refers to the fact that the database must meet the rules of the business before and after the transaction.

Isolation (Isolation)

Isolation is the ability of a database to allow multiple concurrent transactions to read or write data at the same time, and isolation can prevent concurrent execution of multiple transactions, resulting in inconsistencies in data due to the cross execution of their operation commands.

Persistence (Durability)

The persistence of a transaction means that its modification of the data should be permanent after the transaction has ended. Even if the system is not lost in the event of failure, this is the importance of the data decided.

8. The control statement of the transaction

In an Oracle database, no start transaction statements are provided, and all transactions are implicitly started, that is, in Oracle, users cannot begin a transaction by displaying the command. Oracle task the first to modify the statement of the database, Or some situations that require transaction processing are the implicit start of a transaction. However, when a user wants to terminate a transaction, the use of commit and rollback statements must be displayed.

Based on the transaction Acid property, Oracle provides the following transaction control statements:

Set Transaction Setting things properties

Set constrains sets the constraint mode of things

A constraint pattern is when data is modified in a transaction, the constraint in the database is immediately applied to the data, or the constraint is deferred until the end of the current transaction.

SavePoint create a stored point in a transaction. When a transaction is rolled back and the transaction is rollback, you can specify that the transaction be rolled back to a storage point. And then rerun from that storage point.

Release SavePoint Delete Storage point

Rollback ROLLBACK TRANSACTION cancels any action on the database

Commit commits a transaction to make a persistent save to the operation of the database.

9. Set the properties of things

The SET TRANSACTION statement can be used to set various properties of things. The statement must be placed in the first statement of the transaction.

That is, you must be in any INSERT, UPDATE, DELETE statement, and other transactions. A SET TRANSACTION statement allows the user to set the following properties of a transaction: Specify the transaction isolation layer, require the storage space used by the rollback transaction, a named transaction

Note: When you set a property using the SET TRANSACTION statement, the settings for the storage space used by the rollback transaction are rarely used. It is also very simple for a named transaction, which only manifests the purpose of a named transaction in a distributed transaction.

Note: Set transaction is valid only for the current transaction, the transaction terminates, and the current setting of the transaction is invalidated.

10. Data exception

The isolation of a transaction defines the degree to which a transaction is isolated from other transactions. To better understand the isolation layer, let's first discuss what might happen to a concurrent transaction accessing the same database. In the case of concurrent transactions, the following 3 scenarios will generally occur

Wrong reading | Dirty reading

Non-repeat Read | cannot read repeatedly

False Reading | Phantom reading

Bad Read | Dirty read: When one transaction modifies data, another transaction reads the data, but the first transaction cancels the data modification for some reason, and the data is returned to its original state, which is inconsistent with the data read by the second transaction. This is called misreading.

Non-repeat read: A transaction reads data from a database, another transaction updates the data, and when the first transaction reads the data again, it finds that the data has changed, and that is, not duplicate reading. The result of a non-duplicate read is that the data read two times before and after a transaction is different.

False reads: If a transaction reads data based on a condition, then another transaction updates the data in the same table, when the first transaction reads the data again, the different rows are returned based on the criteria of the search, which is the false read.

These exceptions encountered in transactions are related to the isolation settings of the transaction, the more the transaction's isolation settings, the less the exception appears, but the lower the concurrency effect, the less the separation of the transaction, the more the exception appears, the higher the concurrency effect.

Select Isolation Layer

For inconsistencies in the data read in 3, the isolation level of 4 transactions is defined in ANSI SQL standard 92. The following illustration shows:

Isolation layer	Wrong reading | Dirty reading	Non-repeat Read | cannot read repeatedly	False Reading | Phantom reading
READ UNCOMMITTED (non-committed read)	Is	Is	Is
Read Committed (Submit read)	Whether	Is	Is
REPEATABLE READ (REPEATABLE)	Whether	Whether	Is
Serializable (Serial Read)	Whether	Whether	Whether

Oracle supports two of the above four isolation layers: Read committed and Serializable. In addition Oralce also defines read only and read write isolation layers.

Read committed this is the Oracle default isolation layer.

Serializable: When setting the isolation layer of a thing, the transaction is completely separated from the transaction, and the transaction executes serially, which does not mean that a transaction must end in order to start another transaction, but rather that the result of the execution of these transactions is consistent with the result of the transaction executed once.

Read only and read write when using read only, transactions cannot have any action statements that modify the data in the database, including INSERT, UPDATE, DELETE, create statements. Read Only is a subset of serializable, except that read only reads only, and serialzable can perform DML operations. Read Write it is the default setting, which means that you can have access statements and modify statements in a transaction. But it is not used frequently.

Statement to establish SET transaction

Listed below:

Set transaction Read Only

Set Transaction Read Write

Set Transaction ISOLATION LEVEL Read Committed

Set Transaction ISOLATION Level serializable

Note: These statements are mutually exclusive. That is, it is not possible to set two or more than two options at the same time.

To end a transaction:

Under what circumstances is the end of the transaction represented?

Commit COMMIT Transaction

Rollback ROLLBACK TRANSACTION

11. Read-Only cases

Sql> set transaction Read only;

 

Transaction Set sql> SELECT * from users where username= ' MJ '; USERNAME PASSWORD NAME address ZIP-------------------------------------------------- -------------------------------------MJ redarmy Chen Hongjun SQL

 

> Update users set password= ' 123 ' where username= ' MJ '; Update users set password= ' 123 ' where username= ' MJ ' ORA-01456: Cannot perform insert/delete/update operations/Dirty read cases in Read only transactions sql> SE

 

T transaction isolation level Read Committed;

 

Transaction Set sql> SELECT * from users where username= ' MJ '; USERNAME PASSWORD NAME address ZIP-------------------------------------------------- -------------------------------------MJ redarmy Chen Hongjun SQL

 

> Update users set password= ' 123 ' where username= ' MJ '; 1 row updated sql> SelecT * from users where username= ' MJ '; USERNAME PASSWORD NAME address ZIP--------------------------------------------------                                 

 

-------------------------------------MJ 123 Chen Hongjun
 sql> rollback;