ITPub博客

首页 > Linux操作系统 > Linux操作系统 > innodb事务锁小结

innodb事务锁小结

原创 Linux操作系统 作者:myownstars 时间:2013-09-06 16:29:01 0 删除 编辑

https://blogs.oracle.com/mysqlinnodb/entry/introduction_to_transaction_locks_in

innodb事务锁需要用到的数据结构:

enum lock_mode – 枚举锁模式

lock_t 代表一个表锁lock_table_t或行锁lock_rec_t,行锁的hash table位于lock_sys_t  (hash_table_t)

trx_t  代表一个事务,其中的 trx_lock_t  事务锁列表;trx_sys_t 管理活跃事务

dict_table_t 表描述符

 

1 锁模式

enum lock_mode {

        LOCK_IS = 0,    /* intention shared */

        LOCK_IX,        /* intention exclusive */

        LOCK_S,         /* shared */

        LOCK_X,         /* exclusive */

        LOCK_AUTO_INC,  /* locks the auto-inc counter of a table in an exclusive mode */

        LOCK_NONE,      /* this is used elsewhere to note consistent read */

        LOCK_NUM = LOCK_NONE, /* number of lock modes */

        LOCK_NONE_UNSET = 255

};

 

2 /行锁结构

lock_t用于表示一个表锁lock_table_t,或者一组行锁lock_rec_t

struct lock_t {

        trx_t*          trx;

        ulint           type_mode;

        hash_node_t     hash;

        dict_index_t*   index;

        union {

                lock_table_t    tab_lock;/*!< table lock */

                lock_rec_t      rec_lock;/*!< record lock */

        } un_member;                    /*!< lock        details */

};

 

/** A table lock */

struct lock_table_t {

        dict_table_t*   table;          /*!< database table in dictionary cache */

        UT_LIST_NODE_T(lock_t) locks;   /*!< list of locks on the same table */

};

 

/** Record lock for a page */

struct lock_rec_t {

        ulint   space;                  /*!< space id */

        ulint   page_no;                /*!< page number */

        ulint   n_bits;                 /*!< number of bits in the lock bitmap; NOTE: the lock bitmap is placed immediately after the lock struct */

};

Lock bitmap用于表示行锁,紧邻lock struct存放,每bit代表页内1行数据,使用heap_no对应;例如bitmap的第5位如果激活,则表明heap-no=5的行被锁定;

 

Innodb锁系统有1个全局对象lock_sys(type lock_sys_t),而行锁的hash table就存储在其中

struct lock_sys_t {

        ib_mutex_t      mutex;  

        hash_table_t*   rec_hash;   --行锁hash表,以(space_id, page_no)hash key,即同一页的所有锁均在一个hash bucket上,

        ulint           n_lock_max_wait_time;

        // more ...

};

Heap_no

一条行记录可由(space_id, page_no, heap_no)唯一标识,heap_no0(infimum)/1(supremum)开始,按照记录单向链表顺序递增;

If we have inserted 10 records in the page, and if there has been no updates on the page, then the heap_no of the user records would be 2, 3, 4, 5 10, 11. The heap_no will be in the same order in which the records will be accessed in ascending order

 

 

3 事务相关锁结构

struct trx_t {

        trx_id_t        id;        /*!< transaction id */

              trx_lock_t      lock;        /*!< Information about the transaction locks and state. Protected by trx->mutex or lock_sys->mutex or both */

};

struct trx_lock_t {

.

        ib_vector_t*    table_locks;    /*!< All table locks requested by this  transaction, including AUTOINC locks */

.

        UT_LIST_BASE_NODE_T(lock_t)  trx_locks;   /*!< locks requested  by the transaction;  insertions are protected by trx->mutex and lock_sys->mutex; removals are  protected by lock_sys->mutex */

 

};

Innodb事务系统有1个全局对象trx_sys(type trx_sys_t)

struct trx_sys_t{

          // more ...

        trx_list_t      rw_trx_list;    /*!< List of active and committed in memory read-write transactions, sorted on trx id, biggest first. Recovered transactions are always on this list. */

        trx_list_t      ro_trx_list;    /*!< List of active and committed in memory read-only transactions, sorted on trx id, biggest first.

                                                                    NOTE: The order for read-only transactions is not necessary. We should exploit this and increase concurrency during add/remove. */

        // more ...

};

由注释部分可知,事务分为read-writeread-only,其中read-only事务对顺序并无严格要求;

 

 

4 表描述符

Innodb中每个表在数据字典中都对应一个dict_table_t,类似进程的task_struct

struct dict_table_t{

 

        // more

 

        table_id_t      id;     /*!< id of the table */

        char*           name;   /*!< table name */

 

        UT_LIST_BASE_NODE_T(lock_t) locks;  /*!< list of locks on the table; protected by lock_sys->mutex */

};

 

 

 

事务用到的锁按粒度可划分为表级和行级

意向锁和行锁兼容关系,如下表所示,意向锁之间相互兼容

X

IX

S

IS

 

X

Conflict

Conflict

Conflict

Conflict

IX

Conflict

Compatible

Conflict

Compatible

S

Conflict

Conflict

Compatible

Compatible

IS

Conflict

Compatible

Compatible

Compatible

 

表级事务锁

确保某事务访问表时其他事务不会修改表结构;

sql engine提供了MDLInnodb也有相应意向锁LOCK_IS/LOCK_IX

