Visual FoxPro SQL Queries - SQL & Record Locking

Author

SQL & Record Locking

Andy Trezise

2006-02-17, 7:27 am

I have just started using views with a MYSQL back end database. What method
can I use to lock a record on the remote database?

Obviously when I use a view the table is downloaded to my PC as a kind of
offline dataset so I assume using RLOCK is not going to have any effect.
What strategy can I use?

Thanks in advance.

Alex Feldstein

2006-02-17, 7:27 am

On Thu, 16 Feb 2006 23:20:43 -0000, "Andy Trezise" <andy@work.com>
wrote:

>I have just started using views with a MYSQL back end database. What method
>can I use to lock a record on the remote database?
>
>Obviously when I use a view the table is downloaded to my PC as a kind of
>offline dataset so I assume using RLOCK is not going to have any effect.
>What strategy can I use?

Server databases do not have a mechanism for doing pessimistic
locking. RLOCK() is a VFP concept.

A common method is to set a flag on the row or have a separate table
that contains the ID of the rows that should be considered locked.

Alex Feldstein
____________________
____________
Microsoft Visual FoxPro MVP
Please respond in the public groups so that everybody
can benefit from the exchange.
Favor de responder en los foros públicos asi todos se benefician.
(address munged with ROT-13)

Blog: http://www.bloglines.com/blog/AlexFeldstein
Website: http://feldstein.net

Bernhard Sander

2006-02-17, 7:27 am

Hi Andy
> I have just started using views with a MYSQL back end database. What method
> can I use to lock a record on the remote database?
>
> Obviously when I use a view the table is downloaded to my PC as a kind of
> offline dataset so I assume using RLOCK is not going to have any effect.
> What strategy can I use?

As Alex wrote, server DB don't use pessimistic locking.
But you could misuse an open transaction for this purpose:

switch transaction to manual (SQLSETPROP)
replace some known value with itself from every record you want to lock
update the backend
do your editing
update the backend
end transaction (SQLCOMMIT or SQLROLLBACK)

The DB gurus don't like this idea.
But i don't like to resolve conflicts afterwards. I also don't like to run code
on the client side to check extra locking tables or locking flags. I think, this
_must_ be done by the server. If there is another client program, that don't
want to do this kind of checks, you are lost.

Regards
Bernhard Sander

Andy Trezise

2006-02-17, 9:24 am

Thanks Bernhard.....let me make sure I understand you...

> switch transaction to manual (SQLSETPROP)

nConnect = SQLCONNECT("MySQLConnect")

SQLSETPROP(nConnect,
2)

USE my_view IN 0 CONNSTRING nConnect

> replace some known value with itself from every record you want to lock

REPLACE my_view.order_number WITH my_view.order_number

> update the backend

??? =TABLEUPDATE(.t.,.f.,"MY_VIEW")

> do your editing

REPLACE .....

> update the backend

??? =TABLEUPDATE(.t.,.f.,"MY_VIEW")

> end transaction (SQLCOMMIT or SQLROLLBACK)

=SQLCOMMIT(nConnect)

Not quite sure why I'm doing the TABLEUPDATES......how can I check if the
record is locked from another workstation?

"Bernhard Sander" <fuchs@individsoft.de> wrote in message
news:eCq73J8MGHA.984@tk2msftngp13.phx.gbl...
> Hi Andy
>
> As Alex wrote, server DB don't use pessimistic locking.
> But you could misuse an open transaction for this purpose:
>
> switch transaction to manual (SQLSETPROP)
> replace some known value with itself from every record you want to lock
> update the backend
> do your editing
> update the backend
> end transaction (SQLCOMMIT or SQLROLLBACK)
>
> The DB gurus don't like this idea.
> But i don't like to resolve conflicts afterwards. I also don't like to run
> code on the client side to check extra locking tables or locking flags. I
> think, this _must_ be done by the server. If there is another client
> program, that don't want to do this kind of checks, you are lost.
>
> Regards
> Bernhard Sander

Bernhard Sander

2006-02-17, 9:24 am

Hi Andy

> nConnect = SQLCONNECT("MySQLConnect")
> SQLSETPROP(nConnect,
2)
> USE my_view IN 0 CONNSTRING nConnect
>
> REPLACE my_view.order_number WITH my_view.order_number
>
> ??? =TABLEUPDATE(.t.,.f.,"MY_VIEW")
>
> REPLACE .....
>
> ??? =TABLEUPDATE(.t.,.f.,"MY_VIEW")
>
> =SQLCOMMIT(nConnect)

I think you got it.

> Not quite sure why I'm doing the TABLEUPDATES......how can I check if the
> record is locked from another workstation?
It's at the first TABLEUPDATE where the transaction really starts. It depends on
the back end system, whether records or even tables are locked or in which way
the DB ensures the transaction.

The second TABLEUPDATE simply sends your intended changes to the DB.

At both cases you should store and check the result of TABLEUPDATE:
llTableupdate = TABLEUPDATE(...)
IF !llTableupdate
** error handling
ELSE
** normal sequence
ENDIF
If TABLEUPDATE returns .F. then there is a situation where you should not
continue. This can happen, when the desired records are involved in other
transactions or things like that. So this is a point to check whether someone
else "locked" the record.
For more info see VFP help of TABLEUPDATE

Regards
Bernhard Sander

Sponsored Links