Deadlock trying to lock a table that is already locked?

[Gili]

Hi,

I am running into the following deadlock:

12:40:14.717 [qtp793315160-3229] ERROR org.h2.message.TraceWriterAdapter.write() - jdbc[5338] exception

org.h2.jdbc.JdbcSQLException: Deadlock detected. The current transaction was rolled back. Details: "

Session #5330 (user: SA) on thread qtp793315160-3224 is waiting to lock PUBLIC.PERMISSION_PATH while locking PUBLIC.PERMISSION_PATH (shared).

Session #5337 (user: SA) on thread qtp793315160-3229 is waiting to lock PUBLIC.PERMISSION_PATH while locking PUBLIC.AUTHENTICATION (shared), PUBLIC.USER (shared), PUBLIC.CALL (shared), PUBLIC.PERMISSION_PATH (shared)."; SQL statement:

select PERMISSION_PATH.PARENT_ID

from PERMISSION_PATH PERMISSION_PATH

where PERMISSION_PATH.CHILD_ID = ? [40001-182]

Here's the catch: Both connections run in SERIALIZABLE isolation mode, with MVCC=false and (as far as I can tell) both are attempting to acquire a shared lock on a table that they already have a shared lock on (PERMISSION_PATH). Shouldn't this be impossible?

Thanks,

Gili

[Noel Grandin]

You've got two threads which both start out with SHARED locks and then try to upgrade them to EXCLUSIVE locks.

You're going to need to use a 'SELECT....FOR UPDATE' at the start of the transaction to make sure that it starts with an
EXCLUSIVE lock.

> --
> You received this message because you are subscribed to the Google Groups "H2 Database" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to
> h2-database...@googlegroups.com <mailto:h2-database...@googlegroups.com>.
> To post to this group, send email to h2-da...@googlegroups.com <mailto:h2-da...@googlegroups.com>.
> Visit this group at http://groups.google.com/group/h2-database.
> For more options, visit https://groups.google.com/d/optout.

[cowwoc]

Hi Noel,

I know that's what H2 says, but (unless I missed something) the code in
question never asks for an exclusive lock.

I took the stack-trace attached to the exception I quoted and tracked it
back to this query:

select PERMISSION_PATH.PARENT_ID\nfrom PERMISSION_PATH
PERMISSION_PATH\nwhere PERMISSION_PATH.CHILD_ID = ?

As you can see, there is no UPDATE, DELETE or SELECT ... FOR UPDATE
there, so why does H2 think I'm asking for an exclusive lock? Is it
because I'm in SERIALIZABLE mode (LOCK_MODE = 1)?

If you send me your email address, I will send you a log file with more
context. I can't share it with the general public because it contains
some confidential information. My address is cowwoc at bbs.darktech.org.
Please email me there and I'll reply with the log.

Thanks,
Gili

[Gili]

Hi Noel,

I found your email address and sent you the log file.

I've got a related question. If I fire a query:

SELECT a, b, c FROM foo WHERE foo.id = 5;

and a, b, c are FOREIGN KEYs into other tables. Does the database acquire a shared-locks on those foreign tables? Or are locks acquired only for the tables listed under FROM?

Thanks,

Gili

>> h2-database+unsubscribe@googlegroups.com
>> <mailto:h2-database+unsub...@googlegroups.com>.

>> To post to this group, send email to h2-da...@googlegroups.com

>> <mailto:h2-database@googlegroups.com>.

[Noel Grandin]

Hi

Sorry, this turned out to be a bug I recently introduced with some code that prevents connection starvation.
Should be fixed now in SVN.

Regards, Noel.

[Gili]

Hurray! I'm not crazy :) Okay, so what part of the behavior I was seeing was caused by this bug? Was it the upgrade from shared to exclusive lock (even though I didn't ask for one)? Or was it anything beyond that (like the "timeout trying to lock table" I reported in the other discussion thread)?

Do you plan to release an official update in the upcoming week?

Gili

[Noel Grandin]

