July 11, 2012 at 1:50 pm
Lynn Pettis (7/11/2012)
Matt Miller (#4) (7/11/2012)
Lynn Pettis (7/11/2012)
This works as well:
CREATE TABLE [dbo].[TEST1](
[ID] [VARCHAR](10) NULL,
[VALUE] [int] NULL
);
GO
INSERT INTO dbo.TEST1 (ID, VALUE) SELECT 1, 10;
SELECT 'PRE' LABEL1, * FROM dbo.TEST1;
DECLARE @SAVE1 INT;
UPDATE dbo.TEST1 SET
@SAVE1 = VALUE = VALUE+5
WHERE ID = 1;
SELECT @SAVE1
SELECT 'POST' LABEL1, * FROM dbo.TEST1;
GO
DROP TABLE dbo.TEST1;
GO
There's also the OUTPUT clause, which allows you to do this very kind of operation in sets and retain the results of your own operation:
create table #bob (id int identity(1,1),seqname varchar(40), currentval int)
insert #bob(seqname,currentval)
values ('abc',1000), ('def',2000)
declare @fun table(joe int, bob int)
update #bob
set currentval =currentval+10
output deleted.currentval+1,inserted.currentval into @fun
select * from @fun
And the output clause will let you capture both the before and after values.
Still trying to figure out why the SELECT/UPDATE structure. Not really getting anything to support its use. Reminds me of how we had to do things with COBOL/ISAM database updates. Read the record to be updated, change it, write it back.
I was just answering the OP's actual q. Perhaps you have a much deeper understanding of the OP's environment/requirements than I do and he/she does.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 11, 2012 at 1:53 pm
ScottPletcher (7/11/2012)
Lynn Pettis (7/11/2012)
Matt Miller (#4) (7/11/2012)
Lynn Pettis (7/11/2012)
This works as well:
CREATE TABLE [dbo].[TEST1](
[ID] [VARCHAR](10) NULL,
[VALUE] [int] NULL
);
GO
INSERT INTO dbo.TEST1 (ID, VALUE) SELECT 1, 10;
SELECT 'PRE' LABEL1, * FROM dbo.TEST1;
DECLARE @SAVE1 INT;
UPDATE dbo.TEST1 SET
@SAVE1 = VALUE = VALUE+5
WHERE ID = 1;
SELECT @SAVE1
SELECT 'POST' LABEL1, * FROM dbo.TEST1;
GO
DROP TABLE dbo.TEST1;
GO
There's also the OUTPUT clause, which allows you to do this very kind of operation in sets and retain the results of your own operation:
create table #bob (id int identity(1,1),seqname varchar(40), currentval int)
insert #bob(seqname,currentval)
values ('abc',1000), ('def',2000)
declare @fun table(joe int, bob int)
update #bob
set currentval =currentval+10
output deleted.currentval+1,inserted.currentval into @fun
select * from @fun
And the output clause will let you capture both the before and after values.
Still trying to figure out why the SELECT/UPDATE structure. Not really getting anything to support its use. Reminds me of how we had to do things with COBOL/ISAM database updates. Read the record to be updated, change it, write it back.
I was just answering the OP's actual q. Perhaps you have a much deeper understanding of the OP's environment/requirements than I do and he/she does.
No, no deeper understanding. That's why I asked for the reasoning behind the SELECT/UPDATE structure. Without seeing the code and understanding what the OP is trying to do, can't give an intelligent answer to the question.
July 12, 2012 at 3:01 am
Hi Lynn. Thanks for that.
Just 2 questions:
1. Instead of using the two locks, can I use an IsolationLevel instead. I am not sure if the framework I am using allows Locks but it does allow Isolationlvel commands as they are separate and not part of the select statement.
2. If the Locks are used (or IsolationLvel) if another user does a basic Select (not locked and not inside a transaction... just for reporting purposes) at the same time the locks are in place, will those rows be selected out ?
July 13, 2012 at 10:58 am
Didn't fully read all posts here, but since this is a SQL 2008 forum, I would think MERGE would be the obvious solution that completely eliminates all concurrency/locking issues that come with separate-statement solutions to the OP's problem.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 13, 2012 at 11:26 am
gerard-593414 (7/12/2012)
Hi Lynn. Thanks for that.Just 2 questions:
1. Instead of using the two locks, can I use an IsolationLevel instead. I am not sure if the framework I am using allows Locks but it does allow Isolationlvel commands as they are separate and not part of the select statement.
2. If the Locks are used (or IsolationLvel) if another user does a basic Select (not locked and not inside a transaction... just for reporting purposes) at the same time the locks are in place, will those rows be selected out ?
Sorry, but I'm not answering any questions until mine have been answered.
Why the SELECT/UPDATE structure?
What is it giving you?
July 13, 2012 at 2:33 pm
Hi Lynn
Sorry, but I'm not answering any questions until mine have been answered.
Why the SELECT/UPDATE structure?
What is it giving you?
This is my first post in this forum, working around the geography, so not sure if this is directed at me or not, bit i'll answer it anyway.
The framework I am usng has a 1. seelct Construct to seelct into a Business Object and 2. A Save, which does an update, and I am trying to work within that.
So , back to my original query, is there a way , with transactions, or otherwise, to do the following, and be sure nobody else updates the row at the same time. A bonus would be if other users can select (but not update).
Select and Lock row(s)
Change Value in memory
Save
Regards,
Gerard
July 13, 2012 at 2:53 pm
Maybe this is just one of those persistent myths, or maybe I learned this from someone who didn't really know, but in the select/update scenario when incrementing a value, shouldn't the update go first? Even in a transaction wrapper, won't two people be able to issue simultaneous reads to the row and get the same value? If you update first, and then select the updated value, wouldn't that establish the update lock and prevent one of the people from getting a duplicate value?
Using update with output seems like the simplest solution.
July 13, 2012 at 3:00 pm
gerard-593414 (7/13/2012)
The framework I am usng has a 1. seelct Construct to seelct into a Business Object and 2. A Save, which does an update, and I am trying to work within that.So , back to my original query, is there a way , with transactions, or otherwise, to do the following, and be sure nobody else updates the row at the same time. A bonus would be if other users can select (but not update).
Select and Lock row(s)
Change Value in memory
Save
Gerard.
So you are doing the work in the business layer/UI?
What framework are you using? I'm not real familiar with working outside SQL Server, nor are most other posters in this forum. We would typically do all this work in a stored procedure using T-SQL and provide locking hints or change the isolation level in the stored procedure.
The issue with frameworks is that, in most cases, they work in a disconnected fashion. You select into the business object, disconnect, manipulate the object, reconnect, update the database, and this method doesn't lend itself to what you want to do that well.
In .NET I believe you would open a connection, begin a transaction, load the object, manipulate the object, commit changes, commit the transaction, close the connection and that would, in theory, give you the locks you need until the transaction is committed. But you need to make sure any manipulation of the object i quick and that you aren't waiting on user interaction at this point.
I could be wrong.
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 13, 2012 at 3:52 pm
gerard-593414 (7/13/2012)
Hi LynnSorry, but I'm not answering any questions until mine have been answered.
Why the SELECT/UPDATE structure?
What is it giving you?
This is my first post in this forum, working around the geography, so not sure if this is directed at me or not, bit i'll answer it anyway.
The framework I am usng has a 1. seelct Construct to seelct into a Business Object and 2. A Save, which does an update, and I am trying to work within that.
So , back to my original query, is there a way , with transactions, or otherwise, to do the following, and be sure nobody else updates the row at the same time. A bonus would be if other users can select (but not update).
Select and Lock row(s)
Change Value in memory
Save
Regards,
Gerard
Yes, this was directed to you as it is your question we are trying to answer. From the original post, it appears that this is done in an sp (store procedure). Can you post the code that is doing this work? It would help to see what exactly it is doing.
July 14, 2012 at 1:35 pm
How about:
SET @VAR = SELECT VALUE FROM TABLE WHERE ID = 1
then
UPDATE TABLE SET VALUE = @VAR + INCREMENT WHERE ID = 1 AND VALUE = @VAR
Obviously you'd be prepared to return the number of rows updated updated (1) and reexecute this if none updated, because then you know someone else has updated the value. This would only work if you knew how many rows were affected by the update statement.
July 17, 2012 at 3:52 am
Hi Lynn.
At this stage, I am doing some experimenting and want to explore the most effective way of doing things.
I can give you pseudo code (as I am not sure of the best way of doing it I dont have Sql code)
Object: To ensure update is done and I dont end up with duplicate rows
Exercise:
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
From the posts here, I reckon the Update line above will work for the balance, but how do I ensure two rows with an idential number are not created
July 17, 2012 at 6:20 am
The best way to ensure that you don't get duplicates is to create a unique constraint or primary key on the column(s) that define uniqueness. You would then get an unique key violation on all but the first insert and your code would have to handle that error.
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 6:31 am
Hi jack. Thanks for replying.
Ok I see what you are saying. but this does not seem like a very elegant way to do it.
Is there no structures/code within Sql Server which will do this.
It seems from the way you are suggesting ,that you have to force an error, and then code around the error ??
This sounds like a 'Kludgy' way of doing things (i.e. forcing an error condition)
July 17, 2012 at 6:45 am
Based on the business rules you have provided, having a duplicate customer number IS an error and a unique constraint or a primary key are the only ways to guarantee that. I don't consider that kludgy, I consider that good design. Error handling should be a standard part of the application as well, and this would be an anticipated error that you could handle appropriately. Yes, you can also write code that attempts to prevent the error from ever occuring, but when it comes to my data, I'm going to do whatever I can to guarantee that the business rules are enforced, and for uniqueness a unique constraint/primary key is the best way.
So what are the business rules for handling the situation you outline in your example? Should the 2nd transaction do an update, create a different customer number and insert, or fail and why?
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:18 am
Business Rules , in broad terms , would be If the First transaction has a new account, and the second transaction has a new account and both update 'at the same time', the first should create the new account, the second should not create a new account , but should Update the account just created.
If SqlServers way of enforcing this is to force you to create an error and handle it, so be it, and I will proceed on this basis, but I suppose I thought that there might have been some inbuilt mechanism in SqlServer to handle this, rather than having to 'create an error' and then work back from that.
Viewing 15 posts - 16 through 30 (of 57 total)
You must be logged in to reply to this topic. Login to reply