Beware SQL Server Identity might cause Table Lock

Photo by Anita Jankovic on Unsplash

I’ve noticed a strange behavior in MS SQL Server that causes a lock to an entire database table.

We will represent the problem in Kotlin, but the issue exists with any other technology stack

Suppose we have an Audit Entity that we use to log some useful information about our transactions and let me use Kotlin syntax to represent it:

@Entity
data class Audit(var msg: String? = null,
                 val start: Instant = Instant.now(),
                 var stop: Instant? = null,
                 @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
                 var id: Long? = null) {
    fun end() = this.apply { stop = Instant.now() }
}

And here’s the table created in SQL Server to represent the entity:

create table audit
(
    id    bigint identity primary key,
    msg   varchar(255),
    start datetime2,
    stop  datetime2
)

We have one service function that creates a billing customer in a transaction and use the audit table to log this transaction in the database.

The issue occurs when the create billing customer communicates with external API via network calls, in such case the table audit gets locked until the network call ends and the transaction committed (or rollbacked).

@Service
class BillingService(private val auditRepository: AuditRepository) {

    @Transactional
    fun createCustomer() {
        val audit = auditRepository
                .save(Audit("calling billing webservice"))
        callBillingWebService()
        auditRepository.save(audit.end())
    }

    private fun callBillingWebService() {
        Thread.sleep(10 * 1000)
    }
}

The createCustomer method above starts a database transaction that will cause a lock to the Audit entity's table audit until the transaction is committed (for entire 10 seconds).

see the repo here: https://github.com/mhewedy-playground/transactional-idenity-issue-mssql

The same effect can be tested aside from any backend service by opening two SQL Terminals and in the first terminal issue the following:

begin tran
    insert into audit (msg) values ('some message');
    WAITFOR DELAY '00:01';  -- wait for 1 minute
commit;

and in the second terminal execute the following:

select * from audit

you will notice the select statement will be blocked until the first transaction ends, meaning that the insert statement with the identity brings a table-level lock.

You can, however, bypass the lock and read the uncommitted data by changing the isolation level to read uncommited for the statement, use the following statement:

select * from audit with (nolock)

How to fix it?

We saw that the identity insertion if blocked inside a transaction, will cause a table lock until the transaction is committed or rollbacked which might case fetal issues in some cases.

However, this will not happen if you use sequenece instead of the identity for the Primary key value.

Moreover, always try to design your database transactions to avoid containing a network or IO calls. for example, you can rewrite your service method as following:

@Transactional
fun createCustomer() {
    val audit = Audit("calling billing webservice")
    callBillingWebService()
    auditRepository.save(audit.end())
}

And so you only start the database transaction after the callBillingWebService network call returns.