Systems Engineering and RDBMS

Blocking on un-committed unique keys

Posted by decipherinfosys on August 22, 2007

This is one of the common issues that application developers and database developers need to keep in mind. Before I start mentioning the two issues (blocking on un-committed primary keys or blocking on un-committed unique keys), here is a sample table that we are going to use as an example for this post (we will be using SQL Server as an example in this case though the same concept applies to Oracle and DB2 LUW as well):

/*******************************************************************
Create a sample table with an auto-incremental Primary Key
and a unique key column COL2
*******************************************************************/

CREATE TABLE TABLEA (COL1 INT NOT NULL IDENTITY PRIMARY KEY, COL2 VARCHAR(10))
GO
CREATE UNIQUE INDEX TABLEA_IND_1 ON TABLEA (COL2)
/*FILEGROUP AND FILLFACTOR AND OTHER SETTINGS*/

/*******************************************************************
–Now, start a transaction
–in Session #1 and insert a value in it
–and do not commit or rollback it yet
*******************************************************************/
BEGIN TRAN
INSERT INTO TABLEA (COL2) VALUES (‘ABC’)

/*ROLLBACK*/

/*******************************************************************
–In Session #2, go ahead and set the lock time out value to 1 and kick off a transaction that tries to insert the same unique key value in the same table
*******************************************************************/

SET LOCK_TIMEOUT 1
GO

–And now, start the transaction and try to insert the same string again
BEGIN TRAN
INSERT INTO TABLEA (COL2) VALUES (‘ABC’)

–This will get blocked and you will get the lock time out message:

Msg 1222, Level 16, State 47, Line 4
Lock request time out period exceeded.
The statement has been terminated.

Let us set the lock timeout to be a higher value for the second session and let’s execute the command from above again and let’s evaluate the lock structures that are causing the blocking:

spid dbid ObjId IndId Type Resource Mode Status
—— —— ———– —— —- ——————————– ——– ——
53 11 1620200822 2 PAG 1:143 IX GRANT
53 11 1115151018 0 TAB IS GRANT

53 11 1620200822 2 KEY (c600b7fc7c5c) X GRANT

53 11 1620200822 1 PAG 1:141 IX GRANT
53 11 1620200822 0 TAB IX GRANT
53 11 1620200822 1 KEY (010086470766) X GRANT
54 11 1620200822 1 KEY (03000d8f0ecc) X GRANT
54 11 1620200822 1 KEY (0400b4b7d951) X GRANT

54 11 1620200822 2 KEY (c600b7fc7c5c) X WAIT

54 11 1620200822 1 KEY (020068e8b274) X GRANT
54 11 1620200822 0 TAB IX GRANT
54 11 1620200822 1 PAG 1:141 IX GRANT
54 11 1620200822 2 PAG 1:143 IX GRANT

As you can see from above, the wait has developed because of the uncommitted key from the first session. The reason for the wait is that the database engine needs to ensure that the uniqueness is maintained. As a result, it will prevent another session from inserting/updating with the same value that will be in violation of that constraint.

Another time that we have seen this issue being created in the application is when the application is not taking advantage of the surrogate auto-inncremental key from the database. The logic implemented by some developers goes like this in that case:

a) Get the MAX() value for the PK column (thus resulting into a full table scan – or even if they do a select top 1 col1 from tablea order by col1 desc, it is bad enough since it will be a scan nevertheless).
b) Add 1 to it in the application layer.
c) Do the insert.

Under such a scenario, since the database is not the one that is generating the key, not only can this lead to primary key violation issues in a concurrent load scenario, this can once again lead to blocking situations related to un-committed keys. The creation of the surrogate keys should be left to the DB – for identity property columns in SQL Server and DB2 LUW as well as the usage of sequences in Oracle/DB2 LUW.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: