MySQL Bugs: #49190: Shared lock on blob held for whole transaction

Bug #49190	Shared lock on blob held for whole transaction
Submitted:	30 Nov 2009 0:00	Modified:	25 Feb 2010 14:21
Reporter:	Andrew Hutchings	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S3 (Non-critical)
Version:	mysql-5.1-telco-7.0	OS:	Any
Assigned to:	Frazer Clement	CPU Architecture:	Any

Description:
When a SELECT * FROM t1 WHERE pkey = ? is executed in a transaction the shared lock on a blob table is held for the entire transaction.  It does not do this for indexed or non-indexed where conditions.

How to repeat:
mysql1> create table t1 (a int primary key, b int, c text) engine ndb;

(insert a few rows of long text)

mysql1> set autocommit=0;
mysql2> set autocommit=0;

mysql1> select * from t1 where a=1;
*** results ***

ndb_mgm> all dump 2350 3 2

log:
2009-11-29 19:33:07 [MgmtSrvr] INFO     -- Node 2: OP[18]: Tab: 7 frag:
0 TC: 2 API: 4(0x8001)transid: H'00000009 H'00100400 op: READ-SH state:
Pr

mysql2> select * from t1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting
transaction

If you remove the where condition or do "where b=1" then the shared lock
is not held.

Suggested fix:
Clean up that lock after the select.

After IRC discussion with AndrewH:

Changing category to Server:Docs, assigning to myself (and setting lead to Stefan) to see what I can do about improving documentation of this issue, including any workaround Support/devs can come up with. After this, I'll change category back to Server:Cluster (and set lead back to BOcklin) so devs can work on a fix.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Commits updating docs:

http://lists.mysql.com/commits/92885

http://lists.mysql.com/commits/92886 (Referred to wrong bug # in previous commit)

Re-opening as Cluster bug, updating category/status/lead as previously discussed.

Duplicate of 33819?

Bernhard:

I am pretty certain it is.  But we would need a test case for bug #33819 to be sure.

After looking at the test case, bug #33819 is not related

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/98486

