2 replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
source | link

Locking and concurrency is a topic I learnt most about through testing and observation.

Start by reading Isolation Levels in the Database Engine and Concurrency Effects, so you have an understanding of the relationship between the two. Now you can experiment:

  • Construct the SQL for a test transaction. Something simple and self-contained with a context/domain you understand e.g. shopping cart checkout or bank transfer. Ensure you have a method of verifying that the data is in the expected state after testing.
  • Run your scenario, observing the type, duration and sequence of locks applied by the database engine.
  • Alter the isolation level or apply hints and observe the change to the locks taken.
  • When you think you have a transaction that will survive concurrency, load test it.

I tend to check lock sequences with trace flag 1200 or a Profiler traceProfiler trace. I believe extended events could also be used but I've not tried myself as yet.

Locking and concurrency is a topic I learnt most about through testing and observation.

Start by reading Isolation Levels in the Database Engine and Concurrency Effects, so you have an understanding of the relationship between the two. Now you can experiment:

  • Construct the SQL for a test transaction. Something simple and self-contained with a context/domain you understand e.g. shopping cart checkout or bank transfer. Ensure you have a method of verifying that the data is in the expected state after testing.
  • Run your scenario, observing the type, duration and sequence of locks applied by the database engine.
  • Alter the isolation level or apply hints and observe the change to the locks taken.
  • When you think you have a transaction that will survive concurrency, load test it.

I tend to check lock sequences with trace flag 1200 or a Profiler trace. I believe extended events could also be used but I've not tried myself as yet.

Locking and concurrency is a topic I learnt most about through testing and observation.

Start by reading Isolation Levels in the Database Engine and Concurrency Effects, so you have an understanding of the relationship between the two. Now you can experiment:

  • Construct the SQL for a test transaction. Something simple and self-contained with a context/domain you understand e.g. shopping cart checkout or bank transfer. Ensure you have a method of verifying that the data is in the expected state after testing.
  • Run your scenario, observing the type, duration and sequence of locks applied by the database engine.
  • Alter the isolation level or apply hints and observe the change to the locks taken.
  • When you think you have a transaction that will survive concurrency, load test it.

I tend to check lock sequences with trace flag 1200 or a Profiler trace. I believe extended events could also be used but I've not tried myself as yet.

1
source | link

Locking and concurrency is a topic I learnt most about through testing and observation.

Start by reading Isolation Levels in the Database Engine and Concurrency Effects, so you have an understanding of the relationship between the two. Now you can experiment:

  • Construct the SQL for a test transaction. Something simple and self-contained with a context/domain you understand e.g. shopping cart checkout or bank transfer. Ensure you have a method of verifying that the data is in the expected state after testing.
  • Run your scenario, observing the type, duration and sequence of locks applied by the database engine.
  • Alter the isolation level or apply hints and observe the change to the locks taken.
  • When you think you have a transaction that will survive concurrency, load test it.

I tend to check lock sequences with trace flag 1200 or a Profiler trace. I believe extended events could also be used but I've not tried myself as yet.