Record lock and unlock

[Qamar Zaman]

Hello Genius Programmers,

i am converting VFP9 application DBF to SQL Server, in VFP i lock the record for other users and update the record. Through flowing code.

SELECT max_no
RL=.F.
DO WHILE.NOT.RL
RL=RLOCK()
ENDDO
qam=VAL(max_inv)+1
qam2= replicate('0',7-LEN(transform(qam)))+transform(qam)
isu_inv=TRANSFORM(qam2)
replace max_inv WITH isu_inv
UNLOCK

Please suggest me code for SQL Server. Mean lock the record and update table.

Thanks in advance.

 

[Olaf Doschke]

SQL Server, besides other things, is a server. There are

a) other ways for counters. See integer IDENTITY(1,1)

https://docs.microsoft.com/en-us/sq...t-sql-identity-property?view=sql-server-ver15

and Sequences

https://docs.microsoft.com/en-us/sq...e-sequence-transact-sql?view=sql-server-ver15

b) have a server, that means requests are going through a queue, the server prioritzes a few things, usually you get what you want in chronoligical order of requests (sql) sent to the server, so you have less deadlocks. Locking is therefore something you rather not do to avoid deadlocks. You don't get concurrency situations mostly because sql server only executes in parallel what isn't acting on same resources. There are deadlocks, I actually experienced some with a scheduled task running an automatic data cleansing, but in general the queueing avoids such situations.

Having such an active central service works much better as clients acting on the data each by themselves and never knowing what other clients try at the same time, perhaps. It's a big advantage.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Mike Lewis]

Qamar,

What you are doing is called pessimistic locking. In other words, waiting until a lock is available before doing an update. (And that's not the best way to do pessimistic locking, but your question wasn't about that.)

SQL Server supports optimistic locking, not pessimistic. Basically, that means that you attempt to do the update, and worry about what happens if there is a collision (I'm simplifying this). You have to decide, for example, what to do if one user overwrites another's edits. Essentially that's a business decision, not a programming one.

Think about the effects of that sort of collision, then decide how to deal with it. For example, you might decide that a given user's updates is to have priority, or it might be more appropriate to combine the edits in some way. It's all a question of what is the correct solution in your application.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads