如何使用doctrine2在symfony2中锁定整个表？ [英] How to lock a whole table in symfony2 with doctrine2?

问题描述

我需要用doctrine锁定一个整个表（而不是一行），如果可能，我想这样做不需要本机查询。

I need to lock a whole table (not a single row) with doctrine, I would like to do this without native queries if possible.

一个href =http://doctrine-orm.readthedocs.io/projects/doctrine-orm/en/latest/reference/transactions-and-concurrency.html =nofollow noreferrer>悲观锁定描述如何通过以下方法锁定特定实体：

The documentation for pessimistic locking only describes how to lock specific entities through these methods:

• EntityManager＃find


• EntityManager＃lock


• 查询＃setLockMode

我有一个事务需要插入一行，其值取决于表中其余行的值，所以我需要防止在该表上同时执行两个事务。

I have a transaction that needs to insert a row whose values depend on the values of the rest of the rows in the table, so I need to prevent two transactions performing at the same time on that table.

我使用显式事务划分，这应该是锁定的（根据上面的文档）。

I'm using explicit transaction demarcation, which is supposed to work well with locking (according to the documentation above).

注意：乐观锁定在这种情况下不够好，我不能可以重试交易。此外，查询不应该是缓慢的，所以性能不是一个问题。

NOTE: Optimistic locking is not good enough in this case, I can't afford retrying the transaction. Besides the query is not supposed to be slow so performance isn't an issue.

编辑：我举一个例子。想象一下，你想手工构建一个auto_increment，你必须从表中选择max（）来获取以前的结果才能插入下一个结果。您必须确保没有两个事务尝试插入相同的值，以便他们同时选择max（）。

EDIT: I'll give an example. Imagine you want to hand build an auto_increment, and you have to select max() from the table to get the previous result in order to insert the next one. You have to make sure that no two transactions try to insert the same value in case they select max() at the same time.

我正在寻找一个通用解决方案当auto_increment不好时，例如使用字符串或多个列，散列或任何计算，您必须在上一行集上执行此问题。

I'm looking for a general solution to this problem when auto_increment is not good, for example with strings, or multiple columns, hashes or whatever calculation you have to make on the previous row set.

锁定是一个坚实的解决方案，不同于乐观锁定，您不必重试错误。

Locking is a solid solution and, unlike optimistic locking, you don't have to retry on errors.

所以，有没有办法在教条中使用表锁定？

So, is there any way to use table locking in doctrine?

推荐答案

以下建议到目前为止，我尝试过：

Following advice so far, I tried this:

$em->getConnection()->exec('LOCK TABLES table_name WRITE;'); //lock for write access

// calculate $new_number...

// persist $new_number on table_name...
$table_name->setCalculatedNumber($new_number);
$em->persist($table_name);
$em->flush();

$em->getConnection()->exec('UNLOCK TABLES;');

我用JMeter进行了测试，锁定不能用于重负载（16个请求/秒）。跟踪表明，其他实例在被明确放弃之前已经锁定了。问题（由Jens建议的）是flush（）隐含地以START TRANSACTION开始，它会丢弃表锁。
使用本机更新修复了我的问题：

I tested it with JMeter, and the locking wasn't working with a heavy load (16 requests/sec). Tracing showed that other instances got the lock before it had been explicitly given up. The problem (as suggested by Jens) was that flush() implicitly begins with a START TRANSACTION, which drops the table lock. Using a native Update fixed the problem for me:

$em->getConnection()->exec('LOCK TABLES table_name WRITE;'); //lock for write access

// calculate $new_number...

// persist $new_number on table_name...
$em->getConnection()->executeUpdate("UPDATE table_name set ...;");    

$em->getConnection()->exec('UNLOCK TABLES;');
$em->refresh($table_name);

需要拖尾的refresh（）才能使计算的数字在后续查询结果中可用

The trailing refresh() was needed to make the calculated number available in subsequent query results

这篇关于如何使用doctrine2在symfony2中锁定整个表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！