Database Services Memorandum

Abstract: Database Services Memorandum

</ Td> </ tr> <tr> <td height="35" valign="top" class="ArticleTeitle"> <table width = "100%" border = "0" cellspacing = "0" cellpadding = " 0 "> <tr> <td width="502" height="86" align="left" valign="top"> Services is a group of interdependent operations, a move are not cite examples could be cited is the banking transactions and, of course, other things like online shopping, electronic money transactions, and so on, the Panel depends on the success of the operation of these interdependent whether it can implement successful, as long as there is a failure of operation, which means that the failure of the whole affairs. For example: tom bill and the QQ account QQ coins points are 500 points, 100 points of the bill now QQ coins to the QQ tom currency accounts, the Panel includes the following actions acts:
QQ bill - the currency accounts to reduce 100 points
— Tom QQ coins account of an increase of 100 points

These two operations as an indivisible unit of work, if only the first step of successful operation, but the failure of the implementation of the second step, then the whole Affairs failed to rollback before the start of the state affairs, the bill and tom QQ account QQ coins Decimal also remains 500 points. If there is no clear concept of affairs, then the bill will create 100 points QQ coins mysterious disappearance …
Database Services is the above-mentioned services in RDB in the realization of it by a group of interdependent business logic in the SQL statement of the assumption that more than DB QQ account in the qq_account table structure as follows:
————————————
Id name balance
————————————
A bill 500
2 tom 500
————————————

SQL above matters can be expressed as:
UPDATE qq_account SET balance = 400 WHERE id = 1;
UPDATE qq_account SET balance = 600 WHERE id = 2;

As long as there is a two SQL statements implementation fails, the entire affairs will fail, qq_account data in the table must be moved back to the original state, and will not be updated.
Database Services must have ACID properties, the specific meaning of the following:

—– Atomic (atom of): the entire database only the work of the Panel is indivisible unit. Only all of the affairs of successful operation, the entire affairs to be successful in the affairs of any failure of the implementation of a SQL statement, then the implementation has been successful SQL statements must also be revoked, the database should be returned to the implementation of state affairs as before. Through the above example we have seen very clearly.

—– Consistency (consistency): Database Services that can not damage relations between the integrity of the data on the business logic and consistency. For example, whether the example above QQ coins inside the affairs of transfer success or failure, it should ensure that services in the table after the end of qq_account tom bill and the QQ coins amounted to 1,000 points.

—– Isolation (isolation): Concurrency refers to in the environment, different Affairs at the same time when the operation of the same data, each of the services have their own space complete data, the things involved here more , I would like to sum up in the back as a separate article.

—– Durability (persistent): Only refer to the successful conclusion of the Panel, which updates the database must be made permanent preservation. Even in a system crash, restart the database, the database can successfully return to the Panel at the end of the state. </ Td> <td width="182" valign="top"> </ td> </ tr> </ table>

ACID affairs is an abstract concept, the specific RDBMS is achieved. Log database management system used to ensure that atomic affairs, consistency and sustainability. Logging Services has done update of the database, if a particular Affairs in the implementation process in the wrong, can log on to remove Affairs has been done on the database update, retreated to the implementation of the database before the initial state affairs. Therefore, regardless of what the database referred to, will have a chapter devoted speaking log, Oh, and finally, to understand the nature of the role of the log.

As for the affairs of isolation, RDBMS is a lock mechanism to achieve. When more than one Affairs at the same time to update the critical data in the database, only allows holders to lock the affairs of the updated data, other matters must wait until the release of the data before a lock and other matters may only have the opportunity to update, and OS schools in the process of talking about complicated by the lock mechanism almost principle.

Mentioned above, the ACID properties database services, then who is going to ensure that the database is ACID affairs? In fact, as long as the database system to a panel statement, the database system will automatically ensure that the affairs of the ACID properties. Well Below from abstract concept to see how the statement of affairs:
— The beginning of border affairs BEGIN
— COMMIT Affairs end of the normal boundaries, submitted to the Panel, the Panel was the permanent preservation of the updated database state.
— ROLLBACK abnormal end of the border affairs, withdraw services, data services before returning to the implementation of the initial state.

Database System Services support the following two modes:
— Automatic submitted mode: each SQL statement is an independent business database system for the implementation of the End of a SQL statement, will be automatically submitted to the Panel.
— Manually submitted to the Panel: by the database must be client-specific services in the beginning and the end of the border the border.

To MySQL as an example, if we start a MySQL client, it will be an independent database connection. Each database connection has a global variable @ @ autocommit that current affairs model, it has two selectable values:

— 0: that the manual mode.
— 1: The default value that the automatic mode.

