DB2数据库表的解锁

数据库
下面为您介绍DB2数据库中,如何才能将数据库表进行解锁,供您参考,希望对您有所帮助。

#p##p#下面为您介绍DB2数据库中,如何才能将数据库表进行解锁,供您参考,希望对您有所帮助。

在DB2的命令行中输入:

update monitor switches using lock on table on

然后打开另一个DB2命令窗口执行我的那个被吊死的Update语句。

然后在第一个DB2命令窗口执行:

get snapshot for locks on Database_Name(你的数据库的名字)> locks.TXT

然后,可以看到第一个DB2的窗口有一个信息输出,把这些信息输出到TXT中,大致如下:

应用程序句柄                    = 36
应用程序标识                    = AC100C47.IC05.00F6C6095828
序号                          = 0246
应用程序名                      = java.exe
CONNECT 授权标识                = DB2ADMIN
应用程序状态                    = UOW 正在等待
状态更改时间                    = 未收集
应用程序代码页                  = 1208
挂起的锁定                      = 0
总计等待时间(毫秒)            = 0

应用程序句柄                    = 43
应用程序标识                    = *LOCAL.DB2.060512054331
序号                          = 2273
应用程序名                      = java.exe
CONNECT 授权标识                = DB2ADMIN
应用程序状态                    = 联合请求暂挂
状态更改时间                    = 未收集
应用程序代码页                  = 1208
挂起的锁定                      = 6
总计等待时间(毫秒)            = 0 #p#

锁定列表
锁定名称                       = 0x031F9052000000000000000055
锁定属性                       = 0x00000000
发行版标志                     = 0x40000000
锁定计数                       = 255
挂起计数                       = 0
锁定对象名                     = 0
对象类型                       = 内部
方式                           = S

锁定名称                       = 0x26800000000000000000000044
锁定属性                       = 0x00000000
发行版标志                     = 0x40000000
锁定计数                       = 1
挂起计数                       = 0
锁定对象名                     = 0
对象类型                       = 内部
方式                           = S

锁定名称                       = 0x020006000F1700000000000052
锁定属性                       = 0x00000000
发行版标志                     = 0x00000001
锁定计数                       = 1
挂起计数                       = 0
锁定对象名                     = 5903 #p#
对象类型                       = 行
表空间名                       = USERSPACE1
表模式                         = DB2ADMIN
表名                           = C_USER
方式                           = NS

锁定名称                       = 0x01000000010000000500BC0056
锁定属性                       = 0x00000000
发行版标志                     = 0x40000000
锁定计数                       = 1
挂起计数                       = 0
锁定对象名                     = 0
对象类型                       = 内部变化锁定
方式                           = S

锁定名称                       = 0x535953534E333030FD965C0641
锁定属性                       = 0x00000000
发行版标志                     = 0x40000000
锁定计数                       = 1
挂起计数                       = 0
锁定对象名                     = 0
对象类型                       = 内部方案锁定
方式                           = S #p#锁定名称                       = 0x02000600000000000000000054
锁定属性                       = 0x00000000
发行版标志                     = 0x00000001
锁定计数                       = 1
挂起计数                       = 0
锁定对象名                     = 6
对象类型                       = 表
表空间名                       = USERSPACE1
表模式                         = DB2ADMIN
表名                           = C_USER
方式                           = IS

应用程序句柄                    = 557
应用程序标识                    = *LOCAL.DB2.060512053913
序号                          = 1254
应用程序名                      = java.exe
CONNECT 授权标识                = DB2ADMIN
应用程序状态                    = 联合请求暂挂
状态更改时间                    = 未收集
应用程序代码页                  = 1208
挂起的锁定                      = 6
总计等待时间(毫秒)            = 0

锁定列表
锁定名称                       = 0x031F9052000000000000000055
锁定属性                       = 0x00000000
发行版标志                     = 0x40000000
锁定计数                       = 255
挂起计数                       = 0
锁定对象名                     = 0
对象类型                       = 内部
方式                           = S

锁定名称                       = 0x26800000000000000000000044
锁定属性                       = 0x00000000
发行版标志                     = 0x40000000
锁定计数                       = 1 #p#
挂起计数                       = 0
锁定对象名                     = 0
对象类型                       = 内部
方式                           = S

锁定名称                       = 0x02000600071D00000000000052
锁定属性                       = 0x00000000
发行版标志                     = 0x00000001
锁定计数                       = 1
挂起计数                       = 0
锁定对象名                     = 7431
对象类型                       = 行
表空间名                       = USERSPACE1
表模式                         = DB2ADMIN
表名                           = C_USER
方式                           = NS

