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.
We will be using the tables and structures from our previous examples, a simple 1:n relationship between a and b:
or the same definition for b without the constraint.
A normal INSERT and UPDATE
First, let’s look at an insert and update into b without any defined constraints:
We can see that the INSERT did just create an intention-lock on the table, but did not actually have to create any row locks. The update had to change an existing table row b.b_id=10, and hence needed to X-lock the row 10.
INSERT and UPDATE with a foreign key constraint
Redefining the b table as above, with a foreign key constraint defined, produces a much richer set of locks:
In order for the INSERT to be valid, we need to check if the a_id inserted into table b exists in a - that is a lookup operation.
We also need to ensure that the value we checked for stays there and is not modified until we finish the transaction. So we do get an additional intention-lock on a, and then an actual S-lock on a.a_id=30. The intention-lock on b is as before.
Continuing our exploration, we can now try to change a row:
We get another S-lock on a.a_id=20 to ensure that our parent record stays unchanged until completion, and as we modify b.b_id=10, this is X-locked as in the normal case.
Defining foreign key constraints produces more S-locks.
Given a 1:n relationship between a and b, making changes to a_id fields of records in b will S-lock the records in a that the changed rows in b.a_id are pointing to.
We did not see this, but there was also a lookup in a to ensure that the b.a_id actually exists in a.
Add a column data integer not null to b.
Make changes to b.a_id for one row, and to b.data for another row. How does the locking change?
You will find:
Making changes to b.data neither affects the relationship between the tables, nor creates S-locks in a.
Given the observed behavior, is our RMW still correct?
After looking at the locking behavior of these statements, and what we previously learned about locking: How would we INSERT and UPDATE the dependent records (b records) in our 1:n relationship correctly?
We can still manipulate all fields as before that are not foreign keys:
And the RMW for writes to data is as before:
When we make changes to the linking between b and a, we must put a share lock on the new a.a_id we intend to link to, to ensure it is present and does not change or vanish until completion.
As this is a linking to be created, we have to use two distinct select statements for this, and the locks need to be in place before we make the change.
Self-referential structures and locks
Let’s create a tree c where we have records distinguished by c.id, and with c.parent pointing to the parent c.id. We then put in a few records, multiple levels deep:
Again, we insert a new record:
This looks exactly as we would expect from the previous case.
Then we modify an existing record, again, without surprises:
The term “UPSERT” is database-speak for “create a record, or if it exists, update it”. The word is a portmanteau from Insert and Update. It can have three different resolutions for conflicts, and MySQL has three different special commands for this:
Older Record wins: When a new record is to be inserted, but an older record with this primary key already exists, the old record should stay unmodified. MySQL has the command INSERT IGNORE for this.
Newer Record wins: When a new record is to be inserted, but an older record with this primary key already exists, the old record is to be deleted, then the new record is to be inserted. MySQL has the command REPLACE INTO for this.
Merge old and new: When a new record is to be inserted, but an older record with this primary key already exists, the old and new records have to be merged somehow. MySQL has INSERT ON DUPLICATE KEY UPDATE and the VALUES() function for this.
The generic way for this, without special commands, is again a RMW.
This is also what we would expect any ORM to generate, if it has not implemented special support for the MySQL dialect.