buffering can't lock this ?

[FoxWing]

Hi,I have a problem here that netheir optimistic nor persististic buffering can help. I need to lock the agent table in order to select the max no. Both buffering will not lock the table as this point. If that the case, how can i securely sure that no one else has used my max_no when i reach SQL insert ?

SET MULTILOCK ON
CURSORSETPROP("Buffering",5,"agent")

SELECT max(no) as w_max_no from agent into cursor tmp2 <------- point A. Select the max no.

SELECT agent
INSERT INTO agent (no,agent_code,agent_name) values ; <------- someone might have just inserted new record using my result selected at point A
(tmp2.w_max_no + 1,w_agent_code,w_desc)

BEGIN TRANSACTION
..The rest of my codes..

END TRANSACTION

How can i solve this without changing my table structure as i know auto-increament field will solve this trouble.


Thanks.

 

[Mike Lewis]

FoxWing,

As far as I know, there is no way of locking a table for a SELECT.

However, there is a workaround. Use CALCULATE instead of SELECT MAX(no). You can lock a CALCULATE with SET LOCK ON. Check the Help for more details of these two commands.

To lock the INSERT, you will have to obtain the lock "manually" by using FLOCK().

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[FoxWing]

Hi Mike,

Should i use the Manual Lock since i'm using buffering ?
Someone told me, i shouldn't use manual locking as it will defeat the purpose of the buffering.

Thanks.

 

[Mike Lewis]

FoxWing,

Someone told me, i shouldn't use manual locking as it will defeat the purpose of the buffering.

Well, that's not entirely true, but you're right that manual locking can conflict with the buffering.

Based on the code that you posted, there is no point in buffering in this case, since the SQL statements will disregard the buffered data anyway (that's true in VFP 8.0 and below, and in 9.0 by default).

Now that I've looked at your code more closely, I'll withdraw my suggestion about using CALCULATE and SET LOCK ON. I think it would be easier just to issue a FLOCK() before the SELECT MAX() and to release it after the INSERT. And don't do the buffering or the SET MULTILOCKS ON.

So, your code will look something like this:

SELECT Agent
IF FLOCK()
SELECT max(no) as w_max_no from agent into cursor tmp2
INSERT INTO agent (no,agent_code,agent_name) values etc,
UNCLOCK
ELSE
* Lock could not be obtained
ENDIF

Hope that makes sense.

Mike

Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[FoxWing]

Mike,

Thanks for your recommendation for the new approach. But, i'm now confuse why u suggest me NOT to use buffering.

SQL statements will disregard the buffered data anyway

My entire application is using buffering, and fully rely on SQL for INSERT/UPDATE/DELETE and your suggestion of not using buffering really put me into a worry state.

If that's the case, when is the appropriate time to use buffering b'cos without buffering, we might have partial update.

Please help.

 

[Mike Lewis]

FoxWing,

Sorry to have caused any worry. Let me clarify.

When I said that SQL statements disregard buffering, I simply meant that a SELECT statement operates on the data on disk, not the data in the buffer.

So, if you have buffering enabled, then you edit the data, then do a SELECT (before issuing TABLEUPDATE()), the SELECT will ignore the buffered data and return values from the table as it exists on disk. The result set will not include your edits.

This does not affect your INSERT statement in any way. So, as far as the INSERT is concerned, you can leave buffering enabled, and it will work just as you always expected. In fact, your entire code will work properly, regardless of buffering.

However, you have an additional requirement. You want to lock the table while it is being processed by the SELECT. Now, SELECT doesn't normally lock the table; the lock only comes into play when you edit the data, not when you read it. That's why I suggested an explicit lock with FLOCK().

The problem with that is, an FLOCK() will itself commit the buffer. So, if, just before you issued the SELECT, the buffer contains uncommitted edits, the FLOCK() would cause those edits to be written to disk. That might be a problem, for example if you wanted to validate the edits.

I don't know enough about what you are doing here to suggest a definitive solution. If the table hasn't been edited up to the point of the SELECT, there is nothing to worry about. If it has, you should probably be committing the changes at that point, after which you can issue the FLOCK() and carry on as before. If, for any reason, that's not possible, maybe in this particular case you should disable buffering.

I hope the above makes sense. I expect I've only succeeded in confusing you even more.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[MarciaAkins]

Hi FoxWing.

SELECT agent
INSERT INTO agent (no,agent_code,agent_name) values ; <------- someone might have just inserted new record using my result selected at point A
(tmp2.w_max_no + 1,w_agent_code,w_desc)

It looks like the field agent.no is a primary key field. If this is the case, you would be much better off to use a autoincrementing integer field ( VFP 8.0 and later ) or a NewID() function like this one and set up an insert trigger on your agent table to use the NewID stored procedure.

FUNCTION newid( tcTable )
LOCAL lcTable, lnNextVal, lnOldRepro
*** Check Param and convert to upper case
IF EMPTY(tcTable) OR TYPE( "tcTable" ) # "C"
  RETURN 0
ENDIF
lcTable = UPPER(ALLTRIM( tcTable ))
*** Save Settings and Open Systable if not already open
lnOldRepro = SET('REPROCESS')
IF ! USED('systable')
  USE systable IN 0
  *** Make sure that the table is not buffered
  CURSORSETPROP( 'Buffering', 1, 'systable' )
ENDIF
*** Now find the required table
IF SEEK( lcTable, 'systable', 'cTable' )
  *** Found the required table
  *** Get a Lock on systable
  SET REPROCESS TO AUTOMATIC
  IF RLOCK( 'systable' )
    *** Get next value and update systable
    lnNextVal = systable.iLastKey + 1
    REPLACE iLastKey with lnNextVal IN systable
    UNLOCK IN systable
  ELSE
    lnNextVal = 0
  ENDIF
ELSE
  *** Not Found!
  *** Needs a new entry in both systable and sysfields
  lnNextVal = 1
  INSERT INTO systable (cTable, iLastKey) VALUES ( lcTable, lnNextVal )
ENDIF
*** Return New ID
SET REPROCESS TO (lnOldRepro)
RETURN lnNextVal
ENDFUNC

Marcia G. Akins

http://www.TightlineComputers.com

 

[FoxWing]

Thanks guys..Ur comments are really helpful.