Oops my bad..replied before going thru all the statements. I missed the statement ' set connection con1'
I agree if the same connection is being used, it should have worked. Seems like DERBY-1716.
-Rajesh
On 8/31/06, Rajesh Kartha <[EMAIL PROTECTED]> wrote:
Manjula G Kutty wrote:
> Hi
>
> I'm getting a lock time out exception while doing the following
> queries through ij.
>
>
> connect 'jdbc:derby:mailsdb' user 'DBADMIN' password 'admin' as con1;
> autocommit off;
> grant select on DBADMIN.t1 to DBUSER;
> grant update on DBADMIN.t1 to DBUSER;
> connect 'jdbc:derby:mailsdb' user 'DBUSER' password 'user' as con2;
> autocommit off;
> select * from DBADMIN.t1;
> set connection con1;
> revoke update on t1 from DBUSER;
> ERROR 40XL1: A lock could not be obtained within the time requested
>
> FYI: table 1 is is having 2 columns one integer column and one
> char(100) column. And has 2000 rows.
>
> Stack trace from derby.log
> -------------------------------
> 2006-08-31 18:56:22.250 GMT Thread[main,5,main] (XID = 251),
> (SESSIONID = 0), (D
> ATABASE = mailsdb), (DRDAID = null), Cleanup action starting
> 2006-08-31 18:56:22.250 GMT Thread[main,5,main] (XID = 251),
> (SESSIONID = 0), (D
> ATABASE = mailsdb), (DRDAID = null), Failed Statement is: revoke
> update on t1 fr
> om DBUSER
> ERROR 40XL1: A lock could not be obtained within the time requested
> at
> org.apache.derby.iapi.error.StandardException.newException(Unknown So
> urce)
> at
> org.apache.derby.impl.services.locks.LockSet.lockObject(Unknown Sourc
> e)
> at
> org.apache.derby.impl.services.locks.SinglePool.lockAnObject(Unknown
> Source)
> at
> org.apache.derby.impl.services.locks.SinglePool.lockObject(Unknown So
> urce)
> at
> org.apache.derby.impl.store.raw.xact.RowLocking3.lockRecordForWrite(U
> nknown Source)
> at
> org.apache.derby.impl.store.access.heap.HeapController.lockRow(Unknow
> n Source)
> at
> org.apache.derby.impl.store.access.heap.HeapController.lockRow(Unknow
> n Source)
> at
> org.apache.derby.impl.store.access.btree.index.B2IRowLocking3.lockRow
> OnPage(Unknown Source)
> at
> org.apache.derby.impl.store.access.btree.index.B2IRowLocking3._lockSc
> anRow(Unknown Source)
> at
> org.apache.derby.impl.store.access.btree.index.B2IRowLockingRR.lockSc
> anRow(Unknown Source)
> at
> org.apache.derby.impl.store.access.btree.BTreeForwardScan.fetchRows (U
> nknown Source)
> at
> org.apache.derby.impl.store.access.btree.BTreeScan.fetchNext(Unknown
> Source)
> at
> org.apache.derby.impl.sql.catalog.TabInfoImpl.updateRow(Unknown Sourc
> e)
> at
> org.apache.derby.impl.sql.catalog.TabInfoImpl.updateRow(Unknown Sourc
> e)
> at
> org.apache.derby.impl.sql.catalog.DataDictionaryImpl.addRemovePermiss
> ionsDescriptor(Unknown Source)
> at
> org.apache.derby.impl.sql.execute.TablePrivilegeInfo.executeGrantRevo
> ke(Unknown Source)
> at
> org.apache.derby.impl.sql.execute.GrantRevokeConstantAction.executeCo
> nstantAction(Unknown Source)
> at
> org.apache.derby.impl.sql.execute.MiscResultSet.open(Unknown Source)
> at
> org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown So
> urce)
> at
> org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown So
> urce)
> at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown
> Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown
> Source)
> at org.apache.derby.impl.tools.ij.ij.executeImmediate(Unknown
> Source)
> at org.apache.derby.impl.tools.ij.utilMain.doCatch (Unknown
> Source)
> at
> org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(Unknown Source)
> at org.apache.derby.impl.tools.ij.utilMain.go(Unknown Source)
> at org.apache.derby.impl.tools.ij.Main.go (Unknown Source)
> at org.apache.derby.impl.tools.ij.Main.mainCore(Unknown Source)
> at org.apache.derby.impl.tools.ij.Main14.main(Unknown Source)
> at org.apache.derby.tools.ij.main (Unknown Source)
> Cleanup action completed
>
Isn't there a commit missing.. User 'DBADMIN' has executed the grant
statements but they have not been committed yet hence will
be holding the locks.
Can you try adding the commit before the second connection and see.
-Rajesh
