事务(Transaction)是并发控制的单位,是用户定义的一个操作序列。这些操作要么都做,要么都不做,是一个不可分割的工作单位。通过事务,SQL Server能将逻辑相关的一组操作绑定在一起,以便服务器保持数据的完整性
当对多个表进行更新的时候,某条执行失败。为了保持数据的完整性,需要使用事务回滚。
显示设置事务
代码如下 | 复制代码 |
begin try begin transaction insert into shiwu (asd) values ('aasdasda'); commit transaction end try begin catch select ERROR_NUMBER() as errornumber rollback transaction end catch |
隐式设置事务
代码如下 | 复制代码 |
set implicit_transactions on; -- 启动隐式事务 |
显示事务以下语句不能使用,隐式事务可以
代码如下 | 复制代码 |
alter database; |
显示事务可以嵌套使用
代码如下 | 复制代码 |
--创建存储过程 create procedure qiantaoProc @asd nchar(10) as begin begin try begin transaction innerTrans save transaction savepoint --创建事务保存点 insert into shiwu (asd) values (@asd); commit transaction innerTrans end try begin catch rollback transaction savepoint --回滚到保存点 commit transaction innerTrans end catch end go begin transaction outrans exec qiantaoProc 'asdasd'; rollback transaction outrans |
事务嵌套,回滚外层事务时,如果嵌套内的事务已经回滚过则会有异常。此时需要使用事务保存点。如下实例
SQL事务回滚
指定当 Transact-SQL 语句产生运行时错误时,Microsoft® SQL Server™ 是否自动回滚当前事务
方案一:
代码如下 | 复制代码 |
SET XACT_ABORT ON--如果产生错误自动回滚 GO BEGIN TRAN INSERT INTO A VALUES (4) INSERT INTO B VALUES (5) COMMIT TRAN |
也可以使用_ConnectionPtr 对象的方法: BeginTrans、CommitTrans、RollbackTrans,使用该系列函数判断并回滚。一旦调用了 BeginTrans 方法, 在调用 CommitTrans 或 RollbackTrans 结束事务之前, 将不再立即提交所作的任何更改。
方案二
代码如下 | 复制代码 |
BEGIN TRANSACTION INSERT INTO A values (4) ----- 该表含有触发器,UPDATE其他表 IF @@error 0 --发生错误 BEGIN ROLLBACK TRANSACTION END ELSE BEGIN COMMIT TRANSACTION END |
sql事务结合asp.net两种用法
在sql server+ .net 开发环境下,有两种方法能够完成事务的操作,保持数据库的数据完整性;一个就是用/42850.htm target=_blank >sql存储过程,另一个就是在ADO.NET中一种简单的事务处理;现在通过一个典型的银行转账的例子来说明一下这两个例子的用法我们先来看看sql存储过程是如何来完成事务的操作的:首先创建一个表:
代码如下 | 复制代码 |
create database aaaa --创建一个表,包含用户的帐号和钱数gouse aaaacreate table bb( ID int not null primary key, --帐号 moneys money --转账金额)insert into bb values ('1','2000') --插入两条数据insert into bb values ('2','3000')用这个表创建一个存储过程: @toID int, --接收转账的账户 @fromID int , --转出自己的账户 @momeys money --转账的金额 as begin tran --开始执行事务
update bb set moneys=moneys-@momeys where ID=@fromID -执行的第一个操作,转账出钱,减去转出的金额 update bb set moneys=moneys+@momeys where ID=@toID --执行第二个操作,接受转账的金额,增加
if @@error0 --判断如果两条语句有任何一条出现错误 begin rollback tran –开始执行事务的回滚,恢复的转账开始之前状态 return 0 end go
else --如何两条都执行成功 begin commit tran 执行这个事务的操作 return 1 end go |
接下来看看.net 是如何调用这个存储过程的:
代码如下 | 复制代码 |
protected void Button1_Click(object sender, EventArgs e) { SqlConnection con =new SqlConnection(@"Data Source=.SQLEXPRESS;database=aaaa;uid=sa;pwd=jcx"); //连接字符串 SqlCommand cmd = new SqlCommand("mon",con); //调用存储过程 cmd.CommandType = CommandType.StoredProcedure; con.Open(); SqlParameter prar = new SqlParameter();//传递参数 cmd.Parameters.AddWithValue("@fromID", 1); cmd.Parameters.AddWithValue("@toID", 2); cmd.Parameters.AddWithValue("@momeys",Convert.ToInt32( 1.Text) );
cmd.Parameters.Add("@return", "").Direction = ParameterDirection.ReturnValue;//获取存储过程的返回值 cmd.ExecuteNonQuery(); string value = cmd.Parameters["@return"].Value.ToString();//把返回值赋值给value if (value == "1") { Label1.Text = "添加成功"; } else { Label1.Text = "添加失败"; } } |
这个也就是在存储过程里添加事务,再来看看不在数据库写sql存储过程,ADO.NET是如何处理事务的:
代码如下 | 复制代码 |
protected void Button2_Click(object sender, EventArgs e) { SqlConnection con = new SqlConnection(@"Data Source=.SQLEXPRESS;database=aaaa;uid=sa;pwd=jcx"); con.Open(); SqlTransaction tran = con.BeginTransaction();//先实例SqlTransaction类,使用这个事务使用的是con 这个连接,使用BeginTransaction这个方法来开始执行这个事务 SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.Transaction = tran; try { //在try{} 块里执行sqlcommand命令, cmd.CommandText = "update bb set moneys=moneys-'" + Convert.ToInt32(TextBox1.Text) + "' where ID='1'"; cmd.ExecuteNonQuery(); cmd.CommandText = "update bb set moneys=moneys+' aa ' where ID='2'"; cmd.ExecuteNonQuery(); tran.Commit();//如果两个sql命令都执行成功,则执行commit这个方法,执行这些操作
Label1.Text = "添加成功"; } catch { Label1.Text = "添加失败"; tran.Rollback();//如何执行不成功,发生异常,则执行rollback方法,回滚到事务操作开始之前; }
} |
这就是两个事务不同用法的简单例子,ADO.NET 事务处理的方法看起来比较简单,但是他要使用同一个连接来执行这些操作,要是同时使用几个数据库来用一个事务执行,这样就比较繁琐,但是要是用sql存储过程,这样就相对比较简单

InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

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

Hot Article

Hot Tools

MantisBT
Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

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),

Dreamweaver CS6
Visual web development tools

DVWA
Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

ZendStudio 13.5.1 Mac
Powerful PHP integrated development environment