MySQL鎖的基本介紹

1.mysql鎖的基本介紹:
相對其他資料庫而言,MySQL的鎖機制比較簡單,其最顯著的特點的是不同的存盤引擎支持不同的鎖機制，比如,MyISAM和MEMORY存盤引擎采用的是表級鎖(table-level locking);InnoDB存盤引擎既支持行級鎖(row-level locking),也支持表級鎖,但默認情況下是采用行級鎖，

表級鎖:開銷小,加鎖快;不會出現死鎖;鎖定粒度大,發生鎖沖突的概率最高,并發度最低，
行級鎖:開銷大,加鎖慢,會出現死鎖,鎖定粒度最小,發生鎖沖突的概率最低,并發度也最高，
從上述特點,很難籠統地說哪種鎖更好,只能具體應用的特點來說哪種鎖更合適:但從鎖的角度 來說: 表級鎖更適合于以查詢為主,只有少量按索引

2.MyISAM表鎖
MyISAM的表級鎖有兩種模式:表共享讀鎖和表獨占寫鎖
對MySAM表的讀操作,不會阻塞其他用戶對同一表的讀請求,但會阻塞對同一表的寫請求;對MyISAM表的寫請求;則會阻塞其他用戶對于同一表的讀和寫操作:MyISAM表的讀操作和寫操作之間,以及寫操作之間是串行的!

MyISAM寫鎖阻塞讀的案例
首先創建資料庫表,存盤引擎使用MyISAM

CREATE TABLE mylock (
id int(11) NOT NULL AUTO_INCREMENT,
NAME varchar(20) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO mylock (id, NAME) VALUES (‘1’, ‘a’);
INSERT INTO mylock (id, NAME) VALUES (‘2’, ‘b’);
INSERT INTO mylock (id, NAME) VALUES (‘3’, ‘c’);
INSERT INTO mylock (id, NAME) VALUES (‘4’, ‘d’);

首先我們在會話一中設定表寫鎖:lock table mylock write;

我們在會話二中進行查詢此表,發現被阻塞了

此時我們釋放會話一的表寫鎖:unlock tables,發現會話二的讀操作結果便出來了


同樣的操作,我們對會話一進行表寫鎖,會話二的update操作也被阻塞,直到會話一的鎖釋放,會話二的update操作才能夠繼續進行下去，

MyISAM讀阻塞寫的案例
首先給會話一加上讀鎖:lock table mylock read

我們發現無論在會話一還是會話二中,對于此表的讀操作都沒有被阻塞:


此時我們對會話二的寫操作會出現阻塞

而我們對會話一的寫操作則會直接報錯:

錯誤提示也很明顯,告訴已經被讀鎖占用,無法被修改(寫入)
由此我們可見,MyISAM的寫鎖會阻塞其他會話的讀寫操作,而不會阻塞當前會話的任何操作,而MyISAM的讀鎖則會阻塞其他會話的寫操作,而不會阻塞所有的讀操作，但是我們也發現了一個問題,這種表級別的鎖過于暴力,當一個會話上了讀鎖之后,所有的會話都無法做寫操作,這是效率極其低下的，

MySAM在執行查詢陳述句之前,會自動給涉及的所有表加讀鎖,在執行更新操作前,會自動給涉及的所有表加寫鎖,這個程序并不需要用戶干預,因此用戶一般不需要命令來顯示的加鎖

MyISAM表的讀和寫時串行的,這是就總體而言的,在一定條件下,MyISAM也支持查詢和插入操作的并發執行:

首先我們在會話一先加上本地的讀鎖:lock table mylock read local

當前會話無法對資料進行任何的寫操作

而在會話二中我們可以進行寫入操作

但是會話一在未釋放鎖時卻無法查詢到此條資料

而當會話一釋放了讀鎖之后,就可以查看到這條資料了

InnoDB的行鎖模式及加鎖方法
共享鎖: 又稱讀鎖，允許一個事務去讀一行,組織其他事務獲得相同資料集的排它鎖.若事務T對物件A加上S鎖,則事務T可以讀A但不能修改A,其他事務只能再對A加S鎖,而不能加X鎖,直到T釋放A上的S鎖，這保證了其他事務可以讀A,但在T釋放A上的S鎖之前不能對A做任何修改，
排他鎖: 又稱寫鎖,允許獲取其他鎖的事務更新資料,阻止其他事務取得相同的資料集共享共享鎖和排他寫鎖，若事務T對資料物件A加上X鎖,事務T可以讀A也可以修改A,其他排他鎖可以做select***for update 陳述句,加共享鎖可以使用select *** lock in share mode陳述句，所以加排他所的資料行在其他事務中是不能修改資料的,也不能通過for update和lock in share mode 鎖的方式查詢資料,但可以直接通過select ** from 查詢資料,因為普通查詢沒有任何鎖機制

InnoDB行鎖的實作方式
InnoDB行鎖時通過給索引上的索引項加鎖來實作的,這一點MySQL和Oracle不同,后者是通過在資料塊中相應資料行加鎖來實作的，InnoDB這種行鎖特點意味著,只有通過條件索引檢索資料,InnoDB才使用行鎖,否則,InnoDB將使用表鎖

首先我們演示不通過索引條件查詢的時候,innodb使用的是表鎖而不是行鎖:
create table tab_no_index(id int,name varchar(10)) engine=innodb;
insert into tab_no_index values(1,‘1’),(2,‘2’),(3,‘3’),(4,‘4’);

我們在會話一中id為1使用排他鎖:for update

我們在會話二中id為2使用排它鎖,然而我們發現,會話二的行鎖排他鎖被阻塞了

因此我們得出結論,當不存在索引的情況,Innodb是為表加上表鎖;

其次我們演示帶有索引的行鎖排他鎖,首先創建表:
create table tab_with_index(id int,name varchar(10)) engine=innodb;
alter table tab_with_index add index id(id);
insert into tab_with_index values(1,‘1’),(2,‘2’),(3,‘3’),(4,‘4’);

我們同樣在會話一中對id為1的資料進行行鎖排他鎖:

其次我們對會話二中對id為2的資料進行行鎖排他鎖:

此時我們發現,id為2的資料在會話二中正常上鎖,此時可以證明當存在索引的時候,Innodb使用的是行級鎖.

下面我們證明Innodb的行鎖時對索引進行上鎖,而不是對資料行進行上鎖,我們向資料庫插入一條id為1,值為4的資料
insert into tab_with_index values(1,‘4’);
此時我們在會話一對id為1,name為ll的資料進行行鎖

其次,我們在會話二對id為1,name為4的資料進行行鎖:

此時我們驚奇的發現,對id為1,name為4的資料進行行鎖的時候,會話二被阻塞了,這是因為id列是索引列,而Innodb的行鎖就是對索引進行上鎖,因此會話二會被阻塞.