Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
You can ask them directly, on their web site. –  AlexKuznetsov Feb 16 '12 at 15:26
    
@AlexKuznetsov Why would Red Gate answer about what serializable do for DDL-statements? –  Andreas Ågren Feb 16 '12 at 20:48
    
You are their customer, presumable you are either paying them money already, or considering that - that's why. We ask them questions; they answer. So do JetBrains folks. –  AlexKuznetsov Feb 16 '12 at 22:12
add comment

2 Answers

up vote 4 down vote accepted

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.

share|improve this answer
    
Yep, same thing. Using TF 1200 can see it taking RangeI-N locks on the system tables. No idea why Redgate would want serializable semantics though and how preventing phantoms is important. –  Martin Smith Feb 16 '12 at 14:25
    
So basically no other tables or procedures can be created/changed/dropped when "my" script is running? –  Andreas Ågren Feb 16 '12 at 15:00
    
They can, just not anything relating to any objects that you reference. If you query a table, then move on to another table in the same query, you can't change that first table til the whole thing is complete. –  JNK Feb 16 '12 at 15:03
    
What happens technically? Is it as simple as/comparable to a set transaction isolation level serializable; select * from sys.sysobject where object_id = <the objects I'm changing>? (Please note, hightly simplified ;) –  Andreas Ågren Feb 16 '12 at 15:09
    
Locks are issued on any object that you interact with and not released until the entire transaction completes. –  JNK Feb 16 '12 at 15:10
show 3 more comments

For example (TSQL syntax may not be correct):

set transaction isolation level serializable

begin tran

-- If another transaction/statement is doing: update prices set price=99 where product_id=123; Then you cannot read the modified data (price=99) until the other statement outside this transaction commits.

select * from prices where product_id=123;

-- The second statement in this transaction.
select * from products where quantity=1;

-- Another transaction cannot modify (update/delete) the products table where quantity=1, until the end of this three statement transaction. The other transactions can modify rows that are not affected by where quantity=1.

-- The third statement in this transaction.
select * from sales where city='TO';

-- Another transaction outside this transaction cannot insert a new row: insert into sales(city) values ('TO'); till the end of this transaction. They can insert rows other than those that have a value of 'TO'.

end

commit

share|improve this answer
    
Thank you for the answer but the question is about DDL, not DML. I'd also like to point out that your first statement that we are unable to read prices when another transaction updates it is not entirely true, we could use read uncommitted or snapshot isolation level could be active. Secondly, the statement about products would also be applicable for repeatable read. –  Andreas Ågren Feb 16 '12 at 20:45
    
Missed the DML part. I need to look into that. The above transactions are serializable. Read uncommitted is another isolation level. If another transaction was running with read uncommitted, then that transaction would be able to read the prices table, not the serilizable transaction. Also, you cannot change from another isolation level to snapshot isolation, within the transaction. –  StanleyJohns Feb 16 '12 at 21:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.