The introduction
I’ll go back to the database series today. In this article, part of the mysql database series, we will discuss common questions about transactions. So, specific topics have the following :1, tell why to use transactions? What are the four characteristics of transactions? Do you know the isolation level of transactions? Which one do you use in production? 2. How to implement ACID in Innodb? Redo log and binlog are consistent. 4. What are the disadvantages of big business? Did you encounter large transactions on production? How did you troubleshoot and solve it? 5, have you encountered the database downtime and restart, transaction loss situation? 6. How is repeatable read implemented?
Again and again, look at every question! All high-frequency questions! Don’t miss out!
The body of the
**1. Why do you use transactions? What are the four characteristics of transactions? Do you know the isolation level of transactions? Which one do you use in production? ** Answer: Why transaction?
This question is answered in terms of their four major features of transactions: Atomicity, Consistency, Isolation, and Durability. For example, from the atomic point of view, the transaction can only be committed when the money from Joe’s account has been transferred and the money from Joe’s account has been received. If the atomicity is not guaranteed, there will be a situation where Joe’s money is transferred and Joe doesn’t receive it!
Come on! This easy answer, I will not one example, too!
What isolation level is used for production? Read Commited or Repeatable. For example, I used Read Commited because the isolation level is sufficient and gap locks are not needed! See the article “What transaction isolation levels mysql should choose in Internet Projects” for more details!
Also, remember that Repeatable is the default isolation level! Read Uncommitted: A transaction can Read data that another transaction has not committed. This isolation level is not used. There is another isolation level, Seriallzable, at which MVCC mechanisms are not sufficient and database concurrency is too poor to use this isolation level.
Ps: This question really tests your understanding of the isolation levels, so keep in mind the differences between the isolation levels!
**2. How to implement ACID in Innodb? ** 解 析 : transaction ACID in Mysql And the short answer is,
- (1) Use undo log to ensure atomicity
- (2) Use redo logs for persistence
- (3) Use lock and MVCC mechanism to ensure isolation
- (4) Ensure consistency through atomicity, persistence and isolation
** redo log and binlog are consistent. ** Answer: Remember the difference between redo log and binlog! The redo log records physical changes to data. The redo log records physical changes (XXX was modified on page XXXX). When we modify the data, the memory is written, but the data is not actually written to disk. When our database is down, we can restore the data according to the redo log.
A binlog records logical changes to data. In statement mode, a binlog records SQL statements such as UPDATE, DELETE, and INSERT, which are used to copy and restore data.
These two functions are very similar, both are used for “recovery”! Therefore, the two logs must be logically consistent, or data corruption will occur. For example, we write redo log and then binlog. A log outage occurred after redo log writing and the binlog did not have time to write. After restart, data can be restored according to the redo log, but binlog does not record this statement. So, when we use this binlog to restore data, we will lose data!
How to solve mysql? This is an internal XA transaction for mysql! Log two-phase commit protocol! In other words, the commit is divided into two phases: prepare and COMMIT!
Prepare: Write the redo log and set the rollback segment to prepared. Binlog does not perform any operations.
Commit: InnoDB releases the lock, releases the rollback segment, sets the commit state, writes to binlog, and commits to the storage engine layer.
How to recover mysql database from crash? 1> During crash recovery, scan the last Binlog file and extract the XID from it; 2> InnoDB maintains a linked list of prepared transactions and compares the xids of these transactions with those recorded in the binlog. If the xids are present in the binlog, the transaction is committed; otherwise, the transaction is rollback.
Finally, this question blue green factory, boiled water group, the universe have asked!
**4, What are the disadvantages of big business? Did you encounter large transactions on production? How did you troubleshoot and solve it? ** Answer: Big transactions, some articles also called long transactions, as the name implies, the execution of a long transaction!
As for disadvantages, for example, the transaction execution time is too long, which will cause a lot of blocking and lock timeout, and easily cause master/slave delay. Also, rolling back large transactions that fail can be time-consuming… (a thousand words omitted)
How to check? So easy! Monitor the Information_schema. Innodb_trx table and set the long transaction threshold to alarm/kill if it is exceeded;
The following statement queries transactions that last more than 60 seconds
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60;
Copy the codeIn production, statements that monitor large transactions are configured with periodic scripts for monitoring.
How to solve it? According to the business scenario, optimize SQL, split a large transaction into multiple small transactions, or shorten the transaction execution time.
**5, have you ever encountered the database downtime and restart, transaction loss situation? Innodb_flush_log_at_trx_commit specifies the redolog flush_commit time for innodb_flush_log_at_trx_commit time for innodb_flush_redolog. Innodb_flush_log_at_trx_commit =1 and sync_binlog=0 innodb_flush_log_at_trx_commit=1 and sync_binlog=0 MySQL, however, does not control the flushing of binlog; the operating system controls the flushing of its cache.
If you answer this configuration, the risk is that if the system goes down, all the binlog information in the binlog_cache will be lost. Therefore, you can also combine your own company background to answer. For example, our company is small and we don’t have the technical know-how to change the default configuration of mysql. However, I have learned that in the case of this configuration, the values of these two parameters are XXX. Theoretically, there is a risk of XXX when the operating system breaks down. However, operating system outages are rare, so we don’t see many lost transactions.
Innodb_flush_log_at_trx_commit =1 and sync_binlog=1 Redolog is persisted to disk and binlog is persisted to disk each time a transaction commits.
This configuration is undoubtedly the most consistent, with no data loss, but the worst performance. If you answered this configuration, you can say, our system involves some money related business logic, would rather be slow, also can not be consistent error, so our configuration is XXX. As far as performance is concerned, we can improve with some caching, or asynchronous design.
Innodb_flush_log_at_trx_commit =2 and sync_binlog=0; innodb_flush_log_at_trx_commit=2; MySQL actively fsync data in OS cache in batches. MySQL does not control the flushing of binlog; the operating system controls the flushing of its cache. The risk is that if the operating system goes down, it loses data.
Concurrency is best in this configuration. If you answer this configuration, you can say that our business has relatively high requirements for concurrency, so we have modified XX parameters. However, the operating system may break down and data may be lost. However, in the real world, the probability of an operating system crash is much lower than the probability of a MySQL application crash… (Free play)
Not one example, can make sense! Remember, it’s all in one mouth, it’s yours! Say impassable, go back to sum up, change a continue to say! No other, only familiar!
**6, how is repeatable read implemented? Frankly speaking, I don’t know which Angle the interviewer wants to hear. I can only say that I understand, basically has the following two reasons: (1) using clearance lock, prevent the occurrence of phantom read, ensures repeatable read magic problem exists because of new or updated, then conducts range queries if inquiry (lock), will appear inconsistent problems, then use different row lock has no way to meet the requirements, A range of data needs to be locked.
(2)MVCC snapshot generation timing is different. Under the repeatable read isolation level, the read View is created only when the first SELECT request is made in the transaction, so you cannot read the changes committed by other transactions. At the read-committed isolation level, each SELECT gets the latest Read View, so you can read the data committed by other transactions.
Because the View is generated at different times, it is repeatable to read. The answer to this question, there are different opinions can be discussed. Anyway, I don’t understand, this question is to ask what on earth!
conclusion
OK, hope you found this article useful!
This article doesn’t cover the whole issue of transactions, because it’s often asked in interviews in conjunction with Spring transactions and distributed transactions, such as when spring transactions fail, blah blah blah.
About the interview interview I also through some channels to sort out the need for big factory real interview mainly: Ant Financial, Pinduoduo, Ali Cloud, Baidu, Vipshop, Ctrip, and so on primary, intermediate, advanced Java interview set, with super detailed answers, I hope to help you.
