Introduction: MySQL is a relational database management system, which is a product of Oracle. Although the stand-alone performance is not as good as Oracle, it is free and open source, with low stand-alone cost and distributed cluster, so it is favored by Internet companies and is the mainstream database of Internet companies.
What are database transactions? What happens if you don’t have things? What are the characteristics of transactions?
A transaction is a series of operations performed as a single logical unit of work and can be viewed as a collection of SQL statements in a single unit. You either do it all or you don’t.
Without concurrent control of the database, exceptions such as dirty reads, non-repeat reads, phantom reads, and missing changes can occur.
Properties of transactions (ACID)
A, atomacity atomic transactions must be atomic units of work; All or none of its data modifications are performed. Typically, the operations associated with a transaction have a common goal and are interdependent. If the system performs only a subset of these operations, it may defeat the overall goal of the transaction. Atomicity eliminates the possibility of the system handling a subset of operations.
C, consistency A transaction changes a database from one consistent state to the next. That is, when the transaction completes, all the data must be in a consistent state (constraints are not broken).
Changes made by concurrent transactions must be isolated from changes made by any other concurrent transactions. The state in which data is viewed by a transaction, either before it was modified by another concurrent transaction or after it was modified by another transaction, and the data in the intermediate state is not viewed by a transaction. In other words, the effects of one transaction are not visible to other transactions until the transaction commits.
D, Durability After transactions are completed, their impact on the system is permanent. This modification will persist even in the event of a fatal system failure.
What are database transactions? What happens if you don’t have things? What are the characteristics of transactions?
A transaction is a series of operations performed as a single logical unit of work and can be viewed as a collection of SQL statements in a single unit. You either do it all or you don’t.
Without concurrent control of the database, exceptions such as dirty reads, non-repeat reads, phantom reads, and missing changes can occur.
Properties of transactions (ACID)
A, atomacity atomic transactions must be atomic units of work; All or none of its data modifications are performed. Typically, the operations associated with a transaction have a common goal and are interdependent. If the system performs only a subset of these operations, it may defeat the overall goal of the transaction. Atomicity eliminates the possibility of the system handling a subset of operations.
C) consistency
Transactions move the database from one consistent state to the next. That is, when the transaction completes, all the data must be in a consistent state (constraints are not broken).
Changes made by concurrent transactions must be isolated from changes made by any other concurrent transactions. The state in which data is viewed by a transaction, either before it was modified by another concurrent transaction or after it was modified by another transaction, and the data in the intermediate state is not viewed by a transaction. In other words, the effects of one transaction are not visible to other transactions until the transaction commits.
D) durability
After a transaction completes, its impact on the system is permanent. This modification will persist even in the event of a fatal system failure.
“A sends 100 to B”
- Read the balance of account A (500).
- A Account withholding operation (500-100).
- Write the result back to account A (400).
- Read the balance of account B (500).
- B Add account (500+100).
- Write the result back to account B (600).
Atomicity: Ensure that all procedures 1-6 are either executed or not executed. If an exception occurs, roll back.
Before the consistent transfer, A and B have 500+500=1000 yuan in their accounts. After the transfer, A and B have 400+600=1000 yuan in their accounts.
Isolation During the whole process of transfer from A to B, the amount of money in account A and account B will not change as long as the transaction has not been committed.
Persistence Once the transfer is successful (transaction commit), the amount of money in the two accounts actually changes
What is dirty reading? Phantom read? Unrepeatable? What is the isolation level of a transaction? What is the default isolation level for Mysql?
- Dirty read: Transaction A reads the data updated by transaction B, and then TRANSACTION B rolls back the data
- Non-repeatable read: When transaction A reads the same data for many times, transaction B updates and commits the data during the process of reading the same data for many times. As A result, when transaction A reads the same data for many times, the results are inconsistent.
- Phantom reads: system administrators, A database of all the grades of the students from the specific scores to ABCDE level, but the system administrator B at this time by inserting A specific score record, when A system administrator A change after the found there is no change to come over, A record like the illusion, this is called magic to read.
Read Uncommitted, as the name suggests, is when a transaction can Read data from another uncommitted transaction. Read COMMITTED, as the name suggests, is when a transaction waits for another transaction to commit before it can Read data.
Small A to buy something (card has 10,000 yuan), when he paid (transaction open), the system detected his card has 10,000 yuan in advance, at this time!! Little “A” ‘s wife is going to transfer all the money for her family and submit it. When the system prepared to deduct the money, it tested the amount of the card again and found that there was no money left (of course, the amount of the second test should wait for the wife to transfer the amount of transaction submitted). “A” is going to be depressed
If a transaction updates the data, the read transaction can read the data only after the UPDATE transaction is committed. In this case, however, two identical queries within the scope of a transaction return different data, which is called a non-repeatable read.
Repeatable Read: no modification operation is allowed when data is read (transaction is started)
Example: Small A went to buy something (the card has 10,000 yuan), when he paid the bill (the transaction is open, the UPDATE operation of other transactions is not allowed), the charging system detected that his card has 10,000 yuan beforehand. At this point his wife can’t transfer the amount. The next payment system can deduct money.
Analysis: Repeat read can solve the problem of non-repeat read. At this point, it should be understood that non-repeatable reads correspond to modifications, or UPDATE operations. But there may also be phantom problems. Because phantom problems correspond to INSERT operations, not UPDATE operations.
When does phantom reading occur?
Example: Little A went to buy something and spent 2,000 yuan, then his wife checked his consumption record (full watch scan FTS, wife affairs open), and found that it did spend 2,000 yuan, at this time, little A spent 10,000 yuan to buy A computer, INSERT A consumption record, and submit. When his wife printed A’s consumption record list (his wife’s affairs submitted), she found that she spent 12,000 yuan, which seemed to be an illusion. This is hallucination.
The Serializable serialization
Serializable is the highest transaction isolation level, where transactions are serialized and sequentially executed to avoid dirty reads, unrepeatable reads, and phantom reads. However, this transaction isolation level is inefficient and costly to database performance, and is generally not used.
The default isolation level for Mysql is Repeatable Read.
How is the separation of things achieved?
It is implemented based on locks.
What locks are there? Let me introduce you separately
In DBMS, database locks can be divided into row-level locks (INNODB engine), table-level locks (MYISAM engine) and page-level locks (BDB engine) according to the granularity of locks.
Row-level lock The row-level lock is the most fine-grained lock in Mysql. It only locks the current row. Row-level locking can greatly reduce conflicts in database operations. Its locking particle size is the smallest, but the locking cost is also the largest. Row-level locks are divided into shared locks and exclusive locks. Features: high overhead, slow lock; Deadlocks occur; The lock granularity is the lowest, the probability of lock conflict is the lowest, and the concurrency is the highest.
Table level lock Table level lock is the lock with the largest granularity in MySQL. It locks the entire table in the current operation. It is simple to implement, consumes less resources, and is supported by most MySQL engines. The most commonly used MYISAM and INNODB both support table-level locking. Table level locks are classified into shared table read locks (shared locks) and exclusive table write locks (exclusive locks). Features: low overhead, fast lock; No deadlocks occur; The lock granularity is large, and the probability of lock conflict is high and the concurrency is low.
Page-level lock Page-level lock is a type of lock whose granularity is in the middle between row-level lock and table-level lock in MySQL. Table level locking is fast but has many conflicts, while row level locking is slow but has few conflicts. So a compromise page level is taken, locking adjacent sets of records at a time. Features: Overhead and locking time are between table and row locks; Deadlocks occur; The locking granularity is between table locks and row locks, and the concurrency is average
What is a deadlock? How to solve it? (The first couple of questions are my personal favorite, and they basically show the interviewer’s basic skills.)
A deadlock is a vicious cycle in which two or more transactions occupy each other’s resources and request to lock each other’s resources.
Common solutions to deadlocks
1. If different programs concurrently access multiple tables, try to agree to access the tables in the same order, which can greatly reduce the chance of deadlocks.
2, in the same transaction, as far as possible to lock all the resources needed to reduce the probability of deadlock;
3. For services that are prone to deadlocks, upgrade locking granularity can be used to reduce the probability of deadlocks by table-level locking.
Distributed transaction locks or optimistic locks can be used if the business is not doing well
SQL life cycle? What is the order of keywords?
- The application server establishes a connection with the database server
- The database process gets the requested SQL
- Parse and generate an execution plan, execute
- Read data into memory and process it logically
- Send the result to the client through the connection in step 1
- Close the connection and release resources
1, FROM: perform cartesian product (cross join) on the first two tables in FROM clause to generate virtual table VT1. 2, ON: apply ON filter to VT1, only those true will be inserted into TV2. OUTER (JOIN): If OUTER JOIN is specified (as opposed to CROSS JOIN or INNER JOIN), the unmatched rows in the table are added to VT2 as external rows to generate TV3. If the FROM clause contains more than two tables, repeat steps 1 through 3 for the result table generated by the previous join and the next table until all table positions are processed. 4. WHERE: Apply the WHERE filter to TV3 so that only rows that are true are inserted into TV4. 5. GROUP BY: GROUP the rows in TV4 according to the column list in the GROUP BY clause to generate TV5. 6, express | ROLLUP: inserted the super group VT5, generate VT6. HAVING: Apply the HAVING filter to VT6. Only groups that are true are inserted into VT7. 8, SELECT: processing SELECT list, generate VT8. 9, DISTINCT: Delete duplicate lines from VT8, product VT9. 10, ORDER BY: Generate a cursor (VC10) from VT9 rows in ORDER of the column list in the ORDER BY clause. TOP: Select a specified number or proportion of rows from the beginning of VC10 to generate table TV11 and return it to the caller.
What is optimism lock? Pessimistic locks? How to implement it?
Pessimistic locks: pessimistic locks that the data was accidentally conservative attitude change, rely on the native support database locking mechanism to guarantee the security of the current transaction, other concurrent transactions to prevent damage to the target data or damage to other concurrent transactions data, will commence in a transaction or the execution of applications before locking, performed before you release the lock. This can seriously affect the system’s concurrency capability for long transactions. Native database transactions are a classic example of pessimistic locking. Optimistic Lock: Optimistic Lock, as the name implies, is very Optimistic. Every time I go to get data, I think that others will not change it, so I will not Lock it, but when SUBMITTING updates, I will judge whether others have updated the data during this period. Optimistic locking is applicable to scenarios where read is excessive and write is insufficient to improve throughput. Generally, a version number field is added to compare the version number each time it is updated.
How to do paging in big data?
You can refer to the alibaba Java development manual for answers
What is database connection pooling?
As you can see from the previous SQL life cycle topic, connections play an important role, but are frequently created and destroyed, wasting system resources. Because the database is more suitable for long connection, there is a connection pool, can reuse the connection, maintain the connection object, allocation, management, release, can also avoid creating a large number of connections to DB caused by various problems; In addition, through the request queuing, it also alleviates the impact on DB.
Recommended reading
Redis questions that Internet companies must ask in an interview
Mysql (2)
Recently, I started a group chat. Learn Java advanced technology dry goods, practice sharing, job promotion, talk about the ideal. Like-minded friends, welcome to join.
