SQL. data integrity during delete

[bvg]

Здравствуйте,

Столкнулся с несколько нестандартной ситуацией и хотелось бы обсудить как лучше/правильнее поступать в таких случаях.

Есть две таблицы master - detail.
Для каждой строки мастера должна быть хотя бы одна строка деталей.
Как лучше всего обеспечить такое условие на уровне базы данных? MS SQL Server 2008 если это важно.

Стандартный referential integrity просто устанавливает связь 1-M и не позволит заносить строки в детали, для которых нету мастера, и не позволит удалить строку мастера, если есть хотя бы одна строка деталей.

Добавление новых данных проблем не представляет. Программа должно использовать хранимую процедуру при добавлении строки мастера. Процедура позаботится о создании и мастера и первой строки деталей.

Сложности возникают при попытке удалить строку деталей.
Если деталей больше одной, то удалять можно, а если осталась только одна, то удалять уже нельзя.

Как лучше всего написать хранимую процедуру для удаления одной строки деталей с этой проверкой? Естественно, что все должно работать корректно в случае многих пользователей, вносящих изменения одновременно.

Простейший пример:
мастер - одна строка
детали - две строки, относящиеся к одному мастеру

Есть два пользователя на разных клиентах. Оба видят, что деталей две и одновременно первый пользователь пытается удалить первую строку деталей, второй пользователь пытается удалить вторую строку деталей.
Хранимая процедура удаления запускается одновременно два раза. В результате не должно оказаться так, что в деталях не осталось ни одной строки. Какая из двух строк останется - неважно. Можно и обе оставить.

Какие будут идеи?

[ova]

Ну так поставить просто триггер на delete и в нем проверять.

[ova]

Если речь идет о MS SQL - то в нем как раз есть блокировка чтения и в данном случае это очень даже полезно.

[bvg]

Ну так поставить просто триггер на delete и в нем проверять.

Могу упростить ситуацию и гарантировать, что клиентская программа будет всегда вызывать хранимую процедуру для этой операции. Т.е. можно обойтись без триггера.

Основная проблема в том, что проверка не атомарная операция.

TableMaster
ID int (PK)
MasterData int

TableDetail
ID int (PK)
MasterID int (FK)
DetailData int


procedure:

@ParamDetailID int - заданный ID строки, которую пытаются удалить.

-- узнаем ID мастера
declare @VarMasterID int;
select @VarMasterID = MasterID
from TableDetail
where TableDetails.ID = @ParamDetailID;

-- считаем сколько строк в деталях
declare @VarCount int;
select @VarCount = count(*)
from TableDetails
where TableDetails.MasterID = @VarMasterID;

IF @VarCount > 1
BEGIN
delete from TableDetails
where ID = @ParamDetailID;
END;

Если эту процедуру запустят два клиента одновременно, как расписано раньше, то обе проверки покажут, что в деталях две строки и удалят их обе, т.к. собственно подсчет количества строк и удаление две разные операции, а не одна атомарная.

В идеале нужно что-то на уровне самой базы, как Identity, Primary Key, Foreign Key, unique index. Например, если задать unique index на какое-нибудь поле и пытаться вставить два одинаковых значения с двух клиентов одновременно, то вставится только одна строка, даже без всяких дополнительных проверок в процедуре. Вот какой-то похожий по надежности механизм и ищется.

[kyp]

в вашем случае мастером являетса ваш детаил.
referential integrity решает все проблемi m-1

[ova]

в вашем случае мастером являетса ваш детаил.
referential integrity решает все проблемi m-1

Интересная идея , foreign key наоборот...

[ova]

bvg,
Как я написал MS SQL имеет блокировку чтения, поэтому в тот момент когда процедура будет вызвана первый раз и сделает селект на количество строк в подчиненной таблице на этих строках повиснет блокировка, вторая процедура при ображения к тем же строкам повиснет на блокировке. Вот единственное, не уверен можно ли настроить клиента чтобы он не ждал освобождения блокировки а выкинул ошибку - тут не могу сказать, давно работал с MS SQL, может кто есть посвежее, в Оракле есть такое - NO WAIT
Upd - ага нашел, есть такое и в MS SQL.

[bvg]

в вашем случае мастером являетса ваш детаил.
referential integrity решает все проблемi m-1

В дополнение к нормальному referential integrity master-detail добавить еще один в обратную сторону? Мне же тогда ничего нового в мастер ни добавить ни удалить не удастся. Или я чего-то не понимаю.
Попробовал - не дает FK такой создать. Требует, чтобы MasterID был уникальный, а мне надо, чтобы деталей можно было иметь несколько.

[bvg]

bvg,
Как я написал MS SQL имеет блокировку чтения, поэтому в тот момент когда процедура будет вызвана первый раз и сделает селект на количество строк в подчиненной таблице на этих строках повиснет блокировка, вторая процедура при ображения к тем же строкам повиснет на блокировке. Вот единственное, не уверен можно ли настроить клиента чтобы он не ждал освобождения блокировки а выкинул ошибку - тут не могу сказать, давно работал с MS SQL, может кто есть посвежее, в Оракле есть такое - NO WAIT
Upd - ага нашел, есть такое и в MS SQL.

Т.е. хочешь сказать, что тот вариант процедуры будет работать корректно? Сейчас попробую....

[bvg]

bvg,
Как я написал MS SQL имеет блокировку чтения, поэтому в тот момент когда процедура будет вызвана первый раз и сделает селект на количество строк в подчиненной таблице на этих строках повиснет блокировка, вторая процедура при ображения к тем же строкам повиснет на блокировке. Вот единственное, не уверен можно ли настроить клиента чтобы он не ждал освобождения блокировки а выкинул ошибку - тут не могу сказать, давно работал с MS SQL, может кто есть посвежее, в Оракле есть такое - NO WAIT
Upd - ага нашел, есть такое и в MS SQL.

Вот, что я проверял. Может, тест некорректный?
В процедуре удаления я поставил задержку, чтобы подсчет количества строк выдал 2 при двух запусках и запустил в двух окнах одновременно.

USE [tempdb]
GO

/****** Object: Table [dbo].[TableMaster] Script Date: 05/10/2011 16:08:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TableMaster](
[ID] [int] IDENTITY(1,1) NOT NULL,
[MasterData] [int] NOT NULL,
CONSTRAINT [PK_TableMaster] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [tempdb]
GO

/****** Object: Table [dbo].[TableDetail] Script Date: 05/10/2011 16:09:26 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TableDetail](
[ID] [int] IDENTITY(1,1) NOT NULL,
[MasterID] [int] NOT NULL,
[DetailData] [int] NOT NULL,
CONSTRAINT [PK_TableDetail] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[TableDetail] WITH CHECK ADD CONSTRAINT [FK_TableDetail_TableMaster] FOREIGN KEY([MasterID])
REFERENCES [dbo].[TableMaster] ([ID])
GO

ALTER TABLE [dbo].[TableDetail] CHECK CONSTRAINT [FK_TableDetail_TableMaster]
GO


USE [tempdb]
GO

/****** Object: StoredProcedure [dbo].[DeleteDetail] Script Date: 05/10/2011 16:09:40 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[DeleteDetail]
-- Add the parameters for the stored procedure here
@ParamDetailID int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

BEGIN TRANSACTION;
BEGIN TRY

DECLARE @VarMasterID int;
SELECT @VarMasterID = MasterID
FROM dbo.TableDetail
WHERE ID = @ParamDetailID;

PRINT 'MasterID = ' + CAST(ISNULL(@VarMasterID, 0) as nvarchar(10));

PRINT 'Delay 10 seconds';
WAITFOR DELAY '00:00:10'; -- hh:mm:ss

DECLARE @VarItemCount int;
SELECT @VarItemCount = COUNT(*)
FROM dbo.TableDetail
WHERE MasterID = @VarMasterID;

PRINT 'Item count = ' + CAST(ISNULL(@VarItemCount, 0) as nvarchar(10));

PRINT 'Delay 10 seconds';
WAITFOR DELAY '00:00:10'; -- hh:mm:ss

IF @VarItemCount > 1
BEGIN
PRINT 'Deleting...';
DELETE FROM dbo.TableDetail
WHERE ID = @ParamDetailID;
END;

PRINT 'Committing...';
COMMIT TRANSACTION;

END TRY
BEGIN CATCH
PRINT 'Catch...';
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;

ROLLBACK TRANSACTION;
END CATCH;

END

GO


В мастер добавил одну строку с ID=1
В детали добавил две строки с ID=1 и 2, MasterID=1 для обоих строк.

В одном окне:
EXEC @return_value = [dbo].[DeleteDetail]
@ParamDetailID = 1

В другом окне:
EXEC @return_value = [dbo].[DeleteDetail]
@ParamDetailID = 2

Запускаю вручную почти одновременно.

Результат одинаков для обоих окон:

MasterID = 1
Delay 10 seconds
Item count = 2
Delay 10 seconds
Deleting...
Committing...

(1 row(s) affected)

В TableDetail ни одной строки не осталось.
Насколько я понимаю, select count(*) может и блокирует удаление в момент выполнения select, но после завершения select уже можно удалять, что и произошло. Может, нужно в явном виде указывать какие-нибудь специальные transaction isolation level? Или явно заблокировать всю таблицу TableDetails в самом начале транзакции, тогда вторая транзакция просто не станет выполняться. Да, такое должно сработать. Как-то не догадался. Просто exclusive lock на всю таблицу деталей.
Спасибо.

[ova]

bvg,
Так я ж говорю, что транзакцию нужно запускать с NOWAIT, т.е. чтобы не ждала сняти блокировки, а откатывала. Понятно если будет ждать снятия блокировки, том потом успешно пойдет дальше.

[zb0n3]

У вас проблемы с дизайном БД

[bvg]

У вас проблемы с дизайном БД

Возможно. И как лучше это сдизайнить?
Задача довольно типовая.
Есть набор каких-то сущностей (инвойсов, контрактов, запчастей, что угодно) - таблица мастер. Для каждого экземпляра сущности есть ее история - дата события, комментарии пользователя, статус (инвойс создан, инвойс выслан, инвойс получен, оплачен и т.д.) - таблица деталей.
Пользователь может добавлять новые записи в историю и удалять существующие.
И я хочу, чтобы история не смогла стать пустой, чтобы хотя бы одна запись для каждого мастера была всегда.

Как тут подсказали, явное задание блокировок дает нужный эффект. Мне раньше не приходилось с блокировками работать, поэтому и не сообразил, что их тут можно применить.

[zb0n3]

Вам не надо ничего удалять. Сделайте себе колонку "Enabled" и выставляйте его в 0 либо 'N', когда вам какая-то строка не нужна. И не отображайте её в фронтенде.
Вы задачу стали не с того конца решать.

[bvg]

Вам не надо ничего удалять. Сделайте себе колонку "Enabled" и выставляйте его в 0 либо 'N', когда вам какая-то строка не нужна. И не отображайте её в фронтенде.
Вы задачу стали не с того конца решать.

Ну простое введение этого флага не решает проблему, что два пользователя могут одновременно сбросить этот флаг и в результате не останется ни одной строки видимой пользователю. Конечно, т.к. физически данные все еще на месте, то потом можно делать дополнительную проверку, не случилось ли такое событие и восстанавливать одну из строк.
Да, решение вполне рабочее.