Home Database Mysql Tutorial SQL SERVER的锁机制(一)概述(锁的种类与范围)

SQL SERVER的锁机制(一)概述(锁的种类与范围)

Jun 07, 2016 pm 05:44 PM
server mechanism Overview type scope

SQL SERVER的锁机制系列: SQL SERVER的锁机制(一)概述(锁的种类与范围) SQL SERVER的锁机制(二)概述(锁的兼容性与可以锁定的资源) SQL SERVER的锁机制(三)概述(锁与事务隔离级别) SQL SERVER的锁机制(四)概述(各种事务隔离级别发生的影响)

SQL SERVER的锁机制系列:

 SQL SERVER的锁机制(一)——概述(锁的种类与范围)

 SQL SERVER的锁机制(二)——概述(锁的兼容性与可以锁定的资源)

 SQL SERVER的锁机制(三)——概述(锁与事务隔离级别)

SQL SERVER的锁机制(四)——概述(各种事务隔离级别发生的影响)

 

 

锁定:通俗的讲就是加锁。锁定是 Microsoft SQL Server 数据库引擎用来同步多个用户同时对同一个数据块的访问的一种机制。

定义:当有事务操作时,香港虚拟主机,数据库引擎会要求不同类型的锁定,如相关数据行、数据页或是整个数据表,当锁定运行时,会阻止其他事务对已经锁定的数据行、数据页或数据表进行操作。只有在当前事务对于自己锁定的资源不在需要时,才会释放其锁定的资源,供其他事务使用。

一、锁的种类与范围(如下表)

锁类型

说明

共享 (S)

用于不更改或不更新数据的读取操作,如 SELECT 语句。

更新 (U)

用于可更新的资源中。 防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。

独占(也可称排他)(X)

意向

架构

大容量更新 (BU)

在向表进行大容量数据复制且指定了 TABLOCK 提示时使用。

键范围

当使用可序列化事务隔离级别时保护查询读取的行的范围。 确保再次运行查询时其他事务无法插入符合可序列化事务的查询的行。

 

 

(一)共享锁

或无法获取,就必须等待别人释放对该记录中某几种与共享锁互斥的锁,才能在设置共享锁之后,获取该条记录。

 

(二)更新锁

更新锁是一种中继锁。当同一项资源从原来的查询操作转换为更新操作时,锁定机制会从共享锁变为更新锁,再进一步变成独占锁。

独占锁(独占锁(独占锁(锁),并且每个事务都等待另一个事务释放共享模式锁,因此发生死锁。

独占锁(锁)。

),一定会先查找记录,在查找的过程中就会对相关的记录放置共享锁,等找到相应的记录之后,SQL SERVER 会先对记录放置更新锁,以避免发生死锁。因为共享锁与更新锁并不互斥,如果两个人同时对同一条记录放置共享锁,先进行更新的人,可以在别人也对同一条记录放置了共享锁时,继续放置更新锁,但因为更新锁互斥,所以当另一个人想再放置更新锁时,将无法设置,而进入停止等待状态。

 

(三)独占锁(也可称为排他锁)

, 语句在执行所需的操作之前首先执行读取操作以获取数据。 因此,需先对所在的资源放置独占锁,以确保以上操作未完成时,香港虚拟主机,不受到干扰,独占锁在开启事务之后,一直保留到事务结束。例如,

(四)意向锁

在记录上放置共享锁之前,需要对存放该记录的更大范围(如数据页或数据表)上设置意向锁,以避免其他连接对该页放置独占锁。

意向锁有两种用途:

· 防止其他事务以会使较低级别的锁无效的方式修改较高级别资源。

· 提高数据库引擎在较高的粒度级别检测锁冲突的效率。

请求共享意向锁,以防止另一个事务随后在包含那一页的表上尝试放置独占锁(锁)。 意向锁可以提高性能,网站空间,因为数据库引擎仅在表级检查意向锁来确定事务是否可以安全地获取该表上的锁。 而不需要检查表中的每行或每页上的锁以确定事务是否可以锁定整个表。如下图。

 

等等。各种意向锁的说明,如下表。

锁类型

说明

意向共享 (IS)

保护针对层次结构中某些(而并非所有)低层资源请求或获取的共享锁。

意向独占 (IX)

意向独占共享 (SIX)

独占锁以及修改的行上的独占锁。 虽然每个资源在一段时间内只能有一个 

意向更新 (IU)

共享意向更新 (SIU)

更新意向排他 (UIX)

 

下面来实际举例来说明

DELAY

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
1 months ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How to install, uninstall, and reset Windows server backup How to install, uninstall, and reset Windows server backup Mar 06, 2024 am 10:37 AM

WindowsServerBackup is a function that comes with the WindowsServer operating system, designed to help users protect important data and system configurations, and provide complete backup and recovery solutions for small, medium and enterprise-level enterprises. Only users running Server2022 and higher can use this feature. In this article, we will explain how to install, uninstall or reset WindowsServerBackup. How to Reset Windows Server Backup If you are experiencing problems with your server backup, the backup is taking too long, or you are unable to access stored files, then you may consider resetting your Windows Server backup settings. To reset Windows

Use java's StringBuilder.replace() function to replace a specified range of characters Use java's StringBuilder.replace() function to replace a specified range of characters Jul 24, 2023 pm 06:12 PM

Use java's StringBuilder.replace() function to replace a specified range of characters. In Java, the StringBuilder class provides the replace() method, which can be used to replace a specified range of characters in a string. The syntax of this method is as follows: publicStringBuilderreplace(intstart,intend,Stringstr) The above method is used to replace the index star from

Find numbers that are not divisible by any number in a range, using C++ Find numbers that are not divisible by any number in a range, using C++ Sep 13, 2023 pm 09:21 PM

In this article, we will discuss the problem of finding numbers between 1 and n (given) that are not divisible by any number between 2 and 10. Let us understand this with some examples - Input:num=14Output:3Explanation:Therearethreenumbers,1,11,and13,whicharenotdivisible.Input:num=21Output:5Explanation:Therearefivenumbers1,11,13,17,and19,whicharenotdivisible. Solved Simple method if

Windows Server 2025 preview version welcomes update, Microsoft improves Insiders testing experience Windows Server 2025 preview version welcomes update, Microsoft improves Insiders testing experience Feb 19, 2024 pm 02:36 PM

On the occasion of releasing the build 26040 version of Windows Server, Microsoft announced the official name of the product: Windows Server 2025. Also launched is the Windows11WindowsInsiderCanaryChannel version build26040. Some friends may still remember that many years ago someone successfully converted Windows NT from workstation mode to server mode, showing the commonalities between various versions of Microsoft operating systems. Although there are clear differences between Microsoft's current version of the server operating system and Windows 11, those who pay attention to the details may be curious: why Windows Server updated the brand,

How to implement date range selector in Vue? How to implement date range selector in Vue? Jun 25, 2023 am 08:41 AM

How to implement date range selector in Vue? The date range picker is an interface component often used in modern web applications. It allows the user to select a date or a time period from a date range. For web application development that requires a date range selector, Vue.js is a very good choice. Vue.js is a progressive JavaScript framework for building user interfaces. It allows developers to build complex interactive interfaces using componentization.

Overview of operator overloading problems and solutions in C++ Overview of operator overloading problems and solutions in C++ Oct 08, 2023 pm 01:02 PM

Overview of Operator Overloading Problems and Solutions in C++ Introduction: Operator overloading is an important feature of the C++ language, which allows programmers to customize existing operators to operate custom data types. However, operator overloading needs to be used with caution, because if used improperly or excessively, it will lead to problems such as reduced code readability, ambiguity, and reduced efficiency. This article will outline common problems with operator overloading in C++ and provide corresponding solutions and code examples. 1. Problems with operator overloading 1.1 Ambiguity problem in operator overloading

An in-depth exploration of the storage location and mechanism of Golang variables An in-depth exploration of the storage location and mechanism of Golang variables Feb 28, 2024 pm 09:45 PM

Title: An in-depth exploration of the storage location and mechanism of Golang variables. As the application of Go language (Golang) gradually increases in the fields of cloud computing, big data and artificial intelligence, it is particularly important to have an in-depth understanding of the storage location and mechanism of Golang variables. In this article, we will discuss in detail the memory allocation, storage location and related mechanisms of variables in Golang. Through specific code examples, it helps readers better understand how Golang variables are stored and managed in memory. 1.Memory of Golang variables

Python Tkinter shows off its power: Create stunning GUI applications Python Tkinter shows off its power: Create stunning GUI applications Mar 24, 2024 am 09:11 AM

Tkinter is a powerful GUI library in python that can be used to create cross-platform desktop applications. With its ease of use and wide range of features, it provides various tools for building user interfaces, handling events and managing layouts. Creating a GUI Window To create a GUI window, you need to use the Tkinter.Tk() method. This method returns a Tk() object that represents the application's main window. A window can have a title using the title() method, and the window size and position using the geometry() method. importtkinterastkroot=tk.Tk()root.title("My first Tkinter application")root.g

See all articles