December 23, 2004 at 11:01 am
Hi all.
I am developing on a MSDE 2000 platform. I have from anytime 2-4 processing modules accessing the same field in a certain table. This field is a global variable that keys (sequentially) hardware functions (printing a packet to serial printer), processing table updates (increment pointers), or logging info on trace files. At various times any of these modules have access to the field.
int a=1 then process 1 a=1, process 2 a=1
process 1 = a++, process 2 = a++ in order for this to work, you have to be able to lock one process out while another performs its action. Currently, I have locked one process from performing continuous updates to one table, only one table update at a time for one process; but I do not have control of other processes trying to access field. If process 1 a = 2 but process 2 is updating a=1 then both processes will have a result of a=2 instead global variable resulting in a=3. "a" keys other modules to process sequentially for system efficiency. Once this happens, process modules lockup and crash.
Should I use triggers or stored procedures to control table locking or sequental access control to table field. Another thought was to develop a process controller to access table field where all processes report to this process controller.
Any help or suggestions would be greatly appreciated!
sivan
December 28, 2004 at 1:21 pm
YOu should use a locking hint to hold an exclusive lock on the row until your transaction completes.
Select a from tableA with(updlock)
See BOL under Query Hints.
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
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply