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);
    }
}

asked 07 Apr '14, 16:49

rickrat's gravatar image

rickrat
21115
accept rate: 0%

edited 07 Apr '14, 16:49


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.

permanent link

answered 09 Apr '14, 11:11

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
7.3k35107
accept rate: 31%

edited 09 Apr '14, 16:43

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',
permanent link

answered 09 Apr '14, 17:16

Breck%20Carter's gravatar image

Breck Carter
25.7k428592852
accept rate: 20%

edited 09 Apr '14, 17:18

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×200
×56

question asked: 07 Apr '14, 16:49

question was seen: 904 times

last updated: 09 Apr '14, 17:18