Inserting record causes lock

[Former Member]

When I do an insert with the SQL Anywhere 16 .net provider, it creates a shared lock on the table. even with a commit afterwards. How do I prevent it? (Or what am I doing wrong?)

DbCommand command = new SACommand();
command.CommandTimeout = this.Timeout;
bool mustCloseConnection = false;
PrepareCommand(command, connection, null, commandType, commandText, commandParameters, ref mustCloseConnection);
int num2 = command.ExecuteNonQuery();
command.Parameters.Clear();
if (mustCloseConnection)
{
    connection.Close();
}


private void PrepareCommand(IDbCommand command, IDbConnection connection, IDbTransaction transaction, CommandType commandType, string commandText, IDataParameter[] commandParameters, ref bool mustCloseConnection)
{
    if (command == null)
    {
        throw new ArgumentNullException("command");
    }
    if ((commandText == null) || (commandText.Length == 0))
    {
        throw new ArgumentNullException("commandText");
    }
    if (connection.State != ConnectionState.Open)
    {
        connection.Open();
        mustCloseConnection = true;
    }
    else
    {
        mustCloseConnection = false;
    }
    command.Connection = connection;
    command.CommandText = commandText;
    command.CommandTimeout = this.Timeout;
    if (transaction != null)
    {
        if (transaction.Connection == null)
        {
            throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
        }
        command.Transaction = transaction;
    }
    command.CommandType = commandType;
    if (commandParameters != null)
    {
        AttachParameters(command, commandParameters);
    }
}

[Former Member · Accepted solution]

I suspect you are looking at a schema lock here. Committing DML operations will release row locks but if the statement remains prepared then a schema lock is retained. The Command object will probably need to be freed via a Dispose( ) to release that. Of course closing the connection will also accomplish that.

Schema locks only prevent DDL operations on those objects and can usually be ignored unless you alter your tables frequently enough for this to be an administrative barrier.

[Breck_Carter]

Are you sure you see a lock after a COMMIT? ...or have you done some other operation like SELECT after the COMMIT?

Please show us exactly what the lock looks like; e.g., in dbisql:

SELECT * FROM sa_locks();

Here is a demonstration that shows a COMMIT clears all locks, but even a SELECT gets a schema lock:

CREATE TABLE t ( pkey INTEGER NOT NULL PRIMARY KEY );
INSERT t VALUES ( 1 );
COMMIT;
SELECT * FROM sa_locks();
-- no rows

SELECT * FROM t;
SELECT * FROM sa_locks();
-- conn_name,conn_id,user_id,table_type,creator,table_name,index_id,lock_class,lock_duration,lock_type,row_identifier
-- 'ddd16-1',1,'DBA','BASE','DBA','t',,'Schema','Transaction','Shared',