MySQL from a Developers Perspective

https://ift.tt/33qZjIk

So this has turned into a small series, explaining how to work with MYSQL
from a developers perspective. This post is intended as a directory for the
individual articles. It will be amended and re-dated as necessary.

The code for the series is also available in
isotopp/mysql-dev-examples
on GitHub.

The Tag #mysqldev will
reference all articles from this series.

MySQL Transactions – the physical side.
Looking at how MySQL InnoDB handles transactions on the physical media, enabling rollback and commit. Introduces a number of important concepts: The Undo Log, the Redo Log, the Doublewrite Buffer, and the corrosponding in memory structures, the Log Buffer and the InnoDB Buffer Pool, as well as the concept of a page.
MySQL Commit Size and Speed.
This article has code in Github, in mysql-commit-size/. We benchmark MySQL write speed as a function of number of rows written per commit.
MySQL Connection Scoped State.
Looking at things that are stateful and attached to a MySQL connection, and are lost on disconnect.
MySQL Transactions – the logical view.
This article introduces the concept of TRANSACTION ISOLATION LEVEL and how pushing things into the Undo Log, while a necessity to implement ROLLBACK for a Writer, enables features for a Reader.
MySQL Transactions – writing data.
This article has code in Github, in mysql-transactions-counter. We increment a counter in the database, with multiple concurrent writers, and see what happens.
MySQL: Locks and Deadlocks.
When changing multiple rows, it is possible to take out locks in transactions one by one. Depending on how that is done, it may come to deadlocks, and server-initiated rollbacks. When that happens, the transaction must be retried.
MySQL Deadlocks with INSERT
When using the obscure transaction isolation level SERIALIZABLE, it may happen that a single INSERT can deadlock. Here is how, and why.
MySQL Foreign Keys and Foreign Key Constraints
When establishing relationships between tables, you are doing this by putting one tables primary keys into another tables columns. Enforcing valid pointers between tables seems like a sexy idea, but is painful, and maybe hurts more than it helps.
MySQL Foreign Key Constraints and Locking
Looking at tables with foreign key constraints, we check what happens to table and row locks, and how this is different from before.

technology

via Planet MySQL https://ift.tt/2iO8Ob8

August 4, 2020 at 11:17AM