3380 Frazer Clement	2010-01-28
      Bug#49190 Shared lock on blob held for whole transaction
      
      As documented, when Blobs are read in CommittedRead mode, a shared row lock is
      taken.  This lock remains in place until the reading transaction commits or aborts.
      
      This patch modifies the behaviour observed at the SQL level by unlocking the shared
      row lock after the Blob data has been read.  Note that a shared row lock is still
      required, but it will be released prior to transaction commit/abort time.
     @ mysql-test/suite/ndb/r/ndb_blob.result
        Test results
     @ mysql-test/suite/ndb/t/ndb_blob.test
        Verify expected behaviour at SQL level
     @ sql/ha_ndbcluster.cc
        Modify setActive callback to close all Blob handles after reading in non-scan situations.  This allows NdbApi to issue an automatic unlock operation.
     @ storage/ndb/include/kernel/AttributeHeader.hpp
        Two new pseudo columns.
     @ storage/ndb/include/kernel/kernel_types.h
        New operation type
     @ storage/ndb/include/kernel/signaldata/LqhKey.hpp
        Modify LQHKEYCONF
     @ storage/ndb/include/kernel/signaldata/TcKeyReq.hpp
        Const definition for unlock key length
     @ storage/ndb/include/ndb_version.h.in
        Specify minimum version supporting Unlock.
     @ storage/ndb/include/ndbapi/Ndb.hpp
        Add support for LockHandle object tracking and DB node version tracking
     @ storage/ndb/include/ndbapi/NdbBlob.hpp
        Add NdbBlob::close() API method
     @ storage/ndb/include/ndbapi/NdbDictionary.hpp
        New pseudo columns
     @ storage/ndb/include/ndbapi/NdbOperation.hpp
        Add NdbRecAttr Api support for LockHandles
     @ storage/ndb/include/ndbapi/NdbTransaction.hpp
        Add NdbTransaction support for defining an unlock operation and releasing a LockHandle
     @ storage/ndb/src/common/debugger/signaldata/TcKeyReq.cpp
        Add signal trace for Unlock
     @ storage/ndb/src/kernel/blocks/dbdih/DbdihMain.cpp
        Clarify DIH handling of specific dist-key from TC
     @ storage/ndb/src/kernel/blocks/dblqh/Dblqh.hpp
        New defs for LQH Unlock handling + pseudo col reads
     @ storage/ndb/src/kernel/blocks/dblqh/DblqhInit.cpp
        New OP_ID counter initialisation
     @ storage/ndb/src/kernel/blocks/dblqh/DblqhMain.cpp
        LQH handling for new pseudo cols and Unlock operation processing
     @ storage/ndb/src/kernel/blocks/dbtc/Dbtc.hpp
        New TC definitions for unlock op processing
     @ storage/ndb/src/kernel/blocks/dbtc/DbtcMain.cpp
        Modify TC to handle Unlock operation in TCKEYREQ and TCKEYCONF signal handlers
     @ storage/ndb/src/kernel/blocks/dbtup/DbtupRoutines.cpp
        Modify TUP to handle new pseudo cols
     @ storage/ndb/src/ndbapi/ClusterMgr.cpp
        Modify ClusterMgr to track Min DB node version
     @ storage/ndb/src/ndbapi/ClusterMgr.hpp
        Modify ClusterMgr to track min DB node version
     @ storage/ndb/src/ndbapi/Ndb.cpp
        Add Min Db node version function
     @ storage/ndb/src/ndbapi/NdbBlob.cpp
        Modify NdbApi Blobs implementation to support :
        1) 'Automatic' CommittedRead Blob unlock
        2) 'Manual' LM_Read/LM_Exclusive Blob unlock
     @ storage/ndb/src/ndbapi/NdbDictionaryImpl.cpp
        New pseudo col defs
     @ storage/ndb/src/ndbapi/NdbImpl.hpp
        New Ndb freelist type
     @ storage/ndb/src/ndbapi/NdbOperation.cpp
        Modify NdbOperation to track Blob lock upgrade status and support LockHandle methods
     @ storage/ndb/src/ndbapi/NdbOperationDefine.cpp
        LockHandle request processing
     @ storage/ndb/src/ndbapi/NdbOperationExec.cpp
        LockHandle request processing.
        Unlock operation signal building
     @ storage/ndb/src/ndbapi/NdbScanOperation.cpp
        Extra sanity check on scan takeover
     @ storage/ndb/src/ndbapi/NdbTransaction.cpp
        NdbTransaction modifications to support LockHandle and Unlock op definition
     @ storage/ndb/src/ndbapi/NdbUtil.cpp
        LockHandle implementation
     @ storage/ndb/src/ndbapi/NdbUtil.hpp
        LockHandle implementation definition
     @ storage/ndb/src/ndbapi/Ndbif.cpp
        Per-Ndb minDbNodeVersion cache implementation
     @ storage/ndb/src/ndbapi/Ndbinit.cpp
        Min node version cache init
     @ storage/ndb/src/ndbapi/Ndblist.cpp
        Implementation of new methods for LockHandle list
     @ storage/ndb/src/ndbapi/TransporterFacade.hpp
        MinNodeVersion stuff
     @ storage/ndb/src/ndbapi/ndberror.c
        New error codes
     @ storage/ndb/test/include/HugoOperations.hpp
        New Hugo methods
     @ storage/ndb/test/ndbapi/testBasic.cpp
        Basic unlocking tests
     @ storage/ndb/test/ndbapi/testBlobs.cpp
        Add unlocking tests for Blobs
     @ storage/ndb/test/ndbapi/testNdbApi.cpp
        Basic unlocking tests
     @ storage/ndb/test/ndbapi/testNodeRestart.cpp
        Test lock/unlock with node failure + recovery
     @ storage/ndb/test/run-test/daily-devel-tests.txt
        Add new tests to daily-devel
     @ storage/ndb/test/src/HugoOperations.cpp
        Implementation of new Hugo methods
     @ storage/ndb/test/src/HugoTransactions.cpp
        Fix HugoTrans bug

Patch above modifies Blob access so that the shared read lock taken when reading Blob data is released after the data has been read.  This allows other transactions to obtain exclusive locks on the rows before the reading transaction commits/aborts.

Note that Blob access still requires obtaining a shared read row lock, but the lock tenure can be shorter.

At the NdbApi level, the patch adds the concept of an NdbLockHandle object which can be obtained when reading a row with a lock, and an Unlock operation which can be used to unlock a row lock claimed previously in a transaction.

Pushed into 5.1.41-ndb-7.0.13 (revid:jonas@mysql.com-20100129150530-v0ewskyzxiuht051) (version source revid:jonas@mysql.com-20100129145724-l7ow8ob88dvgdjr9) (merge vers: 5.1.41-ndb-7.0.12) (pib:16)

released as part of 7.0.12

Documented bugfix in the NDB-7.0.12 changelog as follows:

        When a primary key lookup on an NDB table containing one or more
        BLOB columns was executed in a transaction, a shared lock on any
        blob tables used by the NDB table was held for the duration of
        the transaction. (This did not occur for indexed or non-indexed
        WHERE conditions.)

        Now in such cases, the lock is released after all BLOB data has
        been read.

Also updated Limitations and Roadmap sections of Manual.

NB: Some associated API changes have been pointed out to me in an email from Frazer; I'll handle these separately.

Set to NM [NEED MERGE] (waiting for merge to 7.1).

Fix was merged to 7.1.1 at the time it was pushed.

Also documented in the NDB-7.1.1 changelog, and noted change in NDB-7.1 Dev History section of Manual.

Closed.