Unit 6 Flashcards
Which of the following is true about a transaction?
It is a group of database operations that are combined into a logical unit.
By default, each SQL statement is treated as a separate transaction.
You can combine any number of SQL statements into a single transaction.
All of the above
A ________ occurs when neither of two transactions can be committed because they each have a lock on a resource needed by the other.
Deadlock
SQL Server’s lock manager always tries to lock resources
at the highest possible granularity
One of the drawbacks of using the SERIALIZABLE isolation level is
it can cause severe performance problems
By default, SQL Server is in autocommit mode. This means
unless you explicitly start a transaction using the BEGIN TRAN statement, each statement is automatically treated as a separate transaction
A lost update occurs when
two transactions select the same row and then update the row based on the values originally selected
Which statement can you use to explicitly start a transaction?
BEGIN TRAN
What does SQL Server automatically use to avoid some concurrency problems?
locks
If @@TRANCOUNT is equal to 1, then the COMMIT TRAN statement
decrements @@TRANCOUNT and commits the transaction
What kind of lock only allows one transaction to access a resource?
exclusive
If you don’t group statements into explicit transactions, SQL Server automatically treats each SQL statement as a separate what?
transaction
If you’ve declared save points, the COMMIT TRAN statement
commits the entire transaction
Which lock indicates that SQL Server plans to obtain a shared lock or an exclusive lock on a finer-grain resource?
intent lock
If neither of two transactions can be committed because they each have a lock on a resource needed by the other, it’s called
a deadlock
Which of the following is the default transaction isolation level for SQL Server?
READ COMMITTED
Each of the following is a valid reason to explicitly code a transaction except for one. Which one?
The results of a SELECT query will be used as a subquery.
When you commit a transaction,
the operations performed by the SQL statements become a permanent part of the database
Which system function can you use to determine how many levels deep transactions are nested?
@@TRANCOUNT
Which of the following is not one of the four types of concurrency problems?
integrity reads
Which of the following is the most restrictive transaction isolation level?
SERIALIZABLE
The relative amount of data that’s included in a resource can be referred to as the resource’s
granularity
A dirty read occurs when
a transaction selects data that isn’t committed by another transaction
Each of the following techniques can help to prevent deadlocks except for one. Which one?
Don’t leave transactions open any longer than necessary.
Use the highest transaction isolation level possible.
Schedule transactions that modify a large number of rows to run when no other transactions will be running.
If you code two transactions that update the same resources, code the updates in the same order in each transaction.
Use the highest transaction isolation level possible.
What is lock promotion?
the conversion of a less exclusive lock to a more exclusive lock
Concurrency is
more of a problem on larger systems with more users and more transactions
only a problem when data is being modified, not when two or more transactions simply read the same data
the ability of a system to support two or more transactions working with the same data at the same time
all of the above
What is lock escalation?
the conversion of several finer-grained locks to a single coarse-grain lock
