search

 介绍

mysql 从4.1 就开始支持事务处理,但是只有用 InnoDB /BDB 类型的引擎创建的数据库才支持事务操作。

查看mysql数据库创建引擎类型:show create table table_name

创建或修改指定类型数据库:Create table .... type=InnoDB; Alter table table_name type=InnoDB;

mysql 事务类型

认为分为两种:

1、begin ,rollback,commit .当然有的人用begin /begin work .推荐用START TRANSACTION 是SQL-99标准启动一个事务。

start transaction;
update from account set money=money-100 where name='a';
update from account set money=money+100 where name='b';
commit;

<code class="language-sql"><code class="language-sql">解释: 这样start transaction 手动开启事务,commit 手动关闭事务。

<code class="language-sql"><code class="language-sql">2、默认的时候autocommit=1 自动提交是开启的,所以你可以理解为每条语句一输入到mysql就commit 了。当你 set autocommit=0 时候,你可以这样:

update from account set money=money-100 where name=&#39;a&#39;;
update from account set money=money+100 where name=&#39;b&#39;;
commit;

<code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql">mysql事务级别


<code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql">在介绍事务级别之前需要了解常见在数据库查询和更新过程中出现的一些问题,简单来讲就是这些事务级别是为了解决什么问题而存在的。 ?

<code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql">1.不可重复读 重复读指的是我们在每次读取的结果都需要是一致的。那么不可重复读就是这个问题会出现每次读取相关的数据出现不一致。出现这个的原因是读取到了其他会话对这条记录进行了修改。<br> <br> 2.幻读 幻读指的是当前会话来读取相关记录时,出现其他会话对这个记录添加了一条记录。为了避免幻读通常将事务的隔离级别设置为 serializable 。但是随之而来的问题就有了,当数据库的事务级别设置为serializable级别,数据库就变成了单线程访问数据库,导致性能下降非常大。<br> <br> 3.脏读 例如:用户A\B同时向t_accout 表操作,A对t_accout做了这样一个操作,update account set money=money+100 ;在没有提交之前,B用户去查询到没有提交的的money,而后A此时有了一个rollback,B用户再查发现钱少了100.这时候为了避免这个情况,一般设置事务的级别为 <strong>read committed 。就是只能读取提交后的东东 ;</strong><br> <br> 为了解决上述的问题出现了事务的级别,以下是数据库中常见的事务级别。<br> <br> 级别<br> <br> 1.读未提交(Read Uncommitted):这种隔离级别可以让当前事务读取到其它事物还没有提交的数据。这种读取应该是 在回滚段中完成的。通过上面的分析,这种隔离级别是最低的,会导致引发脏读,不可重复读,和幻读。 2.读已提交(Read Committed):这种隔离级别可以让当前事务读取到其它事物已经提交的数据。通过上面的分析,这种隔离级别会导致引发不可重复读,和幻读。 3.可重复读取(Repeatable Read):这种隔离级别可以保证在一个事物中多次读取特定记录的时候都是一样的。通过上面的分析,这种隔离级别会导致引发幻读。 4.串行(Serializable):这种隔离级别将事物放在一个队列中,每个事物开始之后,别的事物被挂起。同一个时间点只能有一个事物能操作数据库对象。这种隔离级别对于数据的完整性是最高的,但是同时大大降低了系统的可并发性。<br> <strong>各个级别对应可能出现的问题</strong><br> <br> <strong><img src="/static/imghwm/default1.png" data-src="http://img.bitscn.com/upimg/allimg/c150410/142V3E423Z60-11355.jpg" class="lazy" alt="\" style="max-width:90%" style="max-width:90%"></strong>

<code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql">锁机制


<code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><small><small>除了事务来处理以上不可重复读、幻读、脏读等问题。还有锁也能解决上述问题。</small></small> 在讲解锁之前,需要对常见的几个锁的名词有一个感性的认识。

<code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql">排它锁:


<code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql">由写表操作加上的锁,加锁后其他用户不能获取该表或行的任何锁,典型是mysql事务中。也就是既不能对表查询,也不能对表进行修改。<br> 例如:为user表加排它锁
start transaction;
 
select * from user where userId = 1 for update;

<code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql">共享锁:


<code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql">由读表操作加上的锁,加锁后其他用户只能获取该表或行的共享锁,不能获取排它锁,也就是说只能读不能写 排它锁和共享锁都是针对某个数据库来讲的,而通常我们听到的还有例如悲观锁、乐观锁、行锁、表锁。<br> 悲观锁其实是针对于开发者或者说是程序员来讲的。悲观锁就是认为在操作数据库时,悲观的认为会在同一时间出现并发访问问题。一般是更新多、查询少的时候用。与之对应的是乐观锁,一般在更新少、查询多的时候用。<br> <br> 这里援引来自网上的一个经典的实例来讲解事务的现实使用。

<code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql">实例讲解


<code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql">场景:老公去在 ATM 上取钱,老婆在柜台存钱,假设这个账户中有 1000 元。老公首先执行查询操作,查询到账户余额为 1000 此时程序将 1000 拿到内存中,老公取了 200 元,程序就执行了更新操作将账户余额改为 800,但是当老公的程序没有 commit 的时候,老婆查询账户,此时账户余额还是 1000 元,老婆存入 200 元,程序执行了更新操作将账户余额改为 1200,然后老公将更新语句提交,接着老婆也将更新语句提交。最后导致的结果就是该账户的余额为 1200,这就是更新丢失的问题。引发更新丢失的根源就是查询上,因为双方都是根据从数据库查询到的数据再对数据库中的数据进行更新的。解决更新丢失有三个方案:(1) 将事务隔离级别设置为最高,采用死锁策略。(2) <strong>采用悲观锁</strong>,悲观锁不是数据库中真正的锁,是人们看待事务的态度。(3)<strong> 采用乐观锁</strong>,乐观锁也不是数据库中真正的锁。

 

<code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql">如果我们采用的是第一个方案时,老公进行查询操作,数据库为表增加了共享锁,老婆进行查询操作时数据库也增加了一个共享锁。但是当老公进行更新数据库操作时,由于老婆拿着共享锁,导致老公不能增加排它锁,老婆进行更新操作时,因为老公拿着共享锁,导致老婆也拿不到排它锁,这就发生了死锁现象,你等我,我等你。在 mysql 中,处理死锁的方案是释放掉一方的锁。这样就保证了一方更新成功,但是这种性能极低,因为数据库频繁在解决死锁问题。

 

<code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><strong>悲观锁(更新多,查询少时用)</strong>

<code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql">如果我们采用的是第二个方案时,即采用悲观锁。就是我们在操作数据库时采用悲观的态度,认为别人会在此时并发访问数据库。我们在查询语句中 select * from account where name='aaa' for update; 等于加了排它锁。当老公查询余额的时候,select money from account where name='aaa' for update; 增加了排它锁,老婆查询账户余额的时候, select money from account where name='aaa' for update;也要求对数据库加排它锁,因为老公已经拿到了排它锁,导致老婆不能加锁,所以老婆只有等待老公执行完毕,释放掉锁以后才能继续操作。

 

<code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><strong>乐观锁(更新少,查询多时用)</strong>

<code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql">如果我们采用的是第三个方案时,即采用乐观锁,就是我们在操作数据库的时候会认为没有其它用户并发访问,但是乐观锁也不是完全乐观的,乐观锁是采用版本号的方式进行控制的。在数据库表中有一列版本号。从数据库中查询的时候,将版本号也查询过来,在进行更新操作的时候,将版本号加1,查询条件的版本号还是查询过来的版本号。比如,老公执行查询操作的时候,select money,version from account where name='aaa'; 假设此时查询到的版本号为 0,老公在进行更新操作的时候 update account set money=money+100,version=version+1 where name='aaa' and version=0; 未提交时老婆来查询,查询到的版本号依然是 0,老婆也执行更新操作 update account set money=money+100,version=version+1 where name='aaa' and version=0; 现在老公提交了事务,老婆再提交事务的时候发现版本号为 0 的记录没有了,所以就避免了数据丢失的问题。不过这种情况也导致了多个用户更新操作时,只有一个用户的更新被执行。

 

<code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql">附:各数据库默认事务级别

<code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql">1、mysql的默认事务的隔离级别:可重复读取(repeatable read);

<code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql">2、sqlserver的默认事务的隔离级别:提交读取(read committed);

