java - mysql事务隔离的问题？多线程并发怎么保证可靠？

Question

mysql采用默认事务隔离级别REPEATABLE-READ；
然后我分别执行以下内容：
1、


2、使用jdbc访问数据库：

3、这时候打印结果，发现线程进入阻塞，一直卡在这里

也就是说我开启的另一个事务仍然能读取到数据，只是在最后执行executeUpdate的时候才被锁定不能执行
4、命令行执行commit


5、结果命令行操作被jdbc操作替换掉，数据改为4。

这不就是说REPEATABLE-READ仍然出现了丢失更新，而且没有行锁定吗？
事务与并发到底是什么区别，我在测试多线程执行以上jdbc操作时，发现最后得到的结果总是不对。例我开十个线程，每个都对数据+1，结果可能只加了5.所以在并发情况下怎么做才能保证数据的安全？

Answer 1

If it is only increased by one, the value of number will not be obtained through select, and the value obtained by select may be the old value; then replace it with update test set number = number + 1 where id = ?. The number here will be the latest. This method can avoid the complete serial performance loss of select for update.

Answer 2

REPEATABLE-READ is likely to have phantom reads
If you want to ensure absolute security, you can only set the isolation level to SERIALIZABLE
In this way, all transactions can only be executed sequentially. Naturally, there will be no impact due to concurrency, but the performance will Dropped a lot.

If you don’t want performance to drop but also want to control it so that it doesn’t go wrong, the more common approach now is to use newer version control.
Maintain a field as UpdateVersion. When modifying, updateversion is also passed in as a parameter. Add for example where id=? and update_version = ? in the conditional statement. Of course, update_version+1 is required in the set.
This can be controlled so that only one person can update one version at a time.

Answer 3

Through experiments, I found that I have understood my problem to a certain extent.

I was previously misled by misinformation about transaction isolation levels, thinking that the REPEATABLE-READ level and SERIALIZABLE level can solve the problem of lost updates, but in fact they cannot.
Because mysql’s select ... from table; statement will not block regardless of the isolation level,

事务A读取 id为1 number = 3 ，执行更新操作 number设为1；
事务B读取 id为1 number = 3， 执行+1操作  更新--进入阻塞

Under the isolation level, the write lock (exclusive lock) will only wait for the release when updating data, so multiple threads can read number=3 at the same time. Modifications based on this will inevitably lead to lost updates.

So the solution is to add a pessimistic locking or optimistic locking mechanism to the program.
Pessimistic lock uses select ... for update,

事务A：  select ... for update，执行更新操作，
事务B：  select ... for update --阻塞

At this time, the read operation of transaction B will not be executed. Only when the commit of transaction A is completed, transaction B can continue to be executed, which is equivalent to executing serially one by one.

Optimistic locking is commonly used for version control or timestamp control,

事务A：执行 此时版本为 1；执行完update ...set version=2 where version = 1;
事务B：执行 此时版本为 1；执行完update ...set version=2 where version = 1;

At this time, transaction B finds that version=1 no longer exists, because transaction A first completed the execution and updated the database, setting the version field to 2. This will cause the transaction submission to fail, and we need to determine what to do in the abnormal situation in the program. Continue.

The above is my current understanding. As for the MVCC (Multiple Version Concurrency Control) supported by MySQL itself, I still don’t know how to use it. I don’t know how to implement the exception handling of optimistic locking. It may be different based on the specific business scenario. processing method.

Answer 4

If it is simply Select会被看做是单纯的查询操作，所以不会被挂起。
如果要对某行进行事务，应该先在Select时就使用Select ... For Update这种格式。
使用For Update会对所选择的行加锁，当另外的事务再进行时，会在那个事务的Select ... For Update, it will hang and wait for the current transaction to be completed before continuing to execute. This ensures that there is no middleman between querying and writing.

Answer 5

Controlled by lock.