XML DML and Locking

If you're an XML user, you might wonder whether how SQL Server 2005 locks the XML datatype when you are changing its content through XML DML? For example, does SQL Server put a lock around the complete XML document or does it lock only the node being changed?

The answer (from Michael Rys, the SQL Server XML Program Manager at Microsoft): the XML-DML is embedded in a SQL UPDATE statement. In SQL Server 2005, UPDATE statements lock the row, thus an XML-DML statement will lock the row because the SQL update statement locks the row.

Microsoft is looking into node level concurrency control for a future release (no ETA), so feedback about requirements is appreciated.

So if you have an XML object that contains many sections and you need concurrent writes to different sections, you should place them into different rows. If you can't put them into many separate roles then consider using a lower isolation level (if you can live with the side-effects).

Cheers,

-Kev

P.S. Thanks to my MVP buddy Kent Tengels for sharing this question and the answer.

Comments

Plain text