search
HomeDatabaseMysql Tutorial讲解MySQL中的事务特性_MySQL

一个事务是一个连续的一组数据库操作,就好像它是一个单一的工作单元进行。换言之,永远不会是完整的事务,除非该组内的每个单独的操作是成功的。如果在事务的任何操作失败,则整个事务将失败。

实际上,会俱乐部许多SQL查询到一个组中,将执行所有的人都一起作为事务的一部分。
事务的特性:

事务有以下四个标准属性的缩写ACID,通常被称为:

  •     原子性: 确保工作单元内的所有操作都成功完成,否则事务将被中止在故障点,和以前的操作将回滚到以前的状态。
  •     一致性: 确保数据库正确地改变状态后,成功提交的事务。
  •     隔离性: 使事务操作彼此独立的和透明的。
  •     持久性: 确保提交的事务的结果或效果的系统出现故障的情况下仍然存在。
  • 在MySQL中,事务开始使用COMMIT或ROLLBACK语句开始工作和结束。开始和结束语句的SQL命令之间形成了大量的事务。
  • COMMIT & ROLLBACK:
  • 这两个关键字提交和回滚主要用于MySQL的事务。
  •     当一个成功的事务完成后,发出COMMIT命令应使所有参与表的更改才会生效。
  •     如果发生故障时,应发出一个ROLLBACK命令返回的事务中引用的每一个表到以前的状态。

可以控制的事务行为称为AUTOCOMMIT设置会话变量。如果AUTOCOMMIT设置为1(默认值),然后每一个SQL语句(在事务与否)被认为是一个完整的事务,并承诺在默认情况下,当它完成。 AUTOCOMMIT设置为0时,发出SET AUTOCOMMIT =0命令,在随后的一系列语句的作用就像一个事务,直到一个明确的COMMIT语句时,没有活动的提交。

可以通过使用mysql_query()函数在PHP中执行这些SQL命令。
通用事务例子

这一系列事件是独立于所使用的编程语言,可以建立在任何使用的语言来创建应用程序的逻辑路径。
可以通过使用mysql_query()函数在PHP中执行这些SQL命令。

  •     BEGIN WORK开始事务发出SQL命令
  •     发出一个或多个SQL命令,如SELECT,INSERT,UPDATE或DELETE
  •     检查是否有任何错误,一切都依据的需要。
  •     如果有任何错误,那么问题ROLLBACK命令,否则发出COMMIT命令。

在MySQL中的事务安全表类型:

如果打算使用MySQL事务编程,那么就需要一种特殊的方式创建表。有很多支持事务但最流行的是InnoDB表类型。

从源代码编译MySQL时,InnoDB表支持需要特定的编译参数。如果MySQL版本没有InnoDB支持,请互联网服务提供商建立一个版本的MySQL支持InnoDB表类型,或者下载并安装Windows或Linux/UNIX的MySQL-Max二进制分发和使用的表类型在开发环境中。
如果MySQL安装支持InnoDB表,只需添加一个的TYPE=InnoDB 定义表创建语句。例如,下面的代码创建InnoDB表tcount_tbl:

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> create table tcount_tbl
  -> (
  -> tutorial_author varchar(40) NOT NULL,
  -> tutorial_count INT
  -> ) TYPE=InnoDB;
Query OK, 0 rows affected (0.05 sec)

请查看下面的链接了解更多关于: InnoDB

可以使用其他GEMINI或BDB表类型,但它取决于您的安装,如果它支持这两种类型。

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
What are the different storage engines available in MySQL?What are the different storage engines available in MySQL?Apr 26, 2025 am 12:27 AM

MySQLoffersvariousstorageengines,eachsuitedfordifferentusecases:1)InnoDBisidealforapplicationsneedingACIDcomplianceandhighconcurrency,supportingtransactionsandforeignkeys.2)MyISAMisbestforread-heavyworkloads,lackingtransactionsupport.3)Memoryengineis

What are some common security vulnerabilities in MySQL?What are some common security vulnerabilities in MySQL?Apr 26, 2025 am 12:27 AM

Common security vulnerabilities in MySQL include SQL injection, weak passwords, improper permission configuration, and unupdated software. 1. SQL injection can be prevented by using preprocessing statements. 2. Weak passwords can be avoided by forcibly using strong password strategies. 3. Improper permission configuration can be resolved through regular review and adjustment of user permissions. 4. Unupdated software can be patched by regularly checking and updating the MySQL version.

How can you identify slow queries in MySQL?How can you identify slow queries in MySQL?Apr 26, 2025 am 12:15 AM

Identifying slow queries in MySQL can be achieved by enabling slow query logs and setting thresholds. 1. Enable slow query logs and set thresholds. 2. View and analyze slow query log files, and use tools such as mysqldumpslow or pt-query-digest for in-depth analysis. 3. Optimizing slow queries can be achieved through index optimization, query rewriting and avoiding the use of SELECT*.

How can you monitor MySQL server health and performance?How can you monitor MySQL server health and performance?Apr 26, 2025 am 12:15 AM

To monitor the health and performance of MySQL servers, you should pay attention to system health, performance metrics and query execution. 1) Monitor system health: Use top, htop or SHOWGLOBALSTATUS commands to view CPU, memory, disk I/O and network activities. 2) Track performance indicators: monitor key indicators such as query number per second, average query time and cache hit rate. 3) Ensure query execution optimization: Enable slow query logs, record and optimize queries whose execution time exceeds the set threshold.

Compare and contrast MySQL and MariaDB.Compare and contrast MySQL and MariaDB.Apr 26, 2025 am 12:08 AM

The main difference between MySQL and MariaDB is performance, functionality and license: 1. MySQL is developed by Oracle, and MariaDB is its fork. 2. MariaDB may perform better in high load environments. 3.MariaDB provides more storage engines and functions. 4.MySQL adopts a dual license, and MariaDB is completely open source. The existing infrastructure, performance requirements, functional requirements and license costs should be taken into account when choosing.

How does MySQL's licensing compare to other database systems?How does MySQL's licensing compare to other database systems?Apr 25, 2025 am 12:26 AM

MySQL uses a GPL license. 1) The GPL license allows the free use, modification and distribution of MySQL, but the modified distribution must comply with GPL. 2) Commercial licenses can avoid public modifications and are suitable for commercial applications that require confidentiality.

When would you choose InnoDB over MyISAM, and vice versa?When would you choose InnoDB over MyISAM, and vice versa?Apr 25, 2025 am 12:22 AM

The situations when choosing InnoDB instead of MyISAM include: 1) transaction support, 2) high concurrency environment, 3) high data consistency; conversely, the situation when choosing MyISAM includes: 1) mainly read operations, 2) no transaction support is required. InnoDB is suitable for applications that require high data consistency and transaction processing, such as e-commerce platforms, while MyISAM is suitable for read-intensive and transaction-free applications such as blog systems.

Explain the purpose of foreign keys in MySQL.Explain the purpose of foreign keys in MySQL.Apr 25, 2025 am 12:17 AM

In MySQL, the function of foreign keys is to establish the relationship between tables and ensure the consistency and integrity of the data. Foreign keys maintain the effectiveness of data through reference integrity checks and cascading operations. Pay attention to performance optimization and avoid common errors when using them.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

VSCode Windows 64-bit Download

VSCode Windows 64-bit Download

A free and powerful IDE editor launched by Microsoft

MinGW - Minimalist GNU for Windows

MinGW - Minimalist GNU for Windows

This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.

EditPlus Chinese cracked version

EditPlus Chinese cracked version

Small size, syntax highlighting, does not support code prompt function

SAP NetWeaver Server Adapter for Eclipse

SAP NetWeaver Server Adapter for Eclipse

Integrate Eclipse with SAP NetWeaver application server.

Dreamweaver Mac version

Dreamweaver Mac version

Visual web development tools