July 17, 2012 at 7:26 am
But how do you know that it is really the same account? How is the customer number created?
I think what you really want is MERGE. IN a nutshell MERGE does an UPDATE if the key value exists and an INSERT if the key value does not exist.
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 17, 2012 at 7:44 am
The AccountNum field will identify the account. This will have come from an Customer master table and the table I am updating is a Balances Table (Just to hold balances ) I like to keep the Master Table data separate from the balances table
I will have a look at the MERGE command..seems like it is what I am looking for alright...do you know if this automatically gauards against duplicate rows (or do you still have to code around this with Error genration or Locking or Isolation levels ?)
July 17, 2012 at 9:23 am
gerard-593414 (7/17/2012)
The AccountNum field will identify the account. This will have come from an Customer master table and the table I am updating is a Balances Table (Just to hold balances ) I like to keep the Master Table data separate from the balances tableI will have a look at the MERGE command..seems like it is what I am looking for alright...do you know if this automatically gauards against duplicate rows (or do you still have to code around this with Error genration or Locking or Isolation levels ?)
MERGE should do the job as one transaction must get in first and obtain locks that will be incompatible with any other modification statement. So the second transaction would do an update not an insert, if coded to work that way.
It also seems like the design should have some kind of details row for the customer, then the process could roll-up detail rows into a summary row for the customer balances.
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 17, 2012 at 9:40 am
Ok, I will have a look at the Merge. Thanks for your help
Re design, yes there is a details row for the customer, but for quick reports and enquiries, I need the total in one place, otherwise I will have to do a select out from perhaps thousands of transactions each tme I need a customer balance, and I reckon this is going to be too inefficient
July 17, 2012 at 9:56 am
Sure that's a possibility. Depending on indexing summing the totals for a customer might be acceptable and might be a lesser load on the server than adding a step to each detail transaction to update or insert a summary row. You might also consider a job that does the aggregation on a schedule as well, depending on the business needs.
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 17, 2012 at 11:30 am
gerard-593414 (7/17/2012)
The AccountNum field will identify the account. This will have come from an Customer master table and the table I am updating is a Balances Table (Just to hold balances ) I like to keep the Master Table data separate from the balances tableI will have a look at the MERGE command..seems like it is what I am looking for alright...do you know if this automatically gauards against duplicate rows (or do you still have to code around this with Error genration or Locking or Isolation levels ?)
The merge will not guard against anything, you define that as a unique constraint on the table. What the MERGE does is say "does the account exist? if so, update it. If not insert it." The same can be done with an if statement, but MERGE is more efficient for coding. Ultimately, what should be allowed into the table is defined by the table definition. You only have to do error handling if you are trying to insert rows that already exist. To avoid that, don't insert them if they exist 🙂 Hence, the MERGE or an IF.
Jared
CE - Microsoft
July 17, 2012 at 2:49 pm
But the issue I have is that I dont think an if will suffice to determine if the account already exists.
If two users run the code:
If exists(selectAcnum from Mytable where acnum = @NewAccount)
at the same time
and the account DOES NOT EXIST
then both users will try and insert the new account ,,, which will give an error.
So I need some aditional error checking .... or will some sort of Locking overcome this where I dont need error checking?
July 17, 2012 at 2:53 pm
I think you are thinking too much into this. Let the database engine handle it, that's what it is built to do. You can do an IF EXISTS and if both users are somehow able to execute this at the exact same time, SQL Server will still be able to handle it. They won't "really" both be executed at the same time, one will wait for the other and all will be fine.
Jared
CE - Microsoft
July 17, 2012 at 3:02 pm
gerard-593414 (7/17/2012)
But the issue I have is that I dont think an if will suffice to determine if the account already exists.If two users run the code:
If exists(selectAcnum from Mytable where acnum = @NewAccount)
at the same time
and the account DOES NOT EXIST
then both users will try and insert the new account ,,, which will give an error.
So I need some aditional error checking .... or will some sort of Locking overcome this where I dont need error checking?
The biggest part of the problem is that you haven't provided us with the DDL for the tables, sample data for the tables, the code you are currently using. Given that and we could easily provide you with much better answers to your question, and provide tested code in return.
July 17, 2012 at 4:58 pm
I have given a very simple example , as i dont have specific code, and am currenlty actually writing the code.
The example of updating a Table with just two fields, could not be more simple.
I have done this for years , in another langauge as follows(pseudo code) , but as yet dont have the experience in Sql to know the best way to code it.
Wait to get a Lock on the Table
Locate the row for the Account
If its not found, Insert a new row with the account Number
Unlock the Table
Update the table with the Balance for the account number
The above code runs, and ensures that only one account will be added
How to run the above code in the most efficient way in SQL
Perhaps use Table Locks, Row Locks, Isolation Level or error checking ....I dont know !!!
July 17, 2012 at 6:05 pm
I don't understand why you think that it needs these locks. SQL Server is smart enough to handle this for you when you place a unique constraint and use a MERGE or an IF EXISTS.
Jared
CE - Microsoft
July 17, 2012 at 10:49 pm
gerard-593414 (7/17/2012)
I have given a very simple example , as i dont have specific code, and am currenlty actually writing the code.The example of updating a Table with just two fields, could not be more simple.
I have done this for years , in another langauge as follows(pseudo code) , but as yet dont have the experience in Sql to know the best way to code it.
Wait to get a Lock on the Table
Locate the row for the Account
If its not found, Insert a new row with the account Number
Unlock the Table
Update the table with the Balance for the account number
The above code runs, and ensures that only one account will be added
How to run the above code in the most efficient way in SQL
Perhaps use Table Locks, Row Locks, Isolation Level or error checking ....I dont know !!!
We had folks doing that at another company I used to work for. They also tried the UPDATE/SELECT inside a transaction scenario. They both produced an average of 640 deadlocks per day with occasional spikes to 4000.
I have "NextID" code that will do increments on my home machine. I'm still working so I might not get to it to give you for quite some time but it's what my (at the time) System DBA and I came up with. As soon as we implemented it, the deadlocks (and the duplicate IDs that occasionally occurred) dropped to nearly zero. It used the 3 part UPDATE that Lynn used with a "kicker" for increments.
P.S. Forget about the UPDLOCK here... it's not necessary if you use the 3 part UPDATE. MERGE is not necessay, either. As Michael suggested, OUTPUT is another way to do it.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 18, 2012 at 8:00 am
Jeff Moden (7/17/2012)
gerard-593414 (7/17/2012)
I have given a very simple example , as i dont have specific code, and am currenlty actually writing the code.The example of updating a Table with just two fields, could not be more simple.
I have done this for years , in another langauge as follows(pseudo code) , but as yet dont have the experience in Sql to know the best way to code it.
Wait to get a Lock on the Table
Locate the row for the Account
If its not found, Insert a new row with the account Number
Unlock the Table
Update the table with the Balance for the account number
The above code runs, and ensures that only one account will be added
How to run the above code in the most efficient way in SQL
Perhaps use Table Locks, Row Locks, Isolation Level or error checking ....I dont know !!!
We had folks doing that at another company I used to work for. They also tried the UPDATE/SELECT inside a transaction scenario. They both produced an average of 640 deadlocks per day with occasional spikes to 4000.
I have "NextID" code that will do increments on my home machine. I'm still working so I might not get to it to give you for quite some time but it's what my (at the time) System DBA and I came up with. As soon as we implemented it, the deadlocks (and the duplicate IDs that occasionally occurred) dropped to nearly zero. It used the 3 part UPDATE that Lynn used with a "kicker" for increments.
P.S. Forget about the UPDLOCK here... it's not necessary if you use the 3 part UPDATE. MERGE is not necessay, either. As Michael suggested, OUTPUT is another way to do it.
Am I missing something? Probably... :w00t:
OP says this:
1. Have a table with two fields : CusNum, CusBalance
2. I want a Stored procedure, which has two parameters, @AccountNumber, @Amount and which updates my Table
3. Pseudo code would be:
If Cusnum does not exist in Table
{ Add a Row to the Table and Populate it with AccountNumber}
Update CusBalance set CusBalance = CusBalance + @Amount where CusNum = @AccountNumber
4. I want to ensure that:
1. if two users updating the Table simultaneously, say with a new account, Two new roes with the same account number
are not created
2. The Balance is correctly updated if two users hit the Table with the same customer at the same time
So my suggestion is this:
USE test
GO
--create a sample table with unique constraint on account number
CREATE TABLE accountBalance(accountNo BIGINT PRIMARY KEY, balance MONEY)
GO
--create simple stored proc
CREATE PROCEDURE accountBalanceTest
@accountNo BIGINT,
@Balance MONEY
AS
BEGIN
IF NOT EXISTS (SELECT accountNo FROM accountBalance WHERE accountNo = @accountNo)
INSERT INTO accountBalance(accountNo, balance)
SELECT @accountNo, @Balance
ELSE
UPDATE accountBalance
SET balance = balance + @Balance
WHERE accountNo = @accountNo
END
GO
EXEC accountBalanceTest 10000, 15
GO
EXEC accountBalanceTest 10000, 30
GO
SELECT * FROM accountBalance
GO
EXEC accountBalanceTest 20000, 30
GO
SELECT * FROM accountBalance
GO
FACT: If the account number is already known and 2 users try to pass this into the stored proc at the same time... It WILL be handled by the database engine with absolutely no problem. No explicit locks needed.
Jared
CE - Microsoft
July 18, 2012 at 8:31 am
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.
July 18, 2012 at 8:36 am
The database engine will perform its own locks as needed because of the unique constraint. You seem to assume that things happen asynchronously at the database level, but it is not really the case. There is an internal type of scheduler that SQL Server uses. So even if the 2 records appear to be inserted at the exact same time, they really are not.
Jared
CE - Microsoft
Viewing 15 posts - 31 through 45 (of 57 total)
You must be logged in to reply to this topic. Login to reply