A mechanism for managing concurrent access to shared data or resources by granting temporarily exclusive access to the processes requesting the lock.
3
votes
1answer
396 views
Using (NOLOCK) in a READ COMMITTED transaction block
We're using SQL Server 2000.
I'm wondering what the risks are if we use a NOLOCK hint within a READ COMMITTED transaction block, for example:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
BEGIN ...
0
votes
2answers
1k views
Progress OpenEdge - how to remove lock from table?
I have some kind of bug leaving lock on table in application that is connecting to Progress OpenEdge Database. Because of the lock I'm not able to test what app is doing wrong there (since I can't ...
3
votes
1answer
188 views
Does blocking always mean open transaction?
This may seem a bit dull question but does blocking always mean that there is open transaction and that may cause transaction log grow to infinity because open transaction prevents log truncation ...
6
votes
2answers
773 views
Shared Lock issued on IsolationLevel.ReadUncommitted
I read that if I use IsolationLevel.ReadUncommitted, the query shouldn't issue any lock. However, when I tested this, I saw the following lock:
Resource_Type: HOBT
Request_Mode: S (Shared)
What is ...
1
vote
1answer
1k views
MySQL MyISAM Locking question
It was my understanding that MyISAM uses table locking during inserts and updates.
To me that meant that read and write by other queries on that table is disallowed until the first insertion or update ...
3
votes
1answer
1k views
How to find out what caused the database(DB2) in a locking state
I know how to check the DB is in a lock state or not, but my question is how to find out which query caused the lock on table /db.
IS there any log file getting created if something unexpected ...
5
votes
3answers
1k views
SQL Server - what isolation level for non-blocking select statements?
I have a long running transaction (called, say, T1) that performs some deletes, updates and inserts on a table in SQL Server 2008 R2. At the same time, another process periodically runs select ...
7
votes
2answers
3k views
Difference Between Row Level and Page Level Locking and Consequences
When attempting to run my Maintenance Plan, I receive the following error:
Executing the query "" failed with the following error: "The
index "" (partition 1) on table "" cannot be reorganized
...
4
votes
4answers
1k views
Benefits of table level locking
What are the benefits of table level locking which is used by the MyISAM storage engine? Row level locking has lots of benefits like concurrent updates and reads that do not lock the table.
Edit
Its ...
3
votes
2answers
439 views
Will creating partitions reduce locking and how do we implement this in sql-server?
A client wants our application to process more data faster so arranged a meeting with their dba to discuss options.
This application generates quite a lot of data that is used for reporting. Before ...
2
votes
1answer
356 views
Locking, deadlocking workaround while process data at the same table
My database has a table: tableX.
Task1 makes intensive INSERTs (1000 records per minute) as records to process and very seldom UPDATEs (1-2 records per minute) as records to recalculate.
At the ...
6
votes
4answers
611 views
SQL Server: what should be done when a process is blocking another process?
I'm an accidental DBA and still learning.
When I see from DMV:s or Activity Monitor that some process is blocking another process, what should be done?
Should I simply kill those processes or is ...
8
votes
2answers
1k views
What is blocking and how does it happen?
I tried to find some information about blocking in SQL Server, but I could not find a concise explanation for what it is and how it happens. Could you please enlighten me?
15
votes
3answers
2k views
Adding columns to production tables
What's the best way to add columns to large production tables on SQL Server 2008 R2? According to Microsoft's books online:
The changes specified in ALTER TABLE are implemented immediately. If the ...
3
votes
1answer
534 views
No “Using locked pages for buffer pool” in SQL Server log
As a trial run for a client I am trying to enable the "Lock Pages in Memory" setting on my development server, but no matter what I change, I cannot get the "Using locked pages for buffer pool" to ...
3
votes
2answers
543 views
Why did my UPDATE statement not complete?
Good morning. Running SQL 2008; Mgmt Studio 2008. I am working with a MS SQL job that we have created for our MS CRM 4.0 implementation. For anyone familiar with CRM, this job purges the ...
2
votes
3answers
5k views
Set “lock wait timeout” in MySQL
Where would I set the maximum time a query will wait for a lock in MySQL 5.0.68 before timing out?
16
votes
3answers
2k views
Justify NOT using (nolock) hint in every query
Have you ever had to justify NOT using a query hint?
I am seeing WITH (NOLOCK) in every single query that hits a very busy server. It is to the point that the developers think it should just be on ...
3
votes
2answers
2k views
What is the best practice for storing image varbinary(max) data in a database table?
I have a table that stores images that range in size between 16-100 KB each. Since the images are so small, I've taken Microsoft's advice and not used the FILESTREAM data type. The table is ...
5
votes
2answers
353 views
SQL Server - RangeX-X and RangeI-N locks
I came to a dead point in a deadlock analyze. According to msdn:
RangeX-X are Exclusive range, exclusive resource lock; used when updating a key in a range.
RangeI-N are Insert range, null resource ...
2
votes
1answer
699 views
MySQL : “FLUSH TABLES WITH READ LOCK” started automatically
I would like to understand how this happened. I was running a query that would take a long time, but should not lock up any table. However, my dbs were practically down - it seems like it was being ...
15
votes
3answers
4k views
What is lock escalation?
I was asked this question at an interview and had no answer. Can anyone here explain?
