postgresql 处理并发问题的最佳方式

Best way to handle concurrency issues

提问by Strae

i have a LAPP (linux, apache, postgresql and php) environment, but the question is pretty the same both on Postgres or Mysql.

我有一个 LAPP（linux、apache、postgresql 和 php）环境，但问题在 Postgres 或 Mysql 上都是一样的。

I have an cms app i developed, that handle clients, documents (estimates, invoices, etc..) and other data, structured in 1 postgres DB with many schemas (one for each our customer using the app); let's assume around 200 schemas, each of them used concurrently by 15 people (avg).

我有一个我开发的 cms 应用程序，用于处理客户、文档（估算、发票等）和其他数据，在 1 个 postgres 数据库中构建，具有许多模式（每个使用该应用程序的客户都有一个）；让我们假设大约有 200 个模式，每个模式由 15 人（平均）同时使用。

EDIT: I do have an timestamp field named last_update on every table, and a trigger that update the timestamp every time the row is update.

编辑：我在每个表上都有一个名为 last_update 的时间戳字段，以及一个在每次更新行时更新时间戳的触发器。

The situation is:

情况是：

1. People Foo and Bar are editing the document 0001, using a form with every document details.
2. Foo change the shipment details, for example.
3. Bar change the phone numbers, and some items in the document.
4. Foo press the 'Save' button, the app update the db.
5. Bar press the 'Save' button after bar, resending the form with the old shipment details.
6. In the database, the Foo changes have been lost.

1. People Foo 和 Bar 正在编辑文档 0001，使用包含每个文档详细信息的表单。
2. 例如，Foo 更改装运详细信息。
3. 酒吧更改电话号码，以及文件中的一些项目。
4. Foo 按“保存”按钮，应用程序更新数据库。
5. 栏后按“保存”按钮，重新发送带有旧货件详细信息的表格。
6. 在数据库中，Foo 更改已丢失。

The situation i want to have:

我想要的情况：

1. People Foo, Bar, John, Mary, Paoul are editing the document 0001, using a form with every document details.
2. Foo change the shipment details, for example.
3. Bar and the others change something else.
4. Foo press the 'Save' button, the app update the db.
5. Bar and the others get an alert 'Warning! this document has been changet by someone else. Click here to load the actuals data'.

1. Foo、Bar、John、Mary、Paoul 正在编辑文档 0001，使用包含每个文档详细信息的表单。
2. 例如，Foo 更改装运详细信息。
3. 巴和其他人改变了一些东西。
4. Foo 按“保存”按钮，应用程序更新数据库。
5. Bar 和其他人收到警报“警告！此文件已被其他人更改。单击此处加载实际数据'。

I've wondered to use ajax to do this; simply using an hidden field with the id of the document and the last-updated timestamp, every 5 seconds check if the last-updated time is the same and do nothing, else, show the alert dialog box.

我想知道使用 ajax 来做到这一点；只需使用带有文档 id 和上次更新时间戳的隐藏字段，每 5 秒检查上次更新时间是否相同并且什么都不做，否则，显示警告对话框。

So, the page check-last-update.php should look something like:

因此，页面 check-last-update.php 应该类似于：