锁定名称                       = 0x01000000010000000500BC0056
锁定属性                       = 0x00000000
发行版标志                     = 0x40000000
锁定计数                       = 1
挂起计数                       = 0
锁定对象名                     = 0
对象类型                       = 内部变化锁定
方式                           = S #p#

锁定名称                       = 0x535953534E333030FD965C0641
锁定属性                       = 0x00000000
发行版标志                     = 0x40000000
锁定计数                       = 1
挂起计数                       = 0
锁定对象名                     = 0
对象类型                       = 内部方案锁定
方式                           = S

锁定名称                       = 0x02000600000000000000000054
锁定属性                       = 0x00000000
发行版标志                     = 0x00000001
锁定计数                       = 1
挂起计数                       = 0
锁定对象名                     = 6
对象类型                       = 表
表空间名                       = USERSPACE1
表模式                         = DB2ADMIN
表名                           = C_USER
方式                           = IS #p#

其中应用程序句柄43和557的状态都是死锁了,猜测是这2个应用争用DB2的表,造成死锁,根据日志提示,在DB2的命令窗口输入:

force application (43)

force application (557)

提示这个操作是异步的,我执行list applicaions,结果进程中还有那2个进程,那2个进程可能是在执行比较大的操作,需要耐心等待,如何还不行,则使用下面的命令来强制所有的应用都停止,然后重启DB2:

force application all

terminate

db2stop force

db2start

如果DB2在Window上,则可以使用“控制中心”->实例->右键“应用程序”,可以看到当前的锁定情况,并且可以强行关闭某个进程,也可以显示“锁定链”。
 

在DB2的命令行中输入:

update monitor switches using lock on table on #p#

然后打开另一个DB2命令窗口执行我的那个被吊死的Update语句。

然后在第一个DB2命令窗口执行:

get snapshot for locks on Database_Name(你的数据库的名字)> locks.TXT

然后,可以看到第一个DB2的窗口有一个信息输出,把这些信息输出到TXT中,大致如下:

应用程序句柄                    = 36
应用程序标识                    = AC100C47.IC05.00F6C6095828
序号                          = 0246
应用程序名                      = java.exe
CONNECT 授权标识                = DB2ADMIN
应用程序状态                    = UOW 正在等待
状态更改时间                    = 未收集
应用程序代码页                  = 1208
挂起的锁定                      = 0
总计等待时间(毫秒)            = 0

应用程序句柄                    = 43
应用程序标识                    = *LOCAL.DB2.060512054331
序号                          = 2273
应用程序名                      = java.exe
CONNECT 授权标识                = DB2ADMIN
应用程序状态                    = 联合请求暂挂
状态更改时间                    = 未收集
应用程序代码页                  = 1208
挂起的锁定                      = 6
总计等待时间(毫秒)            = 0 #p#

锁定列表
锁定名称                       = 0x031F9052000000000000000055
锁定属性                       = 0x00000000
发行版标志                     = 0x40000000
锁定计数                       = 255
挂起计数                       = 0
锁定对象名                     = 0
对象类型                       = 内部
方式                           = S

锁定名称                       = 0x26800000000000000000000044
锁定属性                       = 0x00000000
发行版标志                     = 0x40000000
锁定计数                       = 1
挂起计数                       = 0
锁定对象名                     = 0
对象类型                       = 内部
方式                           = S

锁定名称                       = 0x020006000F1700000000000052
锁定属性                       = 0x00000000
发行版标志                     = 0x00000001
锁定计数                       = 1
挂起计数                       = 0
锁定对象名                     = 5903
对象类型                       = 行
表空间名                       = USERSPACE1
表模式                         = DB2ADMIN
表名                           = C_USER
方式                           = NS #p#

锁定名称                       = 0x01000000010000000500BC0056
锁定属性                       = 0x00000000
发行版标志                     = 0x40000000
锁定计数                       = 1
挂起计数                       = 0
锁定对象名                     = 0
对象类型                       = 内部变化锁定
方式                           = S

锁定名称                       = 0x535953534E333030FD965C0641
锁定属性                       = 0x00000000
发行版标志                     = 0x40000000
锁定计数                       = 1
挂起计数                       = 0
锁定对象名                     = 0
对象类型                       = 内部方案锁定
方式                           = S

