Abstract : Recently, there is a problem in the reception demand , Let's assume that an order consultation exceeds 3 I can't see you again , But if two doctors consult the order at the same time , When checking the database, the number of visits found is 2 Time , Both doctors can see , The so-called reception can be understood as updating the number of reception , There is a problem ( I'm in 4 Time ).

In fact, this problem seems very clear , But a complete solution requires understanding the concepts of transactions and locks , In the past, the isolation level of transactions was a little hazy , Now we can make it clear through this case .

Business

The smallest unit of work to operate the database , To put it simply, it is to put more than one dml( Additions and deletions ) Statement joint completion . Or at the same time , Or fail at the same time . Seeing this, you may find that adding transactions alone can't solve the above problems , And after adding the transaction , The relationship between multiple transactions is related to the isolation level of transactions , So look down .

Transaction isolation level

READ UNCOMMITTED( Read uncommitted , Dirty reading )

Changes in transactions , Even if it's not submitted , It's also visible to other conversations . Can read uncommitted data —— Dirty reading . Dirty reading can cause a lot of problems , Generally, this isolation level is not applicable ..

-- ------------------------- read-uncommitted example ------------------------------
-- Set global system isolation level
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Session A
START TRANSACTION;
SELECT * FROM USER;
UPDATE USER SET NAME="READ UNCOMMITTED";
-- commit; -- Session B
SELECT * FROM USER; //SessionB Console You can see Session A To deal with uncommitted matters , In another Session We also see , This is called dirty reading
id name
2 READ UNCOMMITTED
34 READ UNCOMMITTED

READ COMMITTED( Read submitted , It can't be read repeatedly )

Generally, the database uses this isolation level by default (MySQL No ), This isolation level ensures that if a transaction is not fully successful (commit After execution ), Operations in a transaction are not visible to other sessions .

-- ------------------------- read-cmmitted example ------------------------------
-- Set global system isolation level
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Session A
START TRANSACTION;
SELECT * FROM USER;
UPDATE USER SET NAME="READ COMMITTED";
-- COMMIT; -- Session B
SELECT * FROM USER; //Console OUTPUT:
id name
2 READ UNCOMMITTED
34 READ UNCOMMITTED ---------------------------------------------------
-- When Session A Yes commit,Session B The results are as follows :
id name
2 READ COMMITTED
34 READ COMMITTED

REPEATABLE READ ( Repeatable )

A unified read is performed multiple times in a transaction SQL, The result is the same . This isolation level solves the problem of dirty reading , The problem of unreal reading . This means innodb Of rr Level ,innodb Use in next-key Lock pair " The current reading " To lock , Lock the line and the insertion position that may generate unreal reading , Prevent new data insertion from generating unreal lines .

conversation T1 Execute a query in a transaction , And then the conversation T2 Insert a new line of record , This line of record is just enough for T1 The query conditions used . then T1 Use the same Search the table again , But now I see the business T2 The new line just inserted . This new line is called “ Visions ”, Because of T1 It's like suddenly It's the same .innoDB Of RR Level can't avoid unreal reading completely .

SERIALIZABLE ( Serializable )

The strongest level of isolation , By locking every read row in the transaction , Write with write lock , Make sure there are no unreal reading problems , But it will lead to a lot of timeout and lock contention problems .

mysql The default isolation level is repeatable , Seeing this, you should understand , Even if the isolation level is repeatable , But because select The operation is not locked , All of them will find the qualified data , So here we introduce the concept of lock : Row-level locks .

Row-level locks

  • Shared lock (S)  Shared locks are also called read locks , Read lock allows multiple connections to read the same resource concurrently at the same time , Mutual interference ;

  • Exclusive lock (X)  Exclusive locks are also called write locks , One write lock will block other write locks or read locks , Ensure that only one connection can write data at the same time , At the same time, prevent other users from reading and writing this data .

summary ( Solution )

In fact, the above analysis of so many , The final solution is simple . It's in the original read and update Add up the business , The original select Sentence plus exclusive lock , That is to say select Add... After the statement for update. If there's a business A,B, After joining the exclusive lock , What if A Get lock first , Business B We have to wait for business A commit Then we can start select, So what I read is the latest modified data . As for why not add a shared lock , Except for the possibility of dirty writing , In this case, it can also cause a deadlock . If two things  A 、 B  All read the same row of records , Then add a shared lock to this line , however  A  and B  You need to modify this line in the transaction , Then we have to wait for the other party to release the shared lock , The result is a deadlock .

mysql: How to solve the data modification conflict ( Business + The practical use of row level lock ) More articles about

  1. Mysql stay InnoDB The case of index invalidation row level lock changing to table lock under the engine

    Get ready first , establish InnoDB Engine data table , And added the corresponding index DROP TABLE IF EXISTS `innodb_lock`; CREATE TABLE `innodb_lock` ( `a` ) ...

  2. SQL Server in ROWLOCK Row-level locks

    One .ROWLOCK Use 1.ROWLOCK The row level lock ensures , After the user gets the updated line , Go to the line to update , This period of time is not modified by other users . So row level lock can ensure the consistency of data , It can also improve the concurrency of data operation . 2.ROWLOC ...

  3. Mysql Understanding of transaction and row level lock

    In recent development , Come across a need to check in , Each user can only check in once a day , So how to judge whether a user signs in on the same day ? Because when it comes to table design , Check in once per user , Insert a record into the table , According to the number of records and time to determine whether the user sign on the day ...

  4. Mysql Transaction and row level lock

    Transaction isolation level Database transaction isolation level , It's just about whether one transaction can read the intermediate results of other transactions . Read Uncommitted ( Read uncommitted content ) At this isolation level , All transactions can see the results of other uncommitted transactions . This is isolation ...

  5. Mysql Transaction and row level lock of

    from :http://www.cnblogs.com/edwinchen/p/4171866.html Take check-in as an example , Each user can only check in once a day , So how to judge whether a user signs in on the same day ? Because when the watch was designed , ...

  6. [ Database transaction and lock ] Detailed explanation 5 : MySQL Row level locks in , Table lock , Page level lock

    Indicate the : Reprinted from http://www.hollischuang.com/archives/914 In computer science , Lock is a synchronization mechanism used to restrict resource access forcibly when multithreading , That is, it is used to guarantee the mutual exclusion requirement in concurrency control ...

  7. MySQL Row-level locks , Table lock , Page level lock details

    Page level : engine BDB. Table level : engine MyISAM , Lock the whole watch , You can read it at the same time , I can't write Row level : engine INNODB , A single row of records is locked Table level , Lock the whole table directly , While you're locked in , Other processes cannot write to the table ...

  8. MySQL Row-level locks 、 Table lock 、 Page level lock details

    Link to the original text :http://www.jb51.net/article/50047.htm Page level : engine BDB. Table level : engine MyISAM , Lock the whole watch , You can read it at the same time , I can't write. I can't write : engine INNODB , ...

  9. 【 database 】 Lock mechanism of database ,MySQL Row level locks in , Table lock , Page level lock

    Reprint :http://www.hollischuang.com/archives/914 The reading phenomenon of database is introduced in the analysis , In the case of concurrent access , There may be dirty reading . Non repetition and unreal reading , In order to deal with these problems , The mainstream number ...

  10. turn MySQL Row level locks in , Table lock , Page level lock

      Yes mysql Optimism lock . Pessimistic locking . Shared lock . Exclusive lock . Row lock . Understanding the concept of table lock Reprint . https://blog.csdn.net/puhaiyang/article/details/72284702 Experimental environment ...

Random recommendation

  1. Reptile research - Mainly aimed at ali

    A colleague in the company wants to crawl ali The web page of , I tried it myself , found anti-spdier. Get ready to learn about anti crawlers , See how to deal with ali. http://www.freebuf.com/news/topnews/96 ...

  2. MetaWeblog API call

    http://rpc.cnblogs.com/metaweblog/webenh Hang out on the Internet , Suddenly interested in blog interface , After investigation , Most blogs are right about metaWeblog Api Provided support , although windows ...

  3. MATLAB To improve fwrite and fprintf Functional I/O performance

    Improve fwrite and fprintf Functional I/O performance http://www.matlabsky.com/thread-34861-1-1.html     Today we are going to talk about the famous fwrite(fprint ...

  4. About PowerDesigner

    1. PowerDesigner Change all lowercase to uppercase :Tools->Model Option-> In the menu on the left “Naming conversion”->Column->Code – ...

  5. SQL Some good programming habits

    | Reprinted from :cnblog | Link to the original text :http://www.cnblogs.com/MR_ke/archive/2011/05/29/2062085.html We do software development , Most people can't do without a database ...

  6. ( turn )Sublime Text2 Shortcut key summary

    scene : Recently, I feel more and more how important a handy editor is in writing projects , and sublime, No doubt it is the most comfortable for me to use , The most sensational editor ! 1 Shortcut key summary A good editor , Can greatly improve the efficiency of programming . If you are familiar with software ...

  7. C# DataTable extract Distinct data ( No duplicate data )

    http://blog.csdn.net/jyh_jack/article/details/17959821 DataTable dataTable; DataView dataView = data ...

  8. [ course ] Teach you how to make color 3D Print Groot

    http://mc.dfrobot.com.cn/forum.php?mod=viewthread&tid=24916 preparation : <ignore_js_op>  3D High precision printing G ...

  9. Dockerfile in ENTRYPOINT and CMD The difference between

    One .dockerfile Medium CMD 1. Every dockerfile There can only be one of them CMD If there are multiple, only the last one is executed . 2.CMD Equivalent to startup docker See the parameters added later , A simple example : docker ...

  10. Mouse style ——css international organization

    w3c Mouse style provided by international standards organization : http://css-cursor.techstream.org/