Module 8
Download
1 / 24

Module 8 Designing a Transaction and Concurrency Strategy - PowerPoint PPT Presentation


  • 128 Views
  • Uploaded on

Module 8 Designing a Transaction and Concurrency Strategy. Module Overview. Guidelines for Defining Transactions Defining Isolation Levels Guidelines for Designing a Resilient Transaction Strategy. Lesson 1: Guidelines for Defining Transactions . Guidelines for Creating Transactions

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Module 8 Designing a Transaction and Concurrency Strategy' - jagger


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

Module 8

Designing a Transaction and Concurrency Strategy


Module overview
Module Overview

  • Guidelines for Defining Transactions

  • Defining Isolation Levels

  • Guidelines for Designing a Resilient Transaction Strategy


Lesson 1 guidelines for defining transactions
Lesson 1: Guidelines for Defining Transactions

  • Guidelines for Creating Transactions

  • Considerations for Using Nested Transactions

  • Demonstration: How to Create a Nested Transaction


Guidelines for creating transactions
Guidelines for Creating Transactions

ü

Manage transactions across multiple platforms

ü

Handle the errors in a transaction by using TRY…CATCH

ü

Use transaction savepoints

ü

Perform a transaction in:

  • Explicit Mode

  • Autocommit Mode

  • Implicit Mode


Considerations for using nested transactions
Considerations for Using Nested Transactions

Committing inner transactions is ignored by the SQL Server Database Engine

Each call to COMMIT TRANSACTION or COMMIT WORK applies to the last executed BEGIN TRANSACTION

The Transaction_name should not refer to the inner transactions of a set of named nested transactions

The @@TRANCOUNT function records the current transaction nesting level

ü

ü

ü

ü


Demonstration how to create a nested transaction
Demonstration: How To Create a Nested Transaction

  • In this demonstration, you will see how to:

  • Create a nested transaction


Lesson 2 defining isolation levels
Lesson 2: Defining Isolation Levels

Isolation Levels in the Database Engine

Comparing Read Committed Isolation with Snapshot Isolation

Selecting an Isolation Level for a Database

Considerations for Offloading Concurrent Activities


Isolation levels in the database engine
Isolation Levels in the Database Engine

READ UNCOMMITTED: Can read data that has been modified by other transactions but not committed

READ COMMITTED: Cannot read data that has been modified but not committed by other transactions

REPEATABLE READ: No other transactions can modify data that has been read by the current transaction until the current transaction completes

SNAPSHOT: Data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction

SERIALIZABLE: Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until it is complete


Comparing read committed isolation with snapshot isolation
Comparing Read Committed Isolation with Snapshot Isolation

When to Use Read Committed Isolation

  • Reader/writer blocking occurs to the point that concurrency benefits outweigh increased overhead of creating and managing row versions

  • An application requires absolute accuracy for long-running aggregations or queries where data values must be consistent to the point in time that a query starts

When to Use Snapshot Isolation

  • Optimistic concurrency control is desired

  • Probability is low that a transaction would have to be rolled back because of an update conflict

  • An application needs to generate reports based on long-running, multi-statement queries that must have point-in-time consistency

Use read committed isolation when:

Use snapshot isolation when:


Selecting an isolation level for a database
Selecting an Isolation Level for a Database

Database Isolation Level

Determine the minimum isolation level

  • Use alternatives to restrictive isolation levels, such as:

  • Redesigning Transact-SQL logic and tables

  • Redesigning how applications access data

  • Overriding the default locking strategy

Use row versioning isolation levels

Set the Connection Reset property to True in the connections string, for connection pools


Considerations for offloading concurrent activities
Considerations for Offloading Concurrent Activities

Reschedule activities

Scale out the system

Create a data warehouse

ü

ü

ü

  • Offloading concurrent activities helps:

    • Avoid contention and blocking problems

    • Improve system performance

Methods for Offloading Concurrent Activities


Guidelines for Designing an Object Access Strategy

