[Firebird-net-provider] Subsequent error 'lock conflict on no wait transaction' using TransactionScope across DBs

[David C. <wav...@gm...>]

Hi Jiri and all,

We are running FB v2.5, and I am using the ADO v5.5.0 DLL to connect from
my VS2013 C# project to 4 different databases. I have a little tool for
user management, and to narrow the focus, one of the operations is to
delete the user by:
- REVOKE ALL GRANTs across all DBs
- DROP the USER

The approach I have taken is to use
the System.Transactions.TransactionScope object, a
single FirebirdSql.Data.FirebirdClient.FbConnection object (which is
enlisted), and when switching DBs I call FbConnection::ChangeDatabase().
This seems to work fine the first time, but the very next time to call this
method to delete a different user, the error message appears (also
attached):
unsuccessful metadata update
ERASE RDB$USER_PRIVILEGES failed in REVOKE ALL ON ALL
lock conflict on no wait transaction
Acquire lock for relation (RDB$USER_PRIVILEGES) failed

This occurs in both Debug and Release builds. Opening and closing the EXE
between operations and the problem goes away. It seems as if the first
deletion of the user and its associated REVOKEs has left some hanging hook
into things that prevents further operations from being performed. I have
tried changing the IsolationLevel on the connection but that doesn't seem
to matter.

Is the approach taken below the recommended flow? Any tips/ideas would be
welcome.

Best regards,
David

                using (System.Transactions.TransactionScope scope = new
System.Transactions.TransactionScope())
                {
                    System.Transactions.IsolationLevel isolationLevel =
System.Transactions.Transaction.Current.IsolationLevel;
                    System.Diagnostics.Debug.WriteLine("IsolationLevel = "
+ isolationLevel.ToString() + " versus " + csb.IsolationLevel.ToString());
                    csb.IsolationLevel = IsolationLevel.Serializable;
                    System.Diagnostics.Debug.WriteLine("ConnectionTimeout =
" + csb.ConnectionTimeout.ToString());
                    csb.ConnectionTimeout = 4;
                    csb.Enlist = true;
                    using (FirebirdSql.Data.FirebirdClient.FbConnection
conn = new
FirebirdSql.Data.FirebirdClient.FbConnection(csb.ConnectionString))
                    {
                        conn.Open();
                        // REVOKE all rights for the given user. This must
be done for each database.
                        //
http://www.firebirdsql.org/refdocs/langrefupd25-security-grant-revoke.html#langrefupd25-security-revoke-all-on-all
                        string sUser = lstUsers.SelectedItem.ToString();
                        string sSQL = "REVOKE ALL ON ALL FROM " + sUser;
                        FbCommand cmd;
                        foreach (Globals.DbAliases alias in
Enum.GetValues(typeof(Globals.DbAliases)))
                        {

conn.ChangeDatabase(Globals.m_dictDbAliases[alias].Alias);
                            System.Diagnostics.Debug.WriteLine("Revoking
from DB " + conn.Database.ToString());
                            cmd = new FbCommand(sSQL, conn);
                            cmd.ExecuteNonQuery();
                        }
                        // Delete the user.
                        //
http://www.firebirdsql.org/refdocs/langrefupd25-security-sql-user-mgmt.html#langrefupd25-security-drop-user
                        sSQL = "DROP USER " + sUser;
                        cmd = new FbCommand(sSQL, conn);
                        cmd.ExecuteNonQuery();
                    } // using conn
                    //
http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.complete.aspx
                    scope.Complete();
                } // using scope


[image: Inline images 1]