Blocking mechanism and the difference between read and write locks

Source: Internet
Author: User
Tags ticket

We may have experienced in development that concurrent operations of the database can cause many problems, for example, lost updates, cannot read, read dirty data (ghost data) and so on, common such as automatic ticketing system, site 1, 2 read out the remaining votes N Zhang, site 1 sold a ticket, and will n-1 write to the database, Site 2 also sold a ticket and wrote the n-1 to the database, but we all know that the n-2 ticket should be left, which is the typical instance of missing data updates. In order to avoid this kind of problem, we adopt the blockade mechanism, simple point is when a thread to the database operation, first to the database with the appropriate lock, so as to avoid other threads of the operation of the database interference.
Concurrency causes errors because different threads concurrently perform transactions that interfere with each other. So we have to control the concurrency of the transaction, that is concurrency control. The method of concurrency control of common DBMS is blocking mechanism and transaction mechanism.
The blockade is to the data table, data rows and so on before the operation, first to the system to request to lock, said the popular point is the operation before the declaration of permissions.
Blocked cycle (link): Apply for lock, get lock permission, release lock permission
There are two basic types of closures: Exclusive locks (Exclusive locks,x locks) and shared locks (Share locks,s locks).

The exclusive lock is also known as a monopoly lock, write lock or x lock, if Sessiona get the exclusive lock permission of a data table, then Sessiona can only read or modify the table, other sessions can neither read nor modify the table, and can not add any type of lock to the table, Until Sessiona releases the exclusive lock permission. Lock way: Lock tables TableName Write, operation as follows:


From the above figure operation can be seen Sessiona access to the HA table exclusive lock permission after sessionb to perform access to the HA table operation and not display ha data, but wait for Sessiona release lock permission.

Shared lock is also known as read lock or S lock, if Sessiona access to a data table share lock permission, then any session (including Sessiona) can only read the table, cannot modify the table, Sessiona can continue to add X lock on the datasheet, Other sessions can continue to add an S lock to the datasheet, but not an X lock, until Sessiona releases the shared lock permission. Lock mode: Set tables TableName read, operations are as follows:


As you can see from the image above, the HA table can be accessed by both Sessiona and SESSIONB after Sessiona has access to the HA table share lock permission, but when Sessiona wants to change the HA table directly error (Error 1099 (HY000): Table ' Ha ' was The locked with a READ lock and can ' t is updated HA table has a shared lock that cannot be modified, and sessionb changed the HA table without displaying the modification success, but waiting for Sessiona to release the shared lock permission.

Note that when Sessiona obtains both S and X lock permissions for a data table, Sessiona can only select the datasheet, and other sessions will not be able to do anything with the table, including select, Update, DROP, delete, lock, and so on. Until Sessiona releases the associated lock permission.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.