C++ 如何从多个线程并发访问MySQL

How to access MySQL from multiple threads concurrently

提问by Isak Savo

We're doing a small benchmark of MySQL where we want to see how it performs for our data.

我们正在做一个 MySQL 的小型基准测试，我们想看看它对我们的数据的表现如何。

Part of that test is to see how it works when multiple concurrent threads hammers the server with various queries.

该测试的一部分是查看当多个并发线程使用各种查询冲击服务器时它是如何工作的。

The MySQL documentation(5.0) isn't really clear about multi threaded clients. I should point out that I do link against the thread safe library (libmysqlclient_r.so)

在MySQL文档（5.0）是不是多线程的客户真的清楚。我应该指出，我确实链接了线程安全库 ( libmysqlclient_r.so)

I'm using prepared statements and do both read (SELECT) and write (UPDATE, INSERT, DELETE).

我正在使用准备好的语句并执行读取 (SELECT) 和写入 (UPDATE、INSERT、DELETE)。

• Should I open one connection per thread? And if so: how do I even do this.. it seems mysql_real_connect()returns the original DB handle which I got when I called mysql_init())
• If not: how do I make sure results and methods such as mysql_affected_rowsreturns the correct value instead of colliding with other thread's calls (mutex/locks could work, but it feels wrong)

• 我应该为每个线程打开一个连接吗？如果是这样：我该怎么做..它似乎mysql_real_connect()返回了我打电话时得到的原始数据库句柄mysql_init()）
• 如果不是：我如何确保结果和方法（例如mysql_affected_rows返回正确的值而不是与其他线程的调用发生冲突）（互斥锁/锁可以工作，但感觉不对）

回答by chazomaticus

As maintainer of a fairly large C application that makes MySQL calls from multiple threads, I can say I've had no problems with simply making a new connection in each thread. Some caveats that I've come across:

作为从多个线程进行 MySQL 调用的相当大的 C 应用程序的维护者，我可以说我在每个线程中简单地建立一个新连接没有问题。我遇到的一些警告：

• Edit: it seems this bullet only applies to versions < 5.5; see this page for your appropriate version:Like you say you're already doing, link against libmysqlclient_r.
• Call mysql_library_init()(once, from main()). Read the docs about use in multithreaded environments to see why it's necessary.
• Make a new MYSQLstructure using mysql_init()in each thread. This has the side effect of calling mysql_thread_init()for you. mysql_real_connect()as usual inside each thread, with its thread-specific MYSQL struct.
• If you're creating/destroying lots of threads, you'll want to use mysql_thread_end()at the end of each thread (and mysql_library_end()at the end of main()). It's good practice anyway.

• 编辑：似乎此项目符号仅适用于 <5.5 的版本；请参阅此页面以了解您的适当版本：就像您说的那样，链接到libmysqlclient_r.
• 呼叫mysql_library_init()（一次，来自main()）。阅读有关在多线程环境中使用的文档以了解其必要性。
• 在每个线程中MYSQL使用一个新的结构mysql_init()。这具有呼叫mysql_thread_init()您的副作用。 mysql_real_connect()像往常一样在每个线程内部，使用其特定于线程的 MYSQL 结构。
• 如果您要创建/销毁大量线程，则需要mysql_thread_end()在每个线程mysql_library_end()的末尾（以及的末尾main()）使用。无论如何都是很好的做法。

Basically, don't share MYSQLstructs or anything created specific to that struct (i.e. MYSQL_STMTs) and it'll work as you expect.

基本上，不要共享MYSQL结构或为该结构创建的任何内容（即MYSQL_STMTs），它会按您的预期工作。

This seems like less work than making a connection pool to me.

这似乎比为我建立一个连接池工作更少。

回答by Glen

You could create a connection pool. Each thread that needs a connection could request a free one from the pool. If there's no connection available then you either block, or grow the pool by adding a new connection to it.

您可以创建一个连接池。每个需要连接的线程都可以从池中请求一个空闲的连接。如果没有可用连接，那么您可以阻止或通过向池添加新连接来增加池。

There's an article heredescribing the pro's and cons of a connection pool (though it is java based)

有一篇文章在这里描述的连接池的亲的和缺点（尽管它是基于Java的）

Edit: Here's a SO question / answer about connection pools in C

编辑：这是一个关于C 中连接池的问题/答案

Edit2: Here's a link to a sample Connection Pool for MySQLwritten in C++. (you should probably ignore the goto statements when you implement your own.)

编辑 2：这是一个用 C++ 编写的 MySQL 连接池示例的链接。（当您实现自己的语句时，您可能应该忽略 goto 语句。）

回答by Elemental

Seems clear to me from the mySQL Docs that any specific MYSQL structure can be used in a thread without difficulty - using the sameMYSQL structure in different threads simultaneously is clearly going to give you extremely unpredictable results as state is stored within the MYSQL connection.

从 mySQL Docs 中我似乎很清楚，任何特定的 MYSQL 结构都可以毫无困难地在一个线程中使用 -同时在不同的线程中使用相同的MYSQL 结构显然会给您带来极其不可预测的结果，因为状态存储在 MYSQL 连接中。

Thus either create a connection per thread or used a pool of connections as suggested above and protect access to that pool (i.e. reserving or releasing a connection) using some kind of Mutex.

因此，要么为每个线程创建一个连接，要么使用上面建议的连接池，并使用某种互斥锁保护对该池的访问（即保留或释放连接）。

回答by Tiemen

MySQL Threaded Clients in C

C 语言中的 MySQL 线程客户端

It states that mysql_real_connect() is not thread safe by default. The client library needs to be compiled for threaded access.

它指出 mysql_real_connect() 默认不是线程安全的。客户端库需要编译为线程访问。