Home Database Mysql Tutorial 实例演示SQL Server中五个事务隔离的区别

实例演示SQL Server中五个事务隔离的区别

Jun 07, 2016 pm 05:44 PM
server affairs the difference Example Demo isolation

园子里有很不错的介绍SQL Server事务隔离的文章,感觉很多都从概念入手介绍的,对那些初学者来说,看得见摸得着的理解才深刻,故不再重复,重点在于实例演示上面。 首先解释下事务隔离是干什么的,一个事务的隔离级别控制了它怎么样影响其它事务和被其它事务

    园子里有很不错的介绍SQL Server事务隔离的文章,感觉很多都从概念入手介绍的,对那些初学者来说,看得见摸得着的理解才深刻,故不再重复,重点在于实例演示上面。

首先解释下事务隔离是干什么的,一个事务的隔离级别控制了它怎么样影响其它事务和被其它事务所影响。

1.READ UNCOMMITTED,会导致脏读(能读取其它事务没有提交的更改)和不可重复读(事务读取的数据被其它事务所修改,再次读取时不一致)

初始化:

(1,1), val int) INSERT INTO TranLevel(val) values(1) INSERT INTO TranLevel(val) values(2) INSERT INTO TranLevel(val) values(3)

首先执行Query1,再新建查询立即执行Query2

Query1:

BEGIN TRAN Query1 TranLevel DELAY Query1

Query2:

Query2 Tranlevel DELAY TranLevel COMMIT TRAN Query2

下面就看看Query2执行的结果是怎样的:

结果显而易见,美国空间,如果将事务隔离级别设置为未提交读,则会造成脏读和不可重复读的问题,在这几个事务隔离级别中是限制最小的一个,SQL Server分配的资源也最小。

2.READ COMMITTED,虚拟主机,提交读,默认的事务隔离级别,会造成不可重复读。

初始化:

drop Table tranlevel (1,1), val int) INSERT INTO TranLevel(val) values(1) INSERT INTO TranLevel(val) values(2) INSERT INTO TranLevel(val) values(3)

Query3:

Query3 DELAY TranLevel Query3

Query 4:

Query4 TranLevel DELAY TranLevel COMMIT TRAN Query4

执行Query3后,新建查询执行Query4。下面看看Querry4查询得到的数据。

结果就是Query4中的事务查询获得了在Query3提交后的数据,在同一事务中读取的数据不一致,造成了不可重复读。

3.REPEATABLE READ,会锁住那些事务访问的数据行,但不能防止新行的插入,所以会导致幻读。

初始化:

drop Table tranlevel (1,1), val int) INSERT INTO TranLevel(val) values(1) INSERT INTO TranLevel(val) values(2) INSERT INTO TranLevel(val) values(3)

 Query5:

Query5 DELAY TranLevel(val) values(4) COMMIT TRAN Query5

Query6:

Query6 TranLevel TranLevel COMMIT TRAN Query6

执行Query5后,新建查询执行Query6。下面看看Querry6查询得到的数据:

可以看到,服务器空间,即使设置为可重复读,仍然会导致在同一事务中查询的数据不一致的情况,即幻读。

4.SHNAPSHOT 和 SERIALIZABLE 能解决脏读、不可重复读、幻读的问题,就没有必要再写实例来说明了。SHNAPSHOT级别对并发情况采用乐观处理,如果两个事务同时修改了一行数据,则后尝试修改的事务会爆冲突,但如果修改的是不同行,则不会有这个问题。如果不理解的朋友可以参考下面这个链接:

实例sql语句下载地址:

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

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

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)

What are the basic requirements for c language functions What are the basic requirements for c language functions Apr 03, 2025 pm 10:06 PM

C language functions are the basis for code modularization and program building. They consist of declarations (function headers) and definitions (function bodies). C language uses values ​​to pass parameters by default, but external variables can also be modified using address pass. Functions can have or have no return value, and the return value type must be consistent with the declaration. Function naming should be clear and easy to understand, using camel or underscore nomenclature. Follow the single responsibility principle and keep the function simplicity to improve maintainability and readability.

How to set password protection for export PDF on PS How to set password protection for export PDF on PS Apr 06, 2025 pm 04:45 PM

Export password-protected PDF in Photoshop: Open the image file. Click "File"> "Export"> "Export as PDF". Set the "Security" option and enter the same password twice. Click "Export" to generate a PDF file.

Concept of c language function Concept of c language function Apr 03, 2025 pm 10:09 PM

C language functions are reusable code blocks. They receive input, perform operations, and return results, which modularly improves reusability and reduces complexity. The internal mechanism of the function includes parameter passing, function execution, and return values. The entire process involves optimization such as function inline. A good function is written following the principle of single responsibility, small number of parameters, naming specifications, and error handling. Pointers combined with functions can achieve more powerful functions, such as modifying external variable values. Function pointers pass functions as parameters or store addresses, and are used to implement dynamic calls to functions. Understanding function features and techniques is the key to writing efficient, maintainable, and easy to understand C programs.

The difference between H5 and mini-programs and APPs The difference between H5 and mini-programs and APPs Apr 06, 2025 am 10:42 AM

H5. The main difference between mini programs and APP is: technical architecture: H5 is based on web technology, and mini programs and APP are independent applications. Experience and functions: H5 is light and easy to use, with limited functions; mini programs are lightweight and have good interactiveness; APPs are powerful and have smooth experience. Compatibility: H5 is cross-platform compatible, applets and APPs are restricted by the platform. Development cost: H5 has low development cost, medium mini programs, and highest APP. Applicable scenarios: H5 is suitable for information display, applets are suitable for lightweight applications, and APPs are suitable for complex functions.

Why do you need to call Vue.use(VueRouter) in the index.js file under the router folder? Why do you need to call Vue.use(VueRouter) in the index.js file under the router folder? Apr 05, 2025 pm 01:03 PM

The necessity of registering VueRouter in the index.js file under the router folder When developing Vue applications, you often encounter problems with routing configuration. Special...

What are the differences and connections between c and c#? What are the differences and connections between c and c#? Apr 03, 2025 pm 10:36 PM

Although C and C# have similarities, they are completely different: C is a process-oriented, manual memory management, and platform-dependent language used for system programming; C# is an object-oriented, garbage collection, and platform-independent language used for desktop, web application and game development.

How to use XPath to search from a specified DOM node in JavaScript? How to use XPath to search from a specified DOM node in JavaScript? Apr 04, 2025 pm 11:15 PM

Detailed explanation of XPath search method under DOM nodes In JavaScript, we often need to find specific nodes from the DOM tree based on XPath expressions. If you need to...

What are the different ways of promoting H5 and mini programs? What are the different ways of promoting H5 and mini programs? Apr 06, 2025 am 11:03 AM

There are differences in the promotion methods of H5 and mini programs: platform dependence: H5 depends on the browser, and mini programs rely on specific platforms (such as WeChat). User experience: The H5 experience is poor, and the mini program provides a smooth experience similar to native applications. Communication method: H5 is spread through links, and mini programs are shared or searched through the platform. H5 promotion methods: social sharing, email marketing, QR code, SEO, paid advertising. Mini program promotion methods: platform promotion, social sharing, offline promotion, ASO, cooperation with other platforms.

See all articles