Unit 6

Question 1

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.

Answer 1

All of the above

Question 2

A ________ occurs when neither of two transactions can be committed because they each have a lock on a resource needed by the other.

Answer 2

Deadlock

Question 3

SQL Server’s lock manager always tries to lock resources

Answer 3

at the highest possible granularity

Question 4

One of the drawbacks of using the SERIALIZABLE isolation level is

Answer 4

it can cause severe performance problems

Question 5

By default, SQL Server is in autocommit mode. This means

Answer 5

unless you explicitly start a transaction using the BEGIN TRAN statement, each statement is automatically treated as a separate transaction

Question 6

A lost update occurs when

Answer 6

two transactions select the same row and then update the row based on the values originally selected

Question 7

Which statement can you use to explicitly start a transaction?

Answer 7

BEGIN TRAN

Question 8

What does SQL Server automatically use to avoid some concurrency problems?

Answer 8

locks

Question 9

If @@TRANCOUNT is equal to 1, then the COMMIT TRAN statement

Answer 9

decrements @@TRANCOUNT and commits the transaction

Question 10

What kind of lock only allows one transaction to access a resource?

Answer 10

exclusive

Question 11

If you don’t group statements into explicit transactions, SQL Server automatically treats each SQL statement as a separate what?

Answer 11

transaction

Question 12

If you’ve declared save points, the COMMIT TRAN statement

Answer 12

commits the entire transaction

Question 13

Which lock indicates that SQL Server plans to obtain a shared lock or an exclusive lock on a finer-grain resource?

Answer 13

intent lock

Question 14

If neither of two transactions can be committed because they each have a lock on a resource needed by the other, it’s called

Answer 14

a deadlock

Question 15

Which of the following is the default transaction isolation level for SQL Server?

Answer 15

READ COMMITTED

Question 16

Each of the following is a valid reason to explicitly code a transaction except for one. Which one?

Answer 16

The results of a SELECT query will be used as a subquery.

Question 17

When you commit a transaction,

Answer 17

the operations performed by the SQL statements become a permanent part of the database

Question 18

Which system function can you use to determine how many levels deep transactions are nested?

Answer 18

@@TRANCOUNT

Question 19

Which of the following is not one of the four types of concurrency problems?

Answer 19

integrity reads

Question 20

Which of the following is the most restrictive transaction isolation level?

Answer 20

SERIALIZABLE

Question 21

The relative amount of data that’s included in a resource can be referred to as the resource’s

Answer 21

granularity

Question 22

A dirty read occurs when

Answer 22

a transaction selects data that isn’t committed by another transaction

Question 23

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.

Answer 23

Use the highest transaction isolation level possible.

Question 24

What is lock promotion?

Answer 24

the conversion of a less exclusive lock to a more exclusive lock

Question 25

Concurrency is

Answer 25

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

Question 26

What is lock escalation?

Answer 26

the conversion of several finer-grained locks to a single coarse-grain lock