Uncle, I heard you joined a new company?

Uncle surprised of looked at me one eye, ask you this guy how know?

I see you’ve got a new pulse.

Young man, you are new to the job market, so stop looking at your pulse all the time. You might as well learn more if you have the time.

Uncle subway ride

Uncle out of the subway station, you need to open yigong APP, get a subway station QR code, scan the corresponding channel gate will open.

Normally, the money is deducted and the switch opens, and the transaction is over.

The scene of uncle riding the subway is mapped to the database with the following concepts:

1. The atomicity

Outbound charges are treated as one atom.

Sweep the code out of the station after the deduction of the fee, can not deduct the uncle’s money, the gate is not open, so the uncle will certainly jump up.

If you do not deduct the money from your account, the brake will be automatically opened by the uncle. The uncle will be happy, and the subway operation company will not do it either.

2. Isolation

When the scanning gate is open, you have to wait in line by yourself during rush hours, or you may scan to let others in (we usually ensure isolation through artificial intelligence).

3. Persistence

Uncle to the station and the departure of a record of the ride, we need to solidify it.

4. The consistency

Atomicity + isolation + persistence make up consistency

conclusion

In a database, we call this a transaction (ACID), which must satisfy atomicity, isolation, consistency, and persistence.

Consistency in relation to other features

Consistency is the foundation and the end goal. The other three features (atomicity, isolation, and persistence) are all designed to ensure consistency. In simpler scenarios (without high concurrency), some database crashes may occur. Relying on REDO/UNDO operations on logs can guarantee consistency. In complex scenarios (with high concurrency), where many transactions are executed in parallel, it is likely that the final result will not be consistent.

Uncle write work progress report

Every Friday afternoon, uncle’s team would synchronize their work for the week to a weekly wiki.

The problem was solved by learning the concept of database isolation level. Let’s see how he did it.

There are four isolation levels in the database standard:

1. The read is not submitted

Colleague xiao Wang has not submitted the work progress, I saw (hey hey ~), this kind of problem we call dirty reading.

2. Read has been submitted

My colleague Xiao Wang submitted the work progress and I saw it again. This kind of work is called unrepeatable.

3. Repeatable read (mysql uses isolation level by default)

My colleague Xiao Wang submitted the work progress, but I have not quit the editing state at present. The data I read before is still repeatable.

But how did the progress of Xiao Li’s additions show up… (Phantom read caused by newly inserted data)

4. Serializability

Each of the above three solutions looks better than the last, but they all have some problems. I know these problems from learning mysql books, and can be solved by the highest isolation level – serialization

Therefore, the group suggested: only one person can read and write at the same time, and suggested that who wants to edit in the group to say, after the modification, notify the next person to operate, so as to avoid conflicts.

However, serialization can avoid conflicts, but it has a great impact on performance. When notifying the next person, if the next person does not reply, it may take a period of time to confirm, which will waste some time.

Git code is submitted by everyone at the same time. Why is it so hard to create a wiki?

We each generated a version based on our current changes, and then submitted it to compare with the latest version to resolve conflicts.

Multi-version concurrency control for mysql

Concurrency Control. MVCC, for Multi-version Concurrency Control.

The main purpose is to improve the database concurrency performance, with a better way to deal with read-write conflicts, even if there is read conflict, can also do not lock, non-blocking concurrent read, InnoDB transaction repeatable read and read committed isolation level is achieved through MVCC +undo.

Remember that the InnoDB row record we covered in the last session hides two columns transaction trx_ID and roll_pointer.

Each undo log also has a roll_pointer property to record the pointer to the next Undo log, forming a linked list. The undo log will be deleted after the transaction is committed.

Uncle ordered lunch in the canteen

Uncle’s workplace is located in a building in Zhongguancun. Fortunately, there is a canteen on the negative floor of the building. At noon, you can go directly to the elevator (of course, it is not easy to squeeze the elevator)

Last time, because the uncle ordered a fish – flavored pork covered rice, because the merchant did not find fish, to buy in advance, resulting in the uncle waited for an hour to eat rice (stranded ~).

Restaurant problem # 1: You spoke to a waiter at a restaurant, but couldn’t communicate

Uncle to the merchant thought of a way, every time the customer’s request should be written in the service order, to prevent the waiter is too busy (service collapse) and forget to do things later, the waiter only according to the service order can do.

Write Ahead log (WAL) solves the problem of recovering data from service crashes. The key point is to write logs before disks. When data pages are modified, the operation “what has been modified” is recorded in logs instead of flushing the changes to disks immediately. In this way, random write is converted to sequential write, improving performance.

WAL in mysql is the same thing as redo logs. WAL is a physical log that records physical changes to a data page, not changes to a row or rows. WAL is used to restore a physical data page after a commit (and only to the last commit location).

Restaurant problem # 2: Make me wait until I’m halfway through when I don’t have enough ingredients

If you find that the ingredients are not enough, just cancel the order, check the previous payment record and refund the money, I will place a new order.

Undo log records a version by rolling back and forth rows. The undo log is a logical log that is recorded on a per-row basis.

Multiple transactions to InnoDB change records, will record a corresponding undo log, repeatable read isolation level also uses undo log.

Restaurant problem 3: The shredded pork with fish flavor does not use fish

Summary of huilongguan uncle

Uncle finally found a job, also don’t have time to chat with me, of course, this is the last round of mysql, I hope uncle can be successful in the future work and life ~