- 本月热门
-
DesignInTec:科技里的设计（当设计师参与创业） by 前田约翰 -
Go语言内存、垃圾回收、协程简介（1.4 runtime 运行环境） by 雨痕 -
AngularJS the performance parts(AngularJS性能：过去现在和未来) by toddmotto -
HTTP1.1 vs HTTP2 a performance analysis(HTTP2比HTTP1性能提高66%) by mozilla -
How to build a content and social media engine[如何构建内容和社交媒体营销引擎] by by Marcel Santilli -
9 Thought-Provoking Quotes About Work-Life Balance(9个发人深省的关于工作生活平衡的名言) by SalesForce -
How To Shot Web(如何攻破网站) by Jason Haddix -
MongoDB and the Internet of Things(MongoDB和物联网) by Chris Biow -
设计模式之MVC(基于WEB、JSP) by Hesey -
高性能Web应用缓存架构设计浅谈 by Robbin Fan
加收藏
INNODB LOCKING EXPLAINED WITH STICK FIGURES(详解InnoDB的锁机制) by Bill Karwin
第1页
INNODB LOCKING EXPLAINED
WITH STICK FIGURES
Bill Karwin October 29, 2015
WITH STICK FIGURES
Bill Karwin October 29, 2015
第2页
Bill Karwin
• Senior Database Architect at School Messenger (West Corporation)
• Author of SQL Antipatterns: Avoiding the Pitfalls of Database Programming
• 20+ years experience with SQL databases, software development, MySQL consulting and training
• Senior Database Architect at School Messenger (West Corporation)
• Author of SQL Antipatterns: Avoiding the Pitfalls of Database Programming
• 20+ years experience with SQL databases, software development, MySQL consulting and training
第3页
why locking?
• When multiple clients access the same data, they have to avoid clobbering each others’ work.
• Databases must restrict access to one client at a time for a given table or row.
https://commons.wikimedia.org/wiki/File:New_York_City_Gridlock.jpg
• When multiple clients access the same data, they have to avoid clobbering each others’ work.
• Databases must restrict access to one client at a time for a given table or row.
https://commons.wikimedia.org/wiki/File:New_York_City_Gridlock.jpg
第4页
why locking?
• The DBMS creates locks against tables and rows, and gives them to clients, firstcome, first-serve.
• When a client requests a exclusive lock, but a different client currently holds it, the requestor waits until the holder releases its lock.
• Most locks last until the end of the transaction.
https://commons.wikimedia.org/wiki/Traffic_lights#/media/File:LED_traffic_light.jpg
• The DBMS creates locks against tables and rows, and gives them to clients, firstcome, first-serve.
• When a client requests a exclusive lock, but a different client currently holds it, the requestor waits until the holder releases its lock.
• Most locks last until the end of the transaction.
https://commons.wikimedia.org/wiki/Traffic_lights#/media/File:LED_traffic_light.jpg
第5页
the analogy
第6页
a museum
• Many people can visit the museum (a database table) to view art (rows of data).
• Many people can visit the museum (a database table) to view art (rows of data).
第7页
reads and writes
第8页
reads
• Many visitors can view paintings at the same time— no locking required.
https://commons.wikimedia.org/wiki/File:L%27%C3%A9glise_d%27Auvers-‐‑sur-‐‑Oise.jpg
• Many visitors can view paintings at the same time— no locking required.
https://commons.wikimedia.org/wiki/File:L%27%C3%A9glise_d%27Auvers-‐‑sur-‐‑Oise.jpg
第9页
writes
• A curator can change the paintings—while casual visitors are viewing them.
https://commons.wikimedia.org/wiki/File:Vincent_Willem_van_Gogh_128.jpg
• A curator can change the paintings—while casual visitors are viewing them.
https://commons.wikimedia.org/wiki/File:Vincent_Willem_van_Gogh_128.jpg
第10页
writes
• A curator can change the paintings—while casual visitors are viewing them.
• A curator can change the paintings—while casual visitors are viewing them.
第11页
repeatable reads
• The viewers still see the prior painting in spite of the change, because they used their tablets to capture the image.*
* Please do not take photographs of the art in a real museum.
• The viewers still see the prior painting in spite of the change, because they used their tablets to capture the image.*
* Please do not take photographs of the art in a real museum.
第12页
read committed
• If the viewers are okay allowing their view to change, they can simply say so.
READ COMMITTED
• If the viewers are okay allowing their view to change, they can simply say so.
READ COMMITTED
第13页
read committed
• If the viewers are okay allowing their view to change, they can simply say so.
READ COMMITTED
I don’t mind
• If the viewers are okay allowing their view to change, they can simply say so.
READ COMMITTED
I don’t mind
第14页
exclusive locks
第15页
exclusive locks
• The curator can change the painting if they are the exclusive person working on it.
X-‐‑lock!
• The curator can change the painting if they are the exclusive person working on it.
X-‐‑lock!
第16页
exclusive locks
• The curator can change the painting if they are the exclusive person working on it.
X-‐‑lock!
• The curator can change the painting if they are the exclusive person working on it.
X-‐‑lock!
第17页
exclusive locks
• The curator can change the painting if they are the exclusive person working on it.
… X-‐‑lock!
https://commons.wikimedia.org/wiki/Vincent_van_Gogh#/media/File:Vincent_Willem_van_Gogh_107.jpg
• The curator can change the painting if they are the exclusive person working on it.
… X-‐‑lock!
https://commons.wikimedia.org/wiki/Vincent_van_Gogh#/media/File:Vincent_Willem_van_Gogh_107.jpg
第18页
what is an exclusive lock?
• A lock that does not share. • It must be the only lock on the resource. • Request for an exclusive lock waits for the release of
any other shared or exclusive lock on that resource.
• A lock that does not share. • It must be the only lock on the resource. • Request for an exclusive lock waits for the release of
any other shared or exclusive lock on that resource.
第19页
shared locks
第20页
shared locks
• The curator cannot make changes while the art critic is viewing a painting.
S-‐‑lock!
. . .
• The curator cannot make changes while the art critic is viewing a painting.
S-‐‑lock!
. . .
第21页
shared locks
• Art critics can share—they do not block each other.
S-‐‑lock! S-‐‑lock!
. . .
• Art critics can share—they do not block each other.
S-‐‑lock! S-‐‑lock!
. . .
第22页
shared locks
• Once the art critics leaves, the curator can proceed to change the art.
X-‐‑lock!
• Once the art critics leaves, the curator can proceed to change the art.
X-‐‑lock!
第23页
shared locks
• A new art critic will not begin his viewing while the curator is still working on changing the painting.
… X-‐‑lock!
• A new art critic will not begin his viewing while the curator is still working on changing the painting.
… X-‐‑lock!
第24页
what is a shared lock?
• A lock that allows other shared locks on the same table or rows.
• Shared locks blocks exclusive locks. • Exclusive locks block shared locks.
• A lock that allows other shared locks on the same table or rows.
• Shared locks blocks exclusive locks. • Exclusive locks block shared locks.
第25页
table intention locks
第26页
table locks
• A construction worker needs to remodel the museum, but not while visitors are inside.
• A construction worker needs to remodel the museum, but not while visitors are inside.
第27页
table locks
• Each visitor is given a special visitor’s badge as they enter the museum, showing their intention to view the paintings.
• Each visitor is given a special visitor’s badge as they enter the museum, showing their intention to view the paintings.
第28页
table locks
• A construction worker requests exclusive access to the museum, but can not get it.
. . .
• A construction worker requests exclusive access to the museum, but can not get it.
. . .
第29页
table locks
• When all the visitors have left, the worker can finally get his exclusive access to do his work.
IX!
• When all the visitors have left, the worker can finally get his exclusive access to do his work.
IX!
第30页
table locks
• While the construction is going on, visitors cannot
get their badges, and
cannot enter the museum.
IX!
……
…
https://commons.wikimedia.org/wiki/Category:Under_construction_icons#/media/File:Enobras.PNG
• While the construction is going on, visitors cannot
get their badges, and
cannot enter the museum.
IX!
……
…
https://commons.wikimedia.org/wiki/Category:Under_construction_icons#/media/File:Enobras.PNG
第31页
what is an intention lock?
• During SELECT/INSERT/UPDATE/DELETE, a shared intention lock (“IS”) allows access by multiple threads to work on the same table, but blocks exclusive table locks.
• During ALTER TABLE or DROP TABLE, an exclusive intention lock (“IX”) blocks other table locks, either shared or exclusive. So no one else can query, and no one can run their own ALTER/DROP TABLE.
• During SELECT/INSERT/UPDATE/DELETE, a shared intention lock (“IS”) allows access by multiple threads to work on the same table, but blocks exclusive table locks.
• During ALTER TABLE or DROP TABLE, an exclusive intention lock (“IX”) blocks other table locks, either shared or exclusive. So no one else can query, and no one can run their own ALTER/DROP TABLE.
第32页
gap locks
第33页
gap locks
• The art critic needs to view the exact collection without changes and no new inserts.
S-‐‑lock!
. . .
gap
• The art critic needs to view the exact collection without changes and no new inserts.
S-‐‑lock!
. . .
gap
第34页
what is a gap lock?
• A lock on a painting locks the “space” before the painting.
• The gap lock prevents inserts of new paintings before the locked painting (within the space).
• This happens automatically, to prevent “phantom reads”—i.e. the view of data changes during a transaction.
• A lock on a painting locks the “space” before the painting.
• The gap lock prevents inserts of new paintings before the locked painting (within the space).
• This happens automatically, to prevent “phantom reads”—i.e. the view of data changes during a transaction.
第35页
what is a gap lock?
• Exception: if the art critic is okay with seeing the latest additions among the paintings, he can choose to use the READ COMMITTED transaction isolation level.
• Another exception: no gap lock is needed for a UNIQUE or PRIMARY KEY index.
• Exception: if the art critic is okay with seeing the latest additions among the paintings, he can choose to use the READ COMMITTED transaction isolation level.
• Another exception: no gap lock is needed for a UNIQUE or PRIMARY KEY index.
第36页
gap locks
• Optionally be more permissive about inserts.
READ COMMITTED
S-‐‑lock!
gap
• Optionally be more permissive about inserts.
READ COMMITTED
S-‐‑lock!
gap
第37页
gap locks
• Optionally be more permissive about inserts.
READ COMMITTED
S-‐‑lock!
X-‐‑lock!
I don’t mind
• Optionally be more permissive about inserts.
READ COMMITTED
S-‐‑lock!
X-‐‑lock!
I don’t mind
第38页
insert intention locks
第39页
insert intention locks
• One curator wants to update paintings where year > 1886
X-‐‑lock!
gap
• One curator wants to update paintings where year > 1886
X-‐‑lock!
gap
第40页
insert intention locks
• The second curator wants to insert an 1887 painting, but it would fall within the existing gap lock.
X-‐‑lock!
…
gap
• The second curator wants to insert an 1887 painting, but it would fall within the existing gap lock.
X-‐‑lock!
…
gap
第41页
insert intention locks
---TRANSACTION 32070411, ACTIVE 6 sec inserting mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 26, OS thread handle 0x7f2ba845f700, query id 1423 192.168.50.1 root update insert into Museum (year) values (1887)
------- TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3337 page no 4 n bits 72 index `year` of table `test`.`Museum` trx id 32070411 lock_mode X insert intention waiting
---TRANSACTION 32070411, ACTIVE 6 sec inserting mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 26, OS thread handle 0x7f2ba845f700, query id 1423 192.168.50.1 root update insert into Museum (year) values (1887)
------- TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3337 page no 4 n bits 72 index `year` of table `test`.`Museum` trx id 32070411 lock_mode X insert intention waiting
第42页
insert intention locks
• But if the first curator doesn’t care about new paintings entering his view…
READ X-‐‑lock! COMMITTED
gap
• But if the first curator doesn’t care about new paintings entering his view…
READ X-‐‑lock! COMMITTED
gap
第43页
insert intention locks
• Then the second curator gets an insert intention lock, and is then free to insert.
READ X-‐‑lock! COMMITTED
insert-‐‑ lock!
gap
I don’t mind
• Then the second curator gets an insert intention lock, and is then free to insert.
READ X-‐‑lock! COMMITTED
insert-‐‑ lock!
gap
I don’t mind
第44页
insert intention locks
• Then the second curator gets an insert intention lock, and is then free to insert.
READ X-‐‑lock! COMMITTED
X-‐‑lock!
I don’t mind
• Then the second curator gets an insert intention lock, and is then free to insert.
READ X-‐‑lock! COMMITTED
X-‐‑lock!
I don’t mind
第45页
what is an insert intention lock?
• A special kind of gap lock, requested before a client tries to insert a row.
• Insert locks are shared, not exclusive—multiple clients can acquire insert locks on the same gap.
• But insert locks conflict with other exclusive locks.
• A special kind of gap lock, requested before a client tries to insert a row.
• Insert locks are shared, not exclusive—multiple clients can acquire insert locks on the same gap.
• But insert locks conflict with other exclusive locks.
第46页
why is insert intention lock shared?
• Multiple clients prepare to insert into the same gap. • They may be inserting different rows within the same
gap, so they don’t conflict with each other. • But the insert intention lock blocks other clients from
requesting exclusive locks on the same gap.
• Multiple clients prepare to insert into the same gap. • They may be inserting different rows within the same
gap, so they don’t conflict with each other. • But the insert intention lock blocks other clients from
requesting exclusive locks on the same gap.
第47页
auto-inc locks
第48页
auto-inc locks
• Two curators are installing paintings. They both need to post a unique number for self-guided tours.
jQuery110209781224836642509_1446599202841
• Two curators are installing paintings. They both need to post a unique number for self-guided tours.
jQuery110209781224836642509_1446599202841
第49页
auto-inc locks
• There must be one number generator per table.
• One curator at a time can request the next value.
http://www.istockphoto.com/photo/ti cket-‐‑dispenser-‐‑isolated-‐‑9396862
• There must be one number generator per table.
• One curator at a time can request the next value.
http://www.istockphoto.com/photo/ti cket-‐‑dispenser-‐‑isolated-‐‑9396862
第50页
auto-inc locks
• The first curator gets a number.
auto-‐‑inc!
…
• The first curator gets a number.
auto-‐‑inc!
…
第51页
auto-inc locks
• As soon as the first curator gets his number, the second curator can proceed.
1 2 auto-‐‑inc!
• As soon as the first curator gets his number, the second curator can proceed.
1 2 auto-‐‑inc!
第52页
auto-inc locks
• They may both keep locks on the paintings, but they’re done allocating numbers.
X-‐‑lock!
1 2 X-‐‑lock!
• They may both keep locks on the paintings, but they’re done allocating numbers.
X-‐‑lock!
1 2 X-‐‑lock!
第53页
what is an auto-inc lock?
• A table lock, used when a client requests the next unique id for a given table.
• Ensures that each id is given to one client. • Brief—it is released as soon as the id is generated,
instead of lasting to the end of the transaction like other locks. • Because the lock is so brief, neither client can “undo”— i.e. return their id to the stack for someone else to use.
• A table lock, used when a client requests the next unique id for a given table.
• Ensures that each id is given to one client. • Brief—it is released as soon as the id is generated,
instead of lasting to the end of the transaction like other locks. • Because the lock is so brief, neither client can “undo”— i.e. return their id to the stack for someone else to use.
第54页
deadlocks
第55页
deadlocks
• Two curators are updating the art, but they start from different ends of the collection.
X-‐‑lock!
X-‐‑lock!
• Two curators are updating the art, but they start from different ends of the collection.
X-‐‑lock!
X-‐‑lock!
第56页
deadlocks
• A curator requests a lock on the second painting, which is already locked. He waits.
X-‐‑lock!
…
X-‐‑lock!
• A curator requests a lock on the second painting, which is already locked. He waits.
X-‐‑lock!
…
X-‐‑lock!
第57页
deadlocks
• The other curator requests a lock on the first painting, which is already locked. He also waits.
X-‐‑lock!
…
X-‐‑lock! …
• The other curator requests a lock on the first painting, which is already locked. He also waits.
X-‐‑lock!
…
X-‐‑lock! …
第58页
deadlocks
• Neither will give up the lock they have, so they are doomed to wait until one or both of them dies.
X-‐‑lock!
…
X-‐‑lock! …
• Neither will give up the lock they have, so they are doomed to wait until one or both of them dies.
X-‐‑lock!
…
X-‐‑lock! …
第59页
what is a deadlock?
• When two or more concurrent clients wait for each other to release their locks, but since they are both waiting, they will never give up the lock they have.
• In other words, a catch-22 of lock-waits.
• Many people use the term “deadlock” incorrectly— when they are describing a simple one-way lock wait.
• When two or more concurrent clients wait for each other to release their locks, but since they are both waiting, they will never give up the lock they have.
• In other words, a catch-22 of lock-waits.
• Many people use the term “deadlock” incorrectly— when they are describing a simple one-way lock wait.
第60页
resolving deadlocks
• MySQL detects cycles in lock waits, and kills one of the transactions immediately.
X-‐‑lock!
…
X-‐‑lock! …
• MySQL detects cycles in lock waits, and kills one of the transactions immediately.
X-‐‑lock!
…
X-‐‑lock! …
第61页
resolving deadlocks
• MySQL detects cycles in lock waits, and kills one of the transactions immediately.
X-‐‑lock! X-‐‑lock!
• MySQL detects cycles in lock waits, and kills one of the transactions immediately.
X-‐‑lock! X-‐‑lock!
第62页
avoiding deadlocks
• Each client locks everything they need in one atomic request.
• All clients request locks in the same order.
• Each client locks everything they need in one atomic request.
• All clients request locks in the same order.
第63页
avoiding deadlocks
• All clients request locks in the same order.
X-‐‑lock! X-‐‑lock!
…
• All clients request locks in the same order.
X-‐‑lock! X-‐‑lock!
…
第64页
conclusion
第65页
conclusion
• I hope your trip to the museum was educational.
• I hope your trip to the museum was educational.
第66页
license and copyright
Copyright 2015 Bill Karwin http://www.slideshare.net/billkarwin Released under a Creative Commons 3.0 License: http://creativecommons.org/licenses/by-nc-nd/3.0/ You are free to share—to copy, distribute, and transmit this work, under the following conditions:
Attribution. You must attribute this
work to Bill Karwin.
Noncommercial. You may not use this work for commercial
purposes.
No Derivative Works. You may not alter,
transform, or build upon this work.
Copyright 2015 Bill Karwin http://www.slideshare.net/billkarwin Released under a Creative Commons 3.0 License: http://creativecommons.org/licenses/by-nc-nd/3.0/ You are free to share—to copy, distribute, and transmit this work, under the following conditions:
Attribution. You must attribute this
work to Bill Karwin.
Noncommercial. You may not use this work for commercial
purposes.
No Derivative Works. You may not alter,
transform, or build upon this work.