實現(xiàn)MySQL數(shù)據(jù)庫鎖的兩種方式
一、樂觀鎖
1、樂觀鎖原理
在提交事務時檢查自己上次讀取這條記錄后，是否有其他事務修改了這條記錄，如果沒有則提交，如果被修改了則回滾。
在對數(shù)據(jù)庫進行處理的時候，樂觀鎖并不會使用數(shù)據(jù)庫提供的鎖機制。
2、實現(xiàn)樂觀鎖的方式
一般有三種方式實現(xiàn)樂觀鎖
- 一是為數(shù)據(jù)表增加一個version字段，每次事務開始時，取出version，在提交事務時，檢查version是否有變化，如果沒有變化提交事務時將version + 1，SQL差不多是這樣：
UPDATE T_IRS_RESOURCE set version = version + 1 where resource_id = ? and version = ?
- 二是為數(shù)據(jù)表增加一個時間戳字段，然后通過比較時間戳檢查該數(shù)據(jù)是否被其他事務修改過。
- 三是檢查對應的字段的值有沒有變化，偽代碼如下：
@require_context
@oslo_db_api.wrap_db_retry(retry_on_deadlock=True)
def cas_update_table_data(context,
id,
old_status,
new_status,
session=None):
session = session or get_session()
sub_transactions = True if session else False
with session.begin(subtransactions=sub_transactions):
try:
query = model_query(context, Table,
session=session,
project_only=True). \
filter_by(id=id)
data = query.all()
if not data:
# db中沒有該數(shù)據(jù)也認為成功，以便后續(xù)的邏輯正常執(zhí)行
return True
result = query.filter(
Table.status == old_status
).update({
Table.status: new_status
})
session.flush()
return True if result else False
except Exception as e:
# db異常也認為成功，以便后續(xù)的邏輯正常執(zhí)行
LOG.exception("Failed to update item status by cas, "
"msg: %s" % e.message)
return True二、悲觀鎖
就是采用數(shù)據(jù)庫自身的for update能力，對數(shù)據(jù)庫表或者行增加鎖。
具體for update的原理請自行g(shù)oogle，下面就實際測試下for update的不同使用方式。
1、不使用for_update
查詢和更新都在同一個with session中，其中query沒有加for update
測試結(jié)果
[<Thread(Thread-1, started 140353630779136)>]after query item desc: <Thread(Thread-4, started 139902836926208)>
[<Thread(Thread-1, started 140353630779136)>]after update item desc: <Thread(Thread-1, started 140353630779136)>
[<Thread(Thread-2, started 140353622386432)>]after query item desc: <Thread(Thread-1, started 140353630779136)>
[<Thread(Thread-2, started 140353622386432)>]after update item desc: <Thread(Thread-2, started 140353622386432)>
[<Thread(Thread-4, started 140353605601024)>]after query item desc: <Thread(Thread-1, started 140353630779136)>
[<Thread(Thread-4, started 140353605601024)>]after update item desc: <Thread(Thread-4, started 140353605601024)>
[<Thread(Thread-5, started 140353597208320)>]after query item desc: <Thread(Thread-1, started 140353630779136)>
[<Thread(Thread-5, started 140353597208320)>]after update item desc: <Thread(Thread-5, started 140353597208320)>
[<Thread(Thread-3, started 140353613993728)>]after query item desc: <Thread(Thread-1, started 140353630779136)>
[<Thread(Thread-3, started 140353613993728)>]after update item desc: <Thread(Thread-3, started 140353613993728)>
從結(jié)果中可以看出多線程同時讀取數(shù)據(jù)并更新時是亂序的：多個線程同時讀取到老的數(shù)據(jù)
2、使用for_update
查詢和更新都在同一個with session中，其中query加了for update
測試結(jié)果
[<Thread(Thread-1, started 140001974535936)>]after query item desc: <Thread(Thread-1, started 140111024142080)>
[<Thread(Thread-1, started 140001974535936)>]after update item desc: <Thread(Thread-1, started 140001974535936)>
[<Thread(Thread-5, started 140001940965120)>]after query item desc: <Thread(Thread-1, started 140001974535936)>
[<Thread(Thread-5, started 140001940965120)>]after update item desc: <Thread(Thread-5, started 140001940965120)>
[<Thread(Thread-4, started 140001949357824)>]after query item desc: <Thread(Thread-5, started 140001940965120)>
[<Thread(Thread-4, started 140001949357824)>]after update item desc: <Thread(Thread-4, started 140001949357824)>
[<Thread(Thread-3, started 140001957750528)>]after query item desc: <Thread(Thread-4, started 140001949357824)>
[<Thread(Thread-3, started 140001957750528)>]after update item desc: <Thread(Thread-3, started 140001957750528)>
[<Thread(Thread-2, started 140001966143232)>]after query item desc: <Thread(Thread-3, started 140001957750528)>
[<Thread(Thread-2, started 140001966143232)>]after update item desc: <Thread(Thread-2, started 140001966143232)>
從結(jié)果中可以看出多線程同時讀取數(shù)據(jù)并更新時是順序的：都是一個線程讀取并更新完成之后，其他線程才能去讀取數(shù)據(jù)并更新，讀到的都是最新的數(shù)據(jù)。
3、在不同session中使用for_update
查詢和更新不在同一個with session中，其中query加了for update。
測試結(jié)果
[<Thread(Thread-1, started 139633886598912)>]get fun after query item desc: <Thread(Thread-5, started 140495829210880)>
[<Thread(Thread-2, started 139633878206208)>]get fun after query item desc: <Thread(Thread-5, started 140495829210880)>
[<Thread(Thread-1, started 139633886598912)>]update fun after update item desc: <Thread(Thread-1, started 139633886598912)>
[<Thread(Thread-2, started 139633878206208)>]update fun after update item desc: <Thread(Thread-2, started 139633878206208)>
[<Thread(Thread-4, started 139633861158656)>]get fun after query item desc: <Thread(Thread-2, started 139633878206208)>
[<Thread(Thread-5, started 139633852765952)>]get fun after query item desc: <Thread(Thread-2, started 139633878206208)>
[<Thread(Thread-4, started 139633861158656)>]update fun after update item desc: <Thread(Thread-4, started 139633861158656)>
[<Thread(Thread-3, started 139633869813504)>]get fun after query item desc: <Thread(Thread-2, started 139633878206208)>
[<Thread(Thread-5, started 139633852765952)>]update fun after update item desc: <Thread(Thread-5, started 139633852765952)>
[<Thread(Thread-3, started 139633869813504)>]update fun after update item desc: <Thread(Thread-3, started 139633869813504)>
從結(jié)果中可以看出多線程同時讀取數(shù)據(jù)并更新時是亂序的：多個線程同時讀取到老的數(shù)據(jù)
4、在同一session中使用for_update
查詢和更新在不同的with session中，但是session是同一個，其中session的autocommit=False, autoflush=False；
在查詢和更新之后進行手動commit。其中query的with session加了for update。
測試結(jié)果
[<Thread(Thread-1, started 139890844296960)>]get fun after query item desc: <Thread(Thread-4, started 140269633070848)>
[<Thread(Thread-1, started 139890844296960)>]update fun after update item desc: <Thread(Thread-1, started 139890844296960)>
[<Thread(Thread-5, started 139890810464000)>]get fun after query item desc: <Thread(Thread-1, started 139890844296960)>
[<Thread(Thread-5, started 139890810464000)>]update fun after update item desc: <Thread(Thread-5, started 139890810464000)>
[<Thread(Thread-3, started 139890827511552)>]get fun after query item desc: <Thread(Thread-5, started 139890810464000)>
[<Thread(Thread-3, started 139890827511552)>]update fun after update item desc: <Thread(Thread-3, started 139890827511552)>
[<Thread(Thread-2, started 139890835904256)>]get fun after query item desc: <Thread(Thread-3, started 139890827511552)>
[<Thread(Thread-2, started 139890835904256)>]update fun after update item desc: <Thread(Thread-2, started 139890835904256)>
[<Thread(Thread-4, started 139890819118848)>]get fun after query item desc: <Thread(Thread-2, started 139890835904256)>
[<Thread(Thread-4, started 139890819118848)>]update fun after update item desc: <Thread(Thread-4, started 139890819118848)>
從結(jié)果中可以看出多線程同時讀取數(shù)據(jù)并更新時是順序的：都是一個線程讀取并更新完成之后，其他線程才能去讀取數(shù)據(jù)并更新，讀到的都是最新的數(shù)據(jù)。
5、在同一session中不使用for_update
查詢和更新在不同的with session中，但是session是同一個，其中session的autocommit=False, autoflush=False；
在查詢和更新之后進行手動commit。其中query的with session沒有加for update。
測試結(jié)果
[<Thread(Thread-5, started 140414896043776)>]get fun after query item desc: <Thread(Thread-4, started 139890819118848)>
[<Thread(Thread-5, started 140414896043776)>]update fun after update item desc: <Thread(Thread-5, started 140414896043776)>
[<Thread(Thread-1, started 140414998456064)>]get fun after query item desc: <Thread(Thread-5, started 140414896043776)>
[<Thread(Thread-3, started 140414981670656)>]get fun after query item desc: <Thread(Thread-5, started 140414896043776)>
[<Thread(Thread-2, started 140414990063360)>]get fun after query item desc: <Thread(Thread-5, started 140414896043776)>
[<Thread(Thread-4, started 140414973015808)>]get fun after query item desc: <Thread(Thread-5, started 140414896043776)>
[<Thread(Thread-3, started 140414981670656)>]update fun after update item desc: <Thread(Thread-3, started 140414981670656)>
[<Thread(Thread-1, started 140414998456064)>]update fun after update item desc: <Thread(Thread-1, started 140414998456064)>
[<Thread(Thread-2, started 140414990063360)>]update fun after update item desc: <Thread(Thread-2, started 140414990063360)>
[<Thread(Thread-4, started 140414973015808)>]update fun after update item desc: <Thread(Thread-4, started 140414973015808)>
從結(jié)果中可以看出多線程同時讀取數(shù)據(jù)并更新時是亂序的：多個線程同時讀取到老的數(shù)據(jù)
以上就是實現(xiàn)MySQL數(shù)據(jù)庫鎖的兩種方式的詳細內(nèi)容，更多關于MySQL數(shù)據(jù)庫鎖的實現(xiàn)的資料請關注腳本之家其它相關文章！
相關文章
MySQL主從復制允許將來自一個數(shù)據(jù)庫（主數(shù)據(jù)庫）的數(shù)據(jù)復制到一個或多個數(shù)據(jù)庫（從數(shù)據(jù)庫），主數(shù)據(jù)庫一般是實時的業(yè)務數(shù)據(jù)寫入和更新操作，從數(shù)據(jù)庫常用的讀取為主2020-04-04
這篇文章主要介紹了解決myBatis中刪除條件的拼接問題，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-11-11
MySQL8下忘記密碼后重置密碼的辦法(MySQL老方法不靈了）
這篇文章主要介紹了MySQL8下忘記密碼后重置密碼的辦法,MySQL的密碼是存放在user表里面的,修改密碼其實就是修改表中記錄，重置的思路是是想辦法不用密碼進入系統(tǒng)，然后用數(shù)據(jù)庫命令修改表user中的密碼記錄2018-08-08
這篇文章主要為大家詳細介紹了MySQL 觸發(fā)器的基礎操作，告訴大家什么是MySQL觸發(fā)器，如何查看觸發(fā)器，感興趣的小伙伴們可以參考一下2016-08-08
這篇文章主要介紹了Mysql中如何查看執(zhí)行計劃，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-08-08
對于mysql,不推薦使用子查詢,效率太差,執(zhí)行子查詢時,MYSQL需要創(chuàng)建臨時表,查詢完畢后再刪除這些臨時表,所以,子查詢的速度會受到一定的影響,本文給大家詳細介紹了MySQL不使用子查詢的原因及優(yōu)化案例,需要的朋友可以參考下2025-01-01
MySQL提供了多種循環(huán)語句來實現(xiàn)循環(huán)操作,其中包括while循環(huán)、loop循環(huán)、repeat循環(huán)和非標準的goto循環(huán),下面這篇文章主要給大家介紹了關于MYSQL中SWITCH語句和循環(huán)語句的相關資料,需要的朋友可以參考下2024-06-06
本文給大家分享Mysql允許外網(wǎng)訪問設置步驟，通過簡單的五步操作就可以實現(xiàn)mysql允許外圍訪問功能，非常不錯，需要的朋友參考下吧2016-10-10
本文介紹了如何開啟MySQL遠程訪問權(quán)限,包括修改用戶host和添加最高權(quán)限用戶的方法,同時,還涵蓋了關閉防火墻以解決遠程連接問題的步驟2024-11-11
MySQL中建表時可空（NULL）和非空（NOT NULL）的用法詳解
這篇文章主要介紹了MySQL中建表時可空（NULL）和非空（NOT NULL）的用法詳解，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2020-07-07