I use locks and transactions similar to a VBA excel programmer trying to write a multithreaded C ++ server for the first time ...
I tried to ask my colleagues for advice, but so far we are doing pretty well (or think so) when developing complex databases, writing fast and efficient queries using the index and restrictions, when necessary, and so on, none of us have good knowledge on this topic.
All the online resources that I found are either syntactic links or mannequins explaining that the transaction begins with a "begin tran" and ends with a commit or rollback.
I also went around SO without success.
I am looking for a list of simple real-world problems, as well as the correct way to solve them.
Example:
Let's say I have a table with one Active bit column, and I don't want to have two active rows at the same time. Of course, many processes may try to insert data at the same time.
- Should I lock the entire table?
- or perhaps use a data constraint to prevent the insertion of the second “Active” line?
- or use a transaction with a repeatable read isolation level?
or maybe write:
update tbFoo set Active = 0 Insert in tbFoo (foo, Active) select 'foo', 1 where does not exist (select * from tbFoo, where Active = 1)
Please do not comment / do not answer this specific problem and my silly suggestions. I'm just trying to determine the fact that I have no clue :)
, ? , SQL Server 2008
, , , .