MySQL from a Developers Perspective

isotopp image Kristian Köhntopp -
September 7, 2020
a featured image

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.

  • MySQL: Some Character Set Basics A collection and re-evaluation of things I wrote about MySQL character sets in the past, updated to match MySQL 8.

  • MySQL: NULL is NULL Understanding the handling of NULL values in SQL. NULL is not false, nor None/nil/undef, but is a thing specific to SQL.

  • MySQL: Basic usage of the JSON data type MySQL 8 gains support of a JSON data type. How to get data in and out of JSON fields.