When to use Transactions in SQL Server

问题:

There are lots and lots of questions on HOW to use Transactions. What I want to know is WHEN? Under what circumstances? What types of queries? Can Try-Catch blocks suffice instead? Etc...

I've designed a database with ~20 tables and ~20 stored procedures. Currently none of my SPs use a transaction, but there are numerous Try-Catch blocks throughout. The reason is because every time I tried to wrap them in a transaction the SP would cease to function and I would end up with missing data and worse off than had I used Trans.

So again...

1. When is an appropriate time to use a Transaction?
2. As a follow-up question, if I use them, how can I use them in such a way as to ONLY prevent other SPs from accessing the same data at the same time in order to prevent corruption rather than causing my SPs to not function at all?

Here's a little sample SP I wrote for renaming a product:

CREATE PROCEDURE spRenameProduct
    @pKey int = NULL,
    @pName varchar(50)
AS
BEGIN
    BEGIN TRY
        IF LTRIM(RTRIM(@pName)) = '' SET @pName = NULL
        IF NOT @pKey IS NULL AND NOT @pName IS NULL BEGIN
            declare @pKeyExisting int = (select MIN(ID) from rProduct where Product like @pName and not ID = @pKey)
            IF @pKeyExisting is null BEGIN
                update rProduct set IsValid = 1, Product = @pName where ID = @pKey
            END ELSE BEGIN
                update Request set ProductID = @pKeyExisting where ProductID = @pKey
                update StatusReport set ProductID = @pKeyExisting where ProductID = @pKey
                delete from rProduct where ID = @pKey
            END
        END
    END TRY BEGIN CATCH END CATCH
END

Now what if two people were using this at the exact same time? I really don't want to, nor do I have time (unfortunately), to get to fancy. K.I.S.S. is best in this case. :)

回答1:

You use transactions when the set of database operations you are making needs to be atomic.

That is - they all need to succeed or fail. Nothing in between.

Transactions are to be used to ensure that the database is always in a consistent state.

In general, unless there is a good reason not to use them (long running process for instance), use them. See this blog post for details.

---

Try/Catch blocks have nothing to do with transactions - they are used for exception handling. The two concepts are not related and are not replacements for each other.

回答2:

The common answer is that transactions allow database operations to be atomic. The confusion is in what this means. It's not about the particular operations involved whether they are SELECT, UPDATE, DELETE, etc. It's about the semantic meaning of the data itself. From the viewpoint of the operations, from the bottom-up, we say that as a group, they are atomic. But, from the abstract level, looking from the top-down, we say we have conservation of information.

An easy example would be if you had 2 accounts and you did not want money to be created nor destroyed in the transfer between them. Another, more subtle example, would be if you had a group of data that needed to be either created or destroyed as a group. In other words, having partial information doesn't make sense. I guess an example might be if you had a user and wanted to always guarantee they had a first and last name. Not a partial name.

With that said, people come up with phrases and rules of thumb to express what atomic means, such as "the operations all need to succeed or fail". Also, people tend to notice patterns, such as a SELECT would not need a transaction.

回答3:

Before concluding this section on Data Manipulation Language commands there are two further commands, which are very useful.

Changes made to the database by INSERT, UPDATE and DELETE commands are temporary until explicitly committed. This is performed by the command:

COMMIT;

On execution of this command all changes to the database made by you are made permanent and cannot be undone.

A COMMIT is automatically executed when you exit normally from SQL*Plus. However, it does no harm to occasionally issue a COMMIT command.

A COMMIT does not apply to any SELECT commands as there is nothing to commit.

A COMMIT does not apply to any DDL commands (eg CREATE TABLE, CREATE INDEX, etc). These are automatically committed and cannot be rolled back.

If you wished to rollback (ie undo) any changes made to the database since the last commit, you can issue the command:

ROLLBACK;

A group of related SQL commands that all have to complete successfully or otherwise be rolled back, is called a transaction. Part of your research for Outcome 3 includes investigating transaction processing and the implications of rollback and commit.

Furthermore, during the process of inserts, updates and deletes, the RDBMS needs to preserve the integrity of the database and allow multi-user access (ie concurrency). A transaction that has not yet been committed needs to be transparent to users. For example, an uncommitted insert should not be accessible to another user. Also, two users trying to update the same record should not impede each other. This needs to be managed. Concurrency is managed by the RDBMS using locking strategies. Part of your research for Outcome 3 includes investigating locking strategies covering column, row field and table locks.