May 2, 2011 at 9:40 am
I have some constraints which span across multiple tables.
I am wondering weather a proactive or a reactive T-SQL approach in SP is the way to go.
The following SP's are pseudo like.
Reactive SP:
CREATE PROC dbo.InsertCompany(@Id INT,@Name VARCHAR(20),@GeneralLedgerAccountId INT,@OmnibusAccountId INT,@OtherAccounts XML)
AS
BEGIN
BEGIN TRANSACTION
INSERT
INTO Companies(Id, Name, GeneralLedgerAccountId, OmnibusAccountId)
VALUES (@Id, @Name, @GeneralLedgerAccountId, @OmnibusAccountId)
WHERE NOT EXISTS(
SELECT *
FROM Accounts
WHERE GeneralLedgerAccountId = @GeneralLedgerAccountId AND
AccountType != 2 OR
OmnibusAccountId= @OmnibusAccountId AND
AccountType != 6)
IF @@ROWCOUNT = 0
BEGIN
-- find error type here
-- Check General Ledger account
IF EXISTS(SELECT *
FROM Accounts
WHERE GeneralLedgerAccountId = @GeneralLedgerAccountId AND
AccountType != 2)
BEGIN
RAISERROR('General Ledger account can not be of type ...", 16, 1)
ROLLBACK
RETURN
END
-- Check Omnibus account similar to general ledger account
............
END
-- Further process OtherAccountsXml
INSERT INTO .......
IF FAIL ROLLBACK
ELSE COMMIT
END
Proactive SP:
CREATE PROC dbo.InsertCompany(@Id INT,@Name VARCHAR(20),@GeneralLedgerAccountId INT,@OmnibusAccountId INT,@OtherAccounts XML)
AS
BEGIN
BEGIN TRANSACTION
DECLARE @AccountType INT
SELECT @AccountType = AccountType
FROM Accounts WITH(REPEATABLEREAD)
WHERE GeneralLedgerAccountId = @GeneralLedgerAccountId
IF @AccountType != 2
BEGIN
RAISERROR('General Ledger account can not be of type ...", 16, 1)
ROLLBACK
RETURN
END
-- Check Omnibus account similar to general ledger account
............
INSERT
INTO Companies(Id, Name, GeneralLedgerAccountId, OmnibusAccountId)
VALUES (@Id, @Name, @GeneralLedgerAccountId, @OmnibusAccountId)
-- Further process OtherAccountsXml
INSERT INTO .......
IF FAIL ROLLBACK
ELSE COMMIT
END
What is the correct way to go, or is there a better way?
May 2, 2011 at 5:34 pm
Both!
It is always best to perform validation first to ensure the values coming from the application or elsewhere are 'clean' and in the expected form. Then, only proceed if things look good.
However, it is also just as wise to throw in good error handling code so when the UNEXPECTED occurs, you can handle it nicer, log errors in table or do whatever other processing you desire. Your proactive query didn't have any error handling around the INSERT statement.
So, in a perfect world with unlimited time and budget, do both.
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
May 3, 2011 at 8:02 am
Yes, I would definitly have to add some error checking after the insert statement.
The reason why I ask is because i am reading up on the subject and scalability seems to be the big issue these days. Seeing it in the bigger picture, I was wondering if the proactive approach doesn't lean towards too much locking...?
May 3, 2011 at 8:14 am
That's a pretty good point. Looking at your query closer, it would probably be better to move that begin tran to be just before the insert.
You want to start the tran as late as possible and commit it as soon as possible. So rarely would you need to start the tran before the input validation.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
May 3, 2011 at 9:53 am
Would moving the trans not create an integrety problem in that someone could update the AccountType in between the checking and inserting?
I tried to circumvent this by adding the locking hing repeatableread.
May 3, 2011 at 9:59 am
Lasse Johansen (5/3/2011)
Would moving the trans not create an integrety problem in that someone could update the AccountType in between the checking and inserting?I tried to circumvent this by adding the locking hing repeatableread.
Yes it's possible. I've even seen this within a 5 user environement (talk about lock luck).
In this case I was enforcing a weird unique requirement and couldn't use keys.
The way I solved it then was to do this.
INSERT INTO tbl (columns)
SELECT @Lis of parameters WHERE NOT EXISTS (SELECT * FROM tbl WHERE Col = @Param).
In this case the validation AND insert are done in 1 step so nothing could interfere.
Maybe then I could still get screwed if both select ran at exactly the same time. But it never hapenned.
I'm not well versed in locking and transaction isolations so I'll step aside on that part of the question.
May 3, 2011 at 10:56 am
SSChampion>>
Theoretically, the possibility exists even with only 2 users in a system. Shouldn't one assure that this cannot happen, or what is best practice? It looks to me that integrety comes at a high cost of scalability when multitable constraints are involved.
Your update statement is using the reactive approach. I agree that this one maintains 100% integrety.
>>Maybe then I could still get screwed if both select ran at exactly the same time. But it never hapenned. >>
I am not sure what you mean...
A side note: One should add the "repeatableread" locking hint to the reactive approach update statement if 100% correct error codes should be returned.
May 3, 2011 at 11:01 am
I think I'm using more of a proactive approach that reactive.
As I said earlier I don't understand isolation levels and locking enough to make recommendations so I won't say anything else there.
The real solution is to use constraints if possible (check, unique keys). Trigger for final validate if the rest is impossible.
That particular case I had years ago was one where I was stuck using a trigger to validate, but I wanted to precheck to make sure.
Then of course you still need error handling in case it fails anyways. that way all your bases are covered and your butt too!
May 3, 2011 at 11:57 am
Lasse Johansen (5/3/2011)
It looks to me that integrety comes at a high cost of scalability when multitable constraints are involved.
Yep. You are right. It is a sliding scale between integrity and concurrency and where the 'proper' balance is depends on your environment, concurrency needs and usage patterns. Most people can usually at least do some simple validation of input parameters outside of a transaction. Once you start hitting tables, your environment may call for starting the tran before touching any tables. This depends on what you are looking up. If you are looking up a State or Province, then it may not be necessary to start your tran to include that, since it is rare (but still possible) that an error will occur. Someone else may determine any risk to be too high. Again, proper error handling will still catch and allow you to report the issue to the end user, but how user-understandable that error is going to be often depends on upfront validation errors (which you control the message to), rather than SQL errors which are caught (and are typically scary and 'encrypted' from an end user perspective).
Ninja is right in also having additional validation in triggers/constraints, etc. This 'centralizes' your validation. But this is not the only place that validation should exist. Think SQL Injection. Input validation is required as well before anything hits a table.
This is where no one can make a recommendation for you directly. A 'best practice' depends on what you are trying to achieve. In MOST cases, integrity (ACID) is the highest goal, so it may be wise to yield on the side of caution and start your transactions as early AS YOU NEED. When I mentioned before to start your transaction as late as possible, I meant just that - as possible. Or you, that may mean starting the tran before the first hit to a table, but for someone else, they may be able to tolerate looking up a few values and starting the transaction a bit later, confident that constraints/triggers will report very rare concurrency issue.
An example of ACID NOT being the top priority might be Google in their data center. They care less about the integrity of their results - speed and concurrency is most important. If the results are not perfect or the site data they troll is not perfect, it doesn't really matter to most folks. Therefore, they don't need transactions at all, nor Write Ahead Logging - just jam in the data and hope it sticks and if the insert/save fails, oh well, it'll be cleaned up the next time trolling that web site (tomorrow?). Note: This may not exactly describe Google's methodology or attitude toward data integrity, it just serves as a hyperbole to show that not everyone's needs are the same.
I hope this answers more questions than it raises.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
May 3, 2011 at 2:55 pm
Yes, I guess it makes sense to consider the context when making the choice concurrency vs integrety.
Thanks for the help guys, much appreciated.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply