使用MYSQL交易，基本问题
我是数据库编程新手。 我到处都读到，如果我以后遇到问题，我可以使用事务来回滚我的操作。
我想了解事务：
- 事务是否“锁定”数据库？
- 如果发生锁定，访问数据库的其他用户会发生什么？他们有可能收到错误消息吗？我应该以编程方式检查此类错误并重试吗？
有什么事情可以让我的数据库为事务做好准备吗？比如将自动提交设置为关闭？还有别的吗？ 使用交易有缺点吗？对我来说似乎一切都很好。
提前致谢！
I am new to db programming.
I read everywhere that I can use trasactions to rollback my actions if I encounter problem down the road.
I would like to know about transactions:
- does a transaction "lock" the db ?
- If a locking happens, what happens to other users accessing the db ? is it possible that they may get an error message ? Should I programatically check for such an error and try again ?
Are there things to do to make my db ready for transactions ? like setting the autocommit to off ? Is there anything else ?
Is there a downside to using transactions ? It seems all good to me.
Thanks in advance!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
整个数据库永远不会被锁定，这将是非常低效的。
相反，MySQL（取决于存储引擎）会锁定记录，在某些情况下还会锁定整个表。
需要确保存储引擎是InnoDB，否则无法进行事务。
自动提交是一项仅存在于连接客户端中的功能，它只是在每次对数据库进行更改时发出提交。
事务会产生开销，并可能导致严重的性能问题。如果您尝试一次又一次地以事务方式写入同一条记录（假设主页上的点击计数器），那么它会锁定该记录，并且大量写入会被阻止。
仅当您需要在数据库中提供一致性时才应该使用事务，即一堆记录应该一次性写入，或者根本不写入。
The whole database is never locked, that would be very inefficient.
Rather, MySQL (depending on the storage engine), locks records and in some cases whole tables.
You need to make sure the storage engine is InnoDB, otherwise transactions are not possible.
Autocommit is a feature that exists in the connecting client only, it just issues a commit everytime you make changes to the DB.
Transactions will cause overhead and might cause significant performance problems. If you try to transactionally write to the same record (let's say a hit-counter on your homepage) over and over again, then it locks this record and a lot of writes are blocked.
You should only use transactions if you need to provide consistency in your database, i.e. a bunch of records together should be written in 1 go, or not at all.
没有。它们允许查询锁定数据库的特定部分，并允许数据库引擎向其他客户端呈现统一的数据视图，甚至在正在更新的表上也是如此。
他们看到的数据是相关查询发生之前的数据，并且可能会被阻止写入表。
并非所有表引擎都支持事务，因此您需要使用支持事务的引擎（例如 InnoDB），并且事务确实需要非零的时间来处理。
No. They allow queries to lock specific portions of the database, and allow the database engine to present a uniform view of the data to other clients even on tables that are being updated.
They see the data as it was before the relevant query happened, and they may be prevented from writing to the table.
Not all table engines support transactions so you will need to use one that does (such as InnoDB), and transactions do take a non-zero amount of time to process.
您应该知道，根据您使用的数据库引擎（默认情况下为 MyISAM），锁定是按表进行的。有不同的引擎（例如 InnoDB）可以进行行锁定。
此外，还有许多查询可以同时执行，即使它们都位于不同的表上。如果超过这个数量，你也会收到锁。您可以在 phpmyadmin 中检查您收到了多少锁，以了解您的数据库处理是否存在问题。
关于事务：有时您确实希望使用部分信息更新数据库。这实际上取决于您的使用情况。
You should know that according to the db engine you are using, which is MyISAM by default, the locking is table-wise. There are different engines like InnoDB that do Row-Locking.
Also, there is a number of queries that can execute simultaneously even if they are all on different tables. If you exceed this number, you will also receive a lock. You can check out in the phpmyadmin how many locks you received to find out if there are problems in your db handling.
Regarding transactions: sometimes you do wish to update the db with parts of the information. It really depends on your usage.
Ans 1. 不会。它会根据查询的性质锁定部分表或整个表。这些锁可以是读锁或写锁。
Ans 2. 如果用户想要锁定另一个用户拥有写锁的表的一部分，则该用户将被阻止，直到锁空闲为止。如果另一个用户拥有读锁，而该用户想要写锁，则该用户将被保留，直到该锁空闲为止。您需要阅读有关死锁的信息。
希望有帮助。
Ans 1. No. It locks parts of tables or complete tables depending on the nature of the queries. These locaks can be read or write locks.
Ans 2. If the user wants to lock a part of a table that another user has a write lock, the user is held up until the lock becomes free. If the other user has a read lock and the user wants a write lock the user is held up until the lock becomes free. You need to read up about deadlocks.
Hope that helps.
由于我们正在谈论MySQL和事务，所以整个答案是关于InnoDB的，尽管它的大部分对于任何支持事务的存储引擎都有效。
事务中的锁定取决于存储引擎和事务隔离级别。 MySQL/InnoDB 使用行版本控制来实现事务，并减少除可序列化之外的所有隔离级别中使用的锁数量。 MySQL默认的隔离级别是可重复读。
通常更新使用写锁，这意味着另一个事务不能更新同一行，但可以读取它。如果您尝试访问已锁定的行，MySQL 将等待，直到达到锁等待超时，然后将终止查询，通知您已达到锁等待超时。通常这种情况不应该发生。然而，使用事务中最危险的部分是引入
死锁- 当事务A更新record1并尝试更新record2<时/code>，被事务 B锁定 -事务 B已更新record2并尝试更新record1，但record1被锁定A，所以两者都在等待对方。一般建议是保持事务尽可能小，并尽早锁定要更新的行，以防止死锁（您可以使用 select... for update 来实现此目的）。
可以在 MySQL 文档 和MySQL 认证指南。
Since we are talking about MySQL and transactions, the whole answer is about InnoDB, although the most of it is valid for any storage engine, supporting transactions.
The locking in transaction depends on storage engine and on transaction isolation level. MySQL/InnoDB uses row-versioning for implementing transactions, and lower the number of locks used in all isolation level, except for serializable. The default isolation level in MySQL is repeatable read.
Generally updates use write-locks, which means that another transaction cannot update the same row, but can read it. If you try to access a row that is locked, MySQL will wait until lock-wait timeout is reached, then will kill the query, informing you that the lock-wait timeout is reached. Usually this should not happen. The most danger part in using transactions however, is introducing
dead-locks- whentransaction Aupdatesrecord1and tries to updaterecord2, which is locked bytransaction B-transaction Bhave updatedrecord2and tries to updaterecord1, butrecord1is locked byA, so both waits for each other.General advice is to keep your transactions as small as possible, and lock the rows you are going to update as early as possible, to prevent deadlock (you can use select... for update for this).
A lot of information can be found in MySQL documentation and in the MySQL Certification guide.