March 25, 2013 at 2:06 pm
Getting there, but still don't know how this table is being used, what column is being used to provide info for the transaction.
Why don't you try explaining the entire process.
March 25, 2013 at 3:01 pm
abitguru (3/25/2013)
Right now I use updlock hint, but this makes a page lock and locks other rows that I dont want to be locked. (others rows that can be used for other transactions)
Because you have no useful indexes for that query. No useful indexes means SQL has to read the entire table (an index just on that one column in the WHERE probably won't be used for a SELECT * because it's neither covering nor selective enough). Taking row locks when SQL knows that it has to read the entire table is inefficient. Hence, since SQL knows that it has to read the entire table, it takes page locks as a preference. It could have taken table too.
The UPDLOCK hint says please take any locks needed as Update locks, not shared locks. SQL has to read and lock the entire table, hence you get page level update locks (that's what you asked for) and that blocks anyone else.
Fix your indexing first. Then, if you still have a lock problem, try other hints.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 25, 2013 at 3:06 pm
This is all process,
to numerate I select by parameter to use by fields cereq and use the field proximo to get next number,
insert using number get from previuos query
add +1 to update proximo
all in the same transaction.
its simple,
begin
get next number
use it
add +1
commit
hope this info helps, sorry if I don't understand you
March 25, 2013 at 3:11 pm
GilaMonster (3/25/2013)
abitguru (3/25/2013)
Right now I use updlock hint, but this makes a page lock and locks other rows that I dont want to be locked. (others rows that can be used for other transactions)Because you have no useful indexes for that query. No useful indexes means SQL has to read the entire table. Taking row locks when SQL knows that it has to read the entire table is inefficient. Hence, since SQL knows that it has to read the entire table, it takes page locks as a preference. It could have taken table too.
The UPDLOCK hint says please take any locks needed as Update locks, not shared locks. SQL has to read and lock the entire table, hence you get page level update locks (that's what you asked for) and that blocks anyone else.
Fix your indexing first. Then, if you still have a lock problem, try other hints.
Thanks for that explanation.
I make an index to the field I use in where clause. If I understand I make this and have same results, I will try other hints.
March 25, 2013 at 3:15 pm
abitguru (3/25/2013)
2 - A user can generate a internal requirement, so when I store this on the db, I take next number to save the internal requirement with this number.3 - Sopouse two users want to save internal requirements at the same time. If I take next number (stored in param table), I get the same (if I dont lock the row or page).
4 - So, I think how I can prevent this situation?
Ok, when a user is saving a internal requirement, ask for next number, save the internal requirement and increase the parameter +1. Asking for next number and saving is in the same transaction, so I implement a lock to prevent two uses get the same next number.
5 - the second user have to wait until first user transaction ends.
Err, no, that's a rather inefficient way of doing it. You don't need a select and an update, you don't need locking hints, you don't even need a transaction to be honest (though you might for the rest of the code that you still haven't posted or explained)
DECLARE @TableWithNewParameterValue TABLE (
OldParameterValue DECIMAL(8,0),
NewParameterValue DECIMAL(8,0)
)
UPDATE [reque_pa] SET [PROXIMO] = [PROXIMO] + 1
OUTPUT DELETED.PROXIMO, INSERTED.[PROXIMO] INTO @TableWithNewParameterValue
WHERE CEREQ = 1
SELECT * FROM @TableWithNewParameterValue -- use whichever value you need from this for further inserts, updates or whatever
Completely safe for multiple users to run concurrently, no messy locking hints needed at all since everything's in one atomic statement. Edit: And for this you want an index on CEREQ INCLUDE PROXIMO, not just one the one column.
This is why I was asking again and again for your real code because the test you posted had nothing to do with the real problem and there was an easier way to solve the real problem
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 25, 2013 at 3:17 pm
abitguru (3/25/2013)
I make an index to the field I use in where clause. If I understand I make this and have same results, I will try other hints.
As I said, that index probably won't be useful because it's neither covering nor selective. The fact that you were still getting RID locks instead of key locks means SQL was just ignoring that index because it wasn't useful.
You need no hints at all here, please step away from the hints, they're unnecessary. See my last post.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 25, 2013 at 3:19 pm
abitguru (3/25/2013)
This is all process,to numerate I select by parameter to use by fields cereq and use the field proximo to get next number,
insert using number get from previuos query
add +1 to update proximo
all in the same transaction.
its simple,
begin
get next number
use it
add +1
commit
hope this info helps, sorry if I don't understand you
This is the first time you mentioned this part.
declare @UpdVar table ([PROXIMO] [decimal](8, 0));
update [dbo].[reque_pa] SET
proximo = proximo + 1
output DELETED.proximo into @UpdVar([PROXIMO])
where
cereq = 1; -- or use a variable to define this
declare @cur_proximo decimal(8,0);
select @cur_proximo = PROXIMO from @UpdVar;
-- Then use the variable @cur_proximo as it will have the value of PROXIMO prior to the update
Does this help you figure out what you can do?
March 25, 2013 at 3:20 pm
Once you get the indexes correctly applied, would'n it be better to:
begin
Update the number by 1 -- this should establish the lock and keep anyone from reading it
get number you just updated -- this will get you the updated number
use it -- don't do too much work before you use the number, lock will be held while this is done
commit -- free up everything
March 25, 2013 at 3:20 pm
GilaMonster (3/25/2013)
abitguru (3/25/2013)
2 - A user can generate a internal requirement, so when I store this on the db, I take next number to save the internal requirement with this number.3 - Sopouse two users want to save internal requirements at the same time. If I take next number (stored in param table), I get the same (if I dont lock the row or page).
4 - So, I think how I can prevent this situation?
Ok, when a user is saving a internal requirement, ask for next number, save the internal requirement and increase the parameter +1. Asking for next number and saving is in the same transaction, so I implement a lock to prevent two uses get the same next number.
5 - the second user have to wait until first user transaction ends.
Err, no, that's a rather inefficient way of doing it. You don't need a select and an update, you don't need locking hints, you don't even need a transaction to be honest (though you might for the rest of the code that you still haven't posted or explained)
DECLARE @TableWithNewParameterValue TABLE (NewParameterValue DECIMAL(8,0)
)
UPDATE [reque_pa] SET [PROXIMO] = [PROXIMO] + 1
OUTPUT INSERTED.[PROXIMO] INTO @TableWithNewParameterValue
WHERE CEREQ = 1
SELECT * FROM @TableWithNewParameterValue -- use this for further inserts, updates or whatever
Completely safe for multiple users to run concurrently, no messy locking hints needed at all since everything's in one atomic statement. Edit: And for this you want an index on CEREQ INCLUDE PROXIMO, not just one the one column.
This is why I was asking again and again for your real code because the test you posted had nothing to do with the real problem and there was an easier way to solve the real problem
I think we want the DELETED value not the INSERTED value, unless I miss read the logic.
March 25, 2013 at 3:23 pm
Lynn's suggested solution using OUTPUT is superior to the one I gave you. Use his.
March 25, 2013 at 3:48 pm
Thanks so much guys, sorry if I dont put real code, I dont understand it..
I will try Lynn solution, and see if it "fits".
I will post real code of the transaction, and sorry again if I was closed to rowlock, for me it was the only way to reach that.
This is the first time you mentioned this part.
declare @UpdVar table ([PROXIMO] [decimal](8, 0));
update [dbo].[reque_pa] SET
proximo = proximo + 1
output DELETED.proximo into @UpdVar([PROXIMO])
where
cereq = 1; -- or use a variable to define this
declare @cur_proximo decimal(8,0);
select @cur_proximo = PROXIMO from @UpdVar;
-- Then use the variable @cur_proximo as it will have the value of PROXIMO prior to the update
Does this help you figure out what you can do?
I dont understand how to use it, it is a part of a store procedure.. 🙁 sorry
March 25, 2013 at 4:32 pm
abitguru (3/25/2013)
Thanks so much guys, sorry if I dont put real code, I dont understand it..I will try Lynn solution, and see if it "fits".
I will post real code of the transaction, and sorry again if I was closed to rowlock, for me it was the only way to reach that.
This is the first time you mentioned this part.
declare @UpdVar table ([PROXIMO] [decimal](8, 0));
update [dbo].[reque_pa] SET
proximo = proximo + 1
output DELETED.proximo into @UpdVar([PROXIMO])
where
cereq = 1; -- or use a variable to define this
declare @cur_proximo decimal(8,0);
select @cur_proximo = PROXIMO from @UpdVar;
-- Then use the variable @cur_proximo as it will have the value of PROXIMO prior to the update
Does this help you figure out what you can do?
I dont understand how to use it, it is a part of a store procedure.. 🙁 sorry
Well, I can't see what you see.
March 25, 2013 at 5:55 pm
Lynn Pettis (3/25/2013)
abitguru (3/25/2013)
Thanks so much guys, sorry if I dont put real code, I dont understand it..I will try Lynn solution, and see if it "fits".
I will post real code of the transaction, and sorry again if I was closed to rowlock, for me it was the only way to reach that.
This is the first time you mentioned this part.
declare @UpdVar table ([PROXIMO] [decimal](8, 0));
update [dbo].[reque_pa] SET
proximo = proximo + 1
output DELETED.proximo into @UpdVar([PROXIMO])
where
cereq = 1; -- or use a variable to define this
declare @cur_proximo decimal(8,0);
select @cur_proximo = PROXIMO from @UpdVar;
-- Then use the variable @cur_proximo as it will have the value of PROXIMO prior to the update
Does this help you figure out what you can do?
I dont understand how to use it, it is a part of a store procedure.. 🙁 sorry
Well, I can't see what you see.
sorry, I can't understand what you mean with this comment
March 25, 2013 at 6:34 pm
abitguru (3/25/2013)
Lynn Pettis (3/25/2013)
abitguru (3/25/2013)
Thanks so much guys, sorry if I dont put real code, I dont understand it..I will try Lynn solution, and see if it "fits".
I will post real code of the transaction, and sorry again if I was closed to rowlock, for me it was the only way to reach that.
This is the first time you mentioned this part.
declare @UpdVar table ([PROXIMO] [decimal](8, 0));
update [dbo].[reque_pa] SET
proximo = proximo + 1
output DELETED.proximo into @UpdVar([PROXIMO])
where
cereq = 1; -- or use a variable to define this
declare @cur_proximo decimal(8,0);
select @cur_proximo = PROXIMO from @UpdVar;
-- Then use the variable @cur_proximo as it will have the value of PROXIMO prior to the update
Does this help you figure out what you can do?
I dont understand how to use it, it is a part of a store procedure.. 🙁 sorry
Well, I can't see what you see.
sorry, I can't understand what you mean with this comment
Simple, if you don't show us the code for the stored procedure no one can show you how to integrate the changes we have presented to you.
March 25, 2013 at 6:39 pm
Sorry 😛 tomorrow when i'm at work i'll post all the code.
thanks for all your help and patience :-D!!!
Viewing 15 posts - 31 through 45 (of 52 total)
You must be logged in to reply to this topic. Login to reply