Shulou(Shulou.com)05/31 Report--

This article introduces the introduction of MySQL transactions and example analysis, the content is very detailed, interested friends can refer to, hope to be helpful to you.

1. What is a transaction: a transaction is an indivisible working logic unit, and it is used as the smallest control unit when performing concurrent operations on a database system. All the database operation commands he contains are submitted or undone to the department as a whole, and this set of database operation commands are either executed or not executed. two。 Transaction statement begins thing: BEGIN TRANSACTION commit thing: COMMIT TRANSACTION rollback transaction: ROLLBACK TRANSACTION 3. ① atomicity (Atomicity) of four attributes of a transaction: all elements in the transaction are committed or rolled back as a whole, the elements of the transaction are inseparable, and the transaction is a complete operation. ② consistency (Consistemcy): when a thing is completed, the data must be consistent, that is, the data in the data store must be consistent with the data in the data store before the thing begins. Ensure that the data is intact. ③ isolation (Isolation): multiple transactions that modify data are isolated from each other. This indicates that transactions must be independent and should not originate from or affect other transactions in any way. ④ persistence (Durability): after the transaction is completed, its impact on the system is permanent, and the change will be retained even in the event of a system failure, actually modifying database 4. 5. SavePoint for transaction SAVE TRANSACTION SavePoint name-Custom SavePoint name and location ROLLBACK TRANSACTION SavePoint name-rollback to Custom SavePoint-example-BEGIN TRANSACTION-- Start transaction DECLARE @ errorSun INT-define error counter SET @ errorSun=0-yes, 0 UPDATE a SET id=232 WHERE error 1-transaction operation SQL statement SET @ errorSun=@errorSun+@@ERROR-accumulate UPDATE aa SET id=2 WHERE errors 1-transaction operation SQL statement SET @ errorSun=@errorSun+@@ERROR-accumulate errors IF @ errorSun0 BEGIN PRINT 'have errors Rollback of 'ROLLBACK TRANSACTION-- transaction rollback statement END ELSE BEGIN PRINT' succeeded Commit 'COMMIT TRANSACTION-- transaction commit statement END example: create a stored procedure that inserts data replication code into two tables at the same time as follows: Create proc RegisterUser (@ usrName varchar (30), @ usrPasswd varchar (30), @ age int,@sex varchar (10), @ PhoneNum varchar (20), @ Address varchar (50)) as begin begin tran insert into userinfo (userName,userPasswd) values (@ usrName,@usrPasswd) if @ @ error0 begin rollback tran return-1 end insert into userdoc (userName) Age,sex,PhoneNumber,Address) values (@ Usrname,@age,@sex,@PhoneNum,@Address) if @ @ error0 begin rollback tran return-1 end commit tran return 0 end

According to the way transactions are started and executed, transactions can be divided into three categories: display transactions are also called user-defined or user-specified transactions, that is, transactions that start and end can be explicitly defined. Distributed transactions belong to the default transaction management mode of displaying transaction auto-commit transactions. If a statement completes successfully, the statement is committed; if an error is encountered, the statement is rolled back. Implicit transactions when the connection operates in this mode, sql automatically starts a new transaction after the current transaction is committed or rolled back. There is no need to describe the beginning of the transaction, just commit or roll back each transaction. It generates a continuous chain of transactions. First, show that the transaction is completed through statements such as begin transacton, commit transaction, commit work, rollback transaction or rollback work. 1. Start transaction format: begin tran transaction name or variable with mark description 2, end transaction format: commit tran transaction name or variable (transaction name is the same as transaction name in begin tran or commit work but there is no parameter 3, rollback transaction rollback tran transaction name or variable | savepoint_name | savepoint_variable or rollback work description: clear all data modifications made from the beginning of the transaction or to a SavePoint 4, Set SavePoint format within a transaction: save tran savepoint_name | savepoint_variable example: copy code as follows: use bookdb go begin tran mytran insert into book values (9) "windows2000',1,22,' Press') save tran mysave delete book where book_id=9 rollback tran mysave commit tran go select * from book go

As you can see, after the above statement is executed, a record is inserted into the book and not deleted. Because the operation is rolled back to the save point before deletion using the rollback tran mysave statement. 5. Marked transaction format: with mark example: a statement that uses a database token to restore the log to a predefined point in time places a mark in the transaction log. Note that the marked transaction must commit at least one update to mark the log. BEGIN TRAN MyMark WITH MARK UPDATE pubs.dbo.LastLogMark SET MarkTime = GETDATE () COMMIT TRAN MyMark backs up the transaction log in the way you usually do. BACKUP LOG pubs TO DISK='C:\ Backups\ Fullbackup.bak' WITH INIT you can now restore the database to the log marker point. First restore the database and prepare it for log recovery. RESTORE DATABASE pubs FROM DISK=N'C:\ Backups\ Fullbackup.bak' WITH NORECOVERY now restores the log to the point in time that contains the mark and makes it available for use. Note that STOPAT forbids execution when the database is performing bulk logging. RESTORE LOG pubs FROM DISK=N'C:\ Backups\ Logbackup.bak' WITH RECOVERY STOPAT='02/11/2002 17 STOPAT='02/11/2002 35 drop database 00' 5, operations that cannot be used for transactions create database create database modify database alter database delete database drop database restore database restore database load database load database backup log files backup log recovery log files restore log update statistics update statitics authorized operations grant replication transaction logs dump tran disk initialization disk init update system configuration reconfigure II, Auto-commit transaction sql connection starts an explicit transaction in a begin tran statement Or the implicit transaction mode is set to open, the operation will be performed in autocommit mode. When an explicit transaction is committed or rolled back, or the implicit transaction mode is turned off, it returns to autocommit mode. Example: due to a compilation error, none of the three insert executes the copy code as follows: use test go create table testback (cola int primary key, colb char (3)) go insert into testback values (1Magnum aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

No result returned 3. Implicit transaction is set to on by API function or Transact-SQL SET IMPLICIT_TRANSACTIONS ON statement. The next statement automatically starts a new transaction. When the transaction is completed, the next Transact-SQL statement will start a new transaction. When a large number of DDL and DML commands are executed, they automatically start and remain until the user explicitly commits. Switching implicit transactions can use SET IMPLICIT_TRANSACTIONS to set the implicit transaction mode for connections. When set to ON, SET IMPLICIT_TRANSACTIONS sets the connection to implicit transaction mode. When set to OFF, return the connection to autocommit transaction mode statements including: alter table insert open create delete revoke drop select fetch truncate table grant update example: explicit and implicit transactions are used below. It uses the @ @ tracount function to demonstrate open and closed transactions: the copy code is as follows: use test go set nocount on create table T1 (an int) go insert into T1 values (1) go print 'use explicit transactions' begin tran insert into T1 values (2) print 'transactions:' + cast (@ @ trancount as char (5)) commint tran print 'transactions outside transactions:' + cast (@ @ trancount as char (5)) Go print go set implicit_transactions on go print' uses implicit transactions'go insert into T1 values*4) number of transactions within print' transactions:'+ cast (@ @ trancount as char (5)) transactions outside commint tran print' transactions:'+ cast (@ @ trancount as char (5)) go

Execution result: use to show the number of transactions within the transaction: 2 the number of transactions outside the transaction: 1 the number of transactions within the transaction using implicit transactions: 1 the number of transactions outside the transaction: 0 IV, distributed transactions across two or more databases in a single sql server transaction is a distributed transaction. Different from local transactions: must be managed by a transaction manager to avoid a network failure that causes a transaction to be successfully committed by some resource managers but rolled back by other resource managers. Sql server can be supported by DTc microsoft distributed transaction coordinator to handle distributed transactions. You can use the BEgin distributed transaction command to start a distributed transaction in two phases: a preparation phase B commit phase execution tutorial: 1, sql script or application connection execution sql statement to start a distributed transaction 2, the sql that executes the statement executes a distributed query for the master server in the transaction 3, the script or application executes the linked server Or execute a remote stored procedure on a remote server. 4. When the distributed query or remote procedure call is executed, the master server will automatically call msdtc to register the linked server and the remote server in the distributed transaction. 5. When the script or application issues commit or rollback statements, the master sql will call msdtc to manage the two-phase commit process, or notify the linked server and the remote server to roll back its transactions.

An example of a mysql transaction

The copy code is as follows: begin tran declare @ rownum1 int-- number of vacant rooms without order added declare @ rownum2 int-number of vacant rooms added to order declare @ BookID1 int set @ BookID1=0 insert into T_BookRoomInfo (RoomID,CustomerName,CustomerCardID,Discount, EnterTime,DepositMoney,Memo,UserID,UpdTime) values (@ RoomID,@CustomerName,@CustomerCardID,@Discount, getdate (), @ DepositMoney,@Memo,@UserID Getdate () select @ BookID1=@@IDENTITY if (@ BookID10) begin select @ rownum1=count (1) from T_Room where IsEmploy=0 update T_Room set IsEmploy=1 where RoomID=@RoomID select @ rownum1=count (1) from T_Room where IsEmploy=0

If (@ rownum1)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.