<code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql"><code class="language-sql">3、oracle的默认事务的隔离级别:提交读取(read committed).

 

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
How do you handle database upgrades in MySQL?How do you handle database upgrades in MySQL?Apr 30, 2025 am 12:28 AM

The steps for upgrading MySQL database include: 1. Backup the database, 2. Stop the current MySQL service, 3. Install the new version of MySQL, 4. Start the new version of MySQL service, 5. Recover the database. Compatibility issues are required during the upgrade process, and advanced tools such as PerconaToolkit can be used for testing and optimization.

What are the different backup strategies you can use for MySQL?What are the different backup strategies you can use for MySQL?Apr 30, 2025 am 12:28 AM

MySQL backup policies include logical backup, physical backup, incremental backup, replication-based backup, and cloud backup. 1. Logical backup uses mysqldump to export database structure and data, which is suitable for small databases and version migrations. 2. Physical backups are fast and comprehensive by copying data files, but require database consistency. 3. Incremental backup uses binary logging to record changes, which is suitable for large databases. 4. Replication-based backup reduces the impact on the production system by backing up from the server. 5. Cloud backups such as AmazonRDS provide automation solutions, but costs and control need to be considered. When selecting a policy, database size, downtime tolerance, recovery time, and recovery point goals should be considered.

What is MySQL clustering?What is MySQL clustering?Apr 30, 2025 am 12:28 AM

MySQLclusteringenhancesdatabaserobustnessandscalabilitybydistributingdataacrossmultiplenodes.ItusestheNDBenginefordatareplicationandfaulttolerance,ensuringhighavailability.Setupinvolvesconfiguringmanagement,data,andSQLnodes,withcarefulmonitoringandpe

How do you optimize database schema design for performance in MySQL?How do you optimize database schema design for performance in MySQL?Apr 30, 2025 am 12:27 AM

Optimizing database schema design in MySQL can improve performance through the following steps: 1. Index optimization: Create indexes on common query columns, balancing the overhead of query and inserting updates. 2. Table structure optimization: Reduce data redundancy through normalization or anti-normalization and improve access efficiency. 3. Data type selection: Use appropriate data types, such as INT instead of VARCHAR, to reduce storage space. 4. Partitioning and sub-table: For large data volumes, use partitioning and sub-table to disperse data to improve query and maintenance efficiency.

How can you optimize MySQL performance?How can you optimize MySQL performance?Apr 30, 2025 am 12:26 AM

TooptimizeMySQLperformance,followthesesteps:1)Implementproperindexingtospeedupqueries,2)UseEXPLAINtoanalyzeandoptimizequeryperformance,3)Adjustserverconfigurationsettingslikeinnodb_buffer_pool_sizeandmax_connections,4)Usepartitioningforlargetablestoi

How to use MySQL functions for data processing and calculationHow to use MySQL functions for data processing and calculationApr 29, 2025 pm 04:21 PM

MySQL functions can be used for data processing and calculation. 1. Basic usage includes string processing, date calculation and mathematical operations. 2. Advanced usage involves combining multiple functions to implement complex operations. 3. Performance optimization requires avoiding the use of functions in the WHERE clause and using GROUPBY and temporary tables.

An efficient way to batch insert data in MySQLAn efficient way to batch insert data in MySQLApr 29, 2025 pm 04:18 PM

Efficient methods for batch inserting data in MySQL include: 1. Using INSERTINTO...VALUES syntax, 2. Using LOADDATAINFILE command, 3. Using transaction processing, 4. Adjust batch size, 5. Disable indexing, 6. Using INSERTIGNORE or INSERT...ONDUPLICATEKEYUPDATE, these methods can significantly improve database operation efficiency.

Steps to add and delete fields to MySQL tablesSteps to add and delete fields to MySQL tablesApr 29, 2025 pm 04:15 PM

In MySQL, add fields using ALTERTABLEtable_nameADDCOLUMNnew_columnVARCHAR(255)AFTERexisting_column, delete fields using ALTERTABLEtable_nameDROPCOLUMNcolumn_to_drop. When adding fields, you need to specify a location to optimize query performance and data structure; before deleting fields, you need to confirm that the operation is irreversible; modifying table structure using online DDL, backup data, test environment, and low-load time periods is performance optimization and best practice.

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

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

PhpStorm Mac version

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment

mPDF

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

EditPlus Chinese cracked version

EditPlus Chinese cracked version

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