How to manually lock and unlock table such that inserts are prevented

Question

The code below works but does not prevent a different user from inserting a row and thus creating a duplicate ID.
The IDs for the table being updated are auto incremented and assigned. In the code below I do the following:

Get the next available ID (nextID)

Set the ID of each entity to nextID++

Bulk insert

How do I lock the table such that another user cannot insert while the the three tasks above are running? I have seen similar questions that propose setting ISOLATIONLEVEL READCOMMITTED however I don't think that will lock the table at the time I am getting the nextID.

public void BulkInsertEntities(List<Entity> entities)
{
    if (entities == null)
        throw new ArgumentNullException(nameof(entities));

    string tableName = "Entities";

    // -----------------------------------------------------------------
    // Prevent other users from inserting (but not reading) here
    // -----------------------------------------------------------------

    long lastID = GetLastID(tableName);
    entities.ForEach(x => x.ID = lastID++);

    using (SqlConnection con = new SqlConnection(db.Database.GetDbConnection().ConnectionString))
    {
        con.Open();

        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.KeepIdentity))
        {
            bulkCopy.DestinationTableName = tableName;
            DataTable tbl = DataUtil.ToDataTable<Entity>(entities);

            foreach (DataColumn col in tbl.Columns)
                bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);

            bulkCopy.WriteToServer(tbl);
        }
    }

    // ---------------------------
    // Allow other users to insert
    // ---------------------------
}

protected long GetLastID(string tableName)
{
    long lastID = 0;

    using (var command = db.Database.GetDbConnection().CreateCommand())
    {
        command.CommandText = $"SELECT IDENT_CURRENT('{tableName}') + IDENT_INCR('{tableName}')";
        db.Database.OpenConnection();
        lastID = Convert.ToInt64(command.ExecuteScalar());
    }
    return lastID;
}

use an auto-increment ID field in the database table and then you'll always get a unique ID generated by the database. That's the whole point of that type of field. Don't ever use the client code to generate the ID like this. This is relational database theory lesson 1.... (P.S. also locking the table will likely just cause performance problems anyway, so not very desirable). — ADyson, Mar 14 '19 at 15:06
[Here](https://stackoverflow.com/a/6651850/3813116) is a better approach to inserting tables with identities. — Sean Lange, Mar 14 '19 at 15:09
@SeanLange thanks Sean however I need to immediately use the identity of the row that is inserted. If I let SQL server assign it I have no way of knowing what it is. — , Mar 14 '19 at 15:18
@ADyson Did you see: "The IDs for the table being updated are auto incremented and assigned." — , Mar 14 '19 at 15:21
How about using a [SEQUENCE](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-2017) object instead of either an identity or a value generated in the code? Each invocation of `NEXT VALUE FOR` will hand you the value to use as you please, but will insure uniqueness at the database level. — Eric Brandt, Mar 14 '19 at 15:42
A sequence also has `sp_sequence_get_range` to reserve a whole block of values at once. Another option is to wrap this logic in a stored procedure with a TVP, which can use `INSERT` with an `OUTPUT` clause to give you back the assigned IDs. Last but not least, consider if `SqlBulkCopy` is really needed, here -- wrapping multiple inserts in a transaction also cuts down considerably on the insert overhead, and you can use `OUTPUT` (or `SCOPE_IDENTITY`) with individual inserts as well. — Jeroen Mostert, Mar 14 '19 at 15:50
In case of identity you can get the value of the just assigned identity value in SQL Server using `@@IDENTITY` or `SCOPE_IDENTITY()`. Probably you can use the latter — Mohammad, Mar 14 '19 at 15:51
I did see "The IDs for the table being updated are auto incremented and assigned.". I assumed that referred to the bit of C# code where you get the latest ID and then assign IDs to the data items in advance of inserting them. That's not what I mean by using an auto-increment field within the database itself. As the others have already commented above since I last wrote, if you let the database assign the IDs (in the normal way that everyone else does) you can get the newly created IDs back from the database quite easily, if you need to. — ADyson, Mar 14 '19 at 16:36

Clay · Accepted Answer · 2019-03-14T15:50:05.847

For identity-like functionality with a variant on the flexibility, you can create a named sequence:

create sequence dbo.MySequence as int

...and have a default constraint on the table: default(next value for dbo.MySequence).

Nice thing about this is that you can "burn" IDs and send them to clients so they have a key they can put into their data...and then, when the data comes in pre-populated, no harm, no foul. It takes a little more work than identity fields, but it's not too terrible. By "burn" I mean you can get a new ID anytime by calling next value for dbo.MySequence anywhere you like. If you hold onto that value, you know it's not going to be assigned to the table. The table will get the next value after yours. You can then, at your leisure insert a row with the value you got and held...knowing it's a legit key.

There is a feature in SQL Server call application locks. I've only rarely seen it used, but your example might be suitable. Basically, the idea is that you'd put triggers on tables that start by testing for an outstanding app_lock:

if ( applock_test( 'public', 'MyLock', 'Exclusive' ) = 1 )
begin
  raiserror( ... )
  return
  --> or wait and retry
end

...and the long-running process that can't be interrupted gets the applock at the beginning and releases it at the end:

exec @rc = get_applock @dbPrincipal='public', @resource='MyLock', @lockMode='Exclusive'
if ( @rc = 0 )
begin
  --> got the lock, do the damage...
  --> and then, after carefully handling the edge cases,
  --> and making sure we dont skip the release...
  exec release_applock @resource='MyLock' @dbPrincipal='public'
end

There are lots of variations. Session-based locks which can be auto-released when a session ends (beware of connection pooling), timeouts, multiple lock modes (shared, exclusive, etc.), and scoped locks (that may not apply to privileged db users).

How to manually lock and unlock table such that inserts are prevented

1 Answers1