I have also observed this problem in the context of testing RPMs using a new RPM database. 
E.g.
script to reproduce:

WSDIR=/tmp/foobar
mkdir $WSDIR

mkdir "$WSDIR"/rpmdb
rpmdb --initdb --dbpath "$WSDIR"/rpmdb

cd $WSDIR

# Exercise (install RPM)
# we use --nopost so that we don't run the post install script which runs ldconfig.
rpm --dbpath "$WSDIR"/rpmdb --nopost --root `pwd` --relocate /opt=`pwd`/opt -ivh <something>.rpm --nodeps 

Actual results on stderr

"error: can't create transaction lock on /tmp/foobar/rpmdb/__db.000"

I believe this is the same problem as described here because rpm -U
installs the RPM successfully.

I have found that it is not a problem for all systems or all RPMs.

I have observed it fails on the following setup (for a particular RPM):

brucea@:main/test/systemtest>rpm --version
RPM version 4.4.2
brucea@:main/test/systemtest>lsb_release -a
LSB Version: :core-3.1-amd64:core-3.1-ia32:core-3.1-noarch:graphics-3.1-amd64:graphics-3.1-ia32:graphics-3.1-noarch
Distributor ID: RedHatEnterpriseServer
Description: Red Hat Enterprise Linux Server release 5.2 (Tikanga)
Release: 5.2
Codename: Tikanga

I have observed it works on the following setup (for a particular RPM):

brucea@:depot/emma/main>rpm --version
RPM version 4.4.2.3
brucea@:depot/emma/main>lsb_release -a
LSB Version:    :core-4.0-ia32:core-4.0-noarch:graphics-4.0-ia32:graphics-4.0-noarch:printing-4.0-ia32:printing-4.0-noarch
Distributor ID: RedHatEnterpriseClient
Description:    Red Hat Enterprise Linux Client release 5.6 (Tikanga)
Release:        5.6
Codename:       Tikanga

I believe it also works on the following setup (for a different RPM):

[brucea@uktnswg-ams-test01 ~]$ rpm --version
RPM version 4.4.2.3
[brucea@uktnswg-ams-test01 ~]$ lsb_release -a
LSB Version:    :core-3.1-amd64:core-3.1-ia32:core-3.1-noarch:graphics-3.1-amd64:graphics-3.1-ia32:graphics-3.1-noarch
Distributor ID: RedHatEnterpriseServer
Description:    Red Hat Enterprise Linux Server release 5.3 (Tikanga)
Release:        5.3
Codename:       Tikanga

I cannot currently confirm whether updating rpm to version 4.4.2.3 on the failing machine is sufficient to solve this problem.

I have made an odd observation with regard to this.
Given a test script as below the transaction lock either succeeds or fails on the basis of the INSTALLROOT dir chosen.
On the same machine I have one directory where the installation succeeds and all others where it does not. I am at a loss to explain this.

I have also observed that even when it does succeed here the exit status is non-zero.
The man-page for RPM is unusual in not describing the exit status
(or indeed the --fsmdebug & --rpmiodebug options I have seen mention elsewhere)

#!/bin/sh
#
# create RPM db & install AMS RPMs for this test dir
#

INSTALLROOT=`pwd`
RPMDIR=`pwd`/rpms

mkdir $INSTALLROOT
cp -r $RPMDIR $INSTALLROOT/rpms
cd $INSTALLROOT


cd $INSTALLROOT
rm -rf `pwd`/rpmdb

mkdir `pwd`/rpmdb
rpmdb --initdb --dbpath `pwd`/rpmdb

# copy system wide RPM database 
# (otherwise basic things like libc will be missing dependencies)
# cp /var/lib/rpm/* rpmdb/.

rpm --dbpath `pwd`/rpmdb --root `pwd` --force --relocate /=`pwd` -ivh $INSTALLROOT/rpms/*.rpm --nodeps
echo STATUS=$?

BTW The latter observation was with rpm version 4.4.2.3 so I can confirm that this version does not solve this problem.

The difference between -i and -U comes from the fact that with -U, the rpmdb has already been opened (well, created) whereas with -i it's not. With -i, it also depends on whether the package is signed or not (or whether signature checking is disabled) - signature checking also opens (and creates in this case) the rpmdb early, creating the directory where the lock will be placed. The --initdb case is more straightforward: the directory is always missing, causing failure.

devel_ack, the fix is a trivial one-liner patch.

I can reproduce the bug with the rpm -i case but I can't reproduce the rpmdb --initdb one. 

[root@nec-em13 xxx]# rpm -q rpm
rpm-4.4.2.3-22.el5
[root@nec-em13 xxx]# mkdir /tmp/rpmdb
[root@nec-em13 xxx]# rpmdb --initdb --dbpath /tmp/rpmdb
[root@nec-em13 xxx]# ls /tmp/rpmdb/
__db.000  __db.001  __db.002  __db.003  Packages

Am I doing something wrong? Or is it possible that the --initdb case had been already fixed?

Leave out the preceding mkdir, that's what "fixes" the issue. Ie just

rpmdb --initdb --dbpath /no/such/directory

Since the problem described in this bug report should be
resolved in a recent advisory, it has been closed with a
resolution of ERRATA.

For information on the advisory, and where to find the updated
files, follow the link below.

If the solution does not work for you, open a new bug report.

http://rhn.redhat.com/errata/RHBA-2012-0283.html