MyISAMTransaction is not supported.
What is the difference between MyISAM and InnoDB in terms of locks
MyISAMTable level lock is used by default, row level lock is not supported.
InnoDBRow level locks are used by default, and table level locks are also supported.
Read lock / shared lock: other
sessionCan read, but can’t write.
Write lock / exclusive lock: other
sessionCan’t read or write.
MyISAMWhen querying data, a table level read lock is automatically added to the table; when adding, deleting or modifying data, a table level write lock is added to the table. When the “read lock” is not released, another
sessionWriting to this table can be blocked.
InnoDBThe row lock of is similar to the lock of a row. If it is a read lock, other transactions can read but not write. If it is a write lock, other transactions can neither read nor write.
InnoDBThe transaction will be automatically opened for the add, delete, modify and query statement, and will be automatically committed or rolled back. After the transaction is committed or rolled back, the lock will be automatically released at the same time.
If there is no transaction, the whole table will be traversed and promoted to table lock.
- Frequent execution of full table
MyISAMThe number of rows for the entire table is saved.
- The frequency of adding, deleting and modifying data is not high, and the query is very frequent.
- It is suitable for scenarios without transactions.
- Data addition, deletion, modification and query are quite frequent.
- The reliability requirements are relatively high, and support transactions are required.
By lock granularity
Table level lock, row level lock and page level lock.
Table level locks are fast, but they have more conflicts and fewer row level conflicts, but they are slow. The page level lock is between the two, locking the adjacent set of records.
By lock level
Shared lock, exclusive lock.
Divided by locking mode
Automatic lock, explicit lock.
Optimistic lock, pessimistic lock.
Optimistic lock: it is optimistic during the operation and believes that the operation will not cause concurrency problems (no other thread will modify the data), so it will not lock. However, when updating, it will judge whether other threads have modified the data before this.
Pessimistic lock: always assume the worst case. Every time you fetch data, you think that other threads will change, so you will add (pessimistic) lock. Once the lock is applied, only one thread can execute when different threads execute at the same time. Other threads wait at the entrance until the lock is released.
Four characteristics of database transaction
A: atomicity, all operations contained in the transaction are either all successful or all failed.
C: consistency. Transactions ensure that the data only has the pre operation state and post operation state. Intermediate state will never occur during access.
I: isolation. When multiple transactions are executed concurrently, the execution of one transaction should not affect other transactions.
D: persistence. Once a transaction is committed, changes to the data are permanently saved.
Transaction isolation level and concurrent access problems at all levels
Isolation level of MySQL transactions
READ-UNCOMMITTED: in one transaction, uncommitted data changes of other transactions can be read.Not recommended in production environments.
READ-COMMITTED: in one transaction, you can read data changes that have been committed by other transactions. yes
OracleDefault transaction isolation level.
REPEATABLE-READ: in one of the transactions, the data seen at the beginning of the transaction can be read repeatedly until the end of the transaction. yes
MySQLDefault transaction isolation level.
SERIALIZABLE: add table level shared lock to each read data row and table level exclusive lock to each write of data. This will cause
InnoDBWhen the concurrency capability of is reduced, a lot of timeout and lock contention will occur.Not recommended in production environments.
Problems caused by concurrent transaction access and how to avoid them
- Update lost:
MySQLAll transaction isolation levels can be avoided at the database level.
- Dirty read: one transaction reads the uncommitted data of another transaction,
READ-COMMITTEDTransaction isolation level above can be avoided.
- Non repeatable reading: other transactions are committed, resulting in inconsistent results when reading the same data multiple times,
REPEATABLE-READTransaction isolation level above can be avoided.
- Unreal reading: updating
MData. Other transactions added or deleted data, but succeeded
M+N/M-NBar, it’s hallucinating,
SERIALIZABLETransaction isolation level can be avoided. however
REPEATABLE-READIt avoids the phenomenon of unreal reading.
How to avoid unreal reading under InnoDB repeatable-read isolation level
Image: snapshot read (pseudo
Next-key lock(row lock +)
Current and snapshot reads
Current read: that is to say, add, delete, modify and query statements with lock are used to read the latest version of the record. After reading, it is also necessary to ensure that other concurrent transactions cannot modify the current record, so lock is needed.
Snapshot read: non blocking read without lock,
SELECT。 In order to improve the concurrent performance, there is no conflict between reading and writing
MVCC, which avoids the lock operation and reduces the cost.
MVCC: multi version concurrency control, avoid lock, save data snapshot at a certain time point.
Row lock: lock a single row.
GapLock: lock the interval.
Will gap lock be used for primary key index or unique index?
WHEREIf all conditions are hit, it will not be used
GapLock, only record lock will be added.
WHEREConditional partial hit or total miss, the
Gap locks are used in current reads that are not unique indexes or do not walk indexes
Non unique index
Only the adjacent sections of the operation will be locked.
All sections will be locked.
How to realize non blocking read of InnoDB at RC and RR level
- In the data line
read view, which version of data can be seen by the current transaction.