July 18, 2012 at 8:38 am
In addition to that, it is a fact that with a unique constraint on the column, it is IMPOSSIBLE for there to be duplicate account numbers. That's the point of a constraint.
Jared
CE - Microsoft
July 18, 2012 at 8:43 am
Gerard,
What platform is your experience with?
BY default SQL Server does pessimistic locking. So, within a transaction it will lock what it needs to lock to maintain consistency. So when an insert is done SQL Server will lock what it needs to keep a another insert from happening. So in the case of 2 processes trying to insert a row for the same customer number, one must go first, thus taking exclusive locks as needed to keep another process from making the same change, when the insert is completed the locks will be released and the next transaction will go to make the insert and fail. This is why I'd use MERGE. In the IF..ELSE construct there is a very small chance that the 2 processes could both run the IF EXISTS close enough together that both process could attempt an insert, then, without the unique constraint, you could get duplicates, and with the unique constraint you would get an error. With MERGE you don't have the 2 steps, so the second MERGE would be blocked by the first MERGE. The first MERGE would insert a row and the second MERGE would UPDATE that row.
You still want to define the unique constraint.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 18, 2012 at 8:50 am
Jack Corbett (7/18/2012)
Gerard,What platform is your experience with?
BY default SQL Server does pessimistic locking. So, within a transaction it will lock what it needs to lock to maintain consistency. So when an insert is done SQL Server will lock what it needs to keep a another insert from happening. So in the case of 2 processes trying to insert a row for the same customer number, one must go first, thus taking exclusive locks as needed to keep another process from making the same change, when the insert is completed the locks will be released and the next transaction will go to make the insert and fail. This is why I'd use MERGE. In the IF..ELSE construct there is a very small chance that the 2 processes could both run the IF EXISTS close enough together that both process could attempt an insert, then, without the unique constraint, you could get duplicates, and with the unique constraint you would get an error. With MERGE you don't have the 2 steps, so the second MERGE would be blocked by the first MERGE. The first MERGE would insert a row and the second MERGE would UPDATE that row.
You still want to define the unique constraint.
+1 The merge would clearly be the better option. I just wanted to demonstrate the concept with something familiar in other versions. Although, the same could be accomplished by placing the IF ... ELSE within a transaction, no? I mean, it would then wait for the complete transaction to finish? Again, not a better solution than a MERGE, I'm just trying to get a better understanding of the internals.
Jared
CE - Microsoft
July 18, 2012 at 8:51 am
gerard-593414 (7/18/2012)
OK thanks for that.But I dont understand how the database Engine will prevent (without additional error checking or rules) TWO ACCOUNTS WITH THE SAME NUMBER BEING ADDED. Two selects 'COULD' be done at the same time and nothing will be found so both will try to add the same account..... is there something I am missing.
I know this would be unusual but I need to be 110% sure that two accounts will not get on there with the same number.
After all, is this not the reason there are locking mechanisms there in the first place.
I just tested a modified version of SqlKnowitall's routine with a delay between the "IF EXISTS" and the INSERT and the routine fails to run properly under concurrent usage. I would not use routines like these that failed just because they ran slow. If many instances of this ran under a loaded machine, they may run slower too even without embedded delay statements. Any routine I put together must past the concurrency test even with embedded delay statements, just to make sure I don't create rare but substantial issues with mistaken updates.
July 18, 2012 at 8:58 am
patrickmcginnis59 (7/18/2012)
gerard-593414 (7/18/2012)
OK thanks for that.But I dont understand how the database Engine will prevent (without additional error checking or rules) TWO ACCOUNTS WITH THE SAME NUMBER BEING ADDED. Two selects 'COULD' be done at the same time and nothing will be found so both will try to add the same account..... is there something I am missing.
I know this would be unusual but I need to be 110% sure that two accounts will not get on there with the same number.
After all, is this not the reason there are locking mechanisms there in the first place.
I just tested a modified version of SqlKnowitall's routine with a delay between the "IF EXISTS" and the INSERT and the routine fails to run properly under concurrent usage. I would not use routines like these that failed just because they ran slow. If many instances of this ran under a loaded machine, they may run slower too even without embedded delay statements. Any routine I put together must past the concurrency test even with embedded delay statements, just to make sure I don't create rare but substantial issues with mistaken updates.
Obviously not everyone is as picky as me :w00t:
I totally agree... Again, I was just trying to demonstrate the concepts. Error handling should always be implemented to catch extreme cases, IF EXISTS can really be a problem without a proper index or gaps, etc. The MERGE should prove to be the best method here, I think :hehe:
That being said, from a design standpoint I would insert a row into this table with a 0 balance as soon as the account was created, thereby removing the need to ever check for the existence. The fact that there is an account number would mean that a record exists. To take that even 1 step further.... Sine there is only 1 record per account in this table and (assumingly) 1 record per account in an account master table, it makes no sense to me to store this in a separate table. I would store the current balance along with the account record and store all individual debits/credits against that balance in its own table.
Jared
CE - Microsoft
July 18, 2012 at 9:32 am
SQLKnowItAll (7/18/2012)
Am I missing something? Probably... :w00t:
Nah... it's me. Well, kind of... Original Post asked for...
I have a table which holds the next Invoice Number. What SP code should I use to increment it
That doesn't sound anything like the current problem of account number and balance. It sounds like the OP wanted a "NextID" proc for a sequence table. Not the current problem of account and continuous balance.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 18, 2012 at 9:37 am
Ok many thanks for all the contributions.
So. I think we are down to the following options :
1. Use Locks during the update with also a constraints rule
or
2. Have a 'Constraint Rule' and do error trapping
Re having ALL customer Details in one table, the main reason I do not go for this methodolgy in system design is that most other fields will be changed by a User (e.g. say Name ,Tel , Adddress) and If I have stuff in there that is being contilaully changed by Transaction processing, I would have to ensure that if a user changes any data, when I am updating I need to check that another transaction has not got in there from the start of the user seeing the Customer details to when he 'Updates', or else exclude the balance from any of these updates. it seems cleaner to me to keep Balance type info away from the main details
July 18, 2012 at 9:38 am
Jeff Moden (7/18/2012)
SQLKnowItAll (7/18/2012)
Am I missing something? Probably... :w00t:Nah... it's me. Well, kind of... Original Post asked for...
I have a table which holds the next Invoice Number. What SP code should I use to increment it
That doesn't sound anything like the current problem of account number and balance. It sounds like the OP wanted a "NextID" proc for a sequence table. Not the current problem of account and continuous balance.
He also posted "I want to do similar for updating a Balance"
The common thread was updating a column in a concurrent environment and how to insure that we update without blowing away another processes update.
July 18, 2012 at 9:44 am
Ok. so how to enter Constraints ?
I checked SSMS and could not see an option for 'Constraints' where should I be looking ?
July 18, 2012 at 9:55 am
Ok many thanks for all the contributions.
So. I think we are down to the following options :
1. Use Locks during the update with also a constraints rule
or
2. Have a 'Constraint Rule' and do error trapping
Re having ALL customer Details in one table, the main reason I do not go for this methodolgy in system design is that most other fields will be changed by a User (e.g. say Name ,Tel , Adddress) and If I have stuff in there that is being contilaully changed by Transaction processing, I would have to ensure that if a user changes any data, when I am updating I need to check that another transaction has not got in there from the start of the user seeing the Customer details to when he 'Updates', or else exclude the balance from any of these updates. it seems cleaner to me to keep Balance type info away from the main details
Put in the constraint first. Irregardless of the procedure used, your table definition should always enforce the integrity of the data in case someone like a developer tries to manually insert a row, you don't want duplicates. Then handle the error in a sense where the application just sends it again on error. If it fails the first time, it will certainly update the second. To be honest, I have never had this issue even with 100 employees constantly updating records. Usually, the delay in the application to the SQL Server is more than enough time to eliminate any chance that the 2 transactions will occur at EXACTLY the same time which "may" cause an error if the one that is processed first has a delay for some reason.
However, since you are against keeping the data in the same table (I still don't completely understand your concern; i.e. if I look at a customer's screen and 30 seconds later someone else in the company updates their name, it will not automatically update on my screen. As long as nobody is updating the account number, it will never really be an issue...) Why not always insert a record with a 0 balance into the table when the account is created. Then you never have to check for the existence, you just update.
Jared
CE - Microsoft
July 18, 2012 at 9:59 am
A constraint is a unique index.
You can do it in your create table statement: CREATE TABLE accountBalance (accountNo BIGINT PRIMARY KEY --CLUSTERED or NONCLUSTERED
, accountBalance)
You can create a PRIMARY KEY after the table is created.
You can create a unique index after the table is created.
I would choose 1 of the first 2 options... Enforcing a primary key.
Jared
CE - Microsoft
July 18, 2012 at 12:15 pm
SQLKnowItAll (7/18/2012)
Jeff Moden (7/18/2012)
SQLKnowItAll (7/18/2012)
Am I missing something? Probably... :w00t:Nah... it's me. Well, kind of... Original Post asked for...
I have a table which holds the next Invoice Number. What SP code should I use to increment it
That doesn't sound anything like the current problem of account number and balance. It sounds like the OP wanted a "NextID" proc for a sequence table. Not the current problem of account and continuous balance.
Put in the constraint first. Irregardless of the procedure used, your table definition should always enforce the integrity of the data in case someone like a developer tries to manually insert a row, you don't want duplicates. Then handle the error in a sense where the application just sends it again on error. If it fails the first time, it will certainly update the second. To be honest, I have never had this issue even with 100 employees constantly updating records. Usually, the delay in the application to the SQL Server is more than enough time to eliminate any chance that the 2 transactions will occur at EXACTLY the same time which "may" cause an error if the one that is processed first has a delay for some reason.
However, since you are against keeping the data in the same table (I still don't completely understand your concern; i.e. if I look at a customer's screen and 30 seconds later someone else in the company updates their name, it will not automatically update on my screen. As long as nobody is updating the account number, it will never really be an issue...) Why not always insert a record with a 0 balance into the table when the account is created. Then you never have to check for the existence, you just update.
I believe you quoted the wrong post.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 18, 2012 at 12:32 pm
Jeff Moden (7/18/2012)
I believe you quoted the wrong post.
Doh! (or should I say Bwaa ha ha!) This site certainly does some crazy things when you reply and others are posting... I'll go back and fix it 😎
Jared
CE - Microsoft
Viewing 13 posts - 46 through 57 (of 57 total)
You must be logged in to reply to this topic. Login to reply