PDO交易锁? [英] PDO Transactions Locks?

问题描述

我已经通过PHP PDO接口对MySQL事务进行了广泛的研究.对于事务处理方法的实际背景工作，我还是有些模糊.具体来说，我需要知道是否有什么理由应该防止从脚本开始到结束的事务中的所有查询(包括SELECT)?当然，处理事务中的任何错误并在需要时将其回滚.

I have done extensive research about MySQL transactions via the PHP PDO interface. I am still a little fuzzy about the actual background workings of the transaction methods. Specifically, I need to know if there is any reason that I should want to prevent all my queries (SELECTs included) inside a transaction spanning from the beginning of the script to the end? Of course, handling any error in the transaction and rolling them back if need be.

我想知道在事务处理期间是否有任何锁定，如果是，是行级锁定，因为它是InnoDB?

I want to know if there is any locking going on during a transaction and if so, is it row level locking because it is InnoDB?

推荐答案

请勿这样做.

这样做的原因是，交易利用了 MVCC 的机制，通过这种机制，每实际上，更新的数据不是就地更新，而只是插入其他地方.

The reason for this is that transactions take advantage of MVCC a mechanism by which every piece of data updated is in fact not update-in-place but merely inserted in somewhere else.

MVCC意味着分配内存和/或存储空间以累积和操作您发送的所有更改，而无需将它们提交到磁盘，直到发出COMMIT为止.

MVCC implies allocating memory and or storage space to accumulate and operate all of the changes you send it without committing them to disk until you issue a COMMIT.

这意味着在整个脚本运行期间，所有更改都将存储到脚本结束为止.并且您在事务期间尝试和更改的所有记录都被标记为正在进行中"，以便其他进程/线程可以知道该数据将很快失效.

That means that while your entire script runs all changes are stored until the script ends. And all of the records that you try and change during the transaction are marked as "work in progress" so that other processes/threads can know that this data will soon be invalidated.

在脚本的整个长度上都将某些数据标记为正在进行中"，这意味着任何 other 并发更新都将看到该标记，并说我必须等到完成为止"我将获取最新数据".

Having certain pieces of data marked as "work in progress" for the entire length of the script means that any other concurrent update will see the flag and say "i have to wait until this finishes so I'll get the most recent data".

这包括SELECTS，具体取决于隔离级别.选择标记为进行中的工作"的东西可能不是您想要的，因为您可能要联接的某些表可能包含已更新的数据，而其他表未更新但会导致读取错误.

This includes SELECTS depending on isolation levels. Selecting stuff that is marked as "work in progress" may not be what you want because some tables that you may want to join may contain already updated data while other tables are not updated yet resulting in a dirty read.

操作的事务性和原子性是理想的，但代价很高.在需要的地方使用它.是的，这意味着您需要做更多的工作来弄清楚哪里可能发生竞争状况，即使发生竞争状况，您也必须决定它们是否真的很关键或某些"数据丢失/混合是否可以接受.

Transactionality and atomicity of operations is desirable but costly. Use it where it's needed. Yes that means more work for you to figure out where race conditions can happen and even if race conditions occur you have to make the decision if they are really critical or is "some" data loss/mix acceptable.

您是否希望您的日志和访问计数器以及其他统计信息降低您整个网站的速度?还是 信息的质量会提高速度(只要不是分析工具，您就可以偶尔遇到冲突).

Would you like your logs and visit counters and other statistics to drag down the speed of your entire site? Or is the quality of that information sacrificable for speed (as long as it's not an analytics suit you can afford the occasional collision).

您是否想让座位预订应用程序失火，即使座位号为0，也允许更多用户抓住座位?当然不是-在这里您要利用事务和隔离级别来确保永远不会发生.

Would you like a seat reservation application to miss-fire and allow more users to grab a seat even after the seat cout was 0? of course not - here you want to leverage transactions and isolation levels to ensure that never happens.

这篇关于PDO交易锁?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！