Update Locking and Notification

[mobius]

We’re creating a program and need some advice about locking. The program will select data for the user to view; the user may or may not then perform an update on the set. There exists a possibility of another user attempting the same operation at the same time. Thus we want to have the first user lock the data set and the second to be able to select only until the first is complete. We’ve run some tests using the UPDLOCK hint. It works as expect except for the blocking. If a connection attempts to create another Update Lock after another connection has one, then the new connection is blocked. We’re looking for a way to let the program know that an update lock is in existence before it tries to create a new one. The sp_lock procedure can display all the locks, but you can not select specific values (i.e. select any update locks on ObjectID = x). Do any of you know another way or how we can use sp_lock for our purpose?

Thanks in advance.

[skhanal]

Do you really have to make your application serializable?. This has a severe scalibility limitation.

You should let SQL Server handle concurrency, if another user modifies data while a user is viewing the data and tries to update later, your front end application should handle this by checking the original value and if it is different inform the user.

If you still want to pursue the updlock, you can find all lock information from syslockinfo table.

[mobius]

Thanks for the quick reply. I’m not sure I fully understand your recommendation. I’d love to allow the server to take care of everything for us, however how can an application verify data alteration without holding the new altered data and going through each existing (server side) value? In the situation we have this would be some 4k rows / 8 columns.