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
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",
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.,"Categor
sqlexec(han,"if @@trancount > 0 commit")
else
sqlexec(han,"if @@trancount > 0 rollback")
tablerevert(.t.,"categorie
endif
Cetin,
Can u throw more light on this ??
This is in spt although u can modify this for use with cursoradapter.
** han as a valid handle.
cursorsetprop("buffering",
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.,"Categor
sqlexec(han,"if @@trancount > 0 commit")
else
sqlexec(han,"if @@trancount > 0 rollback")
tablerevert(.t.,"categorie
endif
Cetin,
Can u throw more light on this ??
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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.
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.
Suhas,
I understand :) Good sampling.
I understand :) Good sampling.
Hey Cetin,
Thought u had been for lunch .. :)
By the way can u highlight it more clearly and more ways ?
Thought u had been for lunch .. :)
By the way can u highlight it more clearly and more ways ?
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.
Do u have a soft copy of the same ? and if possible can u send it to me .. ofcourse if u dont mind....:)
Forget Pessimistic locking, Atleast more on locking startergies etc if u have a e-version ....
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.
Well can it be "For my Eyes Only" ..... ... :) just joking.
ASKER
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
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
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
**************************
** 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",
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("UpdateName
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("KeyFieldLi
** The next property specifies which fields can be updated.
=CURSORSETPROP("UpdatableF
address, city, state, zip, contract")
** The next property enables you to send updates.
=CURSORSETPROP("SendUpdate
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.
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
**************************
** 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",
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("UpdateName
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("KeyFieldLi
** The next property specifies which fields can be updated.
=CURSORSETPROP("UpdatableF
address, city, state, zip, contract")
** The next property enables you to send updates.
=CURSORSETPROP("SendUpdate
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.
ASKER
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
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
create table ##LocksInEffect (tablename varchar(128), pkid uniqueidentifier[,bySessio
Locking by itself is a chapter and transactions is yet another :(