VFP 8.0 and Sql Server: implementing pessimistic locking


Folks,
As some of your might already know, our VFP 8.0 application is being upgraded to use Cursor Adapters to connect to Sql Server 2000. I'm now in a situation where I'm trying to AVOID designing our own customized locking schema. Previously we would simple issue an RLOCK() when editing a record in a free table, then UNLOCK() when user was done editing. Now things get a bit more complicated.
Because we're still not sure of  how to best implement sql server locking with a VFP front end, my boss has suggested a customzied locking design. His idea is that VFP would write to a central "lock" table when a user decides to modify a record. Then if another user comes in and tries to modify the same record, his session would check the contents of the central "lock" table to see if the first user is already modifying the same record.
Of course, this design is NOT desirable. However, if I don't implement something soon, I'll have to go with his design.

I do understand the different sql server lock modes (S/U/X,etc) and transaction isolation levels (read commited/uncommited, etc) and locking "hints". In light of that, can someone give me a suggestion on how to implement a pessimistic locking scheme? We definitely want to implement the pessismistic method (i.e.  user edits a record and holds the exclusive lock until he's done).

I'm on the fringe of implementing a solution, but I want it to be solid.

Thanks again for all of your past advice.
Sincerey,
John
LVL 1
jgroetchAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cbasozCommented:
At keast if you'd do that with a central table then create a global temp table. Then it'd be SQL server removing the table when all connections end. Otherwise you need to find a way to differentiate a user holding the lock for real vs a dead station. You can create a global temp file using 2 sharps in front of tablename. ie:

create table ##LocksInEffect (tablename varchar(128), pkid uniqueidentifier[,bySessionCookie varchar(50)])

Locking by itself is a chapter and transactions is yet another :(
suhashegdeCommented:
Not 100 % sure but will give u a start as i am learning too.(Although I would never use pessimistic locking as the record would be not avaiable to other users)

This is in spt although u can modify this for use with cursoradapter.

**  han as a valid handle.
cursorsetprop("buffering",5,0)
set multilocks on

Sqlexec(han,"use Northwind")
Sqlexec(han,"Begin Transaction")
Sqlexec(han,"SELECT * FROM Categories with (UPDLOCK,ROWLOCK) WHERE CategoryID=3","categories")
** brow or modify
** now make the cursor Categories updatable
if tableupdate(2,.t.,"Categories")
    sqlexec(han,"if @@trancount > 0 commit")
else
        sqlexec(han,"if @@trancount > 0 rollback")
        tablerevert(.t.,"categories")
endif

Cetin,
Can u throw more light on this ??
edwardsearchCommented:
Do't create any auxilary table/records to hold the locking information for all updation. it is a bad design. It will be required if any admin user is started editing record and rest of the user don't have a permission to edit the record, fot that u han have a logical field for each of your table (like lockstat, lockuser)

As the defaul sql server locking is "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ". So maximum of the select statement will not required locking hint.

Design like this

= SQLEXEC("SELECT TOP 1 FROM FROM Categories","a_cursor")
= CURSORSETPROP(("buffering",5,a_cursor")) && U know the default is always 3 for sql cursors
Then specify the key files to update
then specify the column list to be update

update the records
and execute  tableupdate("a_cursor))

Always lock the sql records using update dbname.tablename set filename = filedname with (updlock holdlock) where <rowindentifier> = nnnnn

Don't use VFP TB for transaction, because SQL will not respect it. (offcourse u required Transaction in both VFP and SQL if you required restart the TB again if any error occurs)

Since i don't have VFP installation in my system, so that i could not give you an example.

- Edward

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

jgroetchAuthor Commented:
Cetin: Thank you for your suggestion, although I had already figured out how to use the "hints" you suggested above. I had already found that SELECT...WITH (XLOCK) does in fact lock my row and so I was considering using that.

Edward: I think your suggestion might be more of what I'm looking for. I didn't really understand how SERIALIZABLE would work for me, but you cleared that up. I just tested it the following way:

/* In Query analyer, open a transaction and leave it open */
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
begin tran
   select * from test_table where id = 101

*-- NOW IN VFP , Create my cursor adap object "lo" and assign my update command
lo.InsertCmd = "INSERT INTO test_table (name) values ('JOHN SMITH, JR.') where id = 101"
append Blank
If Not TableUpdate()
  wait window "can't update..."
endif


In VFP, my TableUpdate hangs until I either commit or rollback my Sql Server transaction on id = 101 (of course if I set the appropriate lock_timeout in lo.InsertCmd, TableUPdate() will timeout with an error).

Thanks again,
John

suhashegdeCommented:
Well,
So far so good.

Leaving the queryanalyser aside,Thinking from the VFP point of view how would u pessimistic lock the row ? for another instance not to read or update or have a shared lock ?

Things work a bit different than the Query Analyser and VFP.


       First senario

** first instance
sqlexec(handle,"Begin Transaction")
sqlexec(handle,"select * from test_table where id = 101")

*** second instance
sqlexec(handle,"Begin Transaction")
sqlexec(handle,"select * from test_table where id = 101")

what would u expect in the second instance ? the row to be locked or timed out.
no there is a read and infact u get a cursor which u can update.

Second Senario

** first instance
sqlexec(handle,"Begin Transaction")
sqlexec(handle,"select * from test_table (updlock,rowlock) where id = 101")

*** second instance
sqlexec(handle,"Begin Transaction")
sqlexec(handle,"select * from test_table where id = 101")
U still can read but try updating the cursor. it times out. Thats after all the work is done that u come to know that the records are locked by other users. Not the pessimistic way.

THird Senario

** first instance
sqlexec(handle,"Begin Transaction")
sqlexec(handle,"select * from test_table (updlock,rowlock) where id = 101")

*** second instance
sqlexec(handle,"Begin Transaction")
sqlexec(handle,"select * from test_table (updlock,rowlock) where id = 101")

The query itself times out Indicating that the record is locked . Use aerror() to determine what is returned.

So when u want to lock the record pessimistically lock in your select queries itself rather than with the update statement.

Another senario with statements.

sqlexec(handle,"Begin Transaction")
sqlexec(handle,"select * from test_table where id = 101")
...
* here the record is shared locked and would be exessible to others for change.
....
sqlexec(handle,"INSERT INTO test_table (name) with (updlock,holdlock) values ('JOHN SMITH, JR.') where id = 101")

** would cause the locks to be held only after the execution of the insert occur -- an optimistic way ??
** But the record would have been excessible to others before the execution of the insert and after the select and others could ** change the record . again an optimistic way ?
** so it is better to lock with in the select and the work.

Also read more on lock escalation to understand better .

I hope U understand what i am trying to say.
suhashegde.
cbasozCommented:
Suhas,
I understand :) Good sampling.
suhashegdeCommented:
Hey Cetin,
Thought u had been for lunch .. :)
By the way can u highlight it more clearly and more ways ?
cbasozCommented:
I'm afraid I can't. From my POV this subject is more than 100 pages long. Whatever I say it'd be incomplete and debatable :) After all I don't really see a good reason for pessimistic locking.
suhashegdeCommented:
Do u have a soft copy of the same ? and if possible can u send it to me .. ofcourse if u dont mind....:)
suhashegdeCommented:
Forget Pessimistic locking, Atleast more on locking startergies etc if u have a e-version ....
cbasozCommented:
Sorry I have it as hard copies ( Inside SQL server 7.0,Inside SQL server 2000, SQL server Database Implementaion and trining (6.5-7.0) all by MS press) . They've soft copies accompanying but is illegal to separate from books as far as I know.
suhashegdeCommented:
Well can it be "For my Eyes Only" ..... ... :) just joking.
jgroetchAuthor Commented:
suhashegde,
  Thanks for the examples. I was doing similar tests directly in query analyer and also with QA and VFP. Your examples make a lot of sense; although when you say "u get a cursor which u can update", the only place you can update that cursor is locally. Any real database updates have to be sent back with the Sql Update command - i.e. SqlExec(lnhandle, "Update test_table set ... where id = 101").
  When you send an Sql Update command (via Sql Pass Thru) in your second session, it does in fact hang.  That is true.
  I use the Cursor Adapter, so I issue the TableUpdate() after updating my cursor.
Thanks again,
John
suhashegdeCommented:
Well I still dont understand why u would like to send the "Update - sql" to the server ? Or may be my knowlegde is lacking.
Cant u make the cursor updatable in CA like in SPT ? and then issue a tableupdate ? still confused.

CA i think are the next to SPT so must provide all the functionality of SPT

I dont think so that only place u can make the curosr updatabale is locally check out the following codes from MSDN.
Cut and Paste from MSDN

************************** Beginning of code ******************

   ** This example is using SQL Server as the back-end.
   ** Your back-end server may vary.
   Handle = SQLCONNECT("SQL421","sa","")
   IF handle<0
     WAIT WINDOW "connection not made"
     CANCEL
   ELSE
    cursorsetprop("buffering",5,0) && added by me
    set multilocks on  && added by me
     =SQLEXEC(Handle, "select * from authors")
     =CURSORSETPROP("Tables", "authors")
     ** The next property must include every remote field matched with the
     ** view cursor field.
     =CURSORSETPROP("UpdateNameList", "au_id authors.au_id, au_lname;
       authors.au_lname, au_fname authors.au_fname, phone;
       authors.phone, address authors.address,;
       city authors.city, state authors.state,;
       zip authors.zip, contract authors.contract")
     =CURSORSETPROP("KeyFieldList", "au_id")
     ** The next property specifies which fields can be updated.
     =CURSORSETPROP("UpdatableFieldList", "au_lname, au_fname, phone,;
       address, city, state, zip, contract")
     ** The next property enables you to send updates.
     =CURSORSETPROP("SendUpdates", .T.)

     BROWSE
    tableupdate(2,.t.)   && added by me
     USE
     =SQLDISCONNECT(handle)
   ENDIF
   ************************* End of code ******************************


<<SqlExec(lnhandle, "Update test_table set ... where id = 101").>>

Does not need a tableupdate as far as I know. It needs a transaction end i.e. Commit or a rollback. CA is different ? Never worked with them.

AFAIK the above statement sends the SQL to the backend for direct execution. Agreed that would work but Suppose there are 10 records u want to update then would u put them in a loop and send the updates ?? Yes a way i would do earlier Until Cetin showed me how.
jgroetchAuthor Commented:
suhashegde:
I do recall testing that solution above using a VFP database with a remote view attached to it. Then I was able to set the cursor prop's as mentioned in that code you posted. If i'm not mistaken it needs a tableupdate, though. I know that in the cur Adap it definitely needs a TableUpdate().
In fact when you instantiate your Cur Adaptor object - say oCA - then  you can assign the Update/Delete/Insert commands with the appropriate prop's:

oCA.UpdateCmd = "Update ... Set...Where..."
oCA.SelectCmd = "Select * From ..."
oCA.DeleteCmd = "Delete from ... Where..."
oCA.InsertCmd = "Insert Into..."

Then you issue the oCA.Cursorfill() command to fetch the data; and when you update the local cursor with the Fox 'replace' command, you then issue TableUpdate(). The tableupdate will then send your Update command to the box. Same thing when you issue the fox Append/Delete command...the tableupdate() sends the changes up.
In my case, however, I'm assigning stored procedures to these properties - i.e. oCA.SelectCmd = "Exec p_SqlSelect <table>, <col list>, <Where>, <Order By>" ...

John



It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FoxPro

From novice to tech pro — start learning today.