DML and Concurrency

最新推荐文章于 2021-04-06 15:14:30 发布

m_linda

最新推荐文章于 2021-04-06 15:14:30 发布

阅读量388

点赞数

本文链接：https://blog.csdn.net/m_linda/article/details/38401171

版权

一. Data Manipulation Language(DML) Statements

1. Insert

Insert one row to one table with one statement

INSERT INTO table [(column [, column...])] VALUES (value [,value...]);

Insert multiple rows to one table with on statement

INSERT INTO table [column [, column ...] ]subquery;

Insert rows into several tables with one statement

2. Update

All the rows updated will be in one table, it is not possible for a single update command to affect rows in multiple tables.

UPDATE table
SET column=[subquery] [,column=subquery...]
WHERE column = (subquery) [AND column=subquery...] ;

3. Delete

A deletion is all or nothing, applies to the whole row.

DELETE FROM table [WHERE condition];

4. Truncate

Truncate is a DDL command, insert/update/delete are DML commands.

Difference: DML --- Made permanent or reversed by users.

DDL --- Immediately permanent, and never be reversed. Faster than DML. DDL commands, such as TRUNCATE, will fail if there is any DML command
active on the table.

Difference: DELETE --- Leaves the high water mark where it is.

TRUNCATE --- Resets the high water mark.

TRUNCATE TABLE table;

二. DML Statement Failures

Syntax errors
References to nonexistent objects or columns
Access permissions
Constraint violations
Space issues

三. Control Transactions

1. Database Transactions

ACID test:

A (Atomicity)

Anything goes wrong before the transaction is complete, the database itself must guarantee that any parts that did go through are reversed.

C(Cnsistency)

The result of a query must be consistent with the state of the database at the time the query started.

I (Isolation)

An incomplete transacation must be invisible to the rest of the world.

D (Durability)

Once a transaction completes, it must be impossible for the database to lose it.

2. Execute SQL statement

Select Statement

Check whether the bolcks containing the data required are already in memory, if not copy it into database buffer cache.

For any DML statements, it is necessary to work on both data blocks and undo blocks, and also to generate redo

Update Statement

Check data blocks in memory.

An empty block of an undo segement is needed.

Locks must be placed.

Redo is generated to table block changes and undo block changes. New value to table block, old value to undo block

Insert and Delete Statement

A crucial difference between INSERT and DELETE is in the amount of undo generated. Insert : write the new rowid to undo.Delete: write the whole row to undo.