Lock only one row in update (lock a row)

Question

Post reply

Lock only one row in update (lock a row)

Viewing 8 posts - 46 through 52 (of 52 total)

You must be logged in to reply to this topic. Login to reply

abitguru Ten Centuries Points: 1170 More actions · Answer 1

Hello, this is the code of the transaction.

BEGIN TRANSACTION

SELECT * FROM Reque_pa WITH (UPDLOCK) WHERE EMPRE=1 AND CEREQ = 1

UPDATE Reque_pa SET Proximo=107918, Pendiente=0 WHERE EMPRE=1 AND CEREQ=1

SELECT @@ROWCOUNT AS row_count__

INSERT INTO Reque_he ( EMPRE, REQUE, CEREQ, FEREQ) VALUES ( 1, 107917, 1, '2013-03-26')

SELECT @@ROWCOUNT AS row_count__

INSERT INTO Reque_db (REQUE, VERSI, EMPRE, CEREQ, SUCUREM, DEPTOEM, SUCUREN, DEPTOEN, SUCURSO, DEPTOSO, FEREQ, COMGE, ARTIC, MATVS, TIART, CLASE, SUBCL, CODEM, CODME, MARCA, DEPTOES, TILCU, CBURQ, APMOV, COMEN, KILOS, KILOSBR, VOLUM, CANPA, FEARP, ESCUM, SUCURDF, DEPTODF, TIREVRF, MODID) VALUES (107917, 1, 1, 1, 1, 910, 1, 910, 6, 135, '2013-03-26', '', 22322, '', 1, 2, 205, 25, 1, 1, 910, 1, 10, 1, '', 44.400, 44.800, 0.2820, 0.130, '2013-03-26 07:32:47', 2, 1, 910, 1, 'GUH')

SELECT @@ROWCOUNT AS row_count__

IF @@TRANCOUNT > 0 BEGIN COMMIT TRANSACTION END

This is all transaction,

I ask for the value of proximo in the table, and lock with updlock.

Update proximo.

Insert using the value.

commit.

Hope this helps,

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;

I can't understand why I don't need to lock reque_pa with this implementation.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

abitguru (3/26/2013)
I can't understand why I don't need to lock reque_pa with this implementation.

Because the select and the update are a single atomic operation and hence the locks SQL takes are quite sufficient.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

abitguru (3/26/2013)
SELECT * FROM Reque_pa WITH (UPDLOCK) WHERE EMPRE=1 AND CEREQ = 1
UPDATE Reque_pa SET Proximo=107918, Pendiente=0 WHERE EMPRE=1 AND CEREQ=1

You're selecting, then updating the table with a fixed value. Where's that increment you've been talking about? Where does that 107918 come from for Proximo? Front end? Hardcoded?

Similarly, where do the values you're inserting into Reque_he come from? Hardcoded?

I can't see any resemblance between this code and the process you explained yesterday, there's no incrementing anywhere in that code, the values returned from Reque_pa are sent to the client and never used in subsequent inserts.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

abitguru Ten Centuries Points: 1170 More actions · Answer 4

Thanks a lot. I tested your code, I had never thought of it that way.

Coming back to my first problem.

If I execute this query (see I update cereq=1)

use desarrollo

begin transaction

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;

print @cur_proximo

--simulate wait

WAITFOR DELAY '00:00:10';

commit transaction

And the meanwhile query 1 is running I run

(see I update cereq = 2)

use desarrollo

begin transaction

declare @UpdVar table ([PROXIMO] [decimal](8, 0));

update [dbo].[reque_pa] SET

proximo = proximo + 1

output DELETED.proximo into @UpdVar([PROXIMO])

where

cereq = 2; -- or use a variable to define this

declare @cur_proximo decimal(8,0);

select @cur_proximo = PROXIMO from @UpdVar;

print @cur_proximo

commit transaction

Query 1 must end, and then query 2 can execute.

Can I make this independent, so query 2 can complete meanwhile query 1 is running ?

Thanks a lot for your help and patience 🙂

abitguru Ten Centuries Points: 1170 More actions · Answer 5

GilaMonster (3/26/2013)
abitguru (3/26/2013)
SELECT * FROM Reque_pa WITH (UPDLOCK) WHERE EMPRE=1 AND CEREQ = 1
UPDATE Reque_pa SET Proximo=107918, Pendiente=0 WHERE EMPRE=1 AND CEREQ=1
You're selecting, then updating the table with a fixed value. Where's that increment you've been talking about? Where does that 107918 come from for Proximo? Front end? Hardcoded?
Similarly, where do the values you're inserting into Reque_he come from? Hardcoded?
I can't see any resemblance between this code and the process you explained yesterday, there's no incrementing anywhere in that code, the values returned from Reque_pa are sent to the client and never used in subsequent inserts.

The increment is in the program code. I get the value of proximo and add 1.

Its not hardcoded.

Reque_he use the same value taked from proximo, not hardcoded.

The value of proximo is used in program code. I get proximo and store in a variable, then add 1 and build the update sentence.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

abitguru (3/26/2013)
GilaMonster (3/26/2013)
abitguru (3/26/2013)
SELECT * FROM Reque_pa WITH (UPDLOCK) WHERE EMPRE=1 AND CEREQ = 1
UPDATE Reque_pa SET Proximo=107918, Pendiente=0 WHERE EMPRE=1 AND CEREQ=1
You're selecting, then updating the table with a fixed value. Where's that increment you've been talking about? Where does that 107918 come from for Proximo? Front end? Hardcoded?
Similarly, where do the values you're inserting into Reque_he come from? Hardcoded?
I can't see any resemblance between this code and the process you explained yesterday, there's no incrementing anywhere in that code, the values returned from Reque_pa are sent to the client and never used in subsequent inserts.
The increment is in the program code. I get the value of proximo and add 1.
Its not hardcoded.
Reque_he use the same value taked from proximo, not hardcoded.
The value of proximo is used in program code. I get proximo and store in a variable, then add 1 and build the update sentence.

The method you are using to communicate with SQL Server isn't recommended. If the calling code borks with an open transaction, you're in trouble. You would be well advised to place your code within stored procedures and call those from your program using parameters. Transactions will be open for far less time. Much less opportunity for a collision.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Seraj Alam-256815 SSC Eights! Points: 804 More actions · Answer 7

If you want a workaround, here it is.

Add a column Filler char(4000). you said there are few rows, should not be overhead.

create clusterd index on Reque_pa(CEREQ).

Enjoy!

Sreaj Alam

SQL DBA, HCL Technologies.

abitguru Ten Centuries Points: 1170 More actions · Answer 8

Seraj Alam-256815 (3/26/2013)
If you want a workaround, here it is.
Add a column Filler char(4000). you said there are few rows, should not be overhead.
create clusterd index on Reque_pa(CEREQ).
Enjoy!
Sreaj Alam
SQL DBA, HCL Technologies.

Thanks Seraj, with this I force one regiter per page.