Transactions and locks in MySQL

This article introduces the relevant knowledge of database transactions and locks in detail. Mainly some conceptual things may seem boring, but as a qualified programmer, you should and must master them. This theoretical knowledge is like a person's internal strength. When we usually code, it is external strength. Only by practicing both internal and external skills and promoting each other can we reach the level of a martial arts master. Okay, without further ado, let’s get started.

Database transaction

Boundary of transaction

Start boundary of transaction (begin)
End boundary of transaction (commit): Submit the transaction and permanently save it after being updated by the transaction database status.
Abnormal end boundary of transaction (rollback): Undo the transaction and return the database to the initial state before executing the transaction.

Every time you start a MySQL.exe program, you will get a separate database connection. Each database connection has a global variable autocommit, which represents the current transaction mode. It has two optional values:

• 0: represents manual commit mode

• 1: Indicates automatic submission mode, default value

We can view and modify this value.

Four characteristics of database transactions (ACID):

• Atomicity: A transaction is an atomic operation unit, and its modification of data, Either execute it all or not execute it at all;

• Consistency (Consistent): The data must remain consistent at the beginning and completion of the transaction;

• Isolation: The database system provides a certain isolation mechanism to ensure that transactions are executed in an "independent" environment that is not affected by external concurrent operations;

• Persistence (Durable) : After the transaction is completed, its modification to the data is permanent and can be maintained even if a system failure occurs.

Transaction isolation level

The database transaction isolation level is only about whether a transaction can read the intermediate results of other transactions.

• Read Uncommitted (read uncommitted content)
  At this isolation level, all transactions can see the execution of other uncommitted transactions result. This isolation level is rarely used in practical applications because its performance is not much better than other levels. Reading uncommitted data is also called dirty read.

• Read Committed (read submission content)
  This is the default isolation level of most database systems (but not the MySQL default). It meets the simple definition of isolation: a transaction can only see changes made by committed transactions. This isolation level also supports so-called nonrepeatable read, because other instances of the same transaction may have new commits during the processing of this instance, so the same select may return different results.

• Repeatable Read
  This is the default transaction isolation level of MySQL. It ensures that multiple instances of the same transaction will see the same data rows when reading data concurrently. However, in theory, this will lead to another thorny problem: phantom read (Phantom Read). Simply put, phantom reading means that when the user reads a certain range of data rows, another transaction inserts a new row in the range. When the user reads the data rows in the range again, he will find that there are new rows. Phantom” OK. InnoDB and Falcon storage engines solve this problem through the multiversion concurrency control (MVCC, Multiversion Concurrency Control) mechanism.

• Serializable (Serializable)
  This is the highest isolation level. It solves the phantom read problem by forcing transactions to be ordered so that they cannot conflict with each other. In short, it adds a shared lock on each data row read. At this level, a lot of timeouts and lock contention can result.

The higher the isolation level, the more complete and consistent the data can be guaranteed, but the greater the impact on concurrency performance.
For most applications, you can effectively consider setting the isolation level of the database system to Read Committed, which can avoid dirty reads and has better concurrency performance. Although it will lead to concurrency problems such as non-repeatable reads, wasted reads, and type II lost updates, in individual situations where such problems may occur, they can be controlled by the application using pessimistic locks and optimistic locks.

Transaction propagation

1. ##PROPAGATION_REQUIRED

   Add the transaction that is currently being executed. If the current transaction does not exist, then start a new transaction. The default transaction propagation behavior of Spring operating database is propagation_required.
   

2. PROPAGATION_SUPPORTS

   If it is currently in a transaction, it will run in the form of a transaction. If it is no longer in a transaction, it will run in the form of a non-transaction.
   

3. PROPAGATION_MANDATORY

   Must be run within a transaction. In other words, it can only be called by a parent transaction. Otherwise, he will throw an exception.
   

4. PROPAGATION_REQUIRES_NEW

   Suspend the current transaction and start a new transaction.
   

5. PROPAGATION_NOT_SUPPORTED
   Transactions are not currently supported. If it is in a transaction, the current transaction will be suspended and it will run in non-transactional behavior.

6. PROPAGATION_NEVER

   Cannot be run in a transaction. If run in a transaction, an exception will be thrown.
   

7. PROPAGATION_NESTED

   The nested transaction depends on the parent transaction. When the parent transaction commits, it will be submitted. When the parent transaction rolls back, it will roll back.
   

Row-level lock

Three types of locks in Mysql:

Row-level: engine INNODB, a single row of records is locked

Page level: Engine BDB locks an adjacent group of records at a time.
Table level: Engine MyISAM, understood as locking the entire table, which can be read at the same time, but not written.
The characteristics of the three types of locks can be roughly summarized as follows:
1) Table-level locks: low overhead, fast locking; no deadlocks; large locking granularity, the highest probability of lock conflicts, and the lowest concurrency.
2) Row-level locks: high overhead, slow locking; deadlocks may occur; the locking granularity is the smallest, the probability of lock conflicts is the lowest, and the concurrency is also the highest.
3) Page lock: The cost and locking time are between table locks and row locks; deadlocks will occur; the locking granularity is between table locks and row locks, and the concurrency is average.

What we are mainly talking about here is row-level locks. Generally, in the flash sale system, we will use row-level locks for product inventory, because inventory is a very important data during the flash sale. We are creating database tables. The following settings may appear:

ENGINE = InnoDB AUTO_INCREMENT=10 DEFAULT CHARACTER SET = utf8 comment='用户表

Set the engine to InnoDB. InnnoDB is different from other engines: first, it supports transactions (TRANCSACTION), and second, it uses row-level locks.

InnoDB中两种模式的行级锁：

1）共享锁：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。 
( Select * from table_name where ……lock in share mode) 
2）排他锁：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和 排他写锁。(select * from table_name where…..for update)

为了允许行锁和表锁共存，实现多粒度锁机制；同时还有两种内部使用的意向锁（都是表锁），分别为意向共享锁和意向排他锁。

• 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。

• 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

注意：InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。应用设计的时候要注意这一点。

行级锁的优缺点

行级锁定的优点：

• 当在许多线程中访问不同的行时只存在少量锁定冲突。

• 回滚时只有少量的更改。

• 可以长时间锁定单一的行。

行级锁定的缺点：

• 比页级或表级锁定占用更多的内存。

• 当在表的大部分数据上使用时，比页级或表级锁定速度慢，因为你必须获取更多的锁。如果你在大部分数据上经常进行GROUP BY操作或

者必须经常扫描整个表，比其它锁定明显慢很多。

hibernate中通过行级锁实现的悲观锁。

一些例子：

假设有个表单products ，里面有id跟name二个栏位，id是主键。 
1: 明确指定主键，并且有此条记录，执行row lock。若查无此记录，无lock。

SELECT * FROM products WHERE id='3' FOR UPDATE;SELECT * FROM products WHERE id='3' and name="cat" FOR UPDATE;

2: 无主键，执行table lock。

SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

3: 主键不明确，table lock。

SELECT * FROM products WHERE id<>&#39;3&#39; FOR UPDATE;

注意： FOR UPDATE仅适用于InnoDB，且必须在事务块(BEGIN/COMMIT)中才能生效。此外，如果A与B都对表id进行查询但查询不到记录，则A与B在查询上不会进行row锁，但A与B都会获取排它锁，此时A再插入一条记录的话则会因为B已经有锁而处于等待中，此时B再插入一条同样的数据则会抛出Deadlock found when trying to get lock; try restarting transaction。然后释放锁，此时A就获得了锁而插入成功。

以上就是MySQL中的事务与锁的内容，更多相关内容请关注PHP中文网（www.php.cn）！