Java&sql5

Transaction

Transactions are used as the smallest control unit, especially with database systems that operate concurrently with multiple users.

A transaction (transaction) is a unified management of a series of data operations bundled into a whole, and if a transaction executes successfully, all data in that transaction commits and becomes a permanent part of the database. If the transaction execution encounters an error and must be canceled or rolled back, the data is all restored to the state before the operation and all changes to the data are cleared.

1 atomicity (atomicity). A transaction is a complete operation, and the individual elements of the transaction are non-divided (atomic). all elements in a transaction must be committed or rolled back as a whole.

2 Conformance (consistency). When the transaction is complete, the data must be in a consistent state. that is, before the transaction begins, the data stored in the database is in a consistent state, and in the ongoing transaction, the data may be in an inconsistent state, as the data may be partially modified. However, when the transaction is complete, the data must return to the known consistent state again.

3 Isolation (lsolation). All concurrent transactions that are modified on the database are isolated from one another, and when one transaction is performing a modification, another transaction cannot be modified at the same time, and must wait until the first transaction is complete before it can operate, thus indicating that the transaction must be independent.

4 Persistence (Durability ). The persistence of a transaction means that the result of the transaction is permanent regardless of whether the system has failed.

MySQL provides a variety of storage engine support transactions, and the storage engine that supports transactions has InnoDB and BDB. InnoDB Storage engine transactions are primarily through the undo log and the redo log, and the MyISAM storage engine does not support transactions.

Any kind of database, there are a variety of logs, used to record the operation of the database, daily operations, error messages and so on.

Undo log: The data before the transaction is copied for the transaction to roll back the data when an exception occurs.

Redo log: Records transactions in execution, each update to the database operation, when the transaction is committed, the content will be flushed to disk.

Syntax for executing a transaction:

1 start a transaction: begin; or start transaction;

2 COMMIT TRANSACTION: Commit;

3 Rollback (undo) transaction: rollback;

example of executing a transaction:

                   /* Start Transaction * / insert a batch of student grades into the student table

BEGIN;

INSERT into ' result ' VALUES

(100000,2,now (), 100),

(100001,1,now (), 90),

(100002,2,now (), 80);

SELECT * from ' result '; #如果用SQLyog会显示已经在表里面更新了数据, the database does not actually update the data

COMMIT; #提交

SELECT * from ' result '; #提交过后数据库里面会更新数据

BEGIN;

INSERT into ' result ' VALUES

(100003,2,now (), 110),

(100004,1,now (), 900);

ROLLBACK; #回滚

SELECT * from ' result '; #执行回滚后查询数据没有任何变化

The transaction is as brief as possible. in multi-user systems, large transactions can consume a large amount of resources, which can affect the performance of the software and even cause the system to crash.

The amount of data accessed in a transaction is minimized. when concurrent execution, the smaller the amount of data that is manipulated by the data, the less contention is to the data between transactions.

try not to use transactions when querying data. the query operation does not update the database, but it wastes system resources.

try not to have an operation waiting for user input during the transaction process. If you need to wait for the user to enter data during the execution of a transaction, the transaction's President event consumes system resources and can cause system congestion.

View

In the actual work, different identities of users are concerned about the database data may be different, the people of different positions on the database query data range permissions are also different.

Database management system provides a view of such database objects, can be reused complex query results saved to a view, you can also set different access scope view, different positions of employees according to their own permissions to view the relevant data to ensure the security of data access.

A view is another way to view data in one or more tables in a database, which is a virtual table that is typically created as a subset of the rows or columns of one or more tables.

A view is typically used to perform four operations:

1. Filter rows in a table

2. Prevent unauthorized users from accessing sensitive data sheets

3. Abstract multiple physical data tables into a single logical data table

4. Reduce the complexity of your data

Benefits of views for users:

1. The results are much easier to understand. When you create a view, you can change the column name to a more meaningful column name without affecting the physical database data.

2. Getting data is easier. You can view the data directly from the view without having to write your own query statements.

Benefits of the view for developers:

1. Restricting data retrieval is easier. Can effectively hide some sensitive data

2. It is more convenient to maintain the application. Debugging a view is easier than debugging a query.

Syntax: CREATE View view name

As

<select Statement >;

In the SQL statement naming specification, views are typically named in the View_xxx or v_xxx style.

Index:

Classification of indexes

1. General Index

2. Unique index

3. Primary KEY index

4. Composite Index

5. Full-Text Indexing

6. Spatial index

Java&sql5