On tables, InnoDB normally acquires only intentional shared (LOCK_IS) or intentional exclusive (LOCK_IX) modes. It does not lock the tables in shared mode (LOCK_S) or exclusive mode (LOCK_X) unless explicitly requested via LOCK TABLES command. One exception to this is in the prepare phase of the online alter table command, where the table is locked in shared mode

 

意向共享锁LOCK_IS

mysql> set transaction isolation level serializable;

mysql> start transaction;

mysql> select * from t1;

5.6只能通过debugger查看

(gdb) set $trx_locklist = trx_sys->rw_trx_list->start->lock->trx_locks

(gdb) p $trx_locklist.start->un_member->tab_lock->table->name

$21 = 0x7fffb0014f70 "test/t1"

(gdb) p lock_get_mode(trx_sys->rw_trx_list->start->lock->trx_locks->start)

$25 = LOCK_IS

(gdb)

 

意向排他锁

mysql> set transaction isolation level serializable;

mysql> start transaction;

mysql> select * from t1;

mysql> insert into t1 values (4, 'W');

t12个锁,selectLOCK_ISinsertLOCK_IX

 

获取表锁的内部过程

每个表依靠dict_table_t唯一标识,调用lock_table()

1 每个表锁请求需提供如下信息:候选表dict_lock_t;锁模式enum lock_mode;所属事务trx_t

2 检查该事务是否已经获取了equal/stronger锁,每个事务通过trx_t::trx_lock_t::table_locks维护已获取的表锁

3 检查其他事务是否已经获取了incompatible锁,表描述符维护锁队列dict_table_t::locks,如果存在则必须等待

4 分配一个锁结构体lock_t,采用第1步的信息将其初始化;并将其添加至dict_table_t::lockstrx_t::trx_lock_t::table_locks

 

 

 

 

行级事务锁

也分2种,隐式和显式;

隐式行锁:不分配lock_t,通过事务的ID计算得来(logically arrived at based on the transaction information in the clustered index or secondary index record)

Lock0lock.cc

If a transaction has modified or inserted an index record, then it owns an implicit x-lock on the record. On a secondary index record, a transaction has an implicit x-lock also if it has modified the clustered index record, the max trx id of the page where the secondary index record resides is >= trx id of the transaction (or database recovery is running), and there are no explicit non-gap lock requests on the secondary index record.

 

当事务执行DML时,对应记录被添加隐式x-lock;修改聚簇索引记录时,其相应的二级索引也会被添加隐式x-lock

事务获取行锁时,首先要检查是否有implicit lock,然后再检查explicit lock

聚簇索引: 检测记录是否已经包含有效事务ID, 如果有表明该事务正在持有implicit exclusive locklock_clust_rec_some_has_impl()

二级索引:假定R1为二级索引记录,获取该页最大事务ID T1,而T2为当前innodb系统minimum事务ID,如果T2>T1则表明没有implicit lock;否则获取R1对应的聚集索引记录及其事务ID  T3,如果T3R1做过DML,则T3R1implicit exclusive lock;对二级索引记录需借助于undo log判定是否有implicit exclusive lock lock_sec_rec_some_has_impl()

 

显示行锁:申请lock_thash table,分为共享和排它;

Lock_S

注:对于repeatable read或更低级别的事务隔离,innodb不会使用共享行锁

mysql> set transaction isolation level serializable;

mysql> start transaction;

mysql> select * from t1;

与上面的例子相似,不同的是这次要debug行锁

(gdb) set $trx_locklist = trx_sys->rw_trx_list->start->lock->trx_locks

(gdb) set $rowlock = $trx_locklist.start->trx_locks->next

(gdb) p $rowlock

$47 = (ib_lock_t *) 0x7fffb00103f0

(gdb) p lock_get_mode($rowlock)

$48 = LOCK_S

(gdb) p *$rowlock     --查看行锁结构

$43 = {trx = 0x7fffb0013f78, trx_locks = {prev = 0x7fffb00103a8, next = 0x0},

  type_mode = 34, hash = 0x0, index = 0x7fffb001a6b8, un_member = {tab_lock = {

      table = 0xc, locks = {prev = 0x3, next = 0x48}}, rec_lock = {space = 12,

      page_no = 3, n_bits = 72}}}   --n_bits为行锁位图,共9字节72

(gdb) x /4t $rowlock + 1   --检查前4个字节,有4行记录加锁

0x7fffb0010438: 00011110       00000000       00000000       00000000

 

Lock_X

mysql> set transaction isolation level serializable;

mysql> start transaction;

mysql> select * from t1 for update;

 

如何获取显式行锁?

-- lock_rec_lock()

1 检查事务是否已经获取了explicit lock(通过global hash table完成),如果没有继续下一步

2 检查其他事务是否已经有不兼容锁,有则等待

3 检查该行是否已有lock struct,有则重用,无则申请

4 初始化lock structtrx/lock mode/page_no/space_id/lock bitmap,依据行的heap_no设置bitmap

5 将此lock struct插入global hash table,同时加入该事务的transaction lock列表trx_t::trx_lock_t::trx_locks

 

 

释放事务锁

事务commit/rollback时释放,可通过trx_t::trx_lock_t::trx_locks获取某事务上的所有事务锁;

对于repeatable/serializable readsql layer无法在事务结束前是否锁;而read committed则允许sql layer调用ha_innobase::unlock_row()在事务结束之前释放锁;

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15480802/viewspace-772297/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-03-18

  • 博文量
    375
  • 访问量
    3085999