SQL Server unique index on nvarchar issue

Question

I have a SQL Server table with a nvarchar(50) column. This column must be unique and can't be the PK, since another column is the PK. So I have set a non-clustered unique index on this column.

During a large amount of insert statements in a serializable transaction, I want to perform select queries based on this column only, in different transaction. But these inserts seem to lock the table. If I change the datatype of the unique column to bigint for example, no locking occurs.

Why isn't nvarchar working, whereas bigint does? How can I achieve the same, using nvarchar(50) as the datatype?

Can you not just add a unique constraint and index to this column? How big is the table? — Jane S, May 01 '14 at 22:07
I'm not sure why the blocking occurs. But, could you put the values in another table and just use a foreign key reference? — Gordon Linoff, May 01 '14 at 22:09
I forgot to say that I have a unique index on the nvarchar(50) column, I will edit my quetsion — TomTee, May 01 '14 at 22:11
How would another table help, since I will still need to do the inserts and the selects to that table? — TomTee, May 01 '14 at 22:14
What sort of isolation do you need for your `SELECT`? Do you **not** want the uncommitted rows? Or is including them in your results okay? — Clockwork-Muse, May 01 '14 at 23:41

score 2 · Accepted Answer · answered May 02 '14 at 12:15

After all, mystery solved! Rather stupid situation I guess..

The problem was in the select statement. The where clause was missing the quotes, but due to a devilish coincidence of the existing data were only numbers, the select wasn't failing but just wasn't executing until the inserts committed. When the first alphanumeric data were inserted, the select statement begun failing with 'Error converting data type nvarchar to numeric'

e.g Instead of

SELECT [my_nvarchar_column]  
FROM [dbo].[my_table]  
WHERE [my_nvarchar_column] = '12345'

the select statement was

SELECT [my_nvarchar_column]  
FROM [dbo].[my_table]  
WHERE [my_nvarchar_column] = 12345

I guess a silent cast was performed, the unique index was not being used which resulted to the block. Fixed the statement and everything works as expected now.

Thanks everyone for their help, and sorry for the rather stupid issue!

score 1 · Answer 2 · edited May 23 '17 at 12:11

First, you can change the PK to be a non-clustered index, then you you could create a clustered index on this field. Of course, that may be a bad idea based on your usage, or just simply not help.

You might have a use case for a covering index, see previous question re: covering index

You might be able to change your "other queries" to non-blocking by changing the isolation level of those queries.

It is relatively uncommon for it to be a necessity to insert a large number of rows in of a single transaction. You may be able to simply not use a transaction, or split up into a smaller set of transactions to avoid locking large sections of the table. E.g., you can insert the records into a pending table (that is not otherwise used in normal activity) in a transaction, then migrate these records in smaller transactions to the main table if real-time posting to the main table is not required.

ADDED

Perhaps the most obvious question. Are you sure you have to use a serializable transaction to insert a large number of records? These are relatively rarely necessary outside of financial transactions, and impose a high concurrency cost compared to the other isolation levels?

ADDED

Based on your comment about "all or none", you are describing atomicity, not serializability. I.e., you might be able to use a different isolation level for your large insert transaction, and still get atomicity.

Second thing, I notice you specify a large amount of insert statements. This just sounds like you should be able to push these inserts into a pending/staging table, then perform a single insert or batches of inserts from the staging table into the production table. Yes, it is more work, but you may just have an existing problem that requires the extra effort.

Changing the index to a clustered one and the PK to a non-clustered index, has the same result. I still can't select based on this column. — TomTee, May 01 '14 at 22:40
Yes, a serializable transaction is necessary. Those large inserts are infrequent, but during that time the table must be accessible. There are no off-hours that the inserts could be done. The inserts must all fail or succeed. I can use a pending table I suppose, but I should maintain the data to this table as well (eg after a deletion), otherwise the unique constraint would have no result. But this seems like a big effort. — TomTee, May 01 '14 at 22:48
Well, it's not just atomicity, since if the inserts fail a rollback must occur for other data previously processed in the same transaction. I will try the pending table solution, as a last but more safe option, even though I guess I should maintain the same data between the two tables, and insert the new rows in small batches. Thanks for the suggestion. — TomTee, May 02 '14 at 00:42

score 0 · Answer 3 · answered May 01 '14 at 23:22

0

You may want to add the NOLOCK hint (a.k.a. READUNCOMMITTED) to your query. It will allow to to perform a "dirty read" of the data that has been already inserted.

e.g.

SELECT [my_nvarchar_column] FROM [dbo].[locked_table] WITH (NOLOCK)

Take a look at a better explanation here:

http://www.mssqltips.com/sqlservertip/2470/understanding-the-sql-server-nolock-hint/

And the READUNCOMMITTED section here:

http://technet.microsoft.com/en-us/library/ms187373.aspx

answered May 01 '14 at 23:22

uhleeka

698
8
19

1

The OP is specifically inserting in a `SERIALIZABLE` transaction, so I'd be a little hesitant to offer something that would essentially disregard the transaction (ie, that the rows might disappear). We probably need more information from the OP... – Clockwork-Muse May 01 '14 at 23:40
Serializable transaction is a requirement. – TomTee May 02 '14 at 00:38

SQL Server unique index on nvarchar issue

3 Answers3