Guidelines for Transaction Rollback

Guidelines for Using Optimizer Hints

Guidelines for Using Locking Hint Functionalities

Guidelines for Monitoring Transactions

Guidelines for Troubleshooting Orphaned Transactions

Demonstration: How to Use Locking Hints

Discussion: Reducing Deadlock Errors

Lesson 3: Guidelines for Designing a Resilient Transaction Strategy


Guidelines for designing an object access strategy
Guidelines for Designing an Object Access Strategy

1

2

3

1

2

3

Communicate the object access order and enforce compliance

Design an object access order based on usage

2

2

1

1

3

3

Manage conflicts when accessing objects

Discourage ad hoc queries


Guidelines for transaction rollback
Guidelines for Transaction Rollback

Minimize errors

Pre-validate data

Monitor for rollbacks

Define an error-handling strategy

Define a rollback strategy


Guidelines for using optimizer hints
Guidelines for Using Optimizer Hints

Query Hints

The query optimizer enforces a join strategy between two tables

Join Hints

Query hints enforce query optimizer to execute the select statement as a whole

Overrides the default behavior of the query optimizer

Table Hints


Guidelines for using locking hint functionalities
Guidelines for Using Locking Hint Functionalities

Locking hint functionalities of SQL Server 2008 include:

  • XLOCK at row level

  • READPAST

  • READCOMMITTEDLOCK

Using locking hints, you can implement the following functionalities:

  • Provide finer locking control

  • Reduce deadlocking

  • Lower the isolation level


Guidelines for monitoring transactions
Guidelines for Monitoring Transactions

sys.dm_tran_database_transactions

ü

DBCC OPENTRAN

ü

Split large transactions into smaller ones

ü

Schedule large transactions to run at off-peak hours

ü

Use the following options to monitor transactions:

Use the following strategies to reduce the impact of large transactions:


Guidelines for troubleshooting orphaned transactions
Guidelines for Troubleshooting Orphaned Transactions

Identify the orphaned processes using sp_who

ü

Ignore the orphaned processes or kill them

ü

Check for any improper procedures or general faults

ü

Check whether the Inter-Process Communication (IPC)session is still active

ü


Demonstration how to use locking hints
Demonstration: How To Use Locking Hints

  • In this demonstration, you will see how to:

  • Create a locking hint


Discussion reducing deadlock errors
Discussion: Reducing Deadlock Errors

  • What are good practices to prevent deadlock errors?

  • How would you use object access strategy to handle deadlock errors?


Lab 8 designing a transaction and concurrency strategy
Lab 8: Designing a Transaction and Concurrency Strategy

Exercise 1: Determining the Database Isolation Level

Exercise 2: Determining the Order of Object Access

Exercise 3: Designing Transactions

Virtual machine

NYC-SQL1

Administrator

User name

Password

Pa$$w0rd

Logon Information

Estimated time: 60 minutes


Lab scenario
Lab Scenario

You are a lead database designer at QuantamCorp. You are working on the Human Resources Vacation and Sick Leave Enhancement (HR VASE) project that is designed to enhance the current HR system of your organization. This system is based on the QuantamCorp2008 sample database in SQL Server 2008.

The main goals of the HR VASE project are as follows:

• Provide managers with current and historical information about employee vacation and sick leave.

• Grant view rights to individual employees to view their vacation and sick leave balances.

• Provide permission to selected employees in the HR department to view and update the vacation and sick leave details of employees.

• Grant the HR manager with the view and update rights to all the data.

The management team has a requirement to generate performance metric for the sales staff. For this purpose, you are required to add a new function to the QuantamCorp HR VASE database that will import information from the CRM database. The management then will use statistical data to generate the performance metric report.


Lab review
Lab Review

  • Describe another way to offload the services usage registration process.

  • If you remove the requirement for simultaneous phone number assignment, what solution would you propose?


Module review and takeaways
Module Review and Takeaways

Review Questions

Real-world Issues and Scenarios

Best Practices


ad