

MySQL locking for the busy web developer - gnufied
http://tech.brightbox.com/posts/2013-10-31-on-mysql-locks/

======
morgo
InnoDB uses MVCC, so the only operations that respect the SELECT .. FOR UPDATE
queries are updates. As long as you use transactions, I wouldn't say this is a
common application pattern.

It was good for the article to mention gap locking/next key locking - a lot of
users are surprised by it. The only comment I would make, is that it not
required when using Row-based-replication:
[http://www.tocker.ca/2013/09/04/row-based-
replication.html](http://www.tocker.ca/2013/09/04/row-based-replication.html)

I also have a 35min video as well which describes locking:
[http://www.tocker.ca/2013/09/19/locking-and-concurrency-
cont...](http://www.tocker.ca/2013/09/19/locking-and-concurrency-control.html)

~~~
Roboprog
Since MySQL + InnoDB now uses MultiVersion Concurrency Control, why does it
need these locks in the article???

(MVCC should allow "speculative", isolated, changes which fail and get rolled
back in case of a conflict)

~~~
morgo
What @kozlovsky said :) Only advice I would add, is SELECT .. FOR UPDATE
doesn't (and shouldn't) use MVCC, so it guarantees you the most recent version
of the row.

In my comment I said the use case for this feature is not typical, but I can
list the case:

If you needed to read the most recent value, apply logic (external to the DB)
and then write back a new value you will likely want serializable to prevent
lost-updates.

A hypothetical (but poor example) might be a stats counter where you read the
value, add one, then save it back.

------
falcolas
If you find that you have a lot of deadlocks due to gap locking, you can
disable this feature:

[http://dev.mysql.com/doc/refman/5.5/en/innodb-record-
level-l...](http://dev.mysql.com/doc/refman/5.5/en/innodb-record-level-
locks.html)

------
agopaul
Row-level locks are really sweet in case you have to handle multiple
row/tables update/insert in the same transaction, in order to keep the db
consistent even under sustained concurrency. But, dead locks really strike you
if you don't use them carefully. I wonder if dead locks are easyer to prevent
in Postgres

~~~
Roboprog
MVCC is the 21st century database hotness :-)

Rather than locking rows or tables (etc), you keep versions on rows, and allow
isolated changes to versioned "duplicates" of the rows in transactions. There
is a process that relatively quickly checks for conflicts during a commit --
is there a "gap" in the version numbers of rows or some such thing. If
inconsistency would result, the first commit (already) wins, and the second
gets rolled back. Otherwise, the replacement (or new) rows become the new
"master" version.

The commit check can become a bottleneck, but it shouldn't deadlock.

Almost forgot to add: PostgreSQL has been using MVCC for quite some time.

My view of MySQL was discolored pretty badly when I needed an SQL database
back in 2001 to prototype some stuff from home, and discovered how "gappy"
MySQL was at the time: rollback did nothing; referential integrity not
enforced; lack of transaction isolation. It felt like XBase hidden under SQL
syntax (something to which I did not want to regress).

OTOH, MySQL ran on Windows, and PostgreSQL didn't (at the time), so I guess
that was "the win" for many, alas. Both DBs have become much more like each
other in the intervening decade, though, which is good news.

~~~
mjb
You are confusing two concepts: locking used by the implementation to provide
isolation and atomicity, and locking used by the application to provide
consistency.

Consistency is a separate concern from Isolation and Atomicity. With the
default isolation level (repeatable read) it is frequently necessary to use
row-level locks to ensure consistency with concurrent transactions. This is an
application-level concern, and an interactions with the isolation level rather
than an interaction with the implementation. Short of SSI (serializability),
hazards exist which can have counterintutive impacts on data consistency. The
implementation's use of MVCC is irrelevant.

Locking is also frequently used in applications to help ensure external
consistency, but that's a pattern with real and significant pitfalls that
should be avoided.

~~~
Roboprog
I'm not going to claim to understand the entire topic of DB locking, but I
intended to refer to what happens within the DB on your behalf when the
application is running a transaction without any explicit locks. If I am
within a transaction, and repeat a read for some reason, I expect the same
result unless my process made modifications. I forget the name of that level,
but that's what I expect.

My gripe with record and related level locking comes from past pain. At one
past job, our DBA spent a lot of time in Sybase (a non-MVCC DB) constantly
twiddling locking granularity levels to keep processes out of each others'
way. (as in deadlock)

------
code_duck
I'm surprised there is no mention of database engines within this article.
Presumably, only InnoDB is being discussed here since MyISAM does not support
transactions at all.

~~~
babuskov
Yup, looks like InnoDB. I find MyISAM locking to work just fine out-of-the-
box.

Also, adding proper indexes is one of the basic database tasks to do if you
want any reasonable performance. I'm often adjusting tables' structure outside
of 3rd normal form, just to be able to use good indexes for the expected use
cases.

~~~
fideloper
If your apps are write-heavy, it makes sense to allow row-locking (InnoDB) vs
table-locking (MyISAM).

------
ivanhoe
Why would you use extra locking inside of a transaction? Transaction gives you
the required atomicity already

~~~
mjb
For Consistency (i.e. C, not I). Depending on the operations you are trying to
perform and the isolation level you have chosen, locking may be the only way
to update the database consistently, even within a single transaction.

See this paper for a good explanation of how C and I interact:
[http://aphyr.com/data/posts/294/tr-95-51.pdf](http://aphyr.com/data/posts/294/tr-95-51.pdf)

~~~
ivanhoe
Not sure if we talk of the same thing? When I say transaction I mean a number
of queries between START TRANSACTION and COMMIT (or ROLLBACK) SQL command, and
mysql will lock automatically all tables inside that block. If you use SELECT
inside the transaction innoDB will work on a data snapshot to insure the data
consistency. (btw. thanks for the link I'll read it later)

~~~
ivanhoe
I stand corrected here, actually it seems that in the default repeatable-read
mode START TRANSACTION will not do any table/row LOCK-ing automatically..

