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