<?php
//[connect to db, postgres or mysql]
$documentId = isset($_POST['document-id']) ? $_POST['document-id'] : 0;
$lastUpdateTime = isset($_POST['last-update-time']) ? $_POST['last-update-time'] : 0;
//in the real life i sanitize the data and use prepared statements;
$qr = pg_query("
    SELECT
        last_update_time
    FROM
        documents
    WHERE
        id = '$documentId'
");
$ray = pg_fetch_assoc($qr);
if($ray['last_update_time'] > $lastUpdateTime){
    //someone else updated the document since i opened it!
    echo 'reload';
}else{
    echo 'ok';
}
?>

But i dont like to stress the db every 5 seconds for every user that have one (or more...) documents opened.

但我不喜欢每 5 秒为每个打开一个（或多个...）文档的用户强调 db。

So, what can be another efficentsolution without nuking the db?

那么，没有破坏数据库的另一种有效解决方案是什么？

I thought to use files, creating for example an empty txt file for each document, and everytime the document is updated, i 'touch' the file updating the 'last modified time' as well... but i guess that this would be slower than db and give problems when i have much users editing the same document.

我想使用文件，例如为每个文档创建一个空的 txt 文件，每次更新文档时，我都会“触摸”更新“上次修改时间”的文件......但我想这会更慢比 db 并在我有很多用户编辑同一文档时出现问题。

If someone else have a better idea or any suggestion, please describe it in details!

如果其他人有更好的想法或任何建议，请详细描述！

* - - - - - UPDATE - - - - - *

* - - - - - 更新 - - - - - *

I definitely choosen to NOT hit the db for check the 'last update timestamp', dont mind if the query will be pretty fast, the (main) database server has other tasks to fullfill, dont like the idea to increase his overload for that thing.

我绝对选择不点击数据库来检查“上次更新时间戳”，不要介意查询是否会很快，（主）数据库服务器还有其他任务需要完成，不喜欢增加他对那件事的过载的想法.

So, im taking this way:

所以，我采取这种方式：

1. Every time a document is updated by someone, i must do something to sign the new timestamp outside the db environment, e.g. without asking the db. My ideas are:
   2. File-system: for each document i create an empry txt files named as the id of the document, everytime the document is update, i 'touch' the file. Im expecting to have thousands of those empty files.
   3. APC, php cache: this will be probably a more flexible way than the first one, but im wondering if keeping thousands and thousands of data permanently in the apc wont slow down the php execution itself, or consume the server memory. Im little bit afraid to choose this way.
   4. Another db, sqlite or mysql (that are faster and lighter with simple db structures) used to store just the documents ID and timestamps.
2. Whatever way i choose (files, apc, sub-db) im seriously thinking to use another web-server (lighttp?) on a sub-domain, to handle all those.. long-polling requests.

1. 每次有人更新文档时，我都必须做一些事情来在数据库环境之外签署新的时间戳，例如不询问数据库。我的想法是：
   2. 文件系统：对于每个文档，我创建一个名为文档 ID 的空 txt 文件，每次文档更新时，我都会“触摸”该文件。我希望有成千上万的空文件。
   3. APC，php 缓存：这可能比第一种更灵活，但我想知道将成千上万的数据永久保存在 apc 中是否不会减慢 php 执行本身，或消耗服务器内存。我有点害怕选择这种方式。
   4. 另一个 db、sqlite 或 mysql（使用简单的 db 结构更快更轻）用于仅存储文档 ID 和时间戳。
2. 无论我选择哪种方式（文件、apc、sub-db），我都在认真考虑在子域上使用另一个网络服务器（lighttp？），来处理所有这些......长轮询请求。

YET ANOTHER EDIT:

另一个编辑：

The file's way wouldnt work.

该文件的方式不起作用。

APC can be the solution.

APC 可以成为解决方案。

Hitting the DB can be the solution too, creating a table just to handle the timestamps (with only two column, document_id and last_update_timestamp) that need to be as fast and light as possible.

命中数据库也可以是解决方案，创建一个表来处理时间戳（只有两列，document_id 和 last_update_timestamp），需要尽可能快和轻。

Long polling: that's the way i'll choose, using lighttpd under apache to load static files (images, css, js, etc..), and just for this type of long-polling; This will lighten the apache2 load, specially for the polling.

长轮询：我会选择这种方式，在apache下使用lighttpd来加载静态文件（图片，css，js等），并且只针对这种类型的长轮询；这将减轻 apache2 的负载，特别是对于轮询。

Apache will proxy-up all those request to lighttpd.

Apache 会将所有这些请求代理到 lighttpd。

Now, i only have to decide between db solution and APC solution..

现在，我只需要在 db 解决方案和 APC 解决方案之间做出决定..

p.s: thanks to all whom already answered me, you have been really usefull!

ps：感谢所有已经回答我的人，你真的很有用！

采纳答案by Arthur Frankel

I agree that I probably wouldn't hit the database for this. I suppose I would use APC cache (or some other in-memory cache) to maintain this information. What you are describing is clearly optimistic locking at the detailed record level. The higher the level in the database structure the less you need to deal with. It sounds like you want to check with multiple tables within a structure.

我同意我可能不会为此访问数据库。我想我会使用 APC 缓存（或其他一些内存缓存）来维护这些信息。您所描述的显然是详细记录级别的乐观锁定。数据库结构中的级别越高，您需要处理的内容就越少。听起来您想检查结构中的多个表。

I would maintain a cache (in APC) of the IDs and the timestamps of the last updated time keyed by the table name. So for example I might have an array of table names where each entry is keyed by ID and the actual value is the last updated timestamp. There are probably many ways to set this up with arrays or other structures but you get the idea. I would probably add a timeout to the cache so that entries in the cache are removed after a certain period of time - i.e., I wouldn't want the cache to grow and assume that 1 day old entries aren't useful anymore).

我会维护一个缓存（在 APC 中）的 ID 和由表名键控的上次更新时间的时间戳。例如，我可能有一个表名数组，其中每个条目都以 ID 为键，实际值是最后更新的时间戳。可能有很多方法可以用数组或其他结构来设置它，但你明白了。我可能会向缓存添加超时，以便在一段时间后删除缓存中的条目 - 即，我不希望缓存增长并假设 1 天前的条目不再有用）。

With this architecture you would need to do the following (in addition to setting up APC):

使用此架构，您需要执行以下操作（除了设置 APC）：

• on any update to any (applicable) table, update the APC cache entry with the new timestamp.

• within ajax just go as far "back" as php (to obtain the APC cache to check the entry) rather than all of the way "back" to the database.

• 在对任何（适用）表进行任何更新时，使用新的时间戳更新 APC 缓存条目。

• 在 ajax 中，只要“返回”到 php（获取 APC 缓存以检查条目），而不是“返回”到数据库的所有方式。

回答by Oso

I think you can use a condition in the UPDATE statement like WHERE ID=? AND LAST_UPDATE=?.

我认为您可以在 UPDATE 语句中使用条件，例如 WHERE ID=? AND LAST_UPDATE=?。

The idea is that you will only succeed in updating when you are the last one reading that row. If someone else has committed something, you will fail, and once you know you've failed, you can query the changes.

这个想法是，只有当您是最后一个阅读该行的人时，您才能成功更新。如果其他人提交了什么，你就会失败，一旦你知道自己失败了，你就可以查询更改。

回答by Richard Levasseur

Donnie's answer (polling) is probably your best option - simple and works. It'll cover almost every case (its unlikely a simple PK lookup would hurt performance, even on a very popular site).

唐尼的回答（投票）可能是你最好的选择——简单而有效。它将涵盖几乎所有情况（即使在非常受欢迎的站点上，简单的 PK 查找也不太可能会损害性能）。

For completeness, and if you wanted to avoid polling, you can use a push-model. There's various ways described in the Wikipedia article. If you can maintain a write-through cache (everytime you update the record, you update the cache), then you can almost completely eliminate the database load.

为了完整性，如果您想避免轮询，您可以使用push-model。维基百科文章中描述了多种方式。如果您可以维护一个直写缓存（每次更新记录时，都会更新缓存），那么您几乎可以完全消除数据库负载。

Don't use a timestamp "last_updated" column, though. Edits within the same second aren't unheard of. You could get away with it if you add extra information (server that did the update, remote address, port, etc) to ensure that, if two requests came in at the same second, to the same server, you could detect the difference. If you need that precision, though, you might as well use a unique revision field (it doesn't necessarily have to be an incrementing integer, just unique within that record's lifespan).

不过，不要使用时间戳“last_updated”列。在同一秒内进行编辑并非闻所未闻。如果您添加额外的信息（执行更新的服务器、远程地址、端口等）以确保如果两个请求在同一秒进入同一服务器，则您可以摆脱它，您可以检测到差异。但是，如果您需要这种精度，您也可以使用唯一的修订字段（它不一定是递增的整数，只需在该记录的生命周期内唯一）。

Someone mentioned persistent connections - this would reduce the setup cost of the polling queries (every connection consumes resources on the database and host machine, naturally). You would keep a single connection (or as few as possible) open all the time (or as long as possible) and use that (in combination with caching and memoization, if desired).

有人提到了持久连接——这将降低轮询查询的设置成本（每个连接自然会消耗数据库和主机上的资源）。您将始终（或尽可能长时间）保持一个连接（或尽可能少）打开并使用它（如果需要，结合缓存和记忆）。

Finally, there are SQL statements that allow you to add a condition on UPDATE or INSERT. My SQl is really rusting, but I think its something like UPDATE ... WHERE .... To match this level of protection, you would have to do your own row locking prior to sending the update (and all the error handling and cleanup that might entail). Its unlikely you'd need this; I'm just mentioning it for completness.

最后，还有一些 SQL 语句允许您在 UPDATE 或 INSERT 上添加条件。我的 SQl 真的生锈了，但我认为它类似于UPDATE ... WHERE .... 要匹配此级别的保护，您必须在发送更新之前进行自己的行锁定（以及可能需要的所有错误处理和清理）。你不太可能需要这个；我只是为了完整性而提及它。

Edit:

编辑：

Your solution sounds fine (cache timestamps, proxy polling requests to a another server). The only change I'd make is to update the cached timestamps on every save. This will keep the cache fresher. I'd also check the timestamp directly from the db when saving to prevent a save sneaking in due to stale cache data.

您的解决方案听起来不错（缓存时间戳、对另一台服务器的代理轮询请求）。我要做的唯一更改是在每次保存时更新缓存的时间戳。这将使缓存保持新鲜。我还会在保存时直接从数据库检查时间戳，以防止由于缓存数据陈旧而偷偷进入保存。

If you use APC for caching, then a second HTTP server doesn't make sense - you'd have to run it on the same machine (APC uses shared memory). The same physical machine would be doing the work, but with the additional overhead of a second HTTP server. If you want to off load the polling requests to a second server (lighttpd, in your case), then it would be better to setup lightttpd in front of Apache on a second physical machine and use a shared caching server (memcache) so that the lighttpd server can read the cached timestamps, and Apache can update the cached timestamps. The rationale for putting lighttpd in front of Apache is, if most requests are polling requests, to avoid the heavier-weight Apache process usage.

如果您使用 APC 进行缓存，那么第二个 HTTP 服务器就没有意义——您必须在同一台机器上运行它（APC 使用共享内存）。同一台物理机器将完成这项工作，但会产生第二个 HTTP 服务器的额外开销。如果您想将轮询请求卸载到第二台服务器（在您的情况下为 lighttpd），那么最好在第二台物理机器上的 Apache 前面设置 lightttpd 并使用共享缓存服务器（memcache），以便lighttpd 服务器可以读取缓存的时间戳，Apache 可以更新缓存的时间戳。将 lighttpd 放在 Apache 前面的基本原理是，如果大多数请求是轮询请求，则可以避免使用更重的 Apache 进程。

You probably don't need a second server at all, really. Apache should be able to handle the additional requests. If it can't, then I'd revisit your configuration (specifically the directives that control how many worker processes you run and how many requests they are allowed to handle before being killed).

您可能根本不需要第二台服务器，真的。Apache 应该能够处理额外的请求。如果不能，那么我将重新访问您的配置（特别是控制您运行的工作进程数量以及它们在被杀死之前允许处理的请求数量的指令）。

回答by Donnie

You will need some type of version stamp field for each record. What it is doesn't matter as long as you can guarantee that making any change to a record will result in that version stamp being different. Best practice is to then check and make sure the loaded record's version stamp is the same as the version stamp in the DB when the user clicks save, and if it's different handle it.

每条记录都需要某种类型的版本戳字段。只要您能保证对记录进行任何更改都会导致该版本标记不同，它是什么并不重要。最佳做法是检查并确保加载记录的版本戳与用户单击保存时数据库中的版本戳相同，如果不同，则对其进行处理。

How you handle it is up to you. At the very least you'd want to offer to reload from the DB so the user can verify that they still want to save. One up from that would be to attempt to merge their changes into the new DB record and then ask them to verify that the merge worked correctly.

你如何处理它取决于你。至少您希望提供从数据库重新加载，以便用户可以验证他们是否仍然想要保存。一种是尝试将他们的更改合并到新的数据库记录中，然后要求他们验证合并是否正常工作。

If you want to periodically poll any DB capable of handling your system should be able to take the poll load. 10 users polling once every 5 seconds is 2 transactions per second. This is a trivial load, and should be no problem at all. To keep the average load close to the actual load, just jitter the polling time slightly (instead of doing it exactly every 5 seconds, do it every 4-6 seconds, for example).

如果您想定期轮询任何能够处理您的系统的数据库应该能够承担轮询负载。每 5 秒轮询一次的 10 个用户是每秒 2 个事务。这是一个微不足道的负载，应该没有问题。为了使平均负载接近实际负载，只需稍微抖动轮询时间（而不是每 5 秒执行一次，例如每 4-6 秒执行一次）。

回答by Mark Tomlin

First off only update the fields that have changed on when writing to the database, this will decrease database load.

首先只更新写入数据库时​​已更改的字段，这将减少数据库负载。

Second, query the timestamp of the last update, if you have a older timestamp then the current version in the database then throw the warning to the client.

其次，查询上次更新的时间戳，如果您有较旧的时间戳，那么数据库中的当前版本则向客户端抛出警告。

Third is to somehow push this information to the client, though some kind of persistent connection with the server, enabling a concurrent two way connection.

第三是以某种方式将此信息推送到客户端，尽管与服务器有某种持久连接，从而实现并发双向连接。

回答by stimms

Your approach of querying the database is the best one. If you do it every 5 seconds and you have 15 concurrent users then you're looking at ~3 queries a second. It should be a very small query too, returning only one row of data. If your database can't handle 3 transactions a second then you might have to look at a better database because 3 queries/second is nothing.

您查询数据库的方法是最好的方法。如果您每 5 秒执行一次并且您有 15 个并发用户，那么您每秒会查看大约 3 个查询。它也应该是一个非常小的查询，只返回一行数据。如果您的数据库每秒不能处理 3 个事务，那么您可能需要查看一个更好的数据库，因为 3 个查询/秒算不了什么。

Timestamp the records in the table so you can quickly see if anything has changed without having to diff each field.

为表中的记录加上时间戳，这样您就可以快速查看是否有任何更改，而无需区分每个字段。

回答by wallenborn

Hibernate uses a version field to do that. Give every table such a field and use a trigger to increment it on every update. When storing an update, compare the current version with the version when the data was read earlier. If those don't match, throw an exception. Use transactions to make the check-and-update atomic.

Hibernate 使用 version 字段来做到这一点。为每个表提供一个这样的字段，并使用触发器在每次更新时增加它。存储更新时，将当前版本与之前读取数据时的版本进行比较。如果这些不匹配，则抛出异常。使用事务使检查和更新原子化。

回答by Ewan Todd

This is slightly off topic, but you can use the PEAR package (or PECL package, I forget which) xdiffto send back good user guidance when you do get a collision.

这有点偏离主题，但您可以使用 PEAR 包（或 PECL 包，我忘记了哪个）xdiff在发生碰撞时发回良好的用户指南。

回答by Sigersted

Polling is rarely a nice solution.
You could do the timstamp check only when the user (with the open document) is doing something active with the document like scrolling, moving the mouse over it or starts to edit. Then the user gets an alert if the document has been changed.

.....
I know it was not what you asked for but ... why not a edit-singleton?
The singleton could be a userID column in the document-table.
If a user wants to edit the document, the document is locked for edit by other users.

轮询很少是一个好的解决方案。
仅当用户（使用打开的文档）正在对文档执行某些活动时，例如滚动、将鼠标移到其上或开始编辑时，您才能进行 timstamp 检查。如果文档已更改，则用户会收到警报。

.....
我知道这不是你要的，但是......为什么不是编辑单例？
单例可以是文档表中的用户 ID 列。
如果用户想要编辑该文档，该文档将被锁定以供其他用户编辑。

Or have edit-singletons on the individual fields/groups of information.

或者对单个字段/信息组进行编辑单例。

Only one user can edit the document at a time. If another user has the document open and want to edit a single timestamp check reveal that the document has been altered and is reloaded.

一次只有一个用户可以编辑文档。如果另一个用户打开了文档并想要编辑单个时间戳检查显示文档已被更改并重新加载。

With a singleton there is no polling and only one timestamp check when the user "touches" and/or wants to edit the document.

对于单例，当用户“触摸”和/或想要编辑文档时，没有轮询，只有一个时间戳检查。

But perhaps a singleton mechanism doesn't fit your system.

Regards
   Sigersted

但也许单例机制不适合您的系统。

问候
   西格斯特德

回答by Strae

Ahhh, i though it was easyer.

啊哈，我虽然这更容易。

