一、樂(lè)觀鎖

1、樂(lè)觀鎖原理

在提交事務(wù)時(shí)檢查自己上次讀取這條記錄后，是否有其他事務(wù)修改了這條記錄，如果沒(méi)有則提交，如果被修改了則回滾。
在對(duì)數(shù)據(jù)庫(kù)進(jìn)行處理的時(shí)候，樂(lè)觀鎖并不會(huì)使用數(shù)據(jù)庫(kù)提供的鎖機(jī)制。

2、實(shí)現(xiàn)樂(lè)觀鎖的方式

一般有三種方式實(shí)現(xiàn)樂(lè)觀鎖

• 一是為數(shù)據(jù)表增加一個(gè)version字段，每次事務(wù)開(kāi)始時(shí)，取出version，在提交事務(wù)時(shí)，檢查version是否有變化，如果沒(méi)有變化提交事務(wù)時(shí)將version + 1，SQL差不多是這樣：

UPDATE T_IRS_RESOURCE
set version = version + 1
where resource_id = ?
and version = ?

• 二是為數(shù)據(jù)表增加一個(gè)時(shí)間戳字段，然后通過(guò)比較時(shí)間戳檢查該數(shù)據(jù)是否被其他事務(wù)修改過(guò)。
• 三是檢查對(duì)應(yīng)的字段的值有沒(méi)有變化，偽代碼如下：

@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中沒(méi)有該數(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ù)庫(kù)自身的for update能力，對(duì)數(shù)據(jù)庫(kù)表或者行增加鎖。
具體for update的原理請(qǐng)自行g(shù)oogle，下面就實(shí)際測(cè)試下for update的不同使用方式。

1、不使用for_update

查詢(xún)和更新都在同一個(gè)with session中，其中query沒(méi)有加for update

測(cè)試結(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é)果中可以看出多線(xiàn)程同時(shí)讀取數(shù)據(jù)并更新時(shí)是亂序的：多個(gè)線(xiàn)程同時(shí)讀取到老的數(shù)據(jù)

2、使用for_update

查詢(xún)和更新都在同一個(gè)with session中，其中query加了for update

測(cè)試結(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é)果中可以看出多線(xiàn)程同時(shí)讀取數(shù)據(jù)并更新時(shí)是順序的：都是一個(gè)線(xiàn)程讀取并更新完成之后，其他線(xiàn)程才能去讀取數(shù)據(jù)并更新，讀到的都是最新的數(shù)據(jù)。

3、在不同session中使用for_update

查詢(xún)和更新不在同一個(gè)with session中，其中query加了for update。

測(cè)試結(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é)果中可以看出多線(xiàn)程同時(shí)讀取數(shù)據(jù)并更新時(shí)是亂序的：多個(gè)線(xiàn)程同時(shí)讀取到老的數(shù)據(jù)

4、在同一session中使用for_update

查詢(xún)和更新在不同的with session中，但是session是同一個(gè)，其中session的autocommit=False, autoflush=False；
在查詢(xún)和更新之后進(jìn)行手動(dòng)commit。其中query的with session加了for update。

測(cè)試結(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é)果中可以看出多線(xiàn)程同時(shí)讀取數(shù)據(jù)并更新時(shí)是順序的：都是一個(gè)線(xiàn)程讀取并更新完成之后，其他線(xiàn)程才能去讀取數(shù)據(jù)并更新，讀到的都是最新的數(shù)據(jù)。

5、在同一session中不使用for_update

查詢(xún)和更新在不同的with session中，但是session是同一個(gè)，其中session的autocommit=False, autoflush=False；
在查詢(xún)和更新之后進(jìn)行手動(dòng)commit。其中query的with session沒(méi)有加for update。

測(cè)試結(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é)果中可以看出多線(xiàn)程同時(shí)讀取數(shù)據(jù)并更新時(shí)是亂序的：多個(gè)線(xiàn)程同時(shí)讀取到老的數(shù)據(jù)

以上就是實(shí)現(xiàn)MySQL數(shù)據(jù)庫(kù)鎖的兩種方式的詳細(xì)內(nèi)容，更多關(guān)于MySQL數(shù)據(jù)庫(kù)鎖的實(shí)現(xiàn)的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論