Shulou(Shulou.com)06/01 Report--

Xiaobian to share with you sql select * from t where c=5 for update exclusive lock example analysis, I believe most people do not know how to share this article for your reference, I hope you read this article after a great harvest, let us go to understand it!

RC isolation level,

update generates an X lock that is not released

Delete statement cannot be executed,

However, UPDATE statements can update records that do not conform to previous X locks.

If the condition is not met, the row lock will be released in time without waiting for the transaction to end.

RC has no concept of gap lock

For non-index field updates, there is a process of locking the full table record.

Updating directly with indexed columns locks only the index lookup values and rows.

RR isolation level, in order to ensure binlog record order

A non-indexed update locks all table records

And there is no gradual release process for unqualified records until the transaction ends.

Neither Delete nor UPDATE statements can be executed

Version 5.7.13

RC mode:

session 1:

begin;

select * from t where c=5 for update;

session 2:

delete from t where c=10

session 3:

insert into t values(100001,8) --Success

session 4 : update t set c=100 where id=10 --Success

session 1:

commit

Session 2: Transaction execution successful

In rr mode:

begin;

select * from t where c=5 for update;

session 2:

delete from t where c=10

session 3:

insert into t values(100001,8) --Wait

session 4 : update t set c=100 where id=10 --Wait

session 1:

commit

Session 2: Transaction execution successful

Session 3: Transaction execution successful

From the above two simple examples, you can get a rough idea of the locking process.

Whether it's rr or rc mode, this statement places MDL S locks on the table at the server level and then goes to the engine level.

RC mode, due to the small amount of data only 10W. It can be proved by experiments that session 1 locks all rows of the table.

This causes other transactions to update all existing records of the table, which is a blocking state. Why is insert successful?

Description rc mode for update statement does not have a gap lock, so do not block insert range plus insert intent lock, so update success.

After session 1 commits,session 2 executes successfully. Indicates that all row x locks are released after transaction commit is complete.

In rr mode,session 1 and session 2 are the same as in rc mode, indicating that X locks are also placed on all lines in rr mode.

The only difference is that insert also waits because in rr mode all records on the cluster index are X-locked for updates without an index. Second, the gaps between each record of the cluster index (GAP) are also added to the GAP lock. Because the gap lock blocks the insert intent lock that the insert is trying to add, the insert is also waiting. Only after session 1 commit is complete. All locks on session 1 will be released,S2,S3 executed successfully

Because the amount of data in the example is still relatively small, if the amount of data reaches tens of millions of levels, it is intuitive to see that locking is a process of locking row by row. Scan one row at a time until all rows are scanned, and put an x lock on all rows in rc mode. In rr mode, not only X locks on all rows, but gap locks on all intervals. The lock on is not released until the transaction commits or the rollback is complete.

The above is "sql select * from t where c=5 for update exclusive lock example analysis" all the content of this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.