StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » How to lock a record?

How to lock a record?

Edhy Rijo — Thu, 06 Oct 2011 07:50:03 GMT

I have the need to update a field "InStockQty" with an accumulated value of "InStockQty + 100" or some other value. In VFP I used "RLOCK" to lock the record and make sure my update is done before allowing somebody else to update this field, now how can I do the same with SF/.NET/MS-SQL?

RE: How to lock a record?

Edhy Rijo — Thu, 06 Oct 2011 07:50:03 GMT

Hi Gerard,
In this particular case I decided to use a BO.CustomFieldProperty (CFP) to show this value and calculate it on the fly with a stored procedure (SP).

Since through a Business Binding Source (BBS) you can read a BO in a report, it works just fine as long as the SP is optimized. For other cases I simply use a BO transaction to save the data, not sure it will work as the VFP RLOCK.

Make sure you check out article links posted by Trent here since they are very interesting.

RE: How to lock a record?

Ger Cannoll — Thu, 06 Oct 2011 04:43:58 GMT

Hi Edhy.

I realise this is a fairly old thread , but I have a similar issue now and want to 'Lock' a record for similar Stock type update.

Just wondering if you would care to share what you did in the end here ?

RE: How to lock a record?

Edhy Rijo — Mon, 04 May 2009 20:13:32 GMT

Hi Peter,
Thanks for the advice, I really have not experience with SQL Triggers, but will sure check it out.

RE: How to lock a record?

Peter Jones — Mon, 04 May 2009 17:36:43 GMT

Hi Edhy,

Not sure what the logic is here but don't forget the good old SQL trigger for keeping data values in sync.

Cheers, Peter

RE: How to lock a record?

Trent L. Taylor — Fri, 01 May 2009 09:17:38 GMT

This is really the best way. :)

RE: How to lock a record?

Edhy Rijo — Fri, 01 May 2009 09:15:45 GMT

Thanks Trent,
I think I will go with the Transaction since I want to try to keep all logic at the BO level since part of this application will escalate to be a Web Application.

RE: How to lock a record?

Trent L. Taylor — Fri, 01 May 2009 09:07:24 GMT

OK, this can get to be a complex topic. However, there are in fact lots of examples out there on how to do this. As you have probably already determined, doing this on a BO is not going to do any good as the data is disconnected. You are going to have to do this as the SQL level.

There are a lot of samples out there. Here are a few:

http://www.wwwcoder.com/tabid/68/type/art/parentid/191/site/6508/default.aspx

http://www.sqlteam.com/article/introduction-to-locking-in-sql-server

Another point here. Depending on what you are trying to do, concurrency may be an option as well as record locking can very easily tie into concurrency. For example, if you are using row versioning, it would be very easy to determine if someone else has touched that record when you save. If so, then you can take it into account.

Another option would be transactions. Going through transactions can also play into the process here as when you start a transaction you can prevent anyone else from touching the records until the transaction is complete. This is actually the approach that we take in our medical software when this could be a potential issue.