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.
Since we now know how to look at the state of locking in a live database, let’s look at what happens when we run a normal insert or update and an insert or update with foreign key relationships defined, and compare.
Foreign Keys are what links tables together and turns a set of tables into a model. Foreign Key Constraints are conditions that must be true for the content of the tables to be an internally consistent model. Foreign Key Constraints can be defined and enforced in InnoDB, but this comes at a considerable price, and for some it may hurt more than it is worth.
Support Channel. “Hi, I am getting deadlocks in the database and they occur when I have to rollback the transactions but if we don’t have to roll back all transactions get executed.” Wait, what? After some back and forth it becomes clear that the Dev experiences deadlocks and has data:
In a previous article we wrote data to the database using atomic update statements, and then using transactions with SELECT ... FOR UPDATE. In this article we will look at what happens when we continue doing this, in a more complicated way. Source code for this article is also available on github.com.
Using the framework for testing we created in earlier articles, let’s try to
modify some data. We are writing a small program that increments a counter.
Our table looks like this, and contains 10 counters:
After having a look how MySQL handles transactions physically, let’s have a look at what is
going on from a logical point of view.
MySQL speaks its own proprietary protocol. It cannot be routed
by a HTTP proxy, and a MySQL connection is entire unlike a HTTP
connection. Specifically, a lot of state and configuration is
tied to a MySQL connection, and it cannot be recovered on
disconnect.
When writing data to disk, for small transactions the cost of writing the
commit out do disk dominates the execution time of the script. In order to
show that, I wrote a little bit of Python.
So you talk to a database, doing transactions. What happens actually, behind the scenes? Let’s have a look.
So I am a Windows User now. I have an old MacBook pro, Late-2013 13” Retina, i7, 16 GB, 1 TB SSD, and the battery is done now, after 7 years. Also, the hardware is aging, and I want it refurbished and upgrade the son’s equipment (which is the previous 2010 MBP I had at that time).
Because Martin wanted some starting point, here is how I set up my Python. There are a lot of other things one can do, but this is supposed to be just a starting point.
Da war also ein Artikel bei Golem: Schulen bemühen sich vergeblich um Geld aus dem Digitalpakt.
Die Schulen wollten das Geld nutzen, die Mittel würden aber trotz vieler Einreichungen nicht freigeben, sagte ein Lehrer und IT-Verantwortlicher eines Berliner Gymnasiums Golem.de.
Two questions from Reddit’s /r/mysql related to Window Functions: How do I make row.numbers happen and Get the difference between two values in different recordings.
A question from Reddit’s /r/mysql:
Really new to MySQL and had a request to export an entire database to csv for review. I can manually export each table using workbench but there are 10+ tables and 10+ databases so I was looking to export the entire database to csv.
An older talk from 2 years ago, which for some reason I was not able to find in the blog.
A rewrite of the same in German from 9 years ago.
So this morning the 08:10 alarm in the living room did not go off. It was set to remind the son that it is time to get going for school. We noticed in time, but when I looked into the “Why?” things got interesting. That alarm is a chime from the household Sonos system, and when I investigated, I got this:
