Transaction sql là gì

  -  

In this article

Applies to:

*
Squốc lộ Server (all supported versions)
*
Azure Squốc lộ Database
*
Azure Squốc lộ Managed Instance
*
Azure Synapse Analytics
*
Analytics Platkhung System (PDW)

Marks the starting point of an explicit, local transaction. Explicit transactions start with the BEGIN TRANSACTION statement and over with the COMMIT or ROLLBACK statement.

Bạn đang xem: Transaction sql là gì

*
Transact-Squốc lộ Syntax Conventions

Syntax

--Applies khổng lồ Squốc lộ Server & Azure Squốc lộ Database BEGIN TRANSACTION <
tran_name_variable < WITH MARK < "description" > > > < ; > --Applies to Azure Synapse Analytics và Parallel Data Warehouse BEGIN TRANSACTION < ; >
Note

To view Transact-Squốc lộ syntax for SQL Server năm trước & earlier, see Previous versions documentation.


Arguments

transaction_nameAPPLIES TO: Squốc lộ Server (starting with 2008), Azure SQL Database

Is the name assigned lớn the transaction. transaction_name must consize to lớn the rules for identifiers, but identifiers longer than 32 characters are not allowed. Use transaction names only on the outermost pair of nested BEGIN...COMMIT or BEGIN...ROLLBACK statements. transaction_name is always case sensitive, even when the instance of SQL Server is not case sensitive sầu.


tran_name_variableAPPLIES TO: Squốc lộ Server (starting with 2008), Azure SQL Database

Is the name of a user-defined variable containing a valid transaction name. The variable must be declared with a char, varchar, nchar, or nvarchar data type. If more than 32 characters are passed to the variable, only the first 32 characters will be used; the remaining characters will be truncated.

WITH MARK < "description" >APPLIES TO: SQL Server (starting with 2008), Azure Squốc lộ Database

Specifies that the transaction is marked in the log. description is a string that describes the mark. A description longer than 128 characters is truncated to lớn 128 characters before being stored in the msdb.dbo.logmarkhistory table.

If WITH MARK is used, a transaction name must be specified. WITH MARK allows for restoring a transaction log lớn a named mark.

General Remarks

BEGIN TRANSACTION increments
TRANCOUNT by 1.

BEGIN TRANSACTION represents a point at which the data referenced by a connection is logically & physically consistent. If errors are encountered, all data modifications made after the BEGIN TRANSACTION can be rolled back lớn return the data lớn this known state of consistency. Each transaction lasts until either it completes without errors & COMMIT TRANSACTION is issued lớn make the modifications a permanent part of the database, or errors are encountered và all modifications are erased with a ROLLBACK TRANSACTION statement.

BEGIN TRANSACTION starts a local transaction for the connection issuing the statement. Depending on the current transaction isolation màn chơi settings, many resources acquired khổng lồ support the Transact-SQL statements issued by the connection are locked by the transaction until it is completed with either a COMMIT TRANSACTION or ROLLBACK TRANSACTION statement. Transactions left outstanding for long periods of time can prsự kiện other users from accessing these locked resources, và also can prevent log truncation.

Although BEGIN TRANSACTION starts a local transaction, it is not recorded in the transaction log until the application subsequently performs an action that must be recorded in the log, such as executing an INSERT, UPDATE, or DELETE statement. An application can perkhung actions such as acquiring locks to lớn protect the transaction isolation level of SELECT statements, but nothing is recorded in the log until the application performs a modification action.

Naming multiple transactions in a series of nested transactions with a transaction name has little effect on the transaction. Only the first (outermost) transaction name is registered with the system. A rollbachồng khổng lồ any other name (other than a valid savepoint name) generates an error. None of the statements executed before the rollbaông xã is, in fact, rolled baông xã at the time this error occurs. The statements are rolled baông chồng only when the outer transaction is rolled bachồng.

Xem thêm: Phim Bluray Là Gì ? Có Nên Sử Dụng Đĩa Bluray

The local transaction started by the BEGIN TRANSACTION statement is escalated to a distributed transaction if the following actions are performed before the statement is committed or rolled back:

An INSERT, DELETE, or UPDATE statement that references a remote table on a linked server is executed. The INSERT, UPDATE, or DELETE statement fails if the OLE DB provider used khổng lồ access the linked VPS does not support the ITransactionJoin interface.

A Call is made to a remote stored procedure when the REMOTE_PROC_TRANSACTIONS option is set to ON.

The local copy of Squốc lộ Server becomes the transaction controller và uses issf.vn Distributed Transaction Coordinator (MS DTC) khổng lồ manage the distributed transaction.

A transaction can be explicitly executed as a distributed transaction by using BEGIN DISTRIBUTED TRANSACTION. For more information, see BEGIN DISTRIBUTED TRANSACTION (Transact-SQL).

When SET IMPLICIT_TRANSACTIONS is mix khổng lồ ON, a BEGIN TRANSACTION statement creates two nested transactions. For more information see, SET IMPLICIT_TRANSACTIONS (Transact-SQL)

Marked Transactions

The WITH MARK option causes the transaction name khổng lồ be placed in the transaction log. When restoring a database khổng lồ an earlier state, the marked transaction can be used in place of a date and time. For more information, see Use Marked Transactions lớn Recover Related Databases Consistently (Full Recovery Model) and RESTORE (Transact-SQL).

Additionally, transaction log marks are necessary if you need to lớn recover a phối of related databases lớn a logically consistent state. Marks can be placed in the transaction logs of the related databases by a distributed transaction. Recovering the mix of related databases to lớn these marks results in a phối of databases that are transactionally consistent. Placement of marks in related databases requires special procedures.

The mark is placed in the transaction log only if the database is updated by the marked transaction. Transactions that do not modify data are not marked.

BEGIN TRAN new_name WITH MARK can be nested within an already existing transaction that is not marked. Upon doing so, new_name becomes the mark name for the transaction, despite the name that the transaction may already have sầu been given. In the following example, M2 is the name of the mark.

BEGIN TRAN T1; UPDATE table1 ...; BEGIN TRAN M2 WITH MARK; UPDATE table2 ...; SELECT * from table1; COMMIT TRAN M2; UPDATE table3 ...; COMMIT TRAN T1; When nesting transactions, trying lớn mark a transaction that is already marked results in a warning (not error) message:

"BEGIN TRAN T1 WITH MARK ...;"

"UPDATE table1 ...;"

"BEGIN TRAN M2 WITH MARK ...;"

"Server: Msg 3920, Level 16, State 1, Line 3"

"WITH MARK option only applies khổng lồ the first BEGIN TRAN WITH MARK."

"The option is ignored."

Permissions

Requires membership in the public role.

Examples

A. Using an explicit transaction

APPLIES TO: SQL Server (starting with 2008), Azure SQL Database, Azure Synapse Analytics, Parallel Data Warehouse

This example uses AdventureWorks.

Xem thêm: Hướng Dẫn Một Số Cách Chơi Vé Số Kiểu Mỹ, Cách Chơi Vé Số Mỹ

BEGIN TRANSACTION; DELETE FROM HumanResources.JobCandidate WHERE JobCandidateID = 13; COMMIT;

B. Rolling baông chồng a transaction

APPLIES TO: Squốc lộ Server (starting with 2008), Azure Squốc lộ Database, Azure Synapse Analytics, Parallel Data Warehouse

The following example shows the effect of rolling bachồng a transaction. In this example, the ROLLBACK statement will roll baông xã the INSERT statement, but the created table will still exist.

CREATE TABLE ValueTable (id INT); BEGIN TRANSACTION; INSERT INTO ValueTable VALUES(1); INSERT INTO ValueTable VALUES(2); ROLLBACK;

C. Naming a transaction

APPLIES TO: SQL Server (starting with 2008), Azure SQL Database