The article is constantly updated every week. It’s not easy to create original articles. It’s my greatest affirmation that “Sanlian” let more people see it. WeChat can search the public for the first time to read the official account of “backend Technology School” (usually one to two updates earlier than blogs).
Learning the relational database mysql is a good starting point. Most people are used to crud in their work. Can you still answer the interviewer’s soul? It is necessary for us to understand some deeper Database Fundamentals.
I sorted out the 10 frequently asked questions about MySQL transaction and storage engine in the interview. All you want to know is here.
What is business?
A transaction is a “set of atomic SQL queries,” or a separate unit of work. If the database engine can successfully apply all the statements of the group query to the database, the group query is executed. If any of these statements cannot be executed because of a crash or other reason, none of the statements will be executed. In other words, all statements in the transaction are executed successfully or failed.
Do you know transaction control syntax?
Begin or start transaction explicitly starts a transaction;
Commit / commit work are equivalent. Commit the transaction and make all changes to the database permanent;
ROLLBACK / ROLLBACK WORK。 Rolling back will end the user’s transaction and undo any uncommitted changes in progress;
Savepoint identifier creates a savepoint in a transaction. A transaction can have multiple savepoints;
Release savepoint identifier deletes the savepoint of a transaction;
Rollback to identifier rolls back the transaction to the mark point;
Set transaction is used to set the isolation level of a transaction. InnoDB storage engine provides transaction isolation levels of read uncommitted, read committed, repeatable read and serializable
Talk about what you understand in popular language
Take banking business as an example. User lemon has two bank cards. One is the salary card of CMBC of China Merchants Bank and the other is the savings card of ICBC. When the salary is paid on the 5th of each month, the 1 million of CMBC card of CMBC should be transferred to the savings card account of CCB. Remember that the bank abbreviation here is the name of the corresponding data sheet. If you can’t remember it, I’ll give you a reason.
China Merchants Bank: “deposit? Idiot
Industrial and Commercial Bank of China
China Construction Bank (CCB): deposit? Do you want to save it
Bank of China: “no deposit!”
Agricultural Bank of China (ABC): “ah, no deposit!”
CMSB: “deposit? SB！ “
CIB: “deposit 100.”
China Development Bank (CDB): “save some!”
HSBC: “or not!”
This transfer operation can be simplified into a transaction, including the following steps:
- Check whether the balance of CMBC account is greater than 1 million
- Subtract 1 million from CMBC account balance
- Add 1 million to ICBC account balance
The following statement creates a transfer transaction accordingly:
SELECT balance FROM CMBC WHERE username=’lemon’;
UPDATE CMBC SET balance = balance – 1000000.00 WHERE username = ‘lemon’;
UPDATE ICBC SET balance = balance + 1000000.00 WHERE username = ‘lemon’;
What is the acid feature of a transaction?
Acid is actually the acronym of transaction characteristics. Its specific meaning is as follows:
- Atomicity a transaction must be regarded as an indivisible minimum unit of work. All operations in the whole transaction either commit successfully or fail to roll back. For a transaction, it is impossible to perform only part of the operations.
- Consistency database always changes from one consistent state to another. In the previous example, consistency ensures that even if the system crashes between the third and fourth statements, the CMBC account will not lose 1 million yuan. Otherwise, lemon will cry to death because the transaction is not committed, so the changes made in the transaction will not be saved to the database.
- In general, changes made by one transaction are not visible to other transactions until they are finally committed. In the previous example, if someone else is ready to deposit money into lemon’s CMBC account when the third statement and the fourth statement are executed, there are still 1 million in lemon’s CMBC account.
- Once a transaction is committed, its changes will be permanently saved to the database. Even if the system crashes, the modified data will not be lost. Persistence is a bit of a vague concept, because actually there are many different levels of persistence. Some persistence policies can provide very strong security, while others may not. What’s more, “it’s impossible to have a strategy that can guarantee 100% persistence.” otherwise, what else do you need to do with backup.
What are dirty reading, unrepeatable reading and unreal reading?
After transaction a modifies the data and before committing the data, another transaction B reads the data. If there is no control, transaction B reads the data modified by a, and then a modifies the data and then commits again, the data read by B is dirty data. This process is called dirty read.
At a certain time after reading some data, a transaction reads the previously read data again, but finds that the read data has changed or some records have been deleted.
When transaction a reads records in a certain range according to query conditions, transaction B inserts new records meeting the conditions in the range. When transaction a queries records by conditions again, new records meeting the conditions will be generated (phantom row)
What is the difference between nonrepeatable reading and unreal reading?
- The key point of non repeatable read is modification: in the same transaction, under the same conditions, the first read data is different from the second read data. (because there are other transactions that commit changes in the middle)
- The key point of phantom reading is to add or delete: in the same transaction, under the same conditions, the “number of records” read out the first time and the second time is different. (because there are other transactions committed to insert / delete)
Do you know the four isolation levels of SQL? What is the specific solution to what problem
SQL implements four standard isolation levels, each of which specifies the changes made in a transaction, which are visible within and between transactions, and which are not. Low level isolation level generally supports higher concurrent processing and lower system overhead.
Each isolation level can solve dirty read, non repeatable read and unreal read. Isolation levels have their own strengths, and there is no perfect solution. Talking about the specific implementation from the business scenario is playing rogue.
Which storage engines in MySQL support transactions?
InnoDB and NDB cluster storage engines in MySQL provide transaction processing capabilities, as well as other third engines supporting transactions.
What is auto submit?
MySQL uses auto submit by default
AUTOCOMMITpattern. In other words, if a transaction is not explicitly started, each query is committed as a transaction.
For transactional tables such as MyISAM or memory table, modify the
AUTOCOMMITIt won’t make any difference. For this kind of watch, there is No
ROLLBACKIt can be said that the concept has always been equivalent to
Can storage engines be mixed in transactions?
Try not to use multiple storage engines in the same transaction. MySQL server layer does not manage transactions, and transactions are implemented by lower storage engines.
If you mix transactional and non transactional tables (such as InnoDB and MyISAM tables) in a transaction, there will be no problem with normal commit.
However, if the transaction needs to be rolled back, the changes on the non transactional tables cannot be undone, which will cause the database to be in an inconsistent state, which is difficult to repair and the final result of the transaction cannot be determined. Therefore, it is very important to choose the appropriate storage engine for each table.
What are the types of MySQL storage engines?
The most commonly used storage engines are InnoDB engine and MyISAM storage engine. InnoDB is the default transaction engine of MySQL.
To view the currently supported engines for database tables:
show table status from ‘your_db_name’ where name=’your_table_name’;
The ‘engine’ field in the query result table indicates the type of storage engine.
Features and application scenarios of InnoDB storage engine?
InnoDB is the default “transaction engine” of MySQL. It is set to handle a large number of short-lived transactions. In most cases, short-term transactions are normally committed and rarely rolled back.
For more information about InnoDB transaction models, please refer to the official MySQL manual. Here is a link: https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-model.html
InnoDB in the modern version of MySQL is called InnoDB plugin in history. This MySQL plug-in was developed in 2008. It was only after Oracle acquired sun in 2010 that the released MySQL 5.5 officially replaced the old version of InnoDB with InnoDB plugin. So far, the “spare wheel” has successfully become the Royal engine of MySQL instead of a plug-in. You can see that every plug-in works so hard.
Mvcc (multi version concurrency control) is used to support high concurrency. And realize four standard isolation levels, through the gap lock
next-key lockingStrategy to prevent the appearance of phantom reading.
The engine’s tables are built based on cluster index, which has high performance for primary key query. But its secondary index
secondary indexA non primary key index must contain a primary key column, so if the primary key column is large, all other indexes will be large. Therefore, if there are more indexes on the table, the primary key should be as small as possible. In addition, the storage format of InnoDB is platform independent.
InnoDB has done a lot of optimization, such as the predictable read ahead method of disk data reading, the adaptive hash index automatically creating hash index in memory to speed up the read operation, and the insert buffer that can speed up the insert operation.
InnoDB supports real hot backup through some mechanisms and tools. Other storage engines of MySQL do not support hot backup. To obtain a consistent view, you need to stop writing to all tables. In the read-write mixed scenario, stopping writing may also mean stopping reading.
Characteristics and application scenarios of MyISAM storage engine?
MyISAM is the default storage engine for MySQL 5.1 and earlier. MyISAM provides a large number of features, including full-text indexing, compression, spatial function (GIS), etc., but MyISAM does not “support transaction and row level locking”. It can still be used for read-only data, or for small tables that can tolerate repair operations.
MyISAM “does not support row level locking, but locks the entire table.”. When reading, the shared lock will be applied to all tables to be read, while the exclusive lock will be added to the table when writing. However, when a table has read operations, it can also insert new records into the table, which is called concurrent insertion.
The MyISAM table can be checked and repaired manually or automatically. However, unlike transaction recovery and crash recovery, it may cause some “data loss”, and the repair operation is very slow.
For the MyISAM table, even if
TEXTEqual length fields can also be indexed based on their first 500 characters. MyISAM also supports full-text indexing, which is a word segmentation based index that can support complex queries.
DELAY_KEY_WRITEWhen each modification is completed, the modified index data will not be written to the disk immediately, but to the key buffer in memory. The corresponding index block will be written to the disk only when the key buffer is cleaned or the table is closed. This method can greatly improve the write performance, but it will cause “index corruption” in case of database or host crash, which needs to be repaired.
InnoDB vs. MyISAM
Having said so many estimates, I didn’t remember at a glance. Here is a list of the main differences between the two engines, as shown in the figure below.
Other storage engines
MySQL also supports some other storage engines, such as memory engine, NDB cluster engine, and CSV engine. Since these engines are not commonly used by InnoDB and MyISAM, they are not introduced here. If you are interested, you can refer to the MySQL document to understand. Here is also an official link: https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html
Two more words
This is the basic part of MySQL. I try to sort out this knowledge in the form of easy to understand and chart. The more basic and underlying knowledge is, the easier it is to be investigated and mastered. The above knowledge points may become an investigation point in the interview. I believe that after reading, I should have a relatively complete understanding of MySQL affairs and storage engine.
Finally, thank you for your reading. The purpose of this article is to share your understanding of knowledge. If there are obvious mistakes in the article, please point out that we can learn from the discussion together.
You can search the official account of WeChat, “backend Technology School”, reply to “information”, “1024”, and have various programming learning materials that I have prepared for you. Articles are updated every week, see you next time!