Databases, Data Modeling, and Data Access
View All Tags
FOR UPDATE locks a row so only you can touch it. FOR SHARE locks it so nobody can change it, while others can hold the same lock with you. They look like small variations, and picking the wrong one can deadlock two sessions that seem to be doing nothing wrong.
You read a row, decide something, and write it back. Between the read and the write, someone else could change it. SELECT ... FOR UPDATE closes that gap: it locks the row while you think. Here's how it behaves from both sides, with real timings.
Under one isolation level, a transaction reads 50 twice while another session has already saved 40. Under the other, its second read says 40. Neither is a bug, and the right choice depends on what your code does with the number. Here are the two levels running side by side, and what a write does under both.
An isolation level decides what a transaction can see of other transactions' work. MySQL has a default and three scopes to change it in, and the three statements look almost identical. This guide checks the current level and changes it at each scope, then proves which level a transaction really ran at.
You run an UPDATE, and it's saved. You didn't ask for that, and that's autocommit doing its job. It's on by default, and turning it off changes what every later statement does in that session. Here's each setting at work, and the surprise waiting when you close the client.
Move 10 units from one product to another, and that's two UPDATE statements. If only the first one runs, 10 units have vanished. A transaction groups statements so they all take effect together, or none of them do. This guide runs one to the end and one back, and shows what another session sees while it's open.
SET SESSION innodb_print_all_deadlocks = ON failed with ERROR 1229 (HY000): Variable 'innodb_print_all_deadlocks' is a GLOBAL variable and should be set with SET GLOBAL. The message tells you exactly what to do. There's also a catch, which is that it takes an administrator.
ERROR 1044 (42000): Access denied for user 'lock_viewer'@'%' to database 'inventory' looks like a login failure, and it isn't one: your password was right. This account just has no rights on the database you named. It's different from ERROR 1045, and has a different fix.
Reading sys.innodb_lock_waits failed with ERROR 1356 (HY000): View 'sys.innodb_lock_waits' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them. The view isn't broken. The end of that long message is the real reason: the account lacks a right.
GRANT PROCESS ON inventory.* TO ... failed with ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES. The syntax is fine. Some privileges apply to the whole server, and can't be limited to one database.