锁定名称                       = 0x02000600000000000000000054
锁定属性                       = 0x00000000
发行版标志                     = 0x00000001
锁定计数                       = 1
挂起计数                       = 0
锁定对象名                     = 6
对象类型                       = 表
表空间名                       = USERSPACE1
表模式                         = DB2ADMIN
表名                           = C_USER #p#
方式                           = IS

应用程序句柄                    = 557 #p#
应用程序标识                    = *LOCAL.DB2.060512053913
序号                          = 1254
应用程序名                      = java.exe
CONNECT 授权标识                = DB2ADMIN
应用程序状态                    = 联合请求暂挂
状态更改时间                    = 未收集
应用程序代码页                  = 1208
挂起的锁定                      = 6
总计等待时间(毫秒)            = 0

锁定列表
锁定名称                       = 0x031F9052000000000000000055
锁定属性                       = 0x00000000
发行版标志                     = 0x40000000
锁定计数                       = 255
挂起计数                       = 0
锁定对象名                     = 0
对象类型                       = 内部
方式                           = S #p#

锁定名称                       = 0x26800000000000000000000044
锁定属性                       = 0x00000000
发行版标志                     = 0x40000000
锁定计数                       = 1
挂起计数                       = 0
锁定对象名                     = 0
对象类型                       = 内部
方式                           = S

锁定名称                       = 0x02000600071D00000000000052
锁定属性                       = 0x00000000
发行版标志                     = 0x00000001
锁定计数                       = 1
挂起计数                       = 0
锁定对象名                     = 7431
对象类型                       = 行
表空间名                       = USERSPACE1
表模式                         = DB2ADMIN
表名                           = C_USER
方式                           = NS

锁定名称                       = 0x01000000010000000500BC0056
锁定属性                       = 0x00000000
发行版标志                     = 0x40000000
锁定计数                       = 1
挂起计数                       = 0
锁定对象名                     = 0
对象类型                       = 内部变化锁定
方式                           = S

锁定名称                       = 0x535953534E333030FD965C0641
锁定属性                       = 0x00000000
发行版标志                     = 0x40000000
锁定计数                       = 1
挂起计数                       = 0
锁定对象名                     = 0 #p#
对象类型                       = 内部方案锁定
方式                           = S

锁定名称                       = 0x02000600000000000000000054
锁定属性                       = 0x00000000
发行版标志                     = 0x00000001
锁定计数                       = 1
挂起计数                       = 0
锁定对象名                     = 6
对象类型                       = 表
表空间名                       = USERSPACE1
表模式                         = DB2ADMIN
表名                           = C_USER
方式                           = IS

其中应用程序句柄43和557的状态都是死锁了,猜测是这2个应用争用DB2的表,造成死锁,根据日志提示,在DB2的命令窗口输入:

force application (43)

force application (557)

提示这个操作是异步的,我执行list applicaions,结果进程中还有那2个进程,那2个进程可能是在执行比较大的操作,需要耐心等待,如何还不行,则使用下面的命令来强制所有的应用都停止,然后重启DB2:

force application all

terminate

db2stop force

db2start

如果DB2在Window上,则可以使用“控制中心”->实例->右键“应用程序”,可以看到当前的锁定情况,并且可以强行关闭某个进程,也可以显示“锁定链”。
 

责任编辑:段燃 来源: 话联网
相关推荐

2010-09-07 09:31:03

DB2数据库锁表

2010-11-03 11:36:53

访问DB2表

2011-03-11 16:02:03

DB2数据库安装

2010-08-04 13:30:49

2010-08-03 13:56:11

DB2表复制

2010-09-01 09:13:29

DB2表空间

2010-08-26 10:37:40

DB2Q复制

2010-11-03 16:32:10

DB2创建数据库

2010-11-01 13:45:16

DB2数据库的优势

2010-08-25 10:50:48

DB2数据库

2010-09-01 13:38:41

DB2数据复制

2010-08-26 16:15:25

DB2数据库管理

2010-09-30 11:49:21

DB2数据库权限

2010-11-01 11:30:41

DB2数据库权限

2010-11-03 16:21:18

DB2数据库授权

2010-07-27 10:11:18

DB2数据库临时表

2010-08-04 11:38:24

Oracle数据库

2009-07-06 17:34:26

远程复制DB2

2010-07-29 09:14:57

DB2数据库表

2011-06-15 09:22:02

DB2
点赞
收藏

51CTO技术栈公众号