Mysql: since repeatable RR transaction isolation solves the problem of phantom reading, what are the advantages of serializable isolation over RR isolation?
Mysql: since repeatable RR transaction isolation solves the problem of phantom reading, what are the advantages of serializable isolation over RR isolation?
it is not good for general business, but it has an impact on performance, which is more convenient for special business
RR solves phantom reading through manual locking. If locking is incorrect, phantom reading will still occur. Without full table locking, it is difficult to avoid that other transactions will not affect the current transaction.
therefore, the advantage of serializable is that it completely isolates the impact of other transactions on current transactions. When developers do not understand the database enough, using serializable is safer than locking manually
Repeatable read can solve phantom reading?
can be read repeatedly. All queries within the same transaction are consistent at the beginning of the transaction, with the InnoDB default level. In the SQL standard, this isolation level eliminates unrepeatable reads, but there are phantom reads.
repeatable reading does not solve phantom reading. There is no interline lock added.
if a scope query or scope update is not needed in a transaction, then repeatable reading is fine.
innodb reads are divided into snapshot reads and current reads.
at the RR level ,
ordinary select is a snapshot read; select in share mode and update are current reads;
current and current reads will not have phantom readings. There is no illusion between snapshot reading and snapshot reading.
but for current and snapshot reads, there may be phantom readings. Therefore, the situation of Cheng Chi-Ming's test came into being.
,
select is also locked by default at the Serializable level, so it is equivalent to current reads.
Summary :
at the RR level, programmers are required to actively avoid alternating snapshot reads and current reads in transactions in order to avoid phantom reading.
at the Serializable level, they are all currently read, so they naturally do not produce phantom readings.
tb_user (user information) id tb_article (article information) id, foreign key user_id-> tb_user.id tb_comment (comment information) id, foreign key user_id-> tb_user.id query the top 5 users of the latest article or comment. order by tb_article.cre...
I ran lock table employees write, on one transaction and then used select statements on another transaction, without for update or lock in share mode select is said to be snapshot read, unlocked, why it is lock table. Write watch lock blocking? ...
two questions, when logging in, typing mysql-u root-p correctly jumps to the password line, but you can t type the password on the keyboard. You can t log in with a password, but you can log in directly by typing mysql. Is this the wrong place? I have...
I want to change the place shown in the picture to null ...
if the data to be saved contains "`, how can it be inserted into the database? A rookie, please give me some advice. ...
jav inserts Mysql data exception Data too long exception information 2018-04-24 00:15:01 ERROR com.comall.cybershop.common.base.DaoSupport org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [update cs_awardCardRe...
EXPLAIN SELECT s.id, s.name, s.price, s.unit, s.qty, s.remark, s.image, s.cuft, s.volume, s.status, s.vender_id AS venderId, s.booth_id AS boothId, s.booth_no AS boothNo, s.show_booth_no AS showBoothNo, s.name_en AS nameEn, ...
Today we have a problem with a compound primary key, which ensures that multiple column combinations are not unique. But in practice, it s the opposite. this is my table structure 12342,2,1: wonder ...
...
using myql-connector-java5.0.8 jar package to connect to the database of MySQL 5.7.22, the following error occurred Exception in thread "main " java.sql.SQLException: No suitable driver found for jdbc:mysql:192.168.31.218:3306 school at java.sql.Dri...
this is the case. Recently, when there is an occasional Mysql insert operation in the system, some field data of a record is lost. When I check the printed sql statement, there is a value, such as : . before the insert operation, I output the attri...
as in the question, when I add or remove indexes for a table in the database, I have two choices use alter table to change the table structure use the create drop index command Which of these two methods is better? What I want to consider is, if ...
such as the title. I searched the Internet for answers, but I still didn t get it (in fact, all the answers are the same, which makes me speechless). And ask the great gods here for advice. ...
CREATE OR REPLACE PROCEDURE QD_MYDTJ (BEGINDATE VARCHAR2, ENDDATE VARCHAR2, xz_name VARCHAR2, xz_number VARCHAR2, --BEGININTEVAL VARCHAR2, --ENDINTEVAL VARCHAR2, start1 IN NUMBER, limit1 IN NUMBER, AN_O_RET_CODE ...
in a transactional method, by reading a collection of table records and cycling the values of update or save fields, the administrative account is updated every time an ordinary account is updated. so re-query the system account . @Transactional(rollba...
< H2 > problem, want to find out a data set similar to Taobao shopping cart < H2 > A user has a shopping cart, there are multiple stores under one shopping cart, and there are multiple items under one store linked table query 1. Check out the shop...
problem description I created a table in the mysql database, one of which is the CURRENT_TIMESTAMP, of the default value of the field create_time. I added a new piece of data when I did the hope test in the project. The project uses the SpringBoot f...
problem description the proportion of gpg,CPU of an unknown process encountered on the database server is high. I am not sure whether it has anything to do with the database. I am not sure whether it has anything to do with the database. I did not dar...
it is said that Internet developers try to not to use foreign keys , so what does the here mean by not using foreign keys ? 1. The meaning here is clear: No foreign key constraints . For example, when deleting data in one table, if you want to casc...
under the separation of mysql read and write, is, select for update lock in share mode performed in the read library or the write library? ...