MySQL database technology (19) _ MySQL-mysql tutorial

Source: Internet
Author: User
MySQL database technology (19) 3.9 features not supported by MySQL

This section describes the functions available in other databases but not in MySQL. This section describes what functions are omitted and what to do when these functions are required. In general, MySQL ignores some features because they have negative performance impact. Some functions are on the developer's plan List. Once a method is found to implement the corresponding functions without affecting the performance, they will be implemented.

■ Sub-selection. Sub-selection is a SELECT statement nested in another SELECT statement, as shown in the following query:

SELECT * FROM score
WHERE event_id IN (SELECT event_id FROM event WHERE type = "T ")

The sub-selection is intended to be provided in MySQL 3.24 and will not be ignored by that time. However, by that time, many queries written using sub-choices can also be written using connections. For more information, see section 3. 8. 1 "write sub-selection as a connection ".

■ Transaction processing and submission/Rollback. Transaction processing is a set of s q l statements that are executed by other clients as a whole without interruption. The submit/roll-back function allows you to specify several statements to be executed as a whole or not. That is, if any statement in the transaction fails, all statements executed before the statement are revoked.
YSQL automatically synchronizes a single SQL statement to avoid mutual interference between clients. (For example, two clients cannot simultaneously write data to the same table .) In addition, you can use lock tables and unlock ta B L ES to form a whole of several statements, which enables you to complete operations that cannot be satisfied by the concurrency control of a single statement. A problem related to MySQL transaction processing is that it cannot automatically organize several statements, and it cannot be rolled back if one of these statements fails.

An example is provided to illustrate the usefulness of transaction processing. If you work in the clothing sales industry, the inventory quantity should be updated whenever your sales staff make a sale. The following example illustrates the problems that may occur when multiple sales personnel update the database at the same time (for example, the initial shirt inventory is 4 7 ):

T1 sales staff 1 sell 3 shirts

T2 sales personnel search current shirt count (4 7 ):

SELECT quantity FROM inventory WHERE item = "shirt"

T3 sales staff 2 Sell 2 shirts

T4 sales staff 2 search current shirt count (4 7)

SELECT quantity FROM inventory WHERE item = "shirt"

T5 salesperson 1 calculates the new inventory quantity as 47-3 = 44 and sets the shirt count to 44:

UPDATE inventory SET quantity = 44 WHERE item = "shirt"

T6 sales staff 2 calculate the new inventory quantity to 47-2 = 45 and set the shirt count to 45:

UPDATE inventory SET quantity = 45 WHERE item = "shirt"

At the end of the event sequence, you have sold 5 shirts, but the inventory is 45 instead of 4 2. The problem is that if you view the inventory in one statement and update its value in another statement, this is a multi-statement transaction. The activities performed in the second statement depend on the values retrieved in the first statement. However, if an independent transaction is processed within the overlapping time range, the statements processed by each transaction will be entangled and interfere with each other. In a transaction-oriented database, each salesperson's statement can be executed as a transaction. in this way, the salesperson's 2 statement will not be executed until the salesperson's 1 statement is completed. In MySQL, two methods are available for this purpose:

■ Method 1: execute a group of statements as a whole. You can use lock tables and unlock tables to organize statements together and execute them as an atomic unit: LOCK the TABLES to be used, publish queries, and release these locks. This prevents others from using these tables when you lock them. With table synchronization, the inventory is as follows:

T1 sales staff 1 sell 3 shirts

T2 salesperson 1 requests a lock and retrieves the current shirt count (47)

Lock tables inventory WRITE

SELECT quantity FROM inventory WHERE item = "shirt"

T3 sales staff 2 Sell 2 shirts

T4 salesperson 2 tries to get a lock: this is blocked because salesperson 1 has occupied the lock:

Lock tables inventory WRITE

T5 salesperson 1 calculates the new inventory quantity to 47-3 = 44, sets the shirt count to 44, and then releases the lock:

UPDATE inventory SET quantity = 44 WHERE item = "shirt"

UNLOCK TABLES

T6 now sales staff 2 lock request successful. Sales staff 2 search current shirt count (44)

SELECT quantity FROM inventory WHERE item = "shirt"

T7 salesperson 2 calculates the new inventory quantity as 44-2 = 42, sets the shirt count to 4 2, and then releases the lock:

UPDATE inventory SET quantity = 42 WHERE item = "shirt"

UNLOCK TABLES

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.