SQL commands can be passed SELECT @ @ autocommit to view current affairs model. If you want to see the business model to replace manual mode, you can use the following SQL command SET autocommit = 0 to achieve.

To say a few more words, MySQL, a database table is divided into three types: INNODB, BDB, MyISAM. Of the first two types of database support services, and MyISAM table do not support the types of services. In the use CREATE TABLE command to build the table for the type of default MyISAM, if it wishes to change the DLL can be carried out as follows:

ALTER TABLE table_name TYPE = INNODB;
If you want to create in the designated table when the type of DLL can be carried out through the following:

CREATE TABLE table_name (
…..
…..
) TYPE = INNODB;

Well, we look at below in practice several applications in a statement affairs template.

1. MySQL.exe Client Services in a statement
We inserted a phrase as an example, of course, is if the automatic mode, each SQL statement is a separate business, we run directly on the following statement:
Mysql> INSERT INTO qq_account VALUES ( 'leon', 1000);

Below we look at the manual mode in order:
Mysql> SET autocommit = 0;
Mysql> BEGIN;
Mysql> INSERT INTO qq_account VALUES ( 'leon', 1000);
Mysql> COMMIT;

Such a transaction is complete, Below we look at how best to remove a panel, perhaps you have a very simple guess it
Mysql> BEGIN;
Mysql> INSERT INTO qq_account VALUES ( 'leon', 1000);
Mysql> ROLLBACK;

2. JDBC API in a statement Service
There is no difficulty, we have to look at the code directly:
Connection con = null;
Statement stmt = null;
Try (
Con = DriverManager.getConnection (dbUrl, dbUser, dbPwd);
/ / Set to manual mode Service
Con.setAutoCommit (false);
Stmt = con.createStatement ();
Stmt.executeUpdate ("……");
/ / Submitted to the Panel
Con.commit ();
) Catch (SQLException e) (
If (con! = Null) (
Try (
/ / Withdrawal operation is unsuccessful Service
Con.rollback ();
) Catch (SQLException e) (
……
)
)
……
Finally ()
If (con! = Null) (
Try (
If (stmt! = Null) (
Try (
Stmt.close ();
) Catch (SQLException e) (
……
)
)
Con.close ();
) Catch (SQLException e) (
……
)
)
)
Have nothing to say at all, the code has been like a detailed technical articles, it should be noted that the new Connection example, in default under the circumstances, the Panel submitted by the automatic mode, by means con.setAutoCommit (false) Manual mode to set submitted.

3. Hibernate API in a statement Service
Hibernate Packaging JDBC API and the JTA API, although applications can be bypassed Hibernate API directly through JDBC API and the JTA API to declare affairs, but this is not conducive to the development of cross-platform, we have only to consider the following Hibernate API Implementation:
Configuration config = new Configuration (). Configure ();
SessionFactory factory = config.buildSessionFactory ();
Session session = factory.openSession ();
Transation tx = null;
Try (
/ / Start a Service
Tx = session.beginTransaction ();
Account account = new Account ( "leon," 1000);
Session.save (account);
/ / Submitted to the Panel
Tx.commit ();
) Catch (HibernateException e) (
If (tx! = Null) (
Try (
/ / Withdrawal operation is unsuccessful Service
Tx.rollback ();
) Catch (HibernateException e) (
……
)
)
……
Finally ()
If (session! = Null) (
Try (
Session.close ();
) Catch (HibernateException e) (
……
)
)
)
The above code and JDBC API is very similar, it is worth noting that at any time, a Session allow only one not submitted affairs. Following the code for a Session at the same time did not submit a statement of the two affairs, which is not allowed:
Tx1 = session.beginTransaction ();
Tx2 = session.beginTransaction ();
……
Tx1.commit ();
Tx2.commit ();

Service with the problem, the first time in learning DB2, it would have been understood, but at that time in order to test, the teacher also did not talk too aware, has been determined to summarize, then, it has been dragged today … gossip talk less, and that is the beginning!

In with the environment, a database system will be at the same time for a variety of client services, at the same time running a number of services, visit these matters when the same data in the database, if there is no mechanism to take the necessary isolation, There will be complications, these problems can be summed up as a five:

— I lost Update: revocation of a business, and other services has been submitted to the updated data coverage.
— Dirty Reading: A Panel read another Affairs did not submit the updated data.
— Virtual Reading: A Panel read another Affairs has been submitted to the insertion of new data.
Reading - not repeat: a panel read another Affairs has submitted updated data.
— The second category lost update: a panel cover other branch of the updating of data has been submitted, this is not a special case of repeated reading.

Such a definition is too rigid, but also difficult to understand people, or through an example to careful analysis of each question with the following quote Sunweiqin teachers "proficient Hibernate: JAVA object persistence technology explain" inside examples personally think that this case is pretty good.

