July 11, 2012 at 7:37 am
I have a table which holds the next Invoice Number. What SP code should I use to increment it (Cant be self incrementing)
There are numerous users and want to ensure that no two users get the same number, and that I dont have a Deadlock.
I want to do similar for updating a Balance, but more that tow users could be looking and want to update the balance at the same time, so I need to do somethinf like:
1. Lock the Row (So that nobody else can read from it)
2. Get the Number or balance
3. Increment the Number (Or Balance)
4. Update the balance
Between 1-4, no other user should be able to read the row for updating purposes, but there will always be other users who want to read ALL balances for reports etc
July 11, 2012 at 8:15 am
When getting the row you can specify the UPDLOCK hint on the select query which will allow users to read the row(s) selected, but not update them.
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 11, 2012 at 9:08 am
Hi Jack. Thanks for your reply.
If the other user can read, won't this cause a problem when he goes to update ?
Take a very simple example:
Table has CustomerRow with 2 fields USBalance = 10 and IRLBalance = 70)
User1 has a Tran to update USBbalance with 5
User 2 has a tran to update IrlBalance with 7
So sequence would be:
1. User 1 reads (UsBalance=10 IrlBalance=70)
2. User 2 Reads (UsBalance=10 IrlBalance=70)
3. User 1 Updates (Now UsBalance = 10 +5=15,IrlBalance = 70+0=70)
4. User 2 Now Updates (UsBalance = 10+ 0, IrlBalance = 70+7 = 77)
So you end up with UsBalance=10, Irlbalance = 77)
Should be Usbalance = 15, IrlBalance = 77)
July 11, 2012 at 9:38 am
you can use a table lock, but then this:
...but there will always be other users who want to read ALL balances for reports etc
will not be possible without using NOLOCK which means you won't have accurate data or changing to use one of the SNAPSHOT ISOLATION LEVELS on the database. http://technet.microsoft.com/en-us/library/ms189122(v=SQL.90).aspx, http://msdn.microsoft.com/en-us/library/ms189050(v=sql.105).aspx
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 11, 2012 at 11:26 am
I can see why the Balance cannot be self incrementing. However, what is the business rule that keeps you from having a sequence self increment?
On the Balance column, do you need it to be incremental? By that I mean do you need each distinct row to contain the balance up to that point in time or do you simply need a balance?
Is the sequence number a sequence within a given dataset rather than based on all rows within the table?
July 11, 2012 at 11:50 am
gerard-593414 (7/11/2012)
I have a table which holds the next Invoice Number. What SP code should I use to increment it (Cant be self incrementing)There are numerous users and want to ensure that no two users get the same number, and that I dont have a Deadlock.
I want to do similar for updating a Balance, but more that tow users could be looking and want to update the balance at the same time, so I need to do somethinf like:
1. Lock the Row (So that nobody else can read from it)
2. Get the Number or balance
3. Increment the Number (Or Balance)
4. Update the balance
Between 1-4, no other user should be able to read the row for updating purposes, but there will always be other users who want to read ALL balances for reports etc
It would help if you could post the DDL (CREATE TABLE statement(s)) for the table(s) involved, sample data (series of INSERT INTO statements), your current code, and the expected results.
Without that, why are you doing a SELECT then UPDATE? Why aren't you just doing an UPDATE?
July 11, 2012 at 12:12 pm
gerard-593414 (7/11/2012)
I have a table which holds the next Invoice Number. What SP code should I use to increment it (Cant be self incrementing)There are numerous users and want to ensure that no two users get the same number, and that I dont have a Deadlock.
I want to do similar for updating a Balance, but more that tow users could be looking and want to update the balance at the same time, so I need to do somethinf like:
1. Lock the Row (So that nobody else can read from it)
2. Get the Number or balance
3. Increment the Number (Or Balance)
4. Update the balance
Between 1-4, no other user should be able to read the row for updating purposes, but there will always be other users who want to read ALL balances for reports etc
To acquire an exclusive row lock, you can use ROWLOCK and XLOCK together.
Put all the steps into a transaction and commit it as soon as possible so you don't hold others up any longer than absolutely necessary.
BEGIN TRY
BEGIN TRANSACTION
SELECT ...
FROM dbo.tablename WITH ( ROWLOCK, XLOCK )
WHERE ...
...
UPDATE dbo.tablename
SET ...
WHERE ...
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
END CATCH
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 12:14 pm
ScottPletcher (7/11/2012)
gerard-593414 (7/11/2012)
I have a table which holds the next Invoice Number. What SP code should I use to increment it (Cant be self incrementing)There are numerous users and want to ensure that no two users get the same number, and that I dont have a Deadlock.
I want to do similar for updating a Balance, but more that tow users could be looking and want to update the balance at the same time, so I need to do somethinf like:
1. Lock the Row (So that nobody else can read from it)
2. Get the Number or balance
3. Increment the Number (Or Balance)
4. Update the balance
Between 1-4, no other user should be able to read the row for updating purposes, but there will always be other users who want to read ALL balances for reports etc
To acquire an exclusive row lock, you can use ROWLOCK and XLOCK together.
Put all the steps into a transaction and commit it as soon as possible so you don't hold others up any longer than absolutely necessary.
BEGIN TRY
BEGIN TRANSACTION
SELECT ...
FROM dbo.tablename WITH ( ROWLOCK, XLOCK )
WHERE ...
...
UPDATE dbo.tablename
SET ...
WHERE ...
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
END CATCH
I guess I'm have a problem understanding why the SELECT/UPDATE pair. Why not base the UPDATE on the criteria of the SELECT?
July 11, 2012 at 12:29 pm
Lynn Pettis (7/11/2012)
ScottPletcher (7/11/2012)
gerard-593414 (7/11/2012)
I have a table which holds the next Invoice Number. What SP code should I use to increment it (Cant be self incrementing)There are numerous users and want to ensure that no two users get the same number, and that I dont have a Deadlock.
I want to do similar for updating a Balance, but more that tow users could be looking and want to update the balance at the same time, so I need to do somethinf like:
1. Lock the Row (So that nobody else can read from it)
2. Get the Number or balance
3. Increment the Number (Or Balance)
4. Update the balance
Between 1-4, no other user should be able to read the row for updating purposes, but there will always be other users who want to read ALL balances for reports etc
To acquire an exclusive row lock, you can use ROWLOCK and XLOCK together.
Put all the steps into a transaction and commit it as soon as possible so you don't hold others up any longer than absolutely necessary.
BEGIN TRY
BEGIN TRANSACTION
SELECT ...
FROM dbo.tablename WITH ( ROWLOCK, XLOCK )
WHERE ...
...
UPDATE dbo.tablename
SET ...
WHERE ...
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
END CATCH
I guess I'm have a problem understanding why the SELECT/UPDATE pair. Why not base the UPDATE on the criteria of the SELECT?
You have to base the new value on the existing value.
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 12:30 pm
ScottPletcher (7/11/2012)
Lynn Pettis (7/11/2012)
ScottPletcher (7/11/2012)
gerard-593414 (7/11/2012)
I have a table which holds the next Invoice Number. What SP code should I use to increment it (Cant be self incrementing)There are numerous users and want to ensure that no two users get the same number, and that I dont have a Deadlock.
I want to do similar for updating a Balance, but more that tow users could be looking and want to update the balance at the same time, so I need to do somethinf like:
1. Lock the Row (So that nobody else can read from it)
2. Get the Number or balance
3. Increment the Number (Or Balance)
4. Update the balance
Between 1-4, no other user should be able to read the row for updating purposes, but there will always be other users who want to read ALL balances for reports etc
To acquire an exclusive row lock, you can use ROWLOCK and XLOCK together.
Put all the steps into a transaction and commit it as soon as possible so you don't hold others up any longer than absolutely necessary.
BEGIN TRY
BEGIN TRANSACTION
SELECT ...
FROM dbo.tablename WITH ( ROWLOCK, XLOCK )
WHERE ...
...
UPDATE dbo.tablename
SET ...
WHERE ...
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
END CATCH
I guess I'm have a problem understanding why the SELECT/UPDATE pair. Why not base the UPDATE on the criteria of the SELECT?
You have to base the new value on the existing value.
Example??
July 11, 2012 at 12:49 pm
I've been toying around with this construct:
CREATE TABLE [dbo].[TEST1](
[ID] [VARCHAR](10) NULL,
[VALUE] [int] NULL
)
GO
INSERT INTO TEST1 (ID, VALUE) SELECT 1, 10
SELECT 'PRE' LABEL1, * FROM TEST1
DECLARE @SAVE1 INT
UPDATE TEST1
SET VALUE = VALUE+5, @SAVE1 = VALUE+5
WHERE ID = 1
SELECT @SAVE1
SELECT 'POST' LABEL1, * FROM TEST1
It saves the new value into @SAVE1, adds to the column in one statement which implies a transaction or atomic operation. That way you can use the value in @SAVE1 as the result without sweating race conditions. Whadya think?
(edit) I would think it works but I'm curious what the more experienced of you think.
July 11, 2012 at 1:16 pm
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
July 11, 2012 at 1:35 pm
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
----------------------------------------------------------------------------------
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?
July 11, 2012 at 1:39 pm
You can return the result set directly from the UPDATE statement.
create table [dbo].[TEST1]
(
[ID][int]not null primary key clustered,
[VALUE][int]not null
);
GO
insert into dbo.TEST1 (ID, VALUE) SELECT 1, 10;
update dbo.TEST1
set
VALUE = VALUE+5
output
inserted.ID,
deleted.VALUE as Old_VALUE,
inserted.VALUE as New_VALUE
where
ID = 1
go
drop table dbo.TEST1;
Results:
(1 row(s) affected)
ID Old_VALUE New_VALUE
----------- ----------- -----------
1 10 15
(1 row(s) affected)
July 11, 2012 at 1:39 pm
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.
Viewing 15 posts - 1 through 15 (of 57 total)
You must be logged in to reply to this topic. Login to reply