preface
A dusty man in a plaid shirt walked towards him, holding a MacBook Pro in his hand, looking at the sparse hair, and the calm eyes.
My heart a quiver, I go, this is the architect, the architect to face my technical side, my heart suddenly not calm, the surface is very stable in fact a batch of panic.
Sure enough, he had my resume in his hand, took a quick look at it, and then looked at me out of the corner of his eye.
Introduction to Mysql Transactions
“Interviewer:” Your resume says that you are proficient in Mysql optimization methods, so why don’t you tell me your understanding of Mysql business first?
My in the mind pleased once, this is simple, elder brother I but Peking University (back big), come before interview again, have early preparation of, do not say two words, go up is back.
“I:” Ok, a database transaction is a logical processing unit of a database consisting of a set of SQL statements in which all SQL operations either succeed or fail.
“I:” here is A set of SQL operations, take A simple and classic example is transfer, transaction A to transfer, then transfer out of the account to deduct money, transfer into the account to add money, these two operations must be executed successfully at the same time, in order to ensure the consistency of data.
Interviewer: “Just now you mentioned data consistency, do you know the characteristics of transactions? Say what you understand.
ACID profile
I: Transactions in Mysql include Atomicity, consistency, Isalotion, and durability (ACID for short).
“I:” atomicity refers to the atomicity of a transaction. All changes to the data either succeed or fail. The atomicity of a transaction is a log-based Redo/Undo mechanism.
“I:” consistency means that the state of the transaction must be consistent before and after execution. It can be understood as data consistency. The isolation focus refers to the isolation of transactions from each other and is closely related to the isolation level set by the transaction.
“I:” persistence means that after a transaction is committed, the state of the transaction is persisted to the database, i.e., the transaction commits, and new and updated data is persisted to the library.
“I:” In my understanding, atomicity, isolation and persistence exist to ensure consistency, which is also the ultimate goal.
Secretly happy in the heart, back over, usually back more, the interview will say, fortunately, not difficult me.
Principle of ACID
Interviewer: “Just now you said atomicity is a log based Redo/Undo mechanism. Can you talk a little bit about Redo/Undo?”
Aha? What did I say? I accidentally buried a big thunder. Don’t panic, brother brain still have goods, pretend to be thoughtful of stop for a few seconds, then back.
The “me:” Redo/Undo mechanism is simple and writes all updates to the data to the log.
The “I:” Redo log is used to log changes to a block of data. It can be used to restore data that was not written to the data file after a successful transaction. The Undo log is used to record the values before the data is updated to ensure that the data can be rolled back if the data fails to be updated.
“I:” If the database crashes during execution, you can use this log to roll back the previous successful operations to achieve transaction consistency.
“Interviewer:” Can you give me a scenario and describe how it would work?
If at some point the database crashes, transaction A and transaction B are executing before the crash, transaction A has committed and transaction B has not committed. When the database is restarted for cra-recovery, committed transaction changes are written to the data file through the Redo log, and uncommitted transactions are rolled back through the Undo log.
Transaction Isolation level
Interviewer: You also mentioned the isolation level of transactions earlier, can you say something about that?
READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE
“I:” read not commit to the uncommitted data of another transaction, dirty read problems, read submitted is solved the dirty, appeared not repeatable read, namely read at any time in a single transaction data may be different, may be limited by other transactions to modify data is submitted, usually for the update operation.
The “I:” repeatable read solves the previous problems of unrepeatable reads and dirty reads, but brings the problem of phantom reads, which are usually for inser operations.
Select * from User where id=100; select * from User where id=100; select * from User where id=100;
Select * from row where id=100; select * from row where id=100; select * from row where id=100;
“Interviewer:” young man, can you demonstrate? I’m not very good. Can you teach me? My computer is here, you demonstrate and I have a look.
Man’s mouth deceivingly ghost, I believe you a ghost, you this bad old man is very bad, come out to pretend X always want to return, can only silently tearful knock it over.
“I:” first create a User table, as a test table, test table with three fields, and insert two test data.
CREATE TABLE User ( id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), age INT DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=gb2312; Copy the code
INSERT INTOuserVALUES (1, 'zhangsan', 23); INSERT INTOuserVALUES (2, 'lisi', 20);
“I:“The default isolation level of Mysql isREPEATABLE-READ.
READ UNCOMMITTED.
“I:“When the isolation level is set, it is invalid for previously opened sessions. You must reopen a window to set the isolation level for it to take effect.
begin/start transactionCommit, or rollback.
The begin/start transaction commands are executed. They are not the starting point of a transaction. The first SQL statement after they are executed indicates that the transaction is actually started.
“I:“Select * from row 1 where id=1; select * from row 1 where id=1; select * from row 1 where id=1;
“I:“The same is true for a read commit. When you start a transaction, the first transaction queries the data, and then the second session updates the data, but it’s not yet committed, so you select the first session, and the data hasn’t changed, and then you commit the second session, In the first session, you select the changed data again.
“Interviewer:” Young man, can you draw a picture of his process? I’m a little confused about what you’re saying. I don’t understand it all.
Ten thousand horses were flying over me, and I wanted to cry. The interviewer was really difficult to wait on. At that moment, I took out a pen from my left hip pocket and a piece of paper from my right hip pocket and began to draw.
This is the interviewer took my picture to have a look, slightly nodded, the mouth of the mouth with a smile, I think you this bad old man should not be difficult for me.
“Interviewer:” Well, you go on to demonstrate your repeatable reading.
“I:“Ok, and then repeatable read, same thing as before.
REPEATABLE READSelect rows in the first transaction and update rows in the second transaction. You can see that even if the second transaction has been committed, the first transaction still has the same select data. This solves the non-repeatable read problem.
“I:” the difference here is that in Mysql, the default non-repeatable read isolation level also solves the phantom read problem.
“I: Select * from row 3 where id=3; select * from row 3 where id=3; select * from row 3 where id=3; No rows with id=3.
“I:” at the end of the serialization, the style step is the same, and the result is the same as the default Mysql repeatable read isolation level, the serialization of the execution process is equivalent to the transaction execution sequence, I will not demonstrate here.
From top to bottom, the isolation effect is increasing, but the performance is getting worse.
Lock mechanism of Mysql
Interviewer: “Oh? Performance is getting worse? Why is the performance getting worse? Can you tell me why?
Oh, my mouth, if I don’t say a word, I’ll die. That’s good. This is the underlying implementation principle, from pretending to be thoughtful, to actually being thoughtful.
Mysq Locks can be divided into “Shared Locks”, “Exclusive Locks”, “gap Locks”, “Record Locks” and “table Locks”.
“I:” locking at the four isolation levels is definitely performance consuming, but read uncommit is not locked at all, so it has no isolation effect, so it also has the best performance.
“I:” is a big lock for serialization of, read and Shared lock, can’t write, write, home is the exclusive lock, block other transactions to write and read, if other affairs for a long time can’t write will be directly submitted to the timeout, so its performance is the worst, for it seemed to have no concurrency.
“I:” for both read commit and repeatable reads, the implementation of both of them is to solve the data problem, and then have some parallel distribution, so the implementation of locking mechanism is much more optimized than serialization, improving concurrency, so the performance will be better.
Their underlying implementation is MVCC (Multi-version Concurrency Control).
“Interviewer:” can you say the concept of this a few locks first? I don’t quite understand, tell me what you understand.
“I:” Oh, ok, shared lock is for the same data, multiple read operations can be performed at the same time, simply read lock, no write and can be read in parallel; An exclusive lock is used for a write operation. If the current write operation has not completed, it blocks all other write locks and read locks.
Row lock and table lock are divided from the granularity of the lock. Row lock locks the current data row. The lock granularity is small, the lock is slow, the probability of lock conflict is small, the concurrency is high, row lock is also one of the differences between MyISAM and InnoDB, InnoDB supports row lock and supports transaction.
“I:” on the other hand, table locks have large lock granularity, fast locking, and low overhead, but the probability of lock conflict is high and the concurrency is low.
There are two types of Gap Locks: Gap Locks and next-key Locks. Gap Locks lock the interval between two indexes. For example, select * from User where ID >3 and ID <5 for update, we add Gap Locks between the interval (3, 5).
“I: Select * from User where id>=3 and id=<5 for update; select * from User where id>=3 and id=<5 for update; Next Key Locks will be added between the interval [3,5].
Interviewer: “When will Mysql lock?”
Lock in share mode or for UPDATE (select) lock in share mode or for update (select)
Underlying implementation principles of transactions
Interviewer: you mentioned MVCC (multi-version concurrency control) above, can you explain how it works?
“Me:” uses the conformance view when implementing MVCC to support read commit and repeatable read implementations.
“I:” to achieve a repeatable read isolation level, you only need to create a consistency view, also called a snapshot, at the start of the transaction. This consistency view is shared by subsequent queries, and changes made by subsequent transactions are not visible to the current transaction, thus achieving repeatable reads.
“I:” while read commit, each statement recalculates a new view, which is the difference between repeatable read and read commit at the MVCC implementation level.
Interviewer: do you know how snapshots (views) work at the bottom of the MVCC?
Each transaction in InnoDB has its own transaction ID, which is unique and increments.
For each row in Mysql, it is possible to have multiple versions. Each time a transaction updates data, it generates a new version of the data and assigns its data ID to the current version of the row trx_ID.
“Interviewer:” young man, can you draw a picture for me to see? I don’t quite understand.
What can I do? Totally no way, can only take out pen and paper from the bottom pocket Li, quickly drew up, quite this interview if but blood loss, wasted my two pieces of paper and pen water, how expensive, can only lose everything.
Versions 1 and 2 do not actually exist, and U1 and U2 in the figure are actually undo logs. The v1 and V2 versions are calculated from the current v3 and undo logs.
Interviewer: “So for a snapshot, do you have any rules to follow?
“I:” Well, in addition to updates to itself, there are three other cases for a transaction view: versions that are not committed are not visible; The version has been committed, but it is not visible if it was committed after the view was created; The version has been committed and is visible if it was committed before the view was created.
“Interviewer:” If two transactions perform a write operation, how can you ensure concurrency?
If transaction 1 and transaction 2 are required to perform an update, transaction 1 obtains a row lock first, and transaction 2 obtains a row lock first, but transaction 1 already has a row lock, transaction 2 can only wait.
If transaction 1 has not released the lock for a long time, transaction 2 will have a timeout exception.
“Interviewer:” this is the condition after update where if there is an index?
“Me:” Well, yes.
“Interviewer:” What about without an index? Can’t quickly locate the data row?
If there is no index, all rows will be acquired and locked. Mysql will filter the rows again and release the lock. Only the rows that meet the criteria will continue to hold the lock.
The performance cost of “me:” is also high.
Interviewer: “Uh-huh
At this time, the interviewer looked at his watch, more than a clock had passed, and it was time for dinner. I thought he should be hungry, so he would not continue to ask. They continued to freeze for 30 seconds, and he finally opened his mouth.
“Interviewer:” young man, now the time has come to dinner, today’s interview is over, you go back to wait for the notice.
“I:”…