Brief description
The relational database has four significant features, namely security, integrity, concurrency, and monitoring. The security of the database is to ensure the security of the data in the database, prevent unauthorized users from inviting data from the database to ensure the security of the data. In most database management systems, it is primarily to ensure the security of the database by license. Integrity is an important feature of the database, and it is also an important mechanism that guarantees data in the database to effectively and prevent errors and implement business rules. In the database, distinguishing the data stored is useless garbage or valuable information, mainly based on whether the integrity of the database is perfect. In SQL Server 7.0, the integrity of data is guaranteed through a range of logic, which is divided into three aspects, namely solid integrity, domain integrity, and reference integrity. It can be said to any system, no monitoring, no optimization. This sentence is used in terms of database management system. Only by performing comprehensive performance monitoring of the database, it can also find the factors and bottlenecks that affect system performance and bottlenecks can be taken to adhere to the actual strategy, solve the problem, and improve the performance of the system. Conaciation is also a very important concept, which is used to solve the problem of multiple users to operate the same data. Especially for the network database, this feature is more prominent. Improve the processing speed of the database, single relying on increasing the physical speed of the computer is not enough, and must fully consider the concurrency problem of the database, improve the efficiency of database concurrency. So how do you guarantee concurrency? In this face of the database product SQL Server 7.0, by using transaction and lock mechanisms, the concurrency problem of the database is solved.
Concept and characteristics
Transactions and locks are two close contact concepts. The transaction is a unit's work, including a series of operations or all success, or all failed. Transaction ensures that modifications of multiple data are handled as a unit. For example, in the banking business, there is a principle of accounting, that is, there is a loan, borrowing, etc. So in order to ensure this principle, you have to ensure that the registration of loans is either successful, or it will fail. If there is only a borrow, or only the loan is recorded, then the wrong account will appear in violation of the principle of accounting. SQL Server manages multiple transactions by supporting transaction mechanisms to ensure consistency of transactions. Transaction is locked to prevent other users from modifying the data in a transaction that has not been completed yet. For multi-user systems, the lock mechanism is necessary. In SQL Server 7.0, use transaction logs to ensure modified integrity and recoverability.
SQL Server has a variety of locks, allowing transactions to lock different resources. The lock is to protect the specified resource, not being operated by other transactions. In order to minimize the cost of lock, SQL Server automatically locks the resource object with a lock corresponding to the task. Lock is relatively small object, such as locking lines, although it can improve concurrency, but there is a high expenditure, because if you lock a lot, then you need to have more locks. Locking relatively large objects, such as locking tables, greatly reduces concurrency, because locking the entire table limits other parts of other transactions to access the table, but cost expenditure is relatively low because only less maintenance is relatively small.
Transactions and locks have the following features:
The transaction is a unit's work, either do, or all do not do business assurance, consistency and recovery Every transaction-SQL statement can be a transaction actual transaction is a user-defined transaction, which includes a series of Operations or statements In multi-server environments, use user-defined distributed transactions, ensuring that the operation of the consistency lock is a resource that guarantees concurrent control, including rows, pages, clusters, tables, and database locks, mainly including shared locks. And row lock special type of lock includes intent lock, modify the lock and mode lock shared lock Allow other transactions to continue to use the lock-locked resource row lock only allows a transaction access data system itself can handle deadlock users can customize lock according to the actual situation Some feature affairs
Definition of a transaction
The transaction refers to the work of a unit, these work is done, or all do not do it. As a logical unit, you must have four properties: automatic sex, consistency, independence, and persistence. Automaticity means that the transaction must be an automatic unit work, either execute all data modifications, or all data modifications are not executed. Consistency means that all data must have a consistent state when the transaction is completed. In the relational database, all rules must be applied to transactions to maintain all data integrity. All internal data structures, such as links between tree indexes and data, must be guaranteed correctly after the end of the transaction. Independence refers to the modification of parallel transactions must be independent of other parallel transactions. The data seen by a transaction is either another transaction to modify the state before these transactions, or the second transaction has modified the completed data, but this transaction cannot see the data being modified. This feature is also known as serialization. Persistence is to refer to the completion of a transaction, its influence is in the system, that is, this modification is written to the database.
The transaction mechanism guarantees that the modification of a set of data is all executed, or all will not be executed. SQL Server uses transaction to ensure the consistency of data and ensures recoverability when the system fails. The transaction is a work that can be recovered, consists of one or more Transact-SQL statements, which can affect a row or multi-line data in the table. After the transaction is opened until the transaction is successfully completed, or if the transaction failure is canceled or rolled back.
