Transaction processing in Sqlserver2000

Sqlserver2000 transaction processing in server|sqlserver| transaction processing   I.        Definition and its nature: transaction: A series of actions performed as a single logical unit of work. Attribute: A logical unit of work must have four properties, called the ACID (atomicity, consistency, isolation, and durability) attribute, which is the only way to become a transaction:1.      atomic: A transaction must be an atomic unit of work , either all or none of its data modifications are performed. 2.      consistency: When a transaction completes, it must keep all data in a consistent state. In the related database, all rules must be applied to the modification of the transaction to preserve the integrity of all data. When a transaction ends, all internal data structures, such as a B-tree index or a two-way list, must be correct. 3.      Isolation: Modifications made by concurrent transactions must be isolated from modifications made by any other concurrent transaction. The state that the data is in when the transaction views the data, either when another concurrent transaction modifies its previous state, or when another transaction modifies its state, the transaction does not view the data in the middle state. This is called serializable because it can reload the starting data and replay a series of transactions so that the state of the data at the end is the same as that of the original transaction. 4.      persistence: After a transaction is completed, its impact on the system is permanent. This modification is maintained even if a system failure occurs. Specifying and enforcing transaction processing SQL programmers are responsible for starting and ending transactions while enforcing the logical consistency of data. Programmers must define the order in which data is modified, keeping the data consistent with the business rules of its organization. The programmer then includes these modification statements in a transaction so that Microsoft? SQL Server? The ability to enforce physical integrity of the transaction. Enterprise database systems, such as SQL Server, have a responsibility to provide a mechanism to ensure the integrity of each transaction physical. SQL Server provides: Locks devices to isolate transactions from each other. Record the equipment to ensure the durability of the transaction. Even if the server hardware, operating system, or SQL Server itself fails, SQL Server can also use the transaction log on reboot to automatically roll back all outstanding transactions to the location where the system fails. Transaction management features that enforce the atomicity and consistency of transactions. After the transaction is started, it must be completed successfully, otherwise the SQL SerVer will undo any changes that were made to the data after the transaction was started. Iii. Control Services:
The application controls transactions primarily by specifying when the transaction starts and ends. This can be used with Transact-SQL statements or database API functions. The system must also be able to properly handle errors that terminate the transaction before the transaction completes. Transactions are managed at the connection level. When a transaction is started on a connection, all Transact-SQL statements executed on that connection are part of the transaction before the transaction ends.
1, start the transaction
In Microsoft®sql Server™, you can start a transaction in an explicit autocommit or implicit mode.

1 Explicit transactions: explicitly starting a transaction by issuing a BEGIN TRANSACTION statement.

2 autocommit transaction: This is the default mode for SQL Server. Each individual Transact-SQL statement is committed after it is completed. You do not have to specify any statement control transactions.

3 Implicit transaction: Sets the implicit transaction mode to open through an API function or Transact-SQL SET IMPLICIT_TRANSACTIONS on statement. The next statement automatically starts a new transaction. When the transaction completes, the next Transact-SQL statement starts a new transaction.

4 The connection mode is managed at the connection layer. If a connection changes from one transaction pattern to another, it has no effect on any other connected transaction modes.
2. End of Business
You can use a COMMIT or ROLLBACK statement to end a transaction.

1 Commit: If the transaction succeeds, commit. A COMMIT statement guarantees that all modifications of a transaction are permanently valid in the database. A COMMIT statement also frees resources, such as locks used by transactions.

2) ROLLBACK: If an error occurs in a transaction, or if the user decides to cancel the transaction, the transaction can be rolled back. The ROLLBACK statement restores all modifications made in the transaction by returning the data to its state at the start of the transaction. ROLLBACK also frees resources that are occupied by transactions.
3. Specify transaction boundary
You can use Transact-SQL statements or API functions and methods to determine when SQL Server transactions start and end.

1 Transact-SQL statements: Using BEGIN TRANSACTION, commit TRANSACTION, commit WORK, ROLLBACK TRANSACTION, ROLLBACK WORK, and SET impli Cit_transactions statement to describe a transaction. These statements are used primarily in db-library applications and Transact-SQL scripts, such as scripts that are run using the osql command prompt utility.

2 API functions and methods: Database APIs (such as ODBC, OLE DB, and ADO) contain functions and methods used to describe transactions. They are the primary mechanism for controlling transactions in SQL Server applications.

3 each transaction must be managed by only one of these methods. The use of two methods in the same transaction can result in an indeterminate result. For example, you should not start a transaction with an ODBC API function, and then use a Transact-SQL COMMIT statement to complete the transaction. This will not notify the SQL Server ODBC driver that the transaction has been committed. In this case, you should use the ODBC SQLEndTran function to end the transaction.
4, the transaction process of errors
1 if a server error fails the transaction to complete successfully, SQL Server automatically rolls back the transaction and frees all resources that the transaction occupies. If a client's network connection to SQL Server is interrupted, all outstanding transactions for that connection are rolled back when the network notifies SQL Server of the interruption. If the client application fails or the client computer crashes or restarts, the connection is interrupted and all outstanding connections are rolled back when the network notifies SQL Server of the interruption. If the customer logs off from the application, all outstanding transactions are also rolled back.

2 If a run-time statement error (such as a constraint violation) occurs in the batch, the default behavior in SQL Server will be to roll back only the statement that generated the error. You can use the SET xact_abort statement to change the behavior. After the SET xact_abort on statement executes, any run-time statement errors cause the current transaction to be automatically rolled back. Compilation errors, such as syntax errors, are not affected by SET Xact_abort.

3 If a run-time error or compilation error occurs, the programmer should write the application code to specify the correct action (COMMIT or ROLLBACK).
Reference <<sqlserver2000 Help file > >