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)

7
votes
1answer
147 views

Deadlock when update different rows with non-clustered index

I'm solving a deadlocking issue while I noticed the lock behavior is different when I use clustered and non-clustered index on the id field. The deadlock issue seems to be solved if clusted index or ...
-1
votes
2answers
52 views

When do I need transactions /locks?

I know that transactions are meant to group several operations as one. But if for example in the same table one transaction does DELETE FROM table where state = 'done' and another does either SELECT ...
0
votes
0answers
29 views

Why does the delete/update block each other in this case?

The following drives me crazy. I have: session1>begin; session2>begin; session2>update dummy set salary=1000 where id=11; session1>delete from dummy where id=1; id is the PK ...
1
vote
2answers
31 views

Is there a rule of thumb regarding the size and number of a buffer pool instances?

I know the consensus seems to be approximately 75% of ram should be dedicated to the buffer pool. But, I'm more interested in the optimal number of buffer pool instances. What are some things to ...
0
votes
1answer
168 views

How can I find who has allocated a user lock via dbms_lock?

We have a print queue process that acquires a user lock via dbms_lock.allocate_unique. It performs the work and then releases the lock. The process works very well. Today, we have a process that ...
0
votes
1answer
73 views

Reporting Services TempDB lock blocking report generation

Two days ago one of our reports in Reporting Services started to take much longer to generate. Its generation time had increased from 4 seconds to 2 minutes. Investigation found: The Stored ...
0
votes
2answers
365 views

MySQL concurrent INSERTs

I have a MySQL database with InnoDB tables. There are different client processes making SELECT (to check the existence of a value) and INSERT or UPDATE (depending on the result of the select) ...
3
votes
1answer
46 views

SQL Server 2008 R2: query RECENT locks, NOT current locks

The database had average lock wait time of 30 seconds for a period of about a minute. I didn't run my lock query in time to see what was happening. I can't seem to find anything that shows recent ...
14
votes
2answers
487 views

Lock CREATE TABLE

In another application I was struck by bad design: multiple threads execute an EnsureDatabaseSchemaExists() method concurrently, which looks basically like this: IF NOT EXISTS (SELECT * FROM ...
2
votes
0answers
82 views

SQL Server - Same query, same database, different results [closed]

I have a database in SQL Server 2008 with 2 tables. I'm running the following query on Management Studio: select M.FD_BROWSER_SHORT_NAME AS X, Count(1) AS Y from TB_EVENTS E INNER JOIN ...
18
votes
3answers
7k views

What is lock escalation?

I was asked this question at an interview and had no answer. Can anyone here explain?
0
votes
1answer
3k views

What is causing Waiting for table level lock errors?

We got the database hanging twice already and trying to find a cause. show processlist Waiting for global read lock | INSERT INTO {myisam_table} ... In here the disk space was full so we thought ...
4
votes
0answers
96 views

User locking tables without privileges

This is a restatement of a question asked on the GIS Stack Exchange here. Although it has GIS-specific elements it may be be a more general PostgreSQL effect. I have a PostgreSQL 8.4.4 database ...
1
vote
1answer
119 views

DB Locks - How would one handle long transactions and maintain high concurrency?

We're maintaining a POS/WMS Application built in powerbuilder that runs on SQL Server 2008 R2 backend. Recently, we've been having a lot of locking issues and I believe it has to do with how we handle ...
1
vote
1answer
56 views

Can I alter a view being referenced with schemabinding if the change fulfills the binding requirement?

In SQL Server 2008, I have views WITH SCHEMABINDING and I need to alter one. I am changing a column while not changing its alias so consumers will be unaffected. If I were to drop all my other views ...
1
vote
1answer
49 views

How, when and why does implicit changing of transaction isolation level happen?

RCSI (Read Committed Snapshot Isolation) is the setting of a database changing the behavior of (the default) Read Committed isolation level in SQL Server. This is, as I understand, the context of ...
1
vote
2answers
67 views

MySQL views and table locking (5.1 MyISAM)

I have a large (160 million rows, 50GB) table in MySQL 5.1 using MyISAM. If I were to construct a view defined by a fairly complicated query, would a SELECT on the view lock the underlying table?
4
votes
1answer
186 views

How is SQL Server returning both a new value and old value during an UPDATE?

We've had issues, during high concurrency, of queries returning non-sensical results - results the violate the logic of the queries being issued. It took a while to reproduce the issue. I've managed ...
0
votes
0answers
21 views

Why are there more MySQL deadlocks on my 8-core machine than 16-core?

I'm running a bunch of scripts in parallel that do: START TRANSACTION REPLACE INTO... REPLACE INTO... REPLACE INTO... ... COMMIT On my 8-core machine, where 8 of these processes are running in ...
11
votes
5answers
168 views

What is the exact relationship between a database transaction and locking?

This is a humble question asked in the spirit of increasing my knowledge; kindly be gentle in your response. As a long-time application developer, I know at some level what a transaction is (I use ...
1
vote
2answers
86 views

Do I need a locking read?

I am inserting rows in a table, based on a value and on the existence of a row, which I get from SELECT statements. This function can be run concurrently, so I need to do some extra obviously. The ...
1
vote
0answers
59 views

How to make SSAS Commit Write take less time

We're using SQL Server Analysis Server 2012 (Enterprise) and have a process to update the cube during the day. This process is running pretty well until it gets to the final part of processing where ...
4
votes
2answers
201 views

In SQL Server, how do read locks work?

Suppose I have the following long-running query UPDATE [Table1] SET [Col1] = 'some value' WHERE [Col2] -- some clause which selects thousands of rows and suppose the following query is executed ...
3
votes
1answer
77 views

When changing the size of a nvarchar column, do I need to drop the unique index? And will the table get locked when recreating the index?

In our database a big table exists that more or less looks like this: CREATE TABLE dbo.production_data ( pd_id BIGINT PRIMARY KEY, serial NVARCHAR(16) NOT NULL UNIQUE, ... ); but now ...
2
votes
1answer
52 views

What is happening with locks in my database?

I'm having some problems on my database. At any given time, I'm trying to execute an update with five records on my table, but this table is locked and I get a time out executing the query. I ...
-1
votes
1answer
57 views

How to determine if an Oracle table is locked or not?

We've been using BI software and a repository database that are installed on Oracle Enterprise 11gR2. Some of these batch reports will try to access a database table which may still be locked. How ...
2
votes
1answer
84 views

How to use proper hints for Upsert involving a top 1 subquery

For this specific case (tracking Load Balancer switches) we wish to optimize an Upsert so that it does not exhibit any race conditions, cause any PK violation, or acquire any over sized locks. ...
1
vote
0answers
189 views

Unable to insert into or update table

A have the following table in a SQL Server 2000 database (service pack 2), with about 600,000 rows: Id [int, primary key] Form [int] Element [int] Data [char(8000)] MoreData [varchar(8000)] ...
1
vote
1answer
56 views

Locks in SQL Server - Update/Select

I am new to SQL Server and am not sure about how the locking mechanism works here. Please help Issue: Need to delete close to 5 mil records in a table. A website pulls data from that table quite ...
0
votes
2answers
195 views

Idle connection plus schema-modifying query causing locked database

As part of our automated deployment process for a web app running on a LAMP stack, we drop all our triggers and stored procedures and recreate them from source control. It turns out there was a hidden ...
1
vote
1answer
58 views

Can I limit the time a table lock is being held server side?

I have a SQL Azure server with some stored procedures with TABLOCKX hints that I call from C# code on a Windows Azure web role VM. I'm kinda paranoid about those hints - what if there's some network ...
0
votes
1answer
71 views

Can we set LOCK_TIMEOUT instance- or database-wide?

It is possible to change the SQL Server's default (which is -1 or indefinite period of time) LOCK_TIMEOUT value session-wide. Is it possible to set LOCK_TIMEOUT default value server-, instance- ...
0
votes
0answers
42 views

MySQL Multiple threads inserting *SAME* record a Bad Thing?

Okay, as I've noted in other questions, I'm not a sophisticated database user even though I took my first course in relational algebra from ted Codd. I've now inherited an application that ...
0
votes
1answer
363 views

Row locking within stored procedure [duplicate]

I have a SQL Server Stored Procedure for getting the next number in sequence stored in a configuration table: declare @val as int SELECT @val = LongValue from tblSystem WHERE [Key] = @Key; SET @val ...
1
vote
1answer
246 views

How does locking work in INSERT..ON DUPLICATE KEY UPDATE statements?

I am encountering deadlocks while trying to update multiple rows (a batch) using INSERT..ON DUPLICATE KEY UPDATE. Does MySQL lock all the rows in one batch or does it lock only the row it is updating ...
1
vote
1answer
292 views

PostgreSQL transaction locked database table: “idle in transaction”

I have a web application interacting with PostgreSQL (v8.4 & Centos Linux) which suddenly started locking some of the database's tables. Still have no idea what happened, since the code is not ...
0
votes
0answers
47 views

Lock timeouts on a table with two keys, where only one connection writes for a given value of the first key at a time

I have a table user_rollup_message_list that is keyed by (user_id, message_reference). It is a write-heavy table, and gets enough writes that I get many lock timeouts, with a timeout of 50 seconds, ...
2
votes
2answers
2k views

Parallel inserts cause deadlock on DB2

The scenario is the following: The application attempts to insert into the same table from two parallel threads making 4000 insertions in each thread (separate transaction per thread). It causes the ...
0
votes
2answers
94 views

Does ROLLBACK TRAN honor LOCK_TIMEOUT value?

I have a following stored procedure on my system. BEGIN TRY BEGIN TRAN SET LOCK_TIMEOUT 10000 UPDATE tbl COMMIT TRAN END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRAN END CATCH ...
2
votes
2answers
2k views

Does a simple select query acquire locks?

I am very new to SQL Server, and would like to understand whether the following, very simple select statement would take any locks. Select * from Student; Please consider the case where the ...
0
votes
2answers
152 views

Example optimisic offline lock in SQL/PL?

Can someone show me an example of an optimistic offline lock in an Oracle Stored Procedure?
1
vote
1answer
684 views

MySQL: Alter Table on very active table gives “Waiting for table metadata lock”

At my work, we've recently upgraded to MySQL 5.5 and since then, have been bitten a few times by the new (and intended) behavior of metadata locking. Basically, whenever we run an ALTER TABLE ...
2
votes
3answers
106 views

In what order does postgresql handle queries which have conflicting locks?

Let's say that I have a long running query SELECT and this query holds a AccessShareLock on a table. While this query is being processed a new query which needs a AccessExclusiveLock on the table ...
1
vote
2answers
71 views

Read committed snapshot:Finding lock on table

IF update command is run on a table and commit is pending then another user cannot be able to update the table. Is there any query to get the user or PC which caused lock on the table.
4
votes
1answer
210 views

Locking in “Read Committed Snapshot”

If an update is run on a table with "Read Committed Snapshot" isolation level and a commit is pending eg: update table1 set col1 = col1 + 1 where PKcol < 3 update table1 set col1 = col1 + 1 ...
0
votes
1answer
264 views

How to avoid locks by large transactions?

We have a large application running, that requires cleanup for performance reasons. This was however not forseen when designing the application. Basically, the delete is executed from a stored ...
2
votes
2answers
3k views

Table Lock on INSERT - How to optimize

we have a few long running procedures in the database, that cause an instant Table Lock on our main text storage table, which causes all other requests of the web app to wait (because the table is ...
1
vote
0answers
74 views

UPLOCK to avoid multiple reads

Given the following table structure... Orders ====== OrderId int PK OrderEvent =========== OrderEventId int PK OrderId int FK EventTypeId int FK I've got multiple concurrent processes SELECT-ing ...
4
votes
3answers
9k 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?
2
votes
2answers
69 views

Lock hints on preconditions

Assuming for a moment that FK references are not available, what lock hints should be placed on the preconditions of the following example to prevent deadlocks and inconsistent data? (This is a ...