Transaction processing and concurrency control for databases

Transaction processing and concurrency control for databases

transactional and concurrency control of database transactions and concurrency controls the transactional and concurrent control transactions for the database are a logical unit of work, and SQL Server 2005 provides several automated mechanisms that can be programmed to complete, including transaction logs, SQL transaction control statements, and the mechanism by which locks guarantee data integrity during transaction processing. Locks are required to ensure transactional integrity and database consistency when users are concurrently accessing the database. Transactions and locks are two closely related concepts. Through the use of transactions, batches, and locks, you can also monitor the system and optimize the physical database. A job is a multi-step task. This chapter focuses on the basic concepts of transactions and locks for SQL Server 2005 database systems, the creation and use of transactions, batches, locks, and the operation of monitoring systems through transactions, batches, locks, and optimization of physical databases, as well as job settings. 12.1 The basic concepts of things and the basic concepts of things and the basic concepts of things and the basic concepts of Things and SQL Server 2005 things handling things deal with things like processing transactions and stored procedures are similar, consisting of a series of T-SQL statements, which is the execution unit of the system of the SQL Server 2005. This section focuses on the concepts of transactions in SQL Server 2005, as well as the creation and use of transactions. 12.1.1 Transaction Overview Transaction Overview Transaction Overview Transaction Overview relational databases have 4 distinct characteristics: security, integrity, detection, and concurrency. The security of database is to ensure the security of database data, to prevent unauthorized users from arbitrarily modifying the data in the database to ensure the security of data. Integrity is an important feature of database, and it is also an important mechanism to ensure data in database is effective, prevent mistakes and realize business rules. In the database, the difference between the stored data is useless garbage or valuable information, mainly based on the integrity of the database, that is, entity integrity, domain integrity and referential integrity. Take realistic strategies, solve problems and improve the performance of any factors and bottlenecks that affect system performance. Concurrency is a problem that is used to solve multiple users working on the same data. Especially for the network database, this feature is more prominent. To improve the processing speed of the database, it is not enough to rely on improving the physical speed of the computer, and it is necessary to fully consider the concurrency of the database and improve the efficiency of database concurrency. So how do you ensure concurrency? In SQL Server 2005, the concurrency of the database can be resolved by using transaction and lock mechanisms. In SQL Server 2005, transaction requirements must be processed with the acid principle, atomicity (a), consistency (c), Isolation (i), and persistence (d). Atomicity: Atomicity, also known as automation, means that a transaction must perform a complete work, or perform all the dataDo, or not all.   Consistency: Consistency means that all data must have a consistent state when the transaction is complete. Isolation isolation: Also known as independence, the modification of a parallel transaction must be independent of the modifications of other parallel transactions. A transactional data is either a state before another transaction executes, or a state after the execution of another transaction, but it cannot process other data that is being processed. Persistent Persistence: When a transaction is complete, the effect is permanently stored in the system, that is, the operation of the transaction is written to the database. This mechanism of a transaction guarantees that a transaction is executed successfully after a commit, or fails to roll back after committing, caricatures, so that the transaction's modification of the data is recoverable, that is, when the transaction fails, its modification to the data reverts to the state it was before the transaction was executed. With a generic batch, there is a possibility that some statements will be executed, while others will not be executed, which may result in inconsistent data. After the transaction begins, the operations of the firm are written to the transaction log in succession. These task actions record a flag in the transaction log that indicates that the operation was performed, and when the transaction is canceled, the system automatically performs an inverse operation of the operation to ensure system consistency. The system automatically generates a checkpoint mechanism, which occurs periodically. The period of the checkpoint is the time interval that the system automatically calculates based on the user-defined time interval and the frequency of system activity. The checkpoint periodically checks the transaction log, and if the transaction is complete in the transaction log, the checkpoint commits the transactions to the database, and a checkpoint commit token is made in the transaction log. If the transaction is not completed in the transaction log, the checkpoint in the transaction log is not committed to the database, and a checkpoint is not committed in the transaction log. Type transactions of type transactions of 12.1.2 transaction types depending on the setting and purpose of the transaction, SQL Server 2005 divides the transaction into multiple types. 1. Classification according to the system settings according to the system settings classification according to the system settings classification according to the system settings, SQL Server 2005 divides transactions into two types: system-provided transactions and user-defined transactions, which are referred to as system transactions and user-defined transactions, respectively. (1) A transaction provided by the system transaction system means that when some statements are executed, a statement is a transaction. But to be clear, the object of a statement can be either a row of data in a table, or a multiline of data in a table, or even all the data in a table. Therefore, a transaction consisting of only one statement may also contain the processing of multiple rows of data. The   system provides the following transaction statements: ALTER TABLE, CREATE, DELETE, DROP, FETCH, GRANT, INSERT, OPEN, Reboke, SELECT, UPDATE,TRUNCATE TABLE These statements themselves constitute a transaction. Example 12-1 creates a table using the CREATE table.       CREATE TABLE student             (ID CHAR),       &NB Sp     Name Char (6),             sex char (2)           &NBSP ; Description: This statement itself constitutes a transaction. Since this statement does not use conditional restrictions, this statement creates a table with 3 columns. Either create all the successes or all fail. (2) User-defined transactions in practical applications, most transactions are handled with user-defined transactions. When developing an application, you can use the BEGIN TRANSACTION statement to define explicit user-defined transactions. When using user-defined transactions, it is important to note that a transaction must have an explicit end statement to end. If you do not end with an explicit end statement, the system may treat all operations from the beginning of the transaction to the user closing the connection as a transaction. A clear end of a transaction can use one of two statements: a COMMIT statement and a rollback statement. A COMMIT statement is a commit statement that explicitly commits all completed statements to the database. A rollback statement is a cancellation statement that cancels all the operations of a transaction, which means that the transaction operation failed. There is also a special user-defined transaction, which is a distributed transaction. Example 12-1 a transaction is an operation on a server with guaranteed data integrity and consistency that refers to integrity and consistency on a server. However, if you have more than one server in a complex environment, you must define a distributed transaction in order to ensure the integrity and consistency of transactions in multiple server environments. In this distributed transaction, all operations can involve operations on multiple servers, and when these operations succeed, all of these actions are committed to the database of the appropriate server, and if one of these operations fails, all operations in the distributed transaction will be canceled. 2. According to the running mode classification according to the operation mode classification according to the running mode classification according to the running mode, SQL Server 2005 divides the transaction into 4 types: autocommit transaction, display transaction, implicit transaction and batch level transaction. (1) Automatic submission of transactions &nbsp

Transaction processing and concurrency control for databases