The database locks exist to protect shared resources or objects, This could be Tables, Data Rows, Data Blocks, Cached Items, Connections or even the Entire Database Systems . The locks ensure reliable transaction that adhere to ACID properties of transactional systems . ACID is an acronym that stands for Atomicity, Consistency, Isolation and Durability . In this post I have explained how InnoDB locks works.<\/p>\n
What are the different kinds of locking supported in InnoDB (MySQL 5.7) ?<\/p>\n
Row-level locking in InnoDB Consider transactions T1 \u00a0and T2 . Transaction T1 is holding shared (S) lock on row R1, Let’s see how MySQL handles locks now when Transaction T2 request for lock on same row R1 :<\/p>\n A request for shared (S) lock from T2 will be granted immediately so now both transactions T1 and T2 are holding an S lock on R1<\/p>\n What if \u00a0Transaction T2 is holding an exclusive (X) lock on row R1 ? Then Transaction T2 have to wait for an lock of either type –\u00a0 shared (S) locks \u00a0and exclusive (X) locks , \u00a0So Transaction T2 has to wait for Transaction T1 to release it’s lock on row R1 .<\/p>\n Intention Locks Intention shared lock (IS)<\/strong> indicates that a transaction intends to set a shared (S) lock<\/em> on individual rows of a table<\/p>\n Intention exclusive lock (IX)<\/strong> indicates that a transaction intends to set an exclusive (X) lock on individual rows of a table<\/p>\n In Intention Locks,\u00a0<\/em>Before a transaction can acquire a shared lock on row of a table, It must acquire either\u00a0Intention shared lock (IS)\u00a0<\/strong>or\u00a0Intention exclusive lock (IX)\u00a0<\/strong>on the table and to acquire an exclusive lock on rows of a table, It must first acquire an\u00a0Intention exclusive lock (IX)\u00a0<\/strong>lock on the table . The lock is granted to a requesting transaction only if it is compatible with existing lock, not definitely if it conflicts with existing locks (to avoid possible deadlocks) . The only purpose of intention locks is to show some transaction is locking a row or going to lock a row of a table . You can monitor transaction data of an intention lock with SHOW ENGINE INNODB STATUS\u00a0<\/strong>.<\/p>\n The database locks exist to protect shared resources or objects, This could be Tables, Data Rows, Data Blocks, Cached Items, Connections or even the Entire Database Systems . The locks ensure reliable transaction that adhere […]<\/a><\/p>\n<\/div>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[82,81,15],"tags":[84,83,16],"yoast_head":"\n
\n<\/strong>InnoDB handles row-level locking with types of locks, shared (S) locks \u00a0and exclusive (X) locks . The shared (S) lock allows transaction that holds the lock to read a row and exclusive (X) lock permits the transaction that holds the lock to update or delete a row . I have explained this below with an example<\/p>\n
\n<\/strong>InnoDB permits coexistence of both row locks and table locks. Intention locks in InnoDB are table-level locks that indicates which type of lock (shared or exclusive) a transaction needs in the future for a row in a table . We have two types of intention locks :<\/p>\n