将以下C#同步逻辑转换为存储过程
本文关键字:转换 存储过程 同步 | 更新日期: 2023-09-27 18:27:00
我有以下实体框架逻辑,我想转换为存储过程,我尝试在存储过程中使用独占锁,但它会导致很多超时。。。
把Page想象成某种有4列的硬盘
Pages
PageID
SpaceAvailable
SpaceOccupied
TotalSpace
我需要在页面中分配我的对象,因为空间是可用的,如果对象不能容纳,它将获得下一个可用的页面。
// a static lock to prevent race condition
static object Locker = new Object();
long AllocateNewPage(MyContext context, int requestedSize){
long pageID = 0;
// what is T-SQL lock equaivalent?
lock(Locker){
using(TransactionScope scope = new TransactionScope()){
var page = context.Pages
.Where(x=>x.SpaceAvailable>requestedSize)
.OrderBy(x=>x.PageID)
.First();
page.SpaceOccupied = page.SpaceOccupied + requestedSize;
page.SpaceAvailable = page.SpaceAvailable - requestedSize;
context.SaveChanges();
scope.Commit();
pageID = page.PageID;
}
}
return pageID;
}
下面是我写的存储过程,但它会导致很多超时,因为我设置了5秒的超时时间,否则同样的事情在C#中运行正确且非常快,唯一的问题是,我必须将其移动到存储过程中,因为数据库现在将为多个客户端服务。
CREATE procedure [GetPageID]
(
@SpaceRequested int
)
AS
BEGIN
DECLARE @DBID int
DECLARE @lock int
DECLARE @LockName varchar(20)
SET @LockName = 'PageLock'
BEGIN TRANSACTION
-- acquire a lock
EXEC @lock = sp_getapplock
@Resource = @LockName,
@LockMode = 'Exclusive',
@LockTimeout = 5000
IF @lock<>0 BEGIN
ROLLBACK TRANSACTION
SET @DBID = -1
SELECT @DBID
return 0
END
SET @DBID = coalesce((SELECT TOP 1 PageID
FROM Pages
WHERE SpaceAvailable > @SpaceRequested
ORDER BY PageID ASC ),0)
UPDATE Pages SET
SpaceAvailable = SpaceAvailable - @SpaceRequested,
SpaceOccupied = SpaceOccupied + @SpaceRequested
WHERE PageID = @DBID
EXEC @lock = sp_releaseapplock @Resource = @LockName
COMMIT TRANSACTION
SELECT @DBID
END
我对存储过程了解不多,但我需要在锁定模式下分配页面,这样页面就不会被过度填充。
我想得太多了吗即使我正在运行事务,我仍然需要锁定吗?
create procedure [GetPageID]
@SpaceRequested int
as
begin
set nocount on;
begin tran;
update top (1) pages
set
SpaceAvailable -= @SpaceRequested,
SpaceOccupied += @SpaceRequested
output
inserted.PageID
where
SpaceAvailable > @SpaceRequested
order by PageID asc;
commit tran;
end;
以上内容也可以分两步编写,您可以在问题中展示(如果您喜欢它,或者您的SQL Server版本足够旧):
create procedure [GetPageID]
@SpaceRequested int
as
begin
set nocount on;
begin tran;
declare @page_id int;
select top (1) @page_id = PageID
from pages with (updlock, rowlock)
where SpaceAvailable > @SpaceRequested
order by PageID asc;
update Pages
set
SpaceAvailable = SpaceAvailable - @SpaceRequested,
SpaceOccupied = SpaceOccupied + @SpaceRequested
where
PageID = @page_id;
commit tran;
select @page_id;
end;