摘要：今天就講講的鎖主講的悲觀鎖和樂觀鎖官方意思就是你普通的查詢，并有保護(hù)功能，也就是沒有鎖，會(huì)出現(xiàn)事務(wù)執(zhí)行的時(shí)候，數(shù)據(jù)出現(xiàn)錯(cuò)亂，然而提供了引擎支持種鎖機(jī)制。

主講：Mysql的悲觀鎖 和 樂觀鎖
官方:If you query data and then insert or update related data within the same transaction, the regular SELECT statement does not give enough protection. Other transactions can update or delete the same rows you just queried. InnoDB supports two types of locking reads that offer extra safety:
意思就是你普通的查詢sql，并有保護(hù)功能，也就是沒有鎖，會(huì)出現(xiàn)事務(wù)執(zhí)行的時(shí)候，數(shù)據(jù)出現(xiàn)錯(cuò)亂，然而MySQL提供了innodb引擎支持2種鎖機(jī)制。

1.悲觀鎖：
????概念：
????????a) 官方：For index records the search encounters, SELECT … FOR UPDATE locks the rows and any associated index entries, the same as if you issued an UPDATE statement for those rows. Other transactions are blocked from updating those rows, from doing SELECT … LOCK IN SHARE MODE, or from reading the data in certain transaction isolation levels. Consistent reads ignore any locks set on the records that exist in the read view. (Old versions of a record cannot be locked; they are reconstructed by applying undo logs on an in-memory copy of the record.
????????b) 簡(jiǎn)單粗俗點(diǎn)就是直接占為己有，等自己用完了才給別人去使用，這種能保證數(shù)據(jù)的操作不會(huì)出錯(cuò)，獲得這個(gè)鎖的人可以修改，查詢數(shù)據(jù)，其他人則什么操作都做不了。
????使用語法：select * from tb for update

2.樂觀鎖：
????概念：
????????a) 官方：SELECT ... LOCK IN SHARE MODE sets a shared mode lock on the rows read. A shared mode lock enables other sessions to read the rows but not to modify them. The rows read are the latest available, so if they belong to another transaction that has not yet committed, the read blocks until that transaction ends.
????????b)簡(jiǎn)單點(diǎn)說就是進(jìn)行寬松鎖的限制，也就是別人也可以疊加鎖上去，然后大家也都可以進(jìn)行select操作，但不能進(jìn)行update或者delete操作（也就是修改操作），哪怕大家的事務(wù)都還沒提交，大家都可以普通的 select 都是可以查詢出數(shù)據(jù)的一定要注意：這個(gè)時(shí)候select是沒有影響的
????使用語法：select...lock in share mode

下面內(nèi)容一定要注意：
All locks set by LOCK IN SHARE MODE and FOR UPDATE queries are released when the transaction is committed or rolled back.

Note
Locking of rows for update using SELECT FOR UPDATE only applies when autocommit is disabled (either by beginning transaction with START TRANSACTION or by setting autocommit to 0. If autocommit is enabled, the rows matching the specification are not locked.

所有的鎖都在事務(wù)回滾或者結(jié)束后釋放。
注意：
使用select..for update 進(jìn)行行鎖，只有在禁用autocommit的時(shí)候生效（或者在START TRANSACTION 或者 設(shè)置 autocommit為0.）如果autocommit是啟用的，那這一行的for update是不會(huì)生效的。

????????更多精彩內(nèi)容也可以關(guān)注此二維碼：
????????