• notice
  • Congratulations on the launch of the Sought Tech site

TransactioniSolationledLevel

There are four database isolation levels, using the instructions in the book "High-performance mysql": p>

Then talk about how to modify the transaction isolation level:

1.Global modification, modify the mysql.ini configuration file, and add

at the end

The code is as follows:

 #Optional parameters are: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.
 [mysqld]
 transaction-isolation=REPEATABLE-READ

The global default here is REPEATABLE-READ.In fact, MySQL is also at this level by default.

2.Modify the current session, after logging in to the mysql client, execute the command:

Remember that mysql has an autocommit parameter, which is on by default.Its function is that each individual query is a transaction, and it starts and commits automatically (it will end automatically after execution, if you want to apply select for update, instead of manually calling start transaction, this for update row lock mechanism is useless, because the row lock is released after automatic commit), so the transaction isolation level and lock mechanism even if you do not explicitly call start transaction, this This mechanism is also applicable in a single query statement.You must pay attention to this when analyzing the operation of the lock.

Let’s talk about the lock mechanism again:
Shared locks: locks added by table reading operations.After locking, other users can only obtain shared locks on the table or row, not exclusive locks, that is, only Can read or write

Exclusive locks: locks added by table write operations.After locking, other users cannot acquire any locks on the table or row, typically in a mysql transaction.

The code is as follows:

start transaction;
select * from user where userId=1 for update;

After executing this sentence

  1) When other transactions want to acquire a shared lock, such as a transaction with a transaction isolation level of SERIALIZABLE, execute

The code is as follows:

  select * from user;

    will be suspended because the select statement of SERIALIZABLE needs to acquire a shared lock

  2) When other affairs are executed

The code is as follows:

select * from user where userId=1 for update;
update user set userAge=100 where userId=1;

It will also be suspended, because for update will obtain an exclusive lock for this row of data, and you need to wait until the previous transaction releases the exclusive lock before you can continue

The scope of the lock:

Row lock: lock a row record
table lock: lock the entire table

In this combination, there are row-level shared locks, table-level shared locks, row-level exclusive locks, and table-level exclusive locks.

Let’s talk about the instance effects of different transaction isolation levels.The example uses InnoDB, opens two clients A and B, changes the transaction isolation level in A, opens the transaction in B and modifies data, and then in A View the effect of B's ​​transaction modification:

1.READ-UNCOMMITTED (read uncommitted content) level

  1) A modify the transaction level and start the transaction, and do a query on the user table

   

  2) B Update a record

   

  3) At this time, transaction B has not yet been submitted, and A performs a query within the transaction and finds that the query result has changed

   

  4) B performs transaction rollback

   

  5) A makes another query, and the query result changes back again

   

  6) A table revises the user table data

   

  7) After B table restarts the transaction, modify the user table record, the modification is suspended until the timeout, but the modification of another piece of data is successful, indicating that the modification of A has added a row shared lock to the data row of the user table (Because select can be used)

   

   You can see the READ-UNCOMMITTED isolation level.When two transactions are in progress at the same time, even if the transaction is not committed, the changes made will affect the query within the transaction.This level is obviously very insecure.But when a row is modified in the table, the row sharing lock will be added to the row

2.READ-COMMITTED (read submitted content)

  1) Set the transaction isolation level of A, and enter the transaction to do a query

   

  2) B starts the transaction and modifies the record

   

  3) A queries the user table again and finds that the record is not affected

   

  4) B commits the transaction

   

  5) A queries the user table again and finds that the record has been modified

   

  6) A modifies the user table

   

  7) B restarts the transaction and modifies the same entry in the user table.It is found that the modification is suspended until the timeout expires, but the modification to another record is successful, indicating that A’s modification adds a row to the user table Shared lock (because it can be selected)

   

   

  READ-COMMITTED transaction isolation level, only after the transaction is committed, will it affect another transaction, and when the table is modified, the row sharing lock will be added to the table data row

3.REPEATABLE-READ (re-readable)

  1) A sets the transaction isolation level, and queries once after entering the transaction

   

  2) B starts the transaction and modifies the user table

   

  3) A view the user table data, the data has not changed

   

  4) B commits the transaction

   

  5) A performs another query, the result is still the same

   

  6) After A submits the transaction, check the result again, the result has been updated

   

  7) A restarts the transaction and modifies the user table

   

   

  8) Table B restarts the transaction and modifies the user table.The modification is suspended until the timeout expires, and the modification to another record is successful, indicating that the row sharing lock was added when A modified the table (selectable)

   

  REPEATABLE-READ transaction isolation level, when two transactions are in progress at the same time, one transaction will modify data without affecting the other transaction, even if the modified transaction has been submitted, it will not affect the other transaction.

   Modifying a record in a transaction will add a row-sharing lock to the record and will not release it until the end of the transaction.

4.SERIERLIZED (serializable)

  1) Modify the transaction isolation level of A and make a query

   

  2) B performs a query on the table, and the result is obtained normally.It can be seen that the query on the user table is possible

   

  3) B starts the transaction and makes changes to the record.Because A transaction has not been committed, B’s modification is in a waiting state, waiting for A’s transaction to end, and finally overtime, indicating that A is in the user table after querying the table Added a shared lock

   

  SERIALIZABLE transaction isolation level is the strictest.Shared locks will be added to the table or row when querying, and other transactions will only be able to perform read operations on the table, but not write operations.

Tags

Technical otaku

Sought technology together

Related Topic

0 Comments

Leave a Reply

+