On 2014-11-13 08:48 AM, Gili wrote:
> Hurray! I'm not crazy :) Okay, so what part of the behavior I was seeing was caused by this bug? Was it the upgrade from
> shared to exclusive lock (even though I didn't ask for one)?

No, there was no upgrade to exclusive lock, I was mistaken about that. It was purely a bug in the recent code that
enforces locking fairness.

> Or was it anything beyond that (like the "timeout trying to
> lock table" I reported in the other discussion thread)?

I have no idea what that was and no more time to dig into this, sorry.

>
> Do you plan to release an official update in the upcoming week?
>

Up to Thomas. In the meantime I would suggest either building it yourself, or using the nightly build.

[christof...@finaris.de]

Hi,

I use the latest stable version of H2 (1.3.176).
During Index creation for very wide tables with nearly unique data in every column/row, OutOfMemory Errors can occur.
I tracked down the reason for this within the H2 source. The reason is the buffering of rows in the addIndex method (source added below).

The size of the buffer is limited to Constants.DEFAULT_MAX_MEMORY_ROWSand not to the amount specified by a "SET MAX_MEMORY_ROWS <n>" statement (which should be available through database.getMaxMemoryRows())


    @Override
    public Index addIndex(Session session, String indexName, int indexId,
            IndexColumn[] cols, IndexType indexType, boolean create,
            String indexComment) {
        if (indexType.isPrimaryKey()) {
            for (IndexColumn c : cols) {
                Column column = c.column;
                if (column.isNullable()) {
                    throw DbException.get(
                            ErrorCode.COLUMN_MUST_NOT_BE_NULLABLE_1, column.getName());
                }
                column.setPrimaryKey(true);
            }
        }
        boolean isSessionTemporary = isTemporary() && !isGlobalTemporary();
        if (!isSessionTemporary) {
            database.lockMeta(session);
        }
        Index index;
        if (isPersistIndexes() && indexType.isPersistent()) {
            int mainIndexColumn;
            if (database.isStarting() &&
                    database.getPageStore().getRootPageId(indexId) != 0) {
                mainIndexColumn = -1;
            } else if (!database.isStarting() && mainIndex.getRowCount(session) != 0) {
                mainIndexColumn = -1;
            } else {
                mainIndexColumn = getMainIndexColumn(indexType, cols);
            }
            if (mainIndexColumn != -1) {
                mainIndex.setMainIndexColumn(mainIndexColumn);
                index = new PageDelegateIndex(this, indexId, indexName,
                        indexType, mainIndex, create, session);
            } else if (indexType.isSpatial()) {
                index = new SpatialTreeIndex(this, indexId, indexName, cols,
                        indexType, true, create, session);
            } else {
                index = new PageBtreeIndex(this, indexId, indexName, cols,
                        indexType, create, session);
            }
        } else {
            if (indexType.isHash()) {
                if (cols.length != 1) {
                    throw DbException.getUnsupportedException(
                            "hash indexes may index only one column");
                }
                if (indexType.isUnique()) {
                    index = new HashIndex(this, indexId, indexName, cols,
                            indexType);
                } else {
                    index = new NonUniqueHashIndex(this, indexId, indexName,
                            cols, indexType);
                }
            } else if (indexType.isSpatial()) {
                index = new SpatialTreeIndex(this, indexId, indexName, cols,
                        indexType, false, true, session);
            } else {
                index = new TreeIndex(this, indexId, indexName, cols, indexType);
            }
        }
        if (database.isMultiVersion()) {
            index = new MultiVersionIndex(index, this);
        }
        if (index.needRebuild() && rowCount > 0) {
            try {
                Index scan = getScanIndex(session);
                long remaining = scan.getRowCount(session);
                long total = remaining;
                Cursor cursor = scan.find(session, null, null);
                long i = 0;
                int bufferSize = (int) Math.min(rowCount, Constants.DEFAULT_MAX_MEMORY_ROWS);
                ArrayList<Row> buffer = New.arrayList(bufferSize);
                String n = getName() + ":" + index.getName();
                int t = MathUtils.convertLongToInt(total);
                while (cursor.next()) {
                    database.setProgress(DatabaseEventListener.STATE_CREATE_INDEX, n,
                            MathUtils.convertLongToInt(i++), t);
                    Row row = cursor.get();
                    buffer.add(row);
                    if (buffer.size() >= bufferSize) {
                        addRowsToIndex(session, buffer, index);
                    }
                    remaining--;
                }
                addRowsToIndex(session, buffer, index);
                if (SysProperties.CHECK && remaining != 0) {
                    DbException.throwInternalError("rowcount remaining=" +
                            remaining + " " + getName());
                }
            } catch (DbException e) {
                getSchema().freeUniqueName(indexName);
                try {
                    index.remove(session);
                } catch (DbException e2) {
                    // this could happen, for example on failure in the storage
                    // but if that is not the case it means
                    // there is something wrong with the database
                    trace.error(e2, "could not remove index");
                    throw e2;
                }
                throw e;
            }
        }
        index.setTemporary(isTemporary());
        if (index.getCreateSQL() != null) {
            index.setComment(indexComment);
            if (isSessionTemporary) {
                session.addLocalTempTableIndex(index);
            } else {
                database.addSchemaObject(session, index);
            }
        }
        indexes.add(index);
        setModified();
        return index;
    }


Kind regards

Christoff Schmitz

F I N A R I S
Financial Software Partner GmbH
Sömmerringstrasse 23
60322 Frankfurt am Main

Fon:      +49 (0)69  / 254 98 - 24
Mobile: +49 (0)176 / 206 34 186
Fax:       +49 (0)69  / 254 98 - 50
eMail:    mailto:Christof...@finaris.de
www:      http://www.finaris.de und http://www.rapidrep.com

================================================================================================================
Disclaimer
The information contained in this e - mail and any attachments ( together the "message") is intended for the addressee only and
may contain confidential and/or privileged information. If you have received the message by mistake please delete it and notify
the sender and do not copy or distribute it or disclose its contents to anyone.

FINARIS Financial Software Partner GmbH, Sömmerringstr. 23, 60322 Frankfurt/Main, Germany
Registered at Frankfurt/Main, HRB 52873, Managing Directors: Dipl. Inf. Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl
================================================================================================================

[Thomas Mueller]

Hi,

As for when is the next release: my current plan is in about 3 weeks.

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.

[Noel Grandin]

Hi

Thanks, this is fixed in SVN and will be available in the next release.

For now, I suggest using the "-Dh2.maxMemoryRows" command line parameter if you are starting up H2 separately, or if you
are using H2 in embedded mode, set it directly in your code using System.putProperty.

Regards, Noel.

On 2014-11-13 12:13 PM, christof...@finaris.de wrote:
> Hi,
>
> I use the latest stable version of H2 (1.3.176).
> During Index creation for very wide tables with nearly unique data in every column/row, OutOfMemory Errors can occur.
> I tracked down the reason for this within the H2 source. The reason is the buffering of rows in the addIndex method
> (source added below).
>

> The size of the buffer is limited to Constants./DEFAULT_MAX_MEMORY_ROWS/and not to the amount specified by a "SET