这个SQL会导致任何问题吗？ [英] Will this SQL cause any problems?
问题描述
我确信每个人都知道线程化时的并发乐趣。
I'm sure everyone knows the joys of concurrency when it comes to threading.
想象一下，在一个虚拟设置MySQL数据库的每个页面加载时出现以下情况：
Imagine the following scenario on every page-load on a noobily set up MySQL db:
UPDATE stats SET visits = (visits+1)
如果一千个用户同时加载页面，计数会导致任何问题吗？这是表锁定/行锁定机制吗？哪一个mysql使用。
If a thousand users load the page at same time, will the count cause any issues? is this that table locking/row locking mechanism? Which one mysql use.
推荐答案
你有两个潜在的问题：
- 你会得到正确的答案吗？
- 你会得到不合理的锁定，你的整个应用程序会变得非常缓慢甚至死锁。
正确的答案取决于两个用户是否可以在相同的访问值上计算（访问+1）。我们可以想象数据库需要执行以下操作：
The right answer depends upon whether two users could compute (visit + 1) on the same value of visit. We can imagine that the database needs to do these actions:
Read visit count
Add one to visit count
Write visit count
因此，如果两个用户同时工作，他们都可以读同样的旧的价值？这就是交易的隔离级别发挥作用的地方。正如Artefacto所观察到的那样，默认隔离级别是可重复读取，因此我们得到：
So if two users are working at the same time could they both read the same old value? That's where the isolation level of the transaction comes into play. As observed by Artefacto the default isolation level is repeatable read, and hence we get:
Grab a lock
Read, increment, Write
Release lock
而不是
Read (two users same old value)
Increment
First user Grab Lock, second waits
Write
Release, second user grabs lock
Write (same value!)
Release
但是级别争用可能会很高，而且很大程度上取决于您的交易范围。假设您有：
However the level of contention could be quite high, and very much depends on the scope of your transaction. Suppose you have:
Begin transaction
Do the visit increment stuff
Do some serious business work
End transaction <==== visit lock is held until here
然后你会得到很多人等待访问锁定。我们不知道您的应用程序的整体结构，无论您是否使用这样的大型事务范围。您很可能每个SQL语句都获得单个事务的默认行为，在这种情况下，您的争用只是在SQL语句的持续时间内，就像您希望的那样。
Then you will get a lot of folks waiting for that visit lock. We don't know the overall structure of your app, whether you are using large transaction scopes like this. Very likely you are getting a default behaviour of a single transaction per SQL statement, and in which case you're contention is just for the duration of the SQL statement, pretty much as you would be hoping.
其他人可能不是那么幸运：有些环境（例如Java EE Servlets）可以由基础设施创建隐式事务范围，然后默认情况下我上面显示的更长寿命的事务发生。更糟糕的是你的代码编写不一致（访问增量始终是第一个，或总是最后一个）你可以得到：
Other folks might not be so fortunate: there are environments (eg. Java EE Servlets) where implicit transaction scopes can be created by the infrastructure and then the longer lived transactions I show above happen by default. Worse is the possibility that your code is not written consistently (with the visit increment always first, or always last) you can get:
Begin transaction
Do the visit increment stuff
Do some serious business work
End transaction <==== visit lock and business locks held until here
和
Begin transaction
Do some other serious business work
Do the visit increment stuff
End transaction <==== visit lock and maybesame business locks held until here
和bingo：死锁
对于大批量网站，你可以考虑写作一个访问事件到一个队列，并有一个守护进程侦听这些事件并保持计数。更复杂但争议问题可能更少。
For high volume sites you bcould consider writing a "Visit" event to a queue, and having a daemon listening for those events and maintaining the count. More complex, but possibly fewer contention issues.
这篇关于这个SQL会导致任何问题吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