Now assume that a bank teller a transfer services and a cheque affairs operations with a bank account of the situation, first of all, we assume that the order of implementation of the two affairs, and not with the implementation, then the whole of the following steps:
— Bank customers in the banking outlook request withdrawals hundred yuan, the first cashier for account information, that the balance of deposits for 1,000 yuan.
— Cashier judgement from the balance of deposits greater than the amount paid to the clients 100 yuan, and the account balance of deposits to 900 yuan.
— Dealing with a teller transfer cheques, cheques to the account of this import 100 yuan, the first cashier for account information, that the balance of deposits for 900 yuan.
— Teller will deposit balance to 1,000 yuan.

Can be very clear that if the implementation of the order of these two services, there will not be any problems, but in reality is not so simple, if the two services by two cashiers at the same time there will be the implementation of it may be complicated The problem, we were to introduce the following:

1. First loss update

Transfer time teller Affairs Panel
————————————————– ——————————————
The start of T1
The start of T2
T3 account for the balance of 1000 yuan
T4 account for the balance of 1000 yuan
T5 into the balance of 100 yuan to 1,100 yuan
T6 submitted to the Panel
T7 out the balance of 100 yuan to 900 yuan
T8 revocation affairs for the restoration of the balance of 1,000 yuan
————————————————– ——————————————–
If over time in accordance with the concurrent execution of the order, due to transfer services on the cheque deposit balance was updated by the withdrawals covered by the withdrawal of services, customers will lose 100 yuan.

2. Dirty Reading

Transfer time teller Affairs Panel
————————————————– ——————————————–
The start of T1
The start of T2
T3 account for the balance of 1000 yuan
T4
T5 out of the balance of 100 yuan to 900 yuan
T6 account for the balance of 900 yuan (dirty Reading)
T7 revocation affairs for the restoration of the balance of 1,000 yuan
T8 import the balance of 100 yuan to 1,000 yuan
T9 submitted to the Panel
————————————————– ——————————————–
The cheque for the cash transfer Affairs Panel did not submit the updated data and the results of this enquiry on the basis of the update operation, if withdrawals Affairs was finally withdrawn, bank customers will lead to loss of 100 yuan.

3. Virtual Reading
Below we give an example to others to see what is false reading.
Time Registration Statistical Service
————————————————– ——————————————–
The start of T1
The start of T2
T3 registration statistics, a total of 10,000 people

T4 register for a new user
T5 submitted to the Panel
T6 registration statistics, a total of 10,001 people
T7 which in the end statistical data effectively?
————————————————– ———————————————

Statistical Service could not believe the results of inquiries because of the query results is uncertain at any time may be other matters to change.
For practical application, will not be in a panel on the same data for two, assuming that the statistical services in the T3 time statistics of the total number of registered customers, to execute a SELECT statement in T6 moment no longer query the database, but print results directly to 10000, Statistics with the results of this database is to be discrepancies between the data, exact, it reflects the state of T3 moment of the data, rather than the current state data. Should be based on actual need to decide whether to allow Virtual Reading. Statistical Service in the above example, if only to find out more or less the total number of registered customers, it allows virtual Reading If a panel, will be based on query results to make accurate decision-making, then we must take the necessary services isolation measures to prevent the false reading.

4. Not repeat Reading

Transfer time teller Affairs Panel
————————————————– ——————————————–
The start of T1
The start of T2
T3 account for the balance of 1000 yuan
T4 account for the balance of 1000 yuan
T5 out of the balance of 100 yuan to 900 yuan
T6 submitted to the Panel
T7 account for the balance of 900 yuan
T8 balance in the end is 1,000 yuan or 900 yuan?
————————————————– ———————————————

As indicated above, if the two cheques transfer services account deposit balance enquiries, but by a different query results, which makes bank teller could not believe query results, because the query results is uncertain at any time may be other matters to change.

5. Second category lost updated

Transfer time teller Affairs Panel
————————————————– ——————————————–
The start of T1
The start of T2
T3 account for the balance of 1000 yuan
T4 account for the balance of 1000 yuan
T5 out of the balance of 100 yuan to 900 yuan
T6 submitted to the Panel
T7
T8 import the balance of 100 yuan to 1,100 yuan
T9 submitted to the Panel
————————————————– ——————————————–
Each branch do not know the existence of other matters, a panel on the final record will be updated by the other matters covered by the records submitted by the update. As the example above, the Panel transfer cheques covering the balance of cash deposits made by the Panel on the upgrading of the losses in the banking final 100 yuan, Hey, do you like to start the second category is not lost update?
How can we eliminate these complications? Please listen next time …….