请教一个SQL Server 2005事务问题

yuweibang001 2010-10-19 07:43:45

SQl Server 2005 有一个存储过程P，会对A,B表进行插入操作。
这个存储过程执行时间很长，大概要20-30分钟。

JavaEE在调P这个存储过程时，会启动一个事务

存储过程执行过程中，其他用户就不能对A,B进行查询，修改。
查询sql管理器，发现阻塞了。

存储过程执行完了，就可以查询了

如何保证P执行在一个事务中，其他用户又能够对A,B表进行查询，修改呢。
谢谢！

...全文

115 点赞收藏 16

写回复

16 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

billpu 2010-10-21

set transaction isolation level read uncommitted 不要加在你insert的存储过程里
加到你需要select的存储过程里
你可以专门定义个存储过程里面用set transaction isolation level read uncommitted定义
如果你还不放心就专门定义另一个连接

回复赞

xiaoxiangqing 2010-10-21

在事务中，其它进程是不能修改的。查询的话也不行,不过加了nolock就可以查询

回复赞

yuweibang001 2010-10-21

下面就是我的存储过程

USE [testforuser]
GO
/****** 对象: StoredProcedure [dbo].[p_prj_upgrade] 脚本日期: 10/21/2010 09:24:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: yuweibang
-- Create date: 2010-7-17
-- Description: --upgrade the project
-- =============================================
ALTER PROCEDURE [dbo].[p_prj_upgrade]
(
@targetprojectid uniqueidentifier,
@sourceprojectid uniqueidentifier
)
AS

declare @r_toctree cursor --toctree cursor

BEGIN
set xact_abort on
set implicit_transactions off
set nocount on
set transaction isolation level read uncommitted

set @status_delete = 'd'
set @cons_readfeature= 'rf'
set @cons_metadatastruct= 'ms'
set @cons_metadatavalue= 'mv'
set @cons_toc= 'toc'
set @cons_toctree='toctree'
set @cons_topic='topic'
set @cons_topicref='topicref'
set @cons_component='component'
set @cons_componentnode='cmpnode'
set @cons_outline = 'outline'
set @cons_outlinenode = 'outnode'

--clear table tbl_temp_idlink
delete from tbl_temp_idlink;

--copy deliver 1
INSERT INTO [dbo].[tbl_prj_deliver]
([id]
,[projectid]
,[zhname]
,[engname]
,[secrecy]
,[reader]
,[description]
,[sceneid]
,[language]
,[platformid]
,[relation]
,[hastemplate])
select
newid()
,@targetprojectid
,[zhname]
,[engname]
,[secrecy]
,[reader]
,[description]
,[sceneid]
,[language]
,[platformid]
,[relation]
,[hastemplate]
from [tbl_prj_deliver] where [projectid] = @sourceprojectid

--copy tbl_prj_reader
insert into tbl_temp_idlink
select id,newid(),@cons_readfeature
from [tbl_prj_reader]
where [projectid] = @sourceprojectid

INSERT INTO [dbo].[tbl_prj_reader]
([id]
,[projectid]
,[meaningfulid]
,[name]
,[job]
,[type]
,[experience]
,[remark])
select (select [newid] from tbl_temp_idlink where id = a.id)
,@targetprojectid
,[meaningfulid]
,[name]
,[job]
,[type]
,[experience]
,[remark]
from [tbl_prj_reader] a where [projectid] = @sourceprojectid;

--copy tbl_prj_feature
insert into tbl_temp_idlink
select id,newid(),@cons_readfeature
from [tbl_prj_feature]
where [projectid] = @sourceprojectid

INSERT INTO [dbo].[tbl_prj_feature]
([id]
,[projectid]
,[meaningfulid]
,[name]
,[owner]
,[type]
,[subtype]
,[remark])
select (select [newid] from tbl_temp_idlink where id = a.id)
,@targetprojectid
,[meaningfulid]
,[name]
,[owner]
,[type]
,[subtype]
,[remark]
from [tbl_prj_feature] a where [projectid] = @sourceprojectid

--copy tbl_prj_metatypestructon
insert into tbl_temp_idlink
select id,newid(),@cons_metadatastruct
from [tbl_prj_metatypestructon]
where [projectid] = @sourceprojectid

end

回复赞

yuweibang001 2010-10-21

select 语句大部份都是hibernate生成的。
把所有的select 都设置为读未提交，应该会影响到一致性把

回复赞

billpu 2010-10-20

更正下事务的隔离级别

回复赞

billpu 2010-10-20

sqlserver 在插入表的时候要看的,一般在行上(key)加排他锁,在页上(看情况升级)一般是意向排他,因为意向排他和共享锁不兼容,所以不能select
方法:使用with(no lock)或者 with (readpast)
如果你觉得麻烦
可以设置数据库的隔离级别为READ UNCOMMITTED,语法如下

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

回复赞

abuying 2010-10-20

查询应该可以！
但插入时，又要修改，不行！

回复赞

yuweibang001 2010-10-20

存储过程没有事务，事务是 JavaEE调存储过程启动的，性能很难优化。毕竟涉及到新插入20万数据量在那

sql server2005 在插入表的时候是排他锁。能不能是行级锁呢，好像oracle是支持的吧

查询可以 with(nolock)，因为查询都是hibernate生成的加上with(no lock)要改很多代码吧。
hibernate 有没有全局设置nolock的呢？
谢谢！

回复赞

ask_chang 2010-10-20

优化存储过程

更改执行存储过程的时间,比如晚上等.

如果能够查询,数据的一致性没有.

回复赞

billpu 2010-10-20

所以说最好提供你的存储过程 ,不然只能猜呵呵
这点确实是挺麻烦的事情,SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED的作用域是连接会话
如果你确实不想修改select语句的话,你只能专门定义一个连接写一个存储过程专门定义uncommitted的事务隔离级别,如果那个20分钟真的对你很重要

回复赞

yuweibang001 2010-10-20

to billpu: 我在P存储过程是加上SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
也是没有用的。
别的用户还是查询不了。
除非查询的时候加上SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED。
但这要做。就将整个系统的隔离级别降低了。应该会影响一致性吧

回复赞

billpu 2010-10-19

不给出你的存储过程加锁方式怎么帮你看,最好贴点代码出来

回复赞