lock on a record?

Just wondering if there is a way to put a lock on a record? For example I
have the table below and a stored procedure reads the records in. Another
procedure increments the value in column 2 and then writes a new record with
this incremented value plus a bunch of other user interactions. The problem
is if a second user performs the operations before the first user is
finished, this incremented value gets thrown off. This is a .net web
application.
*******************************
* pri key * string * int * int *
*******************************
* 1 * abc001 * 3 * 6 *
* 2 * cde 002 * 5 * 9 *
user 1 gets cde 002 and with write back abc 003, note the increment.
user 2 also gets cde 002 as the latest record and also writes back abc 003.
this is incorrect. User 2 should write back abc 004.
If user 2 waited until user 1 was finish, this would take care of the proble
m.
Thanks,
--
Paul G
Software engineer.Errors in data concurrency will always be a issue for develoepr (especially
in WebApp as ong as the db-server will tell you that the row updated and you
have to reload the data, Like in SQL Server 2005)
Try reading this to be prepared for impementation.
http://msdn2.microsoft.com/library/y8fyz6xy(en-us,vs.80).aspx
http://msdn.microsoft.com/msdnmag/i.../09/DataPoints/
HTH, Jens Suessmeyer.
"Paul" <Paul@.discussions.microsoft.com> schrieb im Newsbeitrag
news:A1112DF0-41C1-4DB7-8F8B-6AF601C877CB@.microsoft.com...
> Just wondering if there is a way to put a lock on a record? For example I
> have the table below and a stored procedure reads the records in. Another
> procedure increments the value in column 2 and then writes a new record
> with
> this incremented value plus a bunch of other user interactions. The
> problem
> is if a second user performs the operations before the first user is
> finished, this incremented value gets thrown off. This is a .net web
> application.
> *******************************
> * pri key * string * int * int *
> *******************************
> * 1 * abc001 * 3 * 6 *
> * 2 * cde 002 * 5 * 9 *
> user 1 gets cde 002 and with write back abc 003, note the increment.
> user 2 also gets cde 002 as the latest record and also writes back abc
> 003.
> this is incorrect. User 2 should write back abc 004.
> If user 2 waited until user 1 was finish, this would take care of the
> problem.
> Thanks,
> --
> Paul G
> Software engineer.|||Yep this does seem like it would be a common issue. Will take a look at the
article.
"Jens Sü?meyer" wrote:

> Errors in data concurrency will always be a issue for develoepr (especiall
y
> in WebApp as ong as the db-server will tell you that the row updated and y
ou
> have to reload the data, Like in SQL Server 2005)
> Try reading this to be prepared for impementation.
> http://msdn2.microsoft.com/library/y8fyz6xy(en-us,vs.80).aspx
> http://msdn.microsoft.com/msdnmag/i.../09/DataPoints/
>
> HTH, Jens Suessmeyer.
> "Paul" <Paul@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:A1112DF0-41C1-4DB7-8F8B-6AF601C877CB@.microsoft.com...
>
>sql

Lock 'Childreen' Table

Hi!

I'm new in SQL and I′ve been the problem bellow:

I lock a record in one foreign table and automaticaly SQL lock the record that matches on primary table.

But this occours just in some primaries tables and not in all. I need that just the table that are of SELECT are lock. How can I do this?

Example:

** At open of the invoice:

SET ISOLATION LEVEL READ UNCOMMITTED

BEGIN TRANSACTION

SELECT * FROM INVOICE WITH (ROWLOCK UPLOCK) WHERE ( ID = 15 )

******* at the end of invoice:

INSERT INTO INVOICE ........

COMMIT TRANSACTION

END

******* The relationship are:

INVOICE <>> ITENS_INVOICE

ITENS_INVOICE <>> PRODUCTS

CUSTOMER <>> INVOICE

VENDORS <>> INVOICE

Just the INVOICE and PRODUCTS record′s involved in Transaction are lock. ( The CUSTOMER and VENDORS are not locked for example )

But I need that just INVOICE record be locked.

Thank′s for all and sorry my English.

Igor Sane

S?o Paulo - Brazil

PS: This doesn′t occours in SQL Express, just in SQL Server 2005....

Lock a row in the database

Hello,

I created a form that retrieve a number from a database. Can I lock that record so that other people cannot access it?

Similar to "lock" function in Visual Fox Pro database.

Best regards,
Tee Song Yann

Sounds like you are doing some form of incrementing.

A very good way to handle that would be to use the OUTPUT capability of SQL 2005 -it allows you to BOTH increment and retrieve the incremented value in the same query.

Perhaps something like this:

DECLARE @.MyNumberTable table
( NextNumber int )

UPDATE MyNumberTable
SET NextNumber = ( NextNumber + 1 )
OUTPUT inserted.NextNumber INTO @.MyNumberTable

SELECT NextNumber
FROM @.MyNumberTable

|||

Hello Arnie Rowland,

Thanks for the reply. Your solution solved the first part of my problem. The record locking part is still remain unsolved. A friend from another forum suggested:

SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SNAPSHOT | SERIALIZABLE }[ ; ]

What do you think?

All your help is much appriciated.

Best regards,
Tee Song Yann

|||

Are you attempting to create an auto-incrementing column? If so use an identity column.

Updating a central value and then holding a lock on it will introduce a bottleneck into your application as all connections will eventually be blocked waiting to update the same value.

|||

Dear Jerome Halmans,

My idea is:

1) I have a table that store various last used number for various document.

2) I will use those number combine with a few more code to create a meaningful serial number. (For example: An invoice will consist of XX XXXXXX XXXX which is the company code, year/month/date and next number of last used number.)

3) If another person login from another company, it will have different company code and different last number.

4) The fun part is I do not want to have jump numbers.

5) If the first user cancel the record inserting, then the number should stay unchanged.

6) The only way that I can think of is by locking the record while the first user is inserting the record.

Is there a batter way for me to achieve that?

Best regards,

Tee Song Yann

|||

I HIGHLY recommend dropping the idea of trying to maintain consequtive 'numbers' if users can 'cancel' the insert action. That will prove to be a major headache. That will create a significant blocking issue and a major performance problem.

Unless, of course, you do not assign the 'number' until the data is saved. At that point, the number can be reported back to the user (post-hoc), and there is not a cancellation issue.

|||

Thanks Arnie,

I get what you mean. I think I will follow your second idea.

Then just out of curiosity, SQL server can perform record locking?

Best regards,

Tee Song Yann

|||

Yes. Look up [ Hints -Locking ] in Books Online.

There are several ways to accomplish locking -it depends upon what you want to accomplish. With the exception of TRANSACTIONS, and in the absence of a clear understanding of the implications and effects, it is best to leave record locking behavior to the server.

Lock a row

I wanted to locked to hide a record to other user if the record is viewing
by one user.
Are there any way to do this?
Does lock the record can hide this record to other user?
Any information is great appreciated.
SourisYou can use query hints with a transaction but you're looking at scalability
issues. Why do you want to hide a row while someone is looking at it?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"souris" <soukkris@.viddotron.com> wrote in message
news:eJ3lg3kBFHA.2916@.TK2MSFTNGP10.phx.gbl...
I wanted to locked to hide a record to other user if the record is viewing
by one user.
Are there any way to do this?
Does lock the record can hide this record to other user?
Any information is great appreciated.
Souris|||On Sat, 29 Jan 2005 17:00:05 -0500, souris wrote:

>I wanted to locked to hide a record to other user if the record is viewing
>by one user.
>Are there any way to do this?
>Does lock the record can hide this record to other user?
>Any information is great appreciated.
Hi Souris,
You could use the READPAST locking hint. Keep in mind that this is only
usefull in some very specific circumstances. If you use this locking hint
in a statistical or reporting query, you'll miss some data without having
any indication if and how much data you miss.
A typical situation where the READPAST hint is useful is a queueing
system: each application / connection / agent / whatever reads the first
row from a wrok-queue table and acquire an exclusive lock; other agents
will read past this row and get the next non-locked one. The exclusive
lock ensures that the connection reading the row will be able to change or
even delete is after the data is processed.
BEGIN TRANSACTION
SELECT TOP 1 ....
FROM WorkQueue WITH (READPAST, XLOCK)
ORDER BY Priority
-- Do some stuff
IF (...) -- data changed
UPDATE WorkQueue
SET ... = ...
WHERE ....
IF (...) -- processing completely done
DELETE WorkQueue
WHERE ....
IF (...) -- check for unhandled errors
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks for the message.
This app is for a call center.
Because I do not want the customer get more than one call for the same issue
Souris
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eZJxXhlBFHA.1296@.TK2MSFTNGP10.phx.gbl...
> You can use query hints with a transaction but you're looking at
> scalability
> issues. Why do you want to hide a row while someone is looking at it?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "souris" <soukkris@.viddotron.com> wrote in message
> news:eJ3lg3kBFHA.2916@.TK2MSFTNGP10.phx.gbl...
> I wanted to locked to hide a record to other user if the record is viewing
> by one user.
> Are there any way to do this?
> Does lock the record can hide this record to other user?
> Any information is great appreciated.
> Souris
>|||Thanks for the information,
It is what I am looking for.
Souris
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:4b6ov0dsbu5e3gmhsne0omtr2ul2ac95ae@.
4ax.com...
> On Sat, 29 Jan 2005 17:00:05 -0500, souris wrote:
>
> Hi Souris,
> You could use the READPAST locking hint. Keep in mind that this is only
> usefull in some very specific circumstances. If you use this locking hint
> in a statistical or reporting query, you'll miss some data without having
> any indication if and how much data you miss.
> A typical situation where the READPAST hint is useful is a queueing
> system: each application / connection / agent / whatever reads the first
> row from a wrok-queue table and acquire an exclusive lock; other agents
> will read past this row and get the next non-locked one. The exclusive
> lock ensures that the connection reading the row will be able to change or
> even delete is after the data is processed.
> BEGIN TRANSACTION
> SELECT TOP 1 ....
> FROM WorkQueue WITH (READPAST, XLOCK)
> ORDER BY Priority
> -- Do some stuff
> IF (...) -- data changed
> UPDATE WorkQueue
> SET ... = ...
> WHERE ....
> IF (...) -- processing completely done
> DELETE WorkQueue
> WHERE ....
> IF (...) -- check for unhandled errors
> ROLLBACK TRANSACTION
> ELSE
> COMMIT TRANSACTION
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||I would probably suggest that this is not a great idea. Holding locks from
the client is troublesome, and if the connection dies you are not even going
to see that there was a call in progress. In this case, why not use a
status column (or table, if you want a complete history of status)
Then when the user gets the row, you set the status to in-progress, and set
the user that has the row in use. If they don't complete the call properly,
it will stay in an open state. When the user logs back in, you can check to
see if they have any open sessions and make them deal with them.
All of your other clients will look for rows that are not in-progress or
completed. Use the readpast hint as mentioned by Hugo to keep any clients
from having to wait. This way your management applications can see which
calls are in progress, what their status is, and it will still be "locked"
from the users that it needs to be locked from.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"souris" <soukkris@.viddotron.com> wrote in message
news:%23Fbdm4oBFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Thanks for the information,
> It is what I am looking for.
> Souris
>
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:4b6ov0dsbu5e3gmhsne0omtr2ul2ac95ae@.
4ax.com...
>