The concept
of 11.5.1 Lock
Lock (Lock) is a restriction of access to resources in a multiuser environment. Mechanism when a data source is locked, the data source has a certain access limit. We call this data source "locked". In SQL Server, you can lock on the following objects:
- Data row (ROW): a single row of data in a data page;
- Index row (key): a single row of data in an index page, which is the key value of an index;
- Page: A page is the basic unit of SQL Server Access data, which is 8KB in size;
- Disk Area (Extent): A panel consisting of 8 consecutive pages;
- Tables (table);
- Database.
Category of
11.5.2 locks
in SQL Server, there are two ways to classify locks.
(1) from the point of view of the database system
locks are divided into the following three types:
- Exclusive lock (Exclusive lock)
A resource with exclusive lock locks is allowed only by programs that are locked, and no other action is accepted. When you perform a data Update command, which is the INSERT, UPDATE, or delete command, SQL Server automatically uses exclusive locks. However, you cannot add exclusive locks to an object when there are other locks on it. Exclusive locks are not released until the end of the transaction.
- Share lock (Shared lock)
A resource with a shared lock lock can be read by another user, but other users cannot modify it. When the SELECT command executes, SQL Server typically shares lock locks on the object. The shared lock is immediately freed when a data page with a shared lock is usually read.
- Updating locks (update lock)
Update locks are created to prevent deadlocks. When SQL Server prepares to update data, it first updates lock locks on the data object, so that the data cannot be modified but can be read. When SQL Server determines that the update data operation is to be performed, it automatically wraps the update lock to an exclusive lock. However, you cannot update lock locks on objects when there are other locks on them.
(2) from the programmer's point of view
locks are divided into the following two types:
- Optimistic lock (Optimistic lock)
Optimistic locks assume that when working with data, you do not need to do anything in your application's code to lock the record directly, that is, relying entirely on the database to manage the lock. Typically, SQL Server locks the table that is updated in the transaction scope automatically when the transaction is performed.
- Pessimistic lock (Pessimistic lock)
Pessimistic lock for the automatic management of database system not a cold, requires the programmer to directly manage data or object lock processing, and is responsible for acquiring, sharing and discarding any locks on the data being used.
11.5.3 Isolation Level
Isolation (isolation) is a concept in the computer security science, which is essentially a blockade mechanism. It refers to the relationship between users and resources in automatic data processing system, that is, users and processes are separated from each other, and the protection control of the operating system is also separated. In SQL Server, the isolation level is the degree of isolation of a transaction and other transactions, specifying how the database protects (locks) data that is currently being used by another user or server isolation. Specifying the isolation level of a transaction has the same effect as using a lock option in a SELECT statement to control the locking method.
The following four isolation levels are available in SQL Server:
- READ committed
Under this isolation level, the SELECT command does not return data that has not been committed (committed), nor does it return dirty data. It is the default isolation level for SQL Server.
- READ UNCOMMITTED
In contrast to the Read Committed isolation level, it allows reading of determined data that has been modified by another user but not yet committed.
- Repeatable READ
At this isolation level, data read with the Select command will not be changed during the entire command execution. This option affects the performance of the system, preferably not at this isolation level.
- SERIALIZABLE
The serializable option has the same meaning as the DELETE statement.
The
isolation level needs to use the SET command to set its syntax as follows:
SET TRANSACTION Isolation Level
{READ committed
| READ Uncommitted
| Repeatable READ
| SERIALIZABLE}
11.5.4 View Lock
can view locks through Enterprise Manager or stored procedures.
(1) View lock with Enterprise Manager
in Enterprise Manager, under the Management folder in the Directory tree window, the locks/process ID node in current activity, you can view the currently locked process, and select locks/object of the same sibling node, you can view the currently locked object, as shown in Figure 11-1. In Figure 11-1, when you right-click an object in the Taskpad window and choose Properties from the shortcut menu, the Process Details dialog box for the lock shown in Figure 11-2 appears. In this case, you can refresh or kill the lock process.
The
kill process can also be done with the following Transact-SQL command:
KILL spid
The
SPID is the system process ID, which is the abbreviation for the number of systems processes, as shown in Figure 11-1.
Figure 11-2 Locked process details
(2) sp_lock view lock with system stored procedure
The syntax for the
stored procedure sp_lock is as follows:
sp_lock spid
The process number of SQL Server spid can be found in the master.dbo.sysprocesses system table. The SPID is data of type int, and if you do not specify a spid, all the locks are displayed.
11.5.5 Deadlock and its prevention
Deadlock (deadlocking) is in the context of multiuser or multiple processes, in order to use the same resources generated by the unresolved state of contention, in layman's terms, is that two users each occupy a resource, both want to use each other's resources, but at the same time do not want to give up their own resources, have been waiting for each other to give up resources, If there is no external interference, it will continue to be consumed.
deadlocks can cause a lot of waste of resources and even crash the system. The principle of resolving deadlocks in SQL Server is to "sacrifice one more dead than the other" by singling out a process as the victim, rolling back its transaction, and sending an error message with the number 1205 to the program executing the process. The way to prevent deadlocks is to not allow conditions to meet the deadlock, for this reason, users need to adhere to the following principles:
- Try to avoid concurrent execution of statements involving the modification of data;
- Require each transaction to lock all data to be used at once, otherwise it will not be executed;
- A blocking order in advance all transactions must be blocked on the data in this order, for example, different procedures should be kept in the same order as possible for the update execution of objects within a transaction;
- The execution time for each transaction must not be too long, and the transaction section-length transactions may be considered to be split into several transactions.
Summary of this chapter
This chapter describes the methods of data updating and the concepts of transactions and locks. In addition to using the statements in this chapter to update data, you can use views to update data, and see Chapter 13th, "Cursors and Views," for the use of views.
