mysql优化 - mysql数据insert快还是update比较快

Answer 1

Your application scenario looks like:

Before modifying the user balance, I accidentally changed the user balance to 0 or something because I was afraid that something would go wrong with the modification.
Choose to first insert a record into the user account change table, and then check the data in the account change table to update the user balance field.

---

Use an idiom to describe this behavior: "fearing and not choosing the right path"

Have you ever thought that since you are worried about problems when modifying user balances, you also have to worry about problems inserting into the user account change table. Do you need another table to ensure the account change table? Who will guarantee the other watch?

Actually, you should know the correct solution. Instead of using two unsafe methods to make one safe method. In doing so, not only did you not get a safe method, but you got two unsafe methods.

The correct way is to directly modify the user balance sheet and add an exclusive lock. For example:

// 开始事物
BEGIN ;

// 取出该用户数据，并锁住，防止其他线程（进程）读取该条记录
SELECT * FROM users where id = $id FOR UPDATE ;

// 处理业务...计算用户新的余额

// 更新用户余额
UPDATE users SET money = $new_money;

// 获取影响行数=1,则：{
  // 提交事物（解锁我们锁定的记录）
  COMMIT ;
}else{
  // 发现不对，撤销我们在事物内做的所有操作
  ROLLBACK ;
}

Answer 2

I don’t know if it will cause data loss, but I have an idea. In the case of multi-thread concurrency, it is very likely that later data will overwrite the previous data. Even if you insert into a table first, this may happen in multi-threads. It’s just a case of inserting it later.

So it is recommended that the author enable the transaction function of mysql. For details, you can read this question

https://segmentfault.com/q/10...