I'm not sure from your explanation whether or not you are running these queries in the same window or in separate windows. If they are in the same window, the first operation is always completed before the second begins. But even if they are runnin in different windows, you are likely to see this behavior. That is due to lock escalation.
If an operation generates too many locks at the same level, by default it will escalate the locks. It does this because each lock takes some memory and too many of them eat up all your memory.
So when your first query starts running, each row inserted from tableA gets an exclusive row level lock in TestTable. But rapidly there are lots of these locks created. Very quickly, you exceed the escalation level and SQL changes the locks to an exclusive TABLE lock on TestTable. Now SQL no longer needs all those row level locks and that relieves a lot of memory pressure on the system.
But now the first process has a table lock on TestTable, so the second process is blocked until the first process finishes.
You can turn this behavior off. To do this run
Alter Table dbo.Testtable SET [LOCK_ESCALATION = DISABLE]
Now both processes can continue to insert rows at the same time. However, depending on what else is going on in your system, how much memory you have, etc, disabling lock escalation can slow your system significantly.
If you do disable lock escalation, it will remain off for that table until you turn it back on with
Alter Table dbo.testtable SET [LOCK_ESCALATION = AUTO]
Tom
This set of Database Multiple Choice Questions & Answers [MCQs] focuses on “Lock-Based Protocols”.
1. In order to maintain transactional integrity and database consistency, what technology does a DBMS deploy?
a] Triggers
b] Pointers
c] Locks
d] Cursors
View Answer
Answer: c
Explanation: Locks are used to maintain database consistency.
2. A lock that allows concurrent transactions to access different rows of the same table is known as a
a] Database-level lock
b] Table-level lock
c] Page-level lock
d] Row-level lock
View Answer
Answer: d
Explanation: Locks are used to maintain database consistency.
3. Which of the following are introduced to
reduce the overheads caused by the log-based recovery?
a] Checkpoints
b] Indices
c] Deadlocks
d] Locks
View Answer
Answer: a
Explanation: Checkpoints are introduced to reduce overheads caused by the log-based recovery.
4. Which of the following protocols ensures conflict serializability and safety from deadlocks?
a] Two-phase locking protocol
b] Time-stamp ordering protocol
c]
Graph based protocol
d] None of the mentioned
View Answer
Answer: b
Explanation: Time-stamp ordering protocol ensures conflict serializability and safety from deadlocks.
5. Which of the following is the block that is not permitted to be written back to the disk?
a] Dead code
b] Read only
c] Pinned
d] Zapped
View Answer
Answer: c
Explanation: A block that is not permitted to be written back to the disk is called pinned.
6. If transaction Ti gets an explicit lock on the file Fc in exclusive mode, then it has an __________ on all the records belonging to that file.
a] Explicit lock in exclusive mode
b] Implicit lock in shared mode
c] Explicit lock in shared mode
d] Implicit lock in exclusive mode
View
Answer
Answer: d
Explanation: If transaction Ti gets an explicit lock on the file Fc in exclusive mode, then it has an implicit lock in exclusive mode on all the records belonging to that file.
7. Which refers to a property of computer to run several operation simultaneously and possible as computers await response of each other
a] Concurrency
b] Deadlock
c] Backup
d] Recovery
View
Answer
Answer: a
Explanation: Concurrency is a property of systems in which several computations are executing simultaneously, and potentially interacting with each other.
8. All lock information is managed by a __________ which is responsible for assigning and policing the locks used by the transactions.
a] Scheduler
b] DBMS
c] Lock manager
d] Locking agent
View Answer
Answer: c
Explanation: A distributed lock manager [DLM] provides distributed software applications with a means to synchronize their accesses to shared resources.
9. The ____ lock allows concurrent transactions to access the same row as long as they require the use of different fields within that row.
a] Table-level
b] Page-level
c] Row-level
d] Field-level
View Answer
Answer: d
Explanation: Lock is
limited to the attributes of the relation.
10. Which of the following is a procedure for acquiring the necessary locks for a transaction where all necessary locks are acquired before any are released?
a] Record controller
b] Exclusive lock
c] Authorization rule
d] Two phase lock
View Answer
Answer: d
Explanation: Two-phase lock is a procedure for acquiring the necessary locks for a transaction
where all necessary locks are acquired before any are released.
Sanfoundry Global Education & Learning Series – Database Management System.
Next Steps:
- Get Free Certificate of Merit in Database Management System
- Participate in Database Management System Certification Contest
- Become a Top Ranker in Database Management System
- Take Database Management System Tests
- Chapterwise Practice Tests: Chapter 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
- Chapterwise Mock Tests: Chapter 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
Manish Bhojasia, a technology veteran with 20+ years @ Cisco & Wipro, is Founder and CTO at Sanfoundry. He lives in Bangalore, and focuses on development of Linux Kernel, SAN Technologies, Advanced C, Data Structures & Alogrithms. Stay connected with him at LinkedIn.
Subscribe to his free Masterclasses at Youtube & technical discussions at Telegram SanfoundryClasses.