SA12 - Load Table and Select conflict

[former_SQLA_member1694890]

After moving our product from SA10.0.1 to SA12 (12.0.1 EBF 3311(Linux); 3324(Windows) we encountered with problem which haven't been appears in SA10. In case long Select(few hours report) from table A , Load Table statement from file in the same table A cannot be done and wait till Select will be finished. Select(report) is done from Java server application connection to DB by jconnect(jdbc3) or Insert from Select is done from T-SQL stored procedure.

Question which change between SA10 and SA12 is done regarding to Lock table mechanism and what can be done to solve this problem!?

[johnsmirnios]

LOAD TABLE has always required an exclusive lock on a base table -- that is not new behaviour in SA12. You could use LOAD TABLE to put the data into a temporary table and then use 'insert ... from select ...' to put it into the base table but it is extra processing and requires temp space proportional to the size of the file you are loading.

[johnsmirnios]

Oh, you can alsu use INSERT FROM ... OPENSTRING rather than using a temp table. INSERT will add locks and have the undo-log and redo-log overhead of regular inserts but it doesn't require an exclusive schema lock.

[VolkerBarth]

Does your app use different isolation levels with v10 and v12? Or different transaction processing (fewer commits, longer transactions)?

As to the docs, the exclusive table lock used by the LOAD TABLE statement (AFAIK!) should not prevent a transaction on isolation level 0 to read from that table - but it would block transactions running on higher isolation levels, and vice versa...

[former_SQLA_member1694890]

May be in SA12 SELECT statement from jconnect(jdbc3) or T-SQL have been changed isolation level!?

[Former Member]

There have been no changes to the default isolation level for either cmdseq or TDS connections (ie jConnect).

[johnsmirnios]

LOAD TABLE acquires an exclusive schema lock and always has done so.

[VolkerBarth]

OK, then I stand corrected, and then even at isolation level 0, another connection would be unable to read from that table. So my suggestions above are moot...

---

FWIW: The LOAD TABLE statement docs are not that clear: To cite:

LOAD TABLE places a write lock on the whole table.
...
Requires an exclusive lock on the table.

That (and the fact that LOAD TABLE don't do schema changes) has made me think it would just use an exclusive table lock and no exclusive schema lock.

[former_SQLA_member1694890]

Could be this issue related to follow thread in forum http://sqlanywhere-forum.sap.com/questions/5946/v12-isolation-levels-and-row-locking

[johnsmirnios]

You're right that the doc is not being very explicit about the types of locks acquired. Even the SA doc referenced by the link you provided with your first comment is poorly worded:

"An exclusive table lock prevents any other transaction from accessing the table for any operation (reads, ..."

followed by

"transactions executing at isolation level 0 can still read the rows in a table whose table lock is held exclusively. "

Those are contradictory. The first statement should use "locking" rather than "accessing" but the subtleties of schema vs table locks still aren't incredibly clear with that change.

[VolkerBarth]

Following John's explanation, I don't think so: As LOAD TABLE locks the table exclusively, it shouldn't matter whether your jConnect connection uses isolation 0 or 1: It should block when trying to read from that table. (But as this discussion shows, I'm surely not the expert here...)

[former_SQLA_member1694890]

The problem is not from LOAD TABLE side but from SELECT side what prevent from LOAD TABLE work properly

[johnsmirnios]

The problem isn't on any single side -- it's both together. SELECT needs a read-only schema lock and LOAD TABLE need an exclusive schema lock. Those two cannot coexist so the statements execute in series.

[Breck_Carter]

These comments keep referring to schema locks... Why would a schema lock prevent reads? Don't they just block changes to the schema?

[VolkerBarth]

Well, it's all about exclusive schema locks - and that means the lock is used to prevent other connections from reading a table that is about to be altered.

That's clearly a neccessity for ALTER TABLE. What I do not understand is why LOAD TABLE would need that type of lock (instead of an exclusive table lock) - but John will know.

[johnsmirnios]

LOAD TABLE uses an exclusive schema lock because it uses page-level undo. If the LOAD fails, modified table & index pages are restored from the checkpoint log to return them to how they were at the beginning of the LOAD. The pages are restored in arbitrary order as they come out of the checkpoint log and even an isolation level 0 scan couldn't cope with that,

[former_SQLA_member1694890]

John, if I understand you right, in case of long SELECT on table A even with isolation level 0, LOAD TABLE statement into table A will wait until SELECT statement will be finish!?

[johnsmirnios]

That is correct. Similarly, if a LOAD is in progress, the select will wait for the LOAD to finish. This behaviour is not new to SA12.

[VolkerBarth]

As this is starting to become a "Locking" class, would snapshot isolation be of help here?

I.e. would a SELECT on table A with isolation level "snapshot" (or its variants) still block the LOAD TABLE and vice versa?

Again, the docs are not very clear on this advanced topic, as they primarily explain that snapshot isolation prevents read locks...

[johnsmirnios]

Yes, the LOAD TABLE would block snapshot scans too. Any time a table is referenced, a shared schema lock is required (so that the schema can't change (though we use it for a different purpose for LOAD). Since LOAD gets an exclusive schema lock, nothing else can access the table at all.

[VolkerBarth]

But would the opposite be true: Would an uncommitted snapshot isolation SELECT block the LOAD TABLE?

(Not that I would think this possible difference would matter much in practise...)

[former_SQLA_member1694890]

In general using LOAD TABLE statement for quick data insert to table which could be used for long SELECT(reports generation) looks problematic, one of way is detect connection block LOAD TABLE connection and than drop this block connection.

[VolkerBarth]

Well, if "quick data insert" refers to "not too big amount of data" - instead of "huge amount, which takes too long to insert via INSERT" - then I strongly would follow John's advice (see his answer) to LOAD into a temporary table and then INSERT into the real table from the temporary one. That won't block any readers unnecessarily, and you won't have to drop connections...

[johnsmirnios]

Yes. Snapshots still get a shared schema lock, LOAD TABLE gets an exclusive one. They cannot coexist. It would be the same as for ALTER TABLE.

[johnsmirnios]

Or use INSERT FROM ... OPENSTRING.