Hi Guys,
   As I understand it, TTable.Append briefly locks the Table while it appends the new record then drops into edit mode.
   Does .Append hold the Table lock during the Edit or does it relinquish the Table lock and implement a Record lock for
the Edit phase?

Regards,
Ian

My answer is without knowledge of EDB specifically, but my understanding of an APPEND is that there is no lock until after the POST.  The append would likely just give you a buffer to enter the new information into but wouldn't become part of the table until the POST.  Thus making the locked period incredibly small.  As an example if you are generating an AUTOINC type field there would be no value until after the POST.

Ian


Whilst I have no idea of what's going on under the hood I do know that multiple people can insert & edit simultaneously. Concern about that is the only reason I can guess at for your question. If its something more esoteric, or just general nosiness let us know.

Roy Lambert

Hi Roy,
   The first aspect was to understand what was actually hapening around the .Append/ Edit/ .Post sequence.

   After Greg's post I found the relevant info in the EDB Docs that confirms Greg's info.  Tks Greg.

   The issue/scenario is that there are up to 18 users working in local mode.  The database & Apps are on a Win 2012
Server and the Workfstations are all Win 7.  Every now and then they get an EDN Error # 300 for a Table Lock when they
go to add a new record.  "ElevateDB Error #300 Cannot lock the table LineItems in the schema Default for write access."

   I wanted to make sure I understood what was supposed to happen and therefore where to look.

   Turns out it is on the .Post action.

   I can trap the 'error' however I don't want the attempt to Post to be aborted if possible.

   I had thought of using the OnPostError event to capture the situation but as I said I need to give the User the
opportunity to try to completed the Append/Post action.

Regards,
Ian

Ian


I was trying to work out how you would get collisions with 18 users and finally noticed LineItems. Still seems surprising.

Roy Lambert

Hi Roy,
   Yes, equally unexpected.
   I have increased the Row Locking Retry Wait Time to 200mS to see if that mitigates it at all.

Regards,
Ian

Ian


If my guesses about what you're doing are correct I think transactions are your friend.

My guess is that someone will enter a job ticket, probably into a non database linked form and one or more lines associated with that job ticket. They then click post and your system:

1. posts the job ticket and gets its number
2. loops round posting the lines one by one

if so wrapping a transaction round the lot will guarantee consistency, and the next person posting will wait until the previous transaction has committed or rolled back before starting - wait times may need adjusting but my guess is that posting to these tables shouldn't be a long experience

The other approach of editing tables directly would need a different approach. Header & line is one of the few areas where I consider not using database controls to be good practice. Bit more programming to more stable.

Roy Lambert

Ian,

<< As I understand it, TTable.Append briefly locks the Table while it appends the new record then drops into edit mode. Does .Append hold the Table lock during the Edit or does it relinquish the Table lock and implement a Record lock for the Edit phase? >>

Greg is correct: there are no table/row locks in place until the Post.  Appends/Inserts always behave as an atomic operation and there are no row locks hanging around for the duration of the append, unlike Edit with pessimistic row locking.

Tim Young
Elevate Software
www.elevatesoft.com

Ian,

<< Every now and then they get an EDN Error # 300 for a Table Lock when they go to add a new record.  "ElevateDB Error #300 Cannot lock the table LineItems in the schema Default for write access." >>

That's not a normal error - you should never see a write lock error, and if you do, it indicates a serious issue with the underlying environment/application.  EDB will wait up to ~90 seconds for a write lock, and write locks are *only* held for the duration of writes (they are not something that is acquired and held across multiple operations, even for transactions).

However, what *will* block a write lock is an operation that is causing a read lock to be held on a table for a long period of time.  Un-optimized queries that perform large row scans are the common culprit, so you need to make sure that your queries are all performing in a reasonable manner.  The new statement performance logging in 2.30 is your friend in this regard:

https://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=ENABLE_STATEMENT_LOGGING
https://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=DISABLE_STATEMENT_LOGGING

In a non-EDB Server environment, the logging will take place on a per-client-application-instance basis instead of in a common place (the EDB Server).

Tim Young
Elevate Software
www.elevatesoft.com

Ian,

Sorry, the statement logging is in 2.29, not 2.30.

Tim Young
Elevate Software
www.elevatesoft.com