Does BEGIN TRAN guarantee atomicity?

I have a situation where I want to insert records in both a master and details table, but only under certain condition. So when I insert, I do something like this:

set transaction isolation level serializable
BEGIN TRAN

INSERT INTO master (CustomerId, TypeOfTransAction, ...)
SELECT @customerId, 'TEST', ...
WHERE (SELECT SUM(amount) FROM details WHERE customerId = @customerId) < 1000

IF SCOPE_IDENTITY() IS NULL
    RAISERROR('can''t insert now, there''s too many', 16, 1)

INSERT INTO details (CustomerId, ...)
VALUES (@customerId, ...)

INSERT INTO details (CustomerId, ...)
VALUES (@customerId, ...)

...

COMMIT TRAN

Does this guarantee no more rows will be inserted into the master table (from other connections) until it has finished inserting all rows into the details table?

You have a few different questions in here:

Q: What is atomicity?

Atomicity means your transaction will completely succeed, or completely fail. At the end of your transaction, when you run COMMIT, all of the work should roll forward together in one atomic unit. If, on the other hand, you run ROLLBACK, it should all roll back together.

Q: Does atomicity mean an insert query locks a table?

No. Atomicity is only about the work involved in your own transaction, not others.

Q: If I want to stop other people from touching tables while I work, how do I do it?

You need to explicitly specify that as part of your query, like by using the SERIALIZABLE isolation level. That's generally not a good idea, though: that's the opposite of high performance. You want to write your code in a way that expects other people to be working in the database at the same time as you are.

That concept - why you should avoid exclusive/serializable locks on objects - is a pretty big one to try to tackle in a single answer, though.

StackExchange Database Administrators Q#221802, answer score: 2