DB2中三个有关锁变量DB2_EVALUNCOMMITTED,DB2_SKIPDELETED和DB2
本文主要解释下DB2中三个有关锁变量DB2_EVALUNCOMMITTED,DB2_SKIPDELETED和DB2_SKIPINSERTED的使用 实验环境: DB2 v9.7.0.6 AIX 6.1.0.0 采用默认的隔离级别CS STUDENT表的DDL与初始内容 CREATE TABLE E97Q6C .STUDENT ( AGE INTEGER , NAME CHAR(8) ) IN
本文主要解释下DB2中三个有关锁变量DB2_EVALUNCOMMITTED,DB2_SKIPDELETED和DB2_SKIPINSERTED的使用
实验环境:
DB2 v9.7.0.6AIX 6.1.0.0
采用默认的隔离级别CS
STUDENT表的DDL与初始内容
CREATE TABLE "E97Q6C "."STUDENT" (
"AGE" INTEGER ,
"NAME" CHAR(8) )
IN "USERSPACE1" ;
$ db2 "select * from student"
AGE NAME
----------- --------
3 xu
5 gao
6 mu
6 mu
6 mu
4 three
1 an
7 record(s) selected.
--分割线--
当启用DB2_EVALUNCOMMITTED时,DB2可以对未提交的插入(INSERT)或更新(UPDATE)数据进行谓词判断。
如果未提交的数据不符合该条语句的谓词判断条件,DB2将不对未提交数据加锁,这样避免了因为要对
未提交数据加锁引起的锁等待状态,提高了应用程序访问的并发性。
实验 1
测试如下(该参数与数据库配置参数CUR_COMMIT有冲突,因此,测试之前需要先将CUR_COMMIT参数设为disabled.)
首先在session 1里做一条“未提交的插入(INSERT)操作”
session 1
---------
$ db2 +c "insert into student values(7,'he')"
DB20000I The SQL command completed successfully.
session 2
---------
$ db2 "select * from student"
SQL0911N The current transaction has been rolled back because of a deadlock
or timeout. Reason code "68". SQLSTATE=40001
$ db2 "select * from student where age = 3"
SQL0911N The current transaction has been rolled back because of a deadlock
or timeout. Reason code "68". SQLSTATE=40001
可以看到,在session 2里的两条语句都因锁超时而失败了。原因是如果用户在更改(UPDATE)、插入(INSERT)或
删除(DELETE)一行时,会在这一行加上排它锁,别的用户不能读、写,除非使用UR隔离级别。
现在启用DB2_EVALUNCOMMITTED变量,需要重启实例,之后在session 1里做同样的插入操作,session 2里做同样的查询
操作:
session 1
------------
$ db2set DB2_EVALUNCOMMITTED=ON
$ db2stop force
$ db2start
$ db2 connect to qsmiao
$ db2 +c "insert into student values(7,'he')"
DB20000I The SQL command completed successfully.
session 2
------------
$ db2 "select * from student"
SQL0911N The current transaction has been rolled back because of a deadlock
or timeout. Reason code "68". SQLSTATE=40001
$ db2 "select * from student where age = 3"
AGE NAME
----------- --------
3 xu
1 record(s) selected.
这次可以看到第2条查询语句是成功的,原因是session 1中有未提交的插入操作,session 2在扫描时,对该行进行了谓词判断,发现不符合谓词盼断条件
也就是 age = 3 , session 2就不会对该行进行加锁,因而不会导致锁超时。
DB2_SKIPDELETED变量被启用的效果是:在表访问期间,会无条件地跳过被删除的行。
实验 2:
在默认情况下,即没有设置DB2_EVALUNCOMMITTED和DB2_SKIPDELETED变量的时候,如果session 1用提交的方式删除操作的时候,Session 2若要查询整个表,是要超时的,
如下:
session 1
---------
$ db2set DB2_SKIPDELETED=OFF
$ db2set DB2_EVALUNCOMMITTED=OFF
$ db2stop force
$ db2start
$ db2 connect to qsmiao
$ db2 +c "delete from student where age=6"
DB20000I The SQL command completed successfully.
session 2
---------
$ db2 "select * from student"
SQL0911N The current transaction has been rolled back because of a deadlock
or timeout. Reason code "68". SQLSTATE=40001
如下,现在修改DB2_SKIPDELETED为ON,那么session 2查询的时候,会无条件地跳过被删除的行,因此会成功。
session 1
---------
$ db2 rollback
$ db2set DB2_SKIPDELETED=ON
$ db2stop force
$ db2start
$ db2 connect to qsmiao
$ db2 +c "delete from student where age=6"
DB20000I The SQL command completed successfully.
session 2
---------
$ db2 "select * from student"
AGE NAME
----------- --------
3 xu
5 gao
4 three
1 an
4 record(s) selected.
类似的,DB2_SKIPINSERTED变量的作用是无条件地跳过被插入的行,就像它们还没有被插入。
参考资料:
牛新庄 《高级进阶DB2(第2版)》

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

