Is it normal for an 'Insert' statement on a table, to put an Exclusive (X or IX) lock on the table.
Here is what I tried:
Insert into northwind..shippers values ('xyz', '(123) 456-7890')
When I check now in the 'Current Activity' tab in EM, there are the following locks on 'Shippers' table:
Exclusive lock on KEY (Row level lock)
Intent Exclusive lock on PAG (Page lock)
and Intent Exclusive lock on TAB (Table lock)
Because of this, I am not even able to do a select on this table, till I commit the previous Insert.
A key and page locks are understandable in the above situation, but why table lock and how can we avoid this ?