September 23, 2009 at 8:49 am
Here is the scenario:
I have members belonging to a community, at least one member of a community is an admin.
This scenario requires 2 tables MEMBER and COMMUNITY. There is several members and several communities but a member belongs to only one community.
in table MEMBER, I have Id_Member, Id_Community and Is_admin (bit)
Now I need to assure there is at least one admin in a community so I must not delete a member with is_admin = true if there is no other member with Is_admin = true within the community.
Looks pretty simple but my concern is concurrent delete. Let's say I have two members is_admin = true in community A (member 1 and member 2) and two differents apps user delete each of the admin at the same time.
So the goal is to allow one deletion (because there is still another admin) and not allow the other deletion because it's the only admin remaining.
My question now. Should I have one store proc checking the admin count and if > 2 then delete else returning something to inform no deletion as been made.
or
should I have two differents store proc, one checking the count, the other doing the deletion and my app should call the first one, and call the second one only if it should?
thank you
Martin
September 23, 2009 at 9:23 am
You should write only one SP and check for this condition before deleting. Set proper isolation level in your sp and sql server should be able to handle your concurrency issues. Thanks.
---------------------------------------------------------------------------------
September 23, 2009 at 9:33 am
Thank you for your answer
My knowledge of T-SQL allow me to write two separates Stored proc, but when it's time to merge them I am lost. I even don't know what is "isolation level" or what to return and how to return an information stating the deletion wasn't done.
So I would be please if someone can share his knowledge.
Thank you
Martin
September 23, 2009 at 9:55 am
SQL server handles concurrency for you automatically, even if your two users were to try to delete each other at the EXACT same millisecond, SQL will determine that one of them goes first, locks the data as required, finishes their transaction, and only then would the second person try to get to delete.
if you can give the actual schema of your two tables, it would be easier to help...
if you post that, as well as your prototype delete statement, we can help tweak your command for you.
you will need just a single proc that does the delete, and it will simply feature DELETE FROM, syntax, so it automatically checks what to delete based on the joins and other conditions.
Lowell
September 23, 2009 at 10:35 am
Ok, here is my two queries. My app call the first one, and if it returns a value greater then 1 my app call the second one.
SELECT COUNT(*) FROM MEMBER
WHERE ID_COMMUNITY = @ID_COMMUNITY
AND IS_ADMIN = 1
DELETE MEMBER WHERE ID_MEMBER = @ID_MEMBER
Then I thought it was not efficient the way I do it so I open a thread here.
I have talk about concurrency but it's not real concurrency. If there is 2 admin (member1 and member2) in community A and someone delete member1 and at the same time another user delete member2 (they are not deleting the same member but both members are is_admin = true). then I must accept one delete and decline the second one because I need to preserve at least one admin within community A.
thank you
Martin
September 23, 2009 at 12:08 pm
dubem1-878067 (9/23/2009)
Ok, here is my two queries. My app call the first one, and if it returns a value greater then 1 my app call the second one.Then I thought it was not efficient the way I do it so I open a thread here.
I have talk about concurrency but it's not real concurrency. If there is 2 admin (member1 and member2) in community A and someone delete member1 and at the same time another user delete member2 (they are not deleting the same member but both members are is_admin = true). then I must accept one delete and decline the second one because I need to preserve at least one admin within community A.
thank you
Martin
I would suggest you do the "count check" and delete in the same procedure and in one transaction? (ofcourse if it permits. If your application is doing that way already, cant help it)
Now when you are doing that one transaction from one connection from your application, even if a new connection is established, I think that will wait till this transaction is complete. According to your "count check", you should return proper message from your sp.
Hope this helps.
---------------------------------------------------------------------------------
September 23, 2009 at 1:12 pm
i kinda need some sample data to see if i have the logic down right;
ok, here is how i would do it:
i have a sub select below of every "admin" for every community, which excludes the member id to be deleted.
by joining the current member to that sub table, if the member in quesiton belongs to all the groups that have an admin, you could delete him; if he is an admin in any community that is not on the list(the left join), the SELECT would not find him, therefor, he could not be deleted.
so in a single, kinda complex statement, you are testing whether you can delete member 20 or not; by uncommenting the DELETE MyMemberAlias and commenting the SELECT *,
i believe you have the single-statment-delete you were looking for.
declare @ID_MEMBER int
SET @ID_MEMBER=20
--DELETE MyMemberAlias
SELECT *
FROM MEMBER MyMemberAlias
INNER JOIN COMMUNITY ON MEMBER.Id_Community = COMMUNITY.Id_Community
LEFT OUTER JOIN
--'admins in each community that are NOT the guy you wanna delete
(SELECT
COMMUNITY.ID_MEMBER,
COMMUNITY.Id_Community,
MEMBER.Is_admin
FROM COMMUNITY
INNER JOIN MEMBER
ON COMMUNITY.Id_Community = MEMBER.Id_Community
AND MEMBER.Is_admin = 1
WHERE MEMBER.ID_MEMBER @ID_MEMBER) AllCommunitiesWithAdmins
ON COMMUNITY.Id_Community= AllCommunitiesWithAdmins.Id_Community
WHERE MyMemberAlias.ID_MEMBER = @ID_MEMBER
AND MyMemberAlias.Is_admin = 1 --must be an admin for the community(ies) in question
AND AllCommunitiesWithAdmins.Id_Community IS NULL --if the join is not found on both sides
Lowell
September 23, 2009 at 1:14 pm
to clarify, if the above SELECT returns a row, you were allowed to delete the member, if it does not, noone would be deleted.
Lowell
September 23, 2009 at 2:10 pm
Thank you, look interresting but my problem is simpler than you think.
A member belongs to only one community
here is an exemple with data.
Let's say I have 3 communities A, B and C
and 5 members m1, m2, m3, m4, m5. all five are admin
m1 belongs to A
m2 belongs to A
m3 belongs to B
m4 belongs to B
m5 belongs to C
Then I have two users using my app and they each does a delete action at the same moment.
Scenario 1
user1 delete m5
user2 delete m3
conclusion : m5 is not deleted because he is the only admin in his community (C), m3 is deleted because there is another admin his community (B)
Scenario 2 (not a after scenario 1, it's another scenario, consider scenario 1 has not happens)
User1 delete m1
user2 delete m2
Conclusion : if user1 was a bit faster then user2, action of user1 is done first so m1 is deleted because there is another admin in his community (A) and m2 isn't delete because he is now the only admin in his community (A) since m1 has just been deleted.
I don't think I need any join on the community table, I just want to not delete a member if he is the only admin remaining for his belonging community. The member table contains the ID_COMMUNITY.
Martin
September 23, 2009 at 3:58 pm
ok, again, if you had provided the full DDL of the tables and sample data, this would have been easier...i think you changed from id_Member being an int to being a varchar m1 as you were trying to describe scenarios....but here goes.
with a slight modification, my script works exactly as i was trying to describe.
here's the tables and sample data...
at the end are 4 delete statements. for scnario1 (delete m5 then m3), for the m5 guy who is the only admin, nothing fails, but nothing is deleted either, because the JOIN conditions excluded him. that's what is so cool about using the DELETE FROM syntax...it's doing your test and the delete all in one query statement.
in your scenario 2, the same thing occurs, first m1 is deleted, them m2 is attempted to delete, but because he's the only admin, his criteria prevents his deletion.
try this and see:
CREATE TABLE COMMUNITY(
Id_Community varchar(30) )
INSERT INTO COMMUNITY(Id_Community)
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C'
CREATE TABLE MEMBER(
Id_Member varchar(30),
Id_Community varchar(30),
Is_admin bit default 0 )
INSERT INTO MEMBER(Id_Member,Id_Community,Is_admin)
SELECT 'm1','A',1 UNION ALL
SELECT 'm2','A',1 UNION ALL
SELECT 'm3','B',1 UNION ALL
SELECT 'm4','B',1 UNION ALL
SELECT 'm5','C',1
/*m1 belongs to A
m2 belongs to A
m3 belongs to B
m4 belongs to B
m5 belongs to C
*/
--Scenario 1:delete m5,then delete m3
declare @ID_MEMBER varchar(30)
SET @ID_MEMBER='m5'
DELETE MyMemberAlias
--SELECT *
FROM MEMBER MyMemberAlias
INNER JOIN COMMUNITY ON MyMemberAlias.Id_Community = COMMUNITY.Id_Community
LEFT OUTER JOIN
--'admins in each community that are NOT the guy you wanna delete
(SELECT
MEMBER.ID_MEMBER,
MEMBER.Id_Community,
MEMBER.Is_admin
FROM MEMBER
WHERE MEMBER.Is_admin = 1
AND MEMBER.ID_MEMBER @ID_MEMBER) AllCommunitiesWithAdmins
ON MyMemberAlias.Id_Community= AllCommunitiesWithAdmins.Id_Community
WHERE MyMemberAlias.ID_MEMBER = @ID_MEMBER
AND MyMemberAlias.Is_admin = 1 --must be an admin for the community(ies) in question
AND AllCommunitiesWithAdmins.Id_Community IS NOT NULL --if the join is not found on both sides
--Scenario 1:delete m5,then delete m3
SET @ID_MEMBER='m3'
DELETE MyMemberAlias
--SELECT *
FROM MEMBER MyMemberAlias
INNER JOIN COMMUNITY ON MyMemberAlias.Id_Community = COMMUNITY.Id_Community
LEFT OUTER JOIN
--'admins in each community that are NOT the guy you wanna delete
(SELECT
MEMBER.ID_MEMBER,
MEMBER.Id_Community,
MEMBER.Is_admin
FROM MEMBER
WHERE MEMBER.Is_admin = 1
AND MEMBER.ID_MEMBER @ID_MEMBER) AllCommunitiesWithAdmins
ON MyMemberAlias.Id_Community= AllCommunitiesWithAdmins.Id_Community
WHERE MyMemberAlias.ID_MEMBER = @ID_MEMBER
AND MyMemberAlias.Is_admin = 1 --must be an admin for the community(ies) in question
AND AllCommunitiesWithAdmins.Id_Community IS NOT NULL --if the join is not found on both sides
--Scenario 2 delete m1, then m2
SET @ID_MEMBER='m1'
DELETE MyMemberAlias
--SELECT *
FROM MEMBER MyMemberAlias
INNER JOIN COMMUNITY ON MyMemberAlias.Id_Community = COMMUNITY.Id_Community
LEFT OUTER JOIN
--'admins in each community that are NOT the guy you wanna delete
(SELECT
MEMBER.ID_MEMBER,
MEMBER.Id_Community,
MEMBER.Is_admin
FROM MEMBER
WHERE MEMBER.Is_admin = 1
AND MEMBER.ID_MEMBER @ID_MEMBER) AllCommunitiesWithAdmins
ON MyMemberAlias.Id_Community= AllCommunitiesWithAdmins.Id_Community
WHERE MyMemberAlias.ID_MEMBER = @ID_MEMBER
AND MyMemberAlias.Is_admin = 1 --must be an admin for the community(ies) in question
AND AllCommunitiesWithAdmins.Id_Community IS NOT NULL --if the join is not found on both sides
SET @ID_MEMBER='m2'
DELETE MyMemberAlias
--SELECT *
FROM MEMBER MyMemberAlias
INNER JOIN COMMUNITY ON MyMemberAlias.Id_Community = COMMUNITY.Id_Community
LEFT OUTER JOIN
--'admins in each community that are NOT the guy you wanna delete
(SELECT
MEMBER.ID_MEMBER,
MEMBER.Id_Community,
MEMBER.Is_admin
FROM MEMBER
WHERE MEMBER.Is_admin = 1
AND MEMBER.ID_MEMBER @ID_MEMBER) AllCommunitiesWithAdmins
ON MyMemberAlias.Id_Community= AllCommunitiesWithAdmins.Id_Community
WHERE MyMemberAlias.ID_MEMBER = @ID_MEMBER
AND MyMemberAlias.Is_admin = 1 --must be an admin for the community(ies) in question
AND AllCommunitiesWithAdmins.Id_Community IS NOT NULL --if the join is not found on both sides
Lowell
September 23, 2009 at 6:29 pm
Thanks lowell
I don't think I would have imagine a solution like yours, it's brilliant
But I would like to have your opinion on this query assuming I know the ID_COMMUNITY of the user I want to delete and I can pass two parameters to the SP, the ID_MEMBER and the ID_COMMUNITY of the MEMBER
IF (SELECT COUNT(*) FROM Member WHERE ID_COMMUNITY = @ID_COMMUNITY ) > 1
DELETE MEMBER WHERE ID_MEMBER = @ID_MEMBER
I think it does the job, what do you think?
Martin
September 23, 2009 at 7:12 pm
dubem1-878067 (9/23/2009)
Thanks lowellI don't think I would have imagine a solution like yours, it's brilliant
But I would like to have your opinion on this query assuming I know the ID_COMMUNITY of the user I want to delete and I can pass two parameters to the SP, the ID_MEMBER and the ID_COMMUNITY of the MEMBER
IF (SELECT COUNT(*) FROM Member WHERE ID_COMMUNITY = @ID_COMMUNITY ) > 1
DELETE MEMBER WHERE ID_MEMBER = @ID_MEMBER
I think it does the job, what do you think?
Martin
You're not actually validating that the @MEMBER is part of that @community. The only thing you IF statement does is to check to see if there are more than one member in a given community.
Try this:
DELETE from MEMBER
WHERE ID_MEMBER = @ID_MEMBER
and ID_COMMUNITY in (select ID_COMMUNITY
from member
where ID_COMMUNITY =@id_community
group by ID_community
HAVING count(*)>1
)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply