A mechanism for managing concurrent access to shared data or resources by granting temporarily exclusive access to the processes requesting the lock.

learn more… | top users | synonyms (1)

3
votes
2answers
3k views

LOAD DATA (400k rows) INFILE takes about 7 minutes, cannot kill the “logging slow query” process?

MySQL version: 5.5.13 The query that I'm using: SET AUTOCOMMIT=0; LOAD DATA INFILE '/data10/select_into.outfile/v3_zone_date.out' INTO TABLE v3_zone_date FIELDS TERMINATED BY ','; COMMIT; ...
2
votes
2answers
723 views

How do I determine what object two SQL process are contending over?

When long running Query#1 exclusively locks an object (table, index or page), and then Query#2 is executed by another user and becomes blocked on the lock held by Query:1, how would I get the name of ...
1
vote
1answer
64 views

Reading and writing the common data

I have a couple tables and application service that intensively inserts, updates its. From the other hand some client application have to read data using those tables. Sometimes client application ...
3
votes
1answer
571 views

mysqladmin debug writes out Current locks but PROCESSLIST and INNODB STATUS don't show any

I'm using the 1.1.8 mysql-cacti-templates to gather metrics on MySQL 5.0.77 (CentOS 5.7). Both the InnoDB Current Lock Waits and InnoDB Lock Structures graphs are charting with Cur[rent] values but ...
2
votes
2answers
1k views

SET LOCK_TIMEOUT, is it session or statement based?

For what period does the LOCK_TIMEOUT LAST? I do a "SET LOCK_TIMEOUT 10" and a "SELECT @@LOCK_TIMEOUT" in one command after a login and get 10 returned. Immediately after this I do a "SELECT ...
4
votes
2answers
9k views

SQL Server Transaction Timeout

Is there a way in SQL Server 2008 R2 to cause a timeout for a database modification involving a transaction? We have a scenario where our application code hangs or throws an exception and fails to ...
22
votes
4answers
2k views

Is NOLOCK always bad?

I am a Report Developer who wants to make my queries as efficient as possible. I used to work with a DBA who told me - I believe because I was always dealing with reports on a Production Server - to ...
1
vote
2answers
258 views

What are the disadvantage/consequences of moving updated column to separate table to prevent table lock on the original table?

I have a table p with a column points. There are a couple of million of records in the table and i need to regularly calculate and update the points for each row. This process takes a couple of ...
3
votes
2answers
1k views

Why Sqlcode 100 does not come with exception

I have following query in a PL\SQL procedure on Oracle 10.2: This is the code LOOP BEGIN SELECT a.poid_id0 into v_acc_account_poidid0 FROM account_t a WHERE a.poid_id0=i_acct_id0 ...
2
votes
2answers
401 views

Alternatives to sp_indexoption for Row Locks and Page Locks in SQL Server 2008

Are there better alternatives to setting the Row Locks and Page Locks in SQL Server 2008? The case here is to set the row and page locks to false on the table. The lock is in effect during a series ...
2
votes
1answer
944 views

LOCK IN SHARE MODE

On dev mysql LOCK IN SHARE MODE is described as... SELECT ... LOCK IN SHARE MODE sets a shared mode lock on the rows read. A shared mode lock enables other sessions to read the rows but not to modify ...
3
votes
2answers
3k views

SQL Server 2008 T-SQL select hanging, but not dead locked

This is a pretty strange issue so bear with me. I have a stored proc that does some heavy duty processing. When it runs well it usually takes a few minutes depending on server load, but occasionally ...
2
votes
3answers
1k views

Improve `Update` performance (rows locking issue)

I am running 30 scripts (PHP CLI) on Linux , each script are updating (loop) the data in the MySQL database. When I typed 'mysqladmin proc' in terminal, I can see a lot of rows has been locked for ...
8
votes
2answers
454 views

How does SQL Server determine the order it takes locks in while selecting a table?

I have two stored procedures that are deadlocking when the system is under load. Proc A is selecting from a table while the Proc B is inserting into the same table. The Lock Graph is showing that Proc ...
7
votes
2answers
10k views

Could not continue scan with NOLOCK due to data movement

We run SQL Server 2000 and we get a few of these errors every night. Could not continue scan with NOLOCK due to data movement The query that throws this error is a large complex query that joins ...
3
votes
1answer
641 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
3k 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
251 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 ...
5
votes
2answers
1k 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
2k 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
3k 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 ...
6
votes
3answers
2k 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 ...
8
votes
2answers
6k 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 ...
5
votes
4answers
2k 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 ...
4
votes
2answers
668 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
399 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 ...
7
votes
4answers
1k 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
4k 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?
17
votes
3answers
4k 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
784 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 ...
4
votes
2answers
836 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 ...
4
votes
3answers
10k 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 ...
4
votes
2answers
3k 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
653 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
1k 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 ...
18
votes
3answers
8k views

What is lock escalation?

I was asked this question at an interview and had no answer. Can anyone here explain?