In this chapter, we learned how the InnoDB storage engine guarantees the consistency of data in a concurrent environment. First, we discussed how locks and snapshots work. This low-level understanding of InnoDB mechanisms is necessary to understand how InnoDB transactions work. We discussed the isolation levels and how each isolation level uses locks and snapshots to guarantee a certain level of protection. We discussed how to deal with deadlocks that are normal on a busy database, but a DBA should avoid them becoming a problem. We also discussed metadata locks, which protect transactions from changes in the structures of the tables.
In the final section, we discussed how to avoid ALTER TABLE
locking other transactions for a long period of time.
In the next chapter, we will learn how to manage connections and users, how to assign them limited resources, and how to deal with security.