Can someone please test following situation in Elevatedbmanager:

1. Start a new elevatedbmanager and create a trigger on one test-table:
CREATE TRIGGER "test1" BEFORE UPDATE ON "OP"
BEGIN
  set newrow.status=newrow.status;
END

2. Open one table in your database and change the first dataset / set it in EDIT-Mode and leave it in edit-mode ..

3. Open a new elevatedbmanager
4. Use following SQL (not script) and change the table and column names to your table:

update op set iostatus=iostatus+1 where buno like 'EG18050%'! -- << your first edited row should be in this filter
update op set iostatus=iostatus+1 where buno like 'ABC%'
update op set iostatus=iostatus+1 where buno like 'EG18050%'!

>> Note that it can be any table, change the fields to your fieldnames and the first and last like to somewhat that it changes minimum 2 rows in your table.

5. YES, we know that the second update is not having a delimiter ! on the end, thats the problem we want to show
6. Click to EXECUTE
7. Now you get: ElevateDB Error #1005 Cannot lock the row in the table OP

8. Switch over to the first ElevateDB-Manager where the first datarow is in EDIT and try to SAVE / POST it
9. Not each time but sometime a chance at 50:50 we get
ElevateDB Error #300 Cannot lock the table OP in the schema Default for write access.

10. but sometimes in my tests for one time i get a #601 with corruption

Mostly after this errors the table is locked and can't be freed, only closing edbmanager or restarting service frees the locks.

I don't know if it is the trigger (only this trigger exists) or is it the count of indexes in this table or is it only the elevatedbmanager ... Thus i need some test from you to see if this happens on your side too.

All tests are done with ElevateDB-manager and this settings:
* Remote
* Pessimistic locking

Thanks for some results,
Yusuf Zorlu
MicrotronX

Yusuf


I haven't tried the full sequence since you clearly have defective code

update op set iostatus=iostatus+1 where buno like 'EG18050%'!
update op set iostatus=iostatus+1 where buno like 'ABC%'
update op set iostatus=iostatus+1 where buno like 'EG18050%'!

The way EDBManager works is that this reduces to

update op set iostatus=iostatus+1 where buno like 'EG18050%'! -- << your first edited row should be in this filter
update op set iostatus=iostatus+1 where buno like 'ABC%'update op set iostatus=iostatus+1 where buno like 'EG18050%'!

and should error out. The trigger as posted is also clearly nonsense.

Just to conclude your tests, what happens if you close the instance of EDBManager that's altering the metadata BEFORE you try running the updates?

Roy Lambert

Roy Lambert wrote:

<< The trigger as posted is also clearly nonsense.

Just to conclude your tests, what happens if you close the instance of EDBManager that's altering the metadata BEFORE you try running the updates?

Hi Roy, yes i know that the trigger and the updates are nonsense. It is only something to be able to reproduce the errors, nothing more. It is nothing productive because we're not able to reproduce some problems on customer side, but i was able to reproduce a #300 with a #601 with this small test.

If i close the instance which is altering the tablerow i only get a errormessage that my sql has an error but that is not the problem. The problem is somewhere in the internal locking mechanism which we're searching and trying to reproduce as it was not possible for us and for Tim to reproduce that in the last months.

Today a customer called us with a lock where he had to restart the servers service to release it, nothing else helped so we've started again to search for the problem and now trying to reproduce something for Tim ...
Yusuf Zorlu
MicrotronX

Now i tried another thing:

1. Edit first row in table with first instance of Edbmanager
2. run update to same dataset but having also other rows updates
>> #300 Cannot lock the table OP in the schema Default for write access

3. save your changed data in 1. with post
4. run again 2.

if i do this for 2 to 4 times at some point the edbmanager with the tableview where i changed a row starts hanging and waiting for something on .post

Disconnecting and re-connecting the session does not solve the problem. After it was hanging the first time for some seconds it will hang again and again till you close all edbmanager instances + restart the service.
Yusuf Zorlu
MicrotronX

And now tried it again with a lot simplier example:

1. edbmanager + open table + edit first row
2. second instance edbmanager + update sql to table and try to change rows incl. your edited row
3. #300 error
4. try to post in first instance where the dataset is in dsedit > boom in my case

Will search for a reproduction where i get the problem each time in same order ...
Yusuf Zorlu
MicrotronX

3. is a #1005 and not a #300, sorry
Yusuf Zorlu
MicrotronX

Yusuf



<<If i close the instance which is altering the tablerow i only get a errormessage that my sql has an error but that is not the problem. The problem is somewhere in the internal locking mechanism which we're searching and trying to reproduce as it was not possible for us and for Tim to reproduce that in the last months.>>

I think its partly the problem. Ages ago when I played around on mainframes the SysOps & DBAs would kick everyone out (or run overnight) when making changes to the metadata and then only allow them back in after it was all done. A trigger might not be as sensitive as changing the structure but the same rules apply - you're messing with the system tables. The fact that if you close the instance of EDBManager messing with the system tables means all you get is the sql being rejected is a good indication that you shouldn't be doing that.

Roy Lambert

Yusuf


Maybe I'm totally misunderstanding. You place a row in edit using navigational methods, then try and update the same row using sql. What are expecting to happen - I'd expect any rows updated prior to the locked one to update, then an error and neither that row or the others updated.

I'm assuming here that you have pessimistic locking set.

It might be worth trying locally and see if its the server hanging - it might be that your testing is leaving a wadge of dangling connections.

You have the source for EDBManager you may be able to find what's happening by compiling your own version and adding breakpoints.


Roy Lambert

Yusuf,

I have just recreated here your example and what I see is "ElevateDB Error #1005 Cannot lock the row in the table OP" when the *first* SQL statement is executed. The other SQL statements are never executed because it stops at the first one because of the lock error.
This is exactly what is supposed to happen - I don't understand your point.
The trigger has no effect whatsoever in this example.

--
Fernando Dias
[Team Elevate]

Yusuf Zorlu wrote:

> Can someone please test following situation in Elevatedbmanager:

With the steps that you laid out, I can't reproduce anything other than the Error #1005 that I would expect in this situation.


> All tests are done with ElevateDB-manager and this settings:
> * Remote
> * Pessimistic locking

Can you provide some more details on the server and session settings so that we can duplicate your environment as closely as possible:

Server Engine Settings:
 Is Cache Modules in Memory enabled, and does the table reference any external modules?
 Is File I/O Buffering enabled and if so, what are the settings?

EDB Manager Session Settings:
 What are ALL of the settings set to under the Locking / Buffering tab?
 

You also need to look at potential external access to your data....

Have you configured your anti-virus software to exclude the EDB data files or the folders where you store your data?

Do you have any live backup programs running like Carbonite touching your data?  These can produce the 300 errors that you are seeing if you attempt to update a file while they are locked by the backup software during the actual file backup.   BTW, if it's not currently enabled, enabling the File I/O Buffering on the server engine can prevent these errors because the server will open the files exclusively.