Environment variables are the path to the location (or environment) where applications and programs run. They can be created, edited, managed or deleted by the user and come in handy when managing the behavior of certain processes. Here's how to create a configuration file to manage multiple variables simultaneously without having to edit them individually on Windows. How to use profiles in environment variables Windows 11 and 10 On Windows, there are two sets of environment variables – user variables (apply to the current user) and system variables (apply globally). However, using a tool like PowerToys, you can create a separate configuration file to add new and existing variables and manage them all at once. Here’s how: Step 1: Install PowerToysPowerTo

Strict mode was introduced in PHP7, which can help developers reduce potential errors. This article will explain what strict mode is and how to use strict mode in PHP7 to reduce errors. At the same time, the application of strict mode will be demonstrated through code examples. 1. What is strict mode? Strict mode is a feature in PHP7 that can help developers write more standardized code and reduce some common errors. In strict mode, there will be strict restrictions and detection on variable declaration, type checking, function calling, etc. Pass

Instance variables in Java refer to variables defined in the class, not in the method or constructor. Instance variables are also called member variables. Each instance of a class has its own copy of the instance variable. Instance variables are initialized during object creation, and their state is saved and maintained throughout the object's lifetime. Instance variable definitions are usually placed at the top of the class and can be declared with any access modifier, which can be public, private, protected, or the default access modifier. It depends on what we want this to be

PHP function introduction—strpos(): Check whether a variable is a string In PHP, is_string() is a very useful function, which is used to check whether a variable is a string. When we need to determine whether a variable is a string, the is_string() function can help us achieve this goal easily. Below we will learn about how to use the is_string() function and provide some related code examples. The syntax of the is_string() function is very simple. it only needs to

Using Ajax to obtain variables from PHP methods is a common scenario in web development. Through Ajax, the page can be dynamically obtained without refreshing the data. In this article, we will introduce how to use Ajax to get variables from PHP methods, and provide specific code examples. First, we need to write a PHP file to handle the Ajax request and return the required variables. Here is sample code for a simple PHP file getData.php:

Python is widely used in a wide range of fields with its simple and easy-to-read syntax. It is crucial to master the basic structure of Python syntax, both to improve programming efficiency and to gain a deep understanding of how the code works. To this end, this article provides a comprehensive mind map detailing various aspects of Python syntax. Variables and Data Types Variables are containers used to store data in Python. The mind map shows common Python data types, including integers, floating point numbers, strings, Boolean values, and lists. Each data type has its own characteristics and operation methods. Operators Operators are used to perform various operations on data types. The mind map covers the different operator types in Python, such as arithmetic operators, ratio
![Internal error: Unable to create temporary directory [Resolved]](https://img.php.cn/upload/article/000/000/164/168171504798267.png?x-oss-process=image/resize,m_fill,h_207,w_330)
Windows system allows users to install various types of applications on your system using executable/setup files. Recently, many Windows users have started complaining that they are receiving an error named INTERNALERROR:cannotCreateTemporaryDirectory on their systems while trying to install any application using an executable file. The problem is not limited to this but also prevents the users from launching any existing applications, which are also installed on the Windows system. Some possible reasons are listed below. Run the executable to install without granting administrator privileges. An invalid or different path was provided for the TMP variable. damaged system

Differences in syntax between db2 and oracle: 1. SQL syntax differences. Although db2 and oracle both use structured query language, they have some differences in syntax; 2. db2 and oracle have different data types; 3. Foreign key constraint definition, db2 can be defined when creating the table or added after using the "ALTER TABLE" statement. Oracle needs to be defined together when creating the table; 4. There are also some differences in the syntax of db2 and oracle stored procedures and functions.
