devxlogo

Use Enhanced SQL Server 6.5 Lock Manager’s Row-Level Locking Granularity

Use Enhanced SQL Server 6.5 Lock Manager’s Row-Level Locking Granularity

Enhancements to the lock manager in SQL Server 6.5 provide row-level concurrency for INSERT operations. Properly implemented rowlock increases the speed of multi-user INSERT operations. Insert rowlock (IRL) is OFF by default. IRL can be enabled on individual tables or on an entire database by using the sp_tableoption stored procedure.

The example shows how the dbo can turn on the insert row lock option for all tables in the current database:

 EXECUTE sp_tableoption '%.%', 'insert row lock', 'true' 

The new value for insert rowlock takes immediate effect on existing tables in the current database. For each table subsequently created, IRL must be explicitly set. This example shows how a user other than the system administrator or a dbo can turn off the insert row lock option for a table called MyTable:

 EXECUTE sp_tableoption 'MyTable', 'insert row lock', 'false'
See also  5 Ways to Improve Customer Experience
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist