In 2020, SO far, I have received interview invitations from alibaba, Tencent, Meituan, Pinduoduo, JINGdong, Kuaishou and other Internet giants. Job hunting is a long process, involving job selection, resume delivery, resume evaluation, technical interviews, HR interviews and so on.
I find that in technical interviews things come up very, very often, but luckily I was prepared for the interview. Today combined with the interview experience to write a face – to – face database transaction related knowledge summary.
The transaction
A program execution unit that accesses or changes the contents of a database, or a single execution of one or more SQL statements, is considered a transaction.
- A transaction has a start and an end, and needs to be committed or undone before the end.
Example:
-
Macro: independent and complete
-
Micro: staggered execution
The ACID properties
Atomicity: A set of update operations in a transaction are atomically indivisible, either all or none.
Consistency: Ensure that the operation state of the transaction is correct and consistent with the operation rules. The result of the transaction must move the database from one consistent state to another.
- There are no three typical inconsistencies
Isolation: Multiple transactions executed concurrently do not affect each other.
Persistence: Committed transactions are persistent updates to the database. The impact of the cancelled transaction is recoverable.
- After a transaction is revoked, the disk is not affected
Concurrency control
- Three typical inconsistent states
-
Lost to modify
-
Can’t repeat
-
Dirty read
-
Based on the block
-
Based on the back
Transaction scheduling
- Serializable scheduling
Serializable equivalent serial sequences are not necessarily unique
classification
Implicit transactions: such as INSERT, DELETE, UPDATE, SELECT, etc.
Explicit transactions: marked with an obvious beginning and end.
Two-phase commit
Second order commit protocol, third order commit protocol and Paxos algorithm.
Only coordinators have a timeout mechanism, which means that if no messages from the cohort are received within a certain period of time, it will fail by default.
The two-phase commit protocol can guarantee the strong consistency of data, that is, the atomicity of distributed transactions: all nodes do all or nothing.
It is a distributed algorithm that coordinates all distributed atomic transaction participants and decides to commit or cancel (rollback).
The preparation Phase
The coordinator will notify the transaction participant to prepare to commit or cancel the transaction, write local redo and undo logs, but do not commit, and then proceed to the voting process.
The process is as follows:
-
Write local log BEGIN_COMMIT and enter WAIT state.
-
Send a “VOTE_REQUEST” message to all participants
-
Wait for and receive the response to VOTE_REQUEST sent by the participant. The participant responds to the “VOTE_ABORT” or “VOTE_COMMIT” message to the coordinator.
Commit Phase
The coordinator will make a decision based on the results of the first phase of voting: submit or cancel.
The coordinator will notify all participants to commit the transaction if and only if all participants agree to commit the transaction, otherwise the coordinator will notify all participants to cancel the transaction. After receiving the message from the coordinator, the actor performs the action in response.
The process is as follows:
-
If the VOTE_ABORT message is received from any of the participants; Write the GLOBAL_ABORT log and the ABORT state is changed. Send the GLOBAL_ABORT message to all participants.
-
If the VOTE_COMMIT message from all participants is received, Write local GLOBAL_COMMIT logs to enter the COMMIT state. Send the “GLOBAL_COMMIT” message to all participants.
-
Wait for and receive the acknowledgement response message from all participants. Once the acknowledgement message from all participants is received, the local END_TRANSACTION log writing process is complete.
Two-phase commit defects
Synchronization blocking problem. During execution, all participating nodes are transaction blocking.
When a participant occupies a common resource, other third party nodes have to block access to the common resource.
Single point of failure. Due to the importance of the coordinator, once the coordinator fails, participants will block forever.
If the coordinator is down, you can re-elect a coordinator, but you cannot resolve the problem of participants being blocked because the coordinator is down
The data are inconsistent.
During the commit phase, after the coordinator sent the commit request to the participants, only a few participants received the commit request due to a failure.
Three sections of submission
A timeout mechanism has been set for coordinators and Cohort
- CanCommit phase
The coordinator sends a COMMIT request to the participant, who returns a Yes response if he can commit, or a No response otherwise.
- PreCommit phase
Coordinators decide whether to continue PreCommit transactions based on the response of Cohort.
If a Coordinator receives Yes responses from all the Cohort, it will pre-execute transactions: send pre-submit requests. A Coordinator is sending a PreCommit request to the Cohort and entering the Prepared phase.
- Transaction precommit. Cohort will perform transaction operations after receiving PreCommit requests
The undo and redo information is recorded in the transaction log.
- Respond to feedback. If the Cohort has successfully executed transactions, return an ACK response, and start waiting for the final instruction.
If there is a No response from any Cohort sent to the Coordinator, or the Coordinator does not receive a response from the Cohort after waiting for a timeout, the transaction will be interrupted.
-
Send interrupt request. Coordinator sends abort requests to all Cohort.
-
Interrupt the transaction. Cohort interrupts transactions after receiving an ABORT request from a Coordinator.
- DoCommit phase
commit
-
Send the submit request. If a Coordinator receives ACK responses from the Cohort to be sent, the Coordinator will change from the pre-submission state to the submission state. And send doCommit requests to all Cohort.
-
Transaction commit. After receiving doCommit requests from Cohort, perform formal transaction commits and release all transaction resources after completing transaction commits.
-
Respond to feedback. After the transaction is submitted, an ACK response is sent to the Coordinator.
-
Complete the transaction. A Coordinator receives all ACK responses for the Cohort and completes the transaction.
Interrupt the transaction
- If a Coordinator has not received ACK responses from the Cohort, it will perform interrupted transactions.
The difference between three-phase and two-phase commit protocols
Three-phase commit is a “non-blocking” protocol.
Three-phase commit inserts a preparation phase between the first and second phases of the two-phase commit to avoid delays caused by a mediator crash or an error in which the participant is in an “indeterminate state” of whether to commit or abort.
PreCommit is a buffer that ensures that the states of participating nodes are consistent until the final commit phase.
disadvantages
If the Coordinator sends an ABORT request after entering the PreCommit server, the Coordinator assumes that only one Cohort is received and the abort operation is carried out, and other Cohort whose system status is unknown will be committed based on 3PC. In this case, the system status is inconsistent.
Paxos algorithm
At present, there is another important algorithm is Paxos algorithm, Zookeeper adopts the improvement of Paxos algorithm.
conclusion
Let’s have fun. Let’s have fun. Don’t joke about the interview.
Business in the interview is very, very many times, once asked, we must answer the comprehensive, don’t forget, answer to the point. You should lay down the basics before the interview, including ACID properties of transactions, concurrency control of transactions, transaction scheduling, and distributed commit of transactions.
Refer to the link: www.icourse163.org/learn/HIT-1… Refer to the link: www.cnblogs.com/AndyAo/p/82…