Sql-server – What effect does serializable isolation level have on DDL-statements

ddlisolation-levelsql serversql-server-2008

I'm using Red Gate SQL Compare to create a release script based on differences between SVN and a database. This results in a script containing a bunch of table- and procedure-changes and it works fine. However, one thing puzzles me, it's using transaction isolation level serializable.

I know what it does to dml-statements, but I'm not sure what it means for ddl. Can someone enlighten me, perhaps with an example?

Best Answer

I believe it will mean the same thing for DDL as it does for DML. The msdn article on the topic actually gives you a pretty clear idea under the SERIALIZABLE section:

This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.

Basically as long as your transaction is running, no DDL can be performed on any of the objects you directly or indirectly reference.

Related Solutions

Sql-server – SQL Server – what isolation level for non-blocking select statements

In an ideal world you would have two choices, SNAPSHOT and READ COMMITTED SNAPSHOT (RCSI). Make sure you understand the basics of transaction isolation levels before you decide which is appropriate for your workload. Specifically be aware of the different results you may see as a result of moving to RCSI.

This sounds like it's not an ideal world as you don't have any control over the application that is generating the select statements. In that case, your only option is to enable RCSI for the database in question such that the selects will automatically use RCSI instead of READ COMMITTED.

Sql-server – What do these statements mean in the MS β exam 70-461 “skills measured” list

DML - refers to data manipulation (update, insert, delete). A DML trigger is a trigger created to happen on update, insert or delete of table data.

The update function is the part of a DML trigger where you test if a required update has occured.

For Example:

CREATE TRIGGER myupdate_trigger
ON mytable
FOR UPDATE AS
if UPDATE(column_1)
BEGIN
-- Do something
END

This will trigger a further action every time column column_1 is updated in table mytable. The update function is UPDATE(column_1).

Best Answer

Related Solutions

Sql-server – SQL Server – what isolation level for non-blocking select statements

Sql-server – What do these statements mean in the MS β exam 70-461 “skills measured” list

Related Question