So, lets make the point: i have a generic database (pgsql or mysql doesn't matter), that contains many generic objects.

所以，让我们说明一点：我有一个通用数据库（pgsql 或 mysql 无关紧要），其中包含许多通用对象。

I have $x (actually $x = 200, but is growing, hoping will reach 1000 soon) of exact copy of this database, and for each of them up to 20 (avg 10) users for 9 hours at day.

我有这个数据库的精确副本 $x（实际上 $x = 200，但正在增长，希望很快会达到 1000 个），并且每个人每天最多 20 个（平均 10 个）用户 9 小时。

If one of those users is viewing a record, any record, i must advice him if someone edit the same record.

如果其中一个用户正在查看记录，任何记录，如果有人编辑同一记录，我必须建议他。

Let's say Foo is watching the document 0001, sit up for a coffee, Bar open and edit the same document, when Foo come back he must see an 'Warning, someone else edited this document! click here to refresh tha page.'.

假设 Foo 正在观看文档 0001，坐起来喝杯咖啡，Bar 打开并编辑同一个文档，当 Foo 回来时，他必须看到一个'警告，其他人编辑了这个文档！单击此处刷新页面。'。

That'all i need atm, probably i'll extend this situation, adding a way to see the changes and rollback, but this is not the point.

这就是我需要的所有 atm，可能我会扩展这种情况，添加一种查看更改和回滚的方法，但这不是重点。

Some of you suggested to check the 'last update' timestamp only when foo try to save the document; Can be a solution too, but i need something in real-time ( 10 sec deelay ).

你们中的一些人建议仅在 foo 尝试保存文档时检查“上次更新”时间戳；也可以是一个解决方案，但我需要一些实时的东西（延迟 10 秒）。

Long polling, bad way, but seem to be the only one.

长轮询，糟糕的方式，但似乎是唯一的方式。

So, what i've done:

所以，我做了什么：

1. Installed Lighttp on my machine (and php5 as fastcgi);
2. Loaded apache2's proxy module (all, or 403 error will hit you);
3. Changed the lighttpd port from 80 (that is used by apache2) to 81;
4. Configured apache2 to proxying the request from mydomain.com/polling/* to polling.mydomain.com (served with Lighttp)
5. Now, i have another sub http-service that i'll use both for polling and load static content (images, etc..), in order to reduce the apache2's load.
6. Becose i dont want to nuke the databasefor the timestamp check, i've tryed some caches system (that can be called from php).
   1. APC: quite simple to install and manage, very lightweight and faster, this would be my first choice.. if only the cache would be sharable between two cgi process (i need to store in cache a value from apache2's php process, and read it from lighttpd's php process)
   2. Memcached: around 4-5 times slower than APC, but run as a single process that can be touched everywhere in my environment. I'll go with this one, atm.(even if is slower, the use i'll do of it is relatively simple).

1. 在我的机器上安装 Lighttp（和 php5 作为 fastcgi）；
2. 加载了apache2的代理模块（全部，否则403错误会打你）；
3. 将lighttpd端口由80（即apache2使用的）改为81；
4. 将 apache2 配置为将请求从 mydomain.com/polling/* 代理到 polling.mydomain.com（与 Lighttp 一起提供）
5. 现在，我有另一个子 http 服务，我将使用它来轮询和加载静态内容（图像等），以减少 apache2 的负载。
6. 因为我不想为时间戳检查核对数据库，所以我尝试了一些缓存系统（可以从 php 调用）。
   1. APC：安装和管理非常简单，非常轻量级和更快，这将是我的第一选择..如果只有缓存可以在两个 cgi 进程之间共享（我需要在缓存中存储来自 apache2 的 php 进程的值，并读取它来自lighttpd的php进程）
   2. Memcached：大约比 APC 慢 4-5 倍，但作为单个进程运行，可以在我的环境中的任何地方访问。我会用这个，自动取款机。（即使速度较慢，我将使用它也相对简单）。

Now, i just have to try this system loading some test datas to see ho will move 'under pressure' and optimize it.

现在，我只需要尝试这个系统加载一些测试数据，看看 ho 会“在压力下”移动并优化它。

I suppost this environment will work for other long-polling situations (chat?)

我认为这种环境适用于其他长轮询情况（聊天？）

Thanks to everyone who gave me hear!

感谢所有给我听的人！