實現(xiàn)MySQL數(shù)據(jù)庫鎖的兩種方式
一、樂觀鎖
1、樂觀鎖原理
在提交事務(wù)時檢查自己上次讀取這條記錄后，是否有其他事務(wù)修改了這條記錄，如果沒有則提交，如果被修改了則回滾。
在對數(shù)據(jù)庫進(jìn)行處理的時候，樂觀鎖并不會使用數(shù)據(jù)庫提供的鎖機(jī)制。
2、實現(xiàn)樂觀鎖的方式
一般有三種方式實現(xiàn)樂觀鎖
- 一是為數(shù)據(jù)表增加一個version字段，每次事務(wù)開始時，取出version，在提交事務(wù)時，檢查version是否有變化，如果沒有變化提交事務(wù)時將version + 1，SQL差不多是這樣：
UPDATE T_IRS_RESOURCE set version = version + 1 where resource_id = ? and version = ?
- 二是為數(shù)據(jù)表增加一個時間戳字段，然后通過比較時間戳檢查該數(shù)據(jù)是否被其他事務(wù)修改過。
- 三是檢查對應(yīng)的字段的值有沒有變化，偽代碼如下：
@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ù)也認(rèn)為成功，以便后續(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異常也認(rèn)為成功，以便后續(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；
在查詢和更新之后進(jìn)行手動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；
在查詢和更新之后進(jìn)行手動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ù)庫鎖的兩種方式的詳細(xì)內(nèi)容，更多關(guān)于MySQL數(shù)據(jù)庫鎖的實現(xiàn)的資料請關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
Mysql 5.6.37 winx64安裝雙版本mysql筆記記錄
機(jī)器上現(xiàn)在已經(jīng)存在5.0版本MySQL的情況下裝一個最新版的mysql，下文通過實例代碼給大家介紹Mysql 5.6.37 winx64安裝雙版本mysql筆記記錄，感興趣的朋友一起看看吧2017-07-07
MySQL數(shù)據(jù)庫表分區(qū)注意事項大全【推薦】
這篇文章主要介紹了MySQL數(shù)據(jù)庫表分區(qū)注意事項相關(guān)內(nèi)容，比較全面，這里分享給大家，需要的朋友可以參考。2017-10-10
Windows?Server?2019?MySQL數(shù)據(jù)庫的安裝與配置理論+遠(yuǎn)程連接篇
mysql是一款關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，由MySQL?AB公司開發(fā)，目前屬于Oracle旗下產(chǎn)品，MySQL是最流行的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)之一。MySQL也是一款開源的SQL數(shù)據(jù)庫管理系統(tǒng)，是眾多小型網(wǎng)站作為網(wǎng)站數(shù)據(jù)庫的首選數(shù)據(jù)庫2023-05-05
達(dá)夢數(shù)據(jù)庫獲取SQL實際執(zhí)行計劃方法詳細(xì)介紹
在達(dá)夢數(shù)據(jù)庫中，使用EXPLAIN語句可以查看sql的執(zhí)行計劃，但EXPLAIN只生成執(zhí)行計劃，并不會真正執(zhí)行SQL語句，因此產(chǎn)生的執(zhí)行計劃有可能不準(zhǔn)。本章將帶領(lǐng)大家了解多種獲取SQL實際的執(zhí)行計劃的方法2022-10-10
關(guān)于MySQL主從復(fù)制的幾種復(fù)制方式總結(jié)
這篇文章主要給大家介紹了關(guān)于MySQL主從復(fù)制的幾種復(fù)制方式，文中通過示例代碼介紹的非常詳細(xì)，對大家學(xué)習(xí)或者使用MySQL具有一定的參考學(xué)習(xí)價值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2020-08-08
本篇文章小編為大家介紹，關(guān)于MySQL的安全問題從安裝開始說起，有需要的朋友可以參考一下2013-04-04
這篇文章主要介紹了MySQL5.7 集群配置的步驟，幫助大家更好的理解和學(xué)習(xí)使用MySQL，感興趣的朋友可以了解下2021-03-03
在本篇文章中小編給大家整理了一篇關(guān)于MySQL用limit方式實現(xiàn)分頁的實例方法，有需要的朋友們可以參考學(xué)習(xí)下。2020-01-01
這篇文章主要介紹了如何安全地關(guān)閉MySQL，幫助大家更好的理解和使用MySQL，感興趣的朋友可以了解下2020-12-12
Mysql刪除重復(fù)數(shù)據(jù)并且只保留一條(附實例!)
最近有朋友打電話尋求一個SQL相關(guān)的問題,大致是表中存在重復(fù)數(shù)據(jù),需要刪除掉重復(fù)數(shù)據(jù)保留一條的場景,下面這篇文章主要給大家介紹了關(guān)于Mysql刪除重復(fù)數(shù)據(jù)并且只保留一條的相關(guān)資料,需要的朋友可以參考下2023-02-02