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)

2
votes
1answer
774 views

Minimize locking on UPDATE with multiple WHERE columns

I have a problematic query that's causing a lock timeout: UPDATE <some_table> SET row1=<some value> WHERE row1 IS NULL AND row2 > <some value> ORDER BY row2 LIMIT 100 ...
0
votes
2answers
427 views

How are DB locks tied to connections and sessions?

I've heard of page- and row-level locks, but never heard them used with regards to sessions and connections. Today, our (MySQL) DBA was talking about row locks and implied that their scope can be set ...
3
votes
4answers
4k views

Why is optimistic locking faster than pessimistic locking?

Both forms of locking cause a process to wait for a correct copy of the record if its currently in use by another process. With pessimistic locking, the lock mechanism comes from the DB itself (a ...
1
vote
2answers
134 views

How does optimistic locking actually enforce re-read/update?

My understanding of optimistic locking is that it uses a timestamp on each record in a table to determine the "version" of the record, so that when the record is access by multiple processes at the ...
6
votes
1answer
4k views

When a record is locked in Oracle, can we know which record is locked?

When a record is locked ,can we know which one is locked? How can I get the record rowid or something else info? I can get some info by this sql SELECT ...
2
votes
3answers
5k views

Avoiding “Waiting for table metadata lock” when `ALTER TABLE DROP PARTITION`?

I have some tables that many users need to access to: mysql> show create table v3_cam_date\G *************************** 1. row *************************** Table: v3_cam_date Create Table: ...
3
votes
2answers
2k views

difference between UPDLOCK and FOR UPDATE

given the following code: Set conn = CreateObject("ADODB.Connection") Set RS = CreateObject("ADODB.Recordset") conn.BeginTrans ... //This is the line it is about RS.Open "SELECT a,b FROM c FOR ...
6
votes
1answer
866 views

Global locking for multi-master Oracle GoldenGate replication

This is a very complex scenario, but I figured a state-of-the-art challenge might interest some of the many high-end users of dba.se. Problem I'm working on an intercontinental data replication ...
0
votes
0answers
94 views

What would this non-'unique index with a unique search condition` query lock?

I am not sure about this part of the transaction manual For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement ...
1
vote
2answers
107 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 ...
3
votes
3answers
146 views

Tuning reads (or perhaps the truncate!) for unusual database structure requiring regular truncates

We have an unusual database structure (I leave the reasoning for another question), that in it's current form requires a regular truncation and recreation of allot of row (about 1 million). We're ...
0
votes
0answers
77 views

Which mysql server 5.5 variables would be involved in queries locking in sending data state?

A large portion of my queries (~58%) be it for queries with previously quick return rates or other, are spending in my monitor as sending data. I made some configuration changes but it was very broad ...
1
vote
1answer
179 views

What is the locking behavior for Updatable/Insertable Views?

Consider I have a View that is merely a simple join between 2 tables, selecting all columns. The View is Updatable and Insertable. What would be the locking behavior when executing an UPDATE or ...
0
votes
1answer
462 views

InnoDB row level locks without SELECTs?

I have several (>30) MySQL clients inserting into InnoDB tables concurrently. I keep encountering lock wait timeouts and can't figure out why. Here is the situation: Each client runs the following ...
1
vote
1answer
154 views

SQL Server 2005 Blocking Issue

I am experiencing a blocking issue in sql server 2005. An end user executes a query (e.g. select statements) and seems to get into a never-ending blocking until i kill the process e.g. for hours on ...
0
votes
2answers
2k views

Create a “barrier” (prevent parallel execution) in Oracle PL/SQL?

I'm maintaining some stored procedures in an Oracle 11g RAC environment that are part of the pipeline of steps for data-interchange with other systems. One in particular has been producing duplicate ...
0
votes
2answers
413 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) ...
4
votes
1answer
3k views

ASPState database locking and growth problems

We use an ASPState database to persist .NET Session state on a SQL Server 2005 cluster. We are seeing some strange behavior during peak periods The DeleteExpiredSessions proc is run every minute ...
6
votes
4answers
2k views

MySQL InnoDB locks primary key on delete even in READ COMMITTED

Preface Our application runs several threads that execute DELETE queries in parallel. The queries affect isolated data, i.e. there should be no possibility that concurrent DELETE occurs on the same ...
1
vote
0answers
275 views

Transactional Replication Snapshots causing serious locking after 2012 upgrade

I am experiencing serious locking when adding new articles to an existing transactional replication publication, which is hanging the website. This is the first time I have attempted this since I ...
7
votes
2answers
976 views

Options for setting NOLOCK hint in dataset queries

Some context: At first we wrote reports just "straight up", without any locking hints in the queries. With the larger reports this would sometimes cause locking problems. At first we remedied this by ...
4
votes
1answer
2k views

How to force the use of row locks?

I am trying to get my table to use row locks. I have already disabled lock escalation. The isolation level is READ_COMMITTED_SNAPSHOT alter table <TABLE_NAME> SET (LOCK_ESCALATION=DISABLE) go ...
4
votes
1answer
156 views

Do isolation levels only apply to SELECTS and not UPDATES? [closed]

Scenario that might demonstrate different behavior for SELECTS depending on the isolation level: 1) 0:00 Thread A runs a query that returns 1000 rows that takes 5 minutes to complete 2) 0:02 Thread B ...
5
votes
2answers
4k views

How to correctly implement optimistic locking in MySQL

How does one correctly implement optimistic locking in MySQL? Our team has deduced that we must do #4 below or else there is a risk that another thread can update the same version of the record, but ...
1
vote
0answers
263 views

Oracle locked sessions with ASP.Net

I am connecting to an Oracle database in ASP.Net using SQLDataSource. My connection string is: <add name="ConnectionString3" connectionString="Data Source=sml; User ID=sml; Password=sml; ...
4
votes
2answers
2k views

Deadlock on insert for a table with an insert trigger that updates column in same triggered table

By specification each table uses a guid as the primary key. Another requirement is that each resource requires a 6 number identifier (100001, 100002, ...) as a public id that is unique within the ...
2
votes
2answers
6k 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
1answer
452 views

drop postgres table with minimal locking

I'm trying to run the following Postgres migration script DROP TABLE some_table; DROP TABLE another_table; The problem is that this script causes excessive locking when run on the production ...
12
votes
4answers
4k views

Locking issue with concurrent DELETE / INSERT in PostgreSQL

This is pretty simple, but I'm baffled by what PG does (v9.0). We start with a simple table: CREATE TABLE test (id INT PRIMARY KEY); and a few rows: INSERT INTO TEST VALUES (1); INSERT INTO TEST ...
0
votes
1answer
1k views

SQL Server 2008, database completely locked

Our database becomes completely locked, and it seems that many tables not related to each other are locked at once. When this happens, it becomes locked for approximately 2 minutes, and when this ...
4
votes
2answers
360 views

Schema blocking in READ_COMMITTED_SNAPSHOT

I am trying to troubleshoot locking behavior and the READ_COMMITTED_SNAPSHOT isolation level while attempting to resolve concurrency issues. Background: Assume an online ordering system (ecommerce). ...
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 ...
8
votes
3answers
1k views

How can I ensure that only one copy of a procedure is running in Oracle?

We have the need to ensure that only one copy of a particular procedure is running in Oracle. If it is already running and a user tries to open another, then it should error. Whats the best method ...
8
votes
3answers
1k views

Does SSRS lock the table when querying?

My senior DBA told me that SQL Query execution by default doesn't lock the table. I was having some issues with my SQL Server Reporting Services (SSRS) report which seems to be getting some issues ...
1
vote
1answer
741 views

Can operations in a loop cause table locks?

I know a bit about locking etc, that when a transaction is working on a table, if it is altering a certain amount of data it might try and lock the entire table (I'm sure that is over simplified, but ...
0
votes
0answers
100 views

Being careful about additional records being added in a duplicate record merge?

We have a multi-user MS-Access 2003 database. Connecting to it using SQuirrel SQL, and the JDBC/ODBC bridge, I've managed to eek transactions of some sort out of JET by turning off autocommit. I've ...
10
votes
1answer
2k views

How do I swap tables in MySQL?

Suppose, I have a table foo, which contains some statistics that are computed every now and then. It is heavily used by other queries. That's why I want to compute more recent statistics in foo_new ...
3
votes
1answer
289 views

SQL Server : KEY lock prevents FK on nearby row, but not SELECT [closed]

I have the following table structure (simplified, pseudocode): TABLE dbo.Users (Id int NOT NULL IDENTITY PRIMARY KEY) TABLE dbo.Transfers ( Id int NOT NULL IDENTITY PRIMARY KEY, ...
5
votes
2answers
173 views

DELETE LOW_PRIORITY row visibility

I have a large MyISAM table on MySQL 5.5(Windows XP x64) on which I will have to run DELETE LOW_PRIORITY queries. Does DELETE LOW_PRIORITY make the rows invisible to SELECT statements immediately and ...
3
votes
2answers
325 views

Writing transactions to .MYD file to take Backup

I'm trying to backup MySQL DB by archiving the data directory of required databases. I'm doing FLUSH TABLES WITH READ LOCK before taking the backup, but even then some of the transactions were not ...
4
votes
2answers
18k views

way to prevent queries from waiting for table level lock

We've encountered a problem after moving the database of our customer to an extra server. This should have had positive effects on the site's performance, but there is a problem with table locking in ...
3
votes
3answers
2k views

Cannot write to mysql database while select query in process

Running Mysql 5.1.57 on InnoDB, and have a database locking issue. I have two separate sessions connected to a single database MySQL. On the first session, I run a long SELECT query (technically a ...
7
votes
2answers
4k views

FIFO queue table for multiple workers in SQL Server

I was attempting to answer the following stackoverflow question: What SQL Server 2005/2008 locking approach should I use to process individual table rows in multiple server application instances? ...
2
votes
1answer
151 views

Optimising a query using hint locks

I have a stored procedure that is being called very frequently. For some database servers it is as often as 20 times per second. Unfortunately because different clients call this procedure, I can't ...
3
votes
1answer
254 views

All-or-none exclusive lock on 2 SQL tables

My simplistic testing suggested that I can acquire an exclusive lock on 2 tables or postpone acquiring the lock on either until both can be acquired at once by using a query like this: SELECT ...
0
votes
1answer
1k views

Lock wait timeout exceeded on insert despite low lock_time in the slow query log

Our Java/Spring application reports occasional lock wait timeouts on single record INSERTs to a MySQL 5.5 InnoDB table. Here are a few slow query log records for some of the failed INSERTs. # ...
2
votes
2answers
771 views

enq: TX - row lock contention creating session waits

The database I'm working on has session waits due to DMLs on numerous tables in a procedure. The DBA identifies this as enq: TX - row lock contention. How can I resolve this issue?
3
votes
1answer
582 views

Locking order in cursors (Oracle)

I wonder if the order in which records are locked is guaranteed when I use SELECT ... FOR UPDATE in cursors ? For example, DECLARE CURSOR test_cursor IS SELECT field1, field2 FROM table1 ORDER ...
4
votes
1answer
4k views

MySQL get next unique value without auto increment

I have an MySQL database (InnoDB) that I'm developing for tracking Work Orders at my organization. There are multiple 'sites', and each site has work order numbers starting at 100. WorkorderID ...
2
votes
1answer
854 views

In InnoDB, does a Transaction imply any implicit locking of a table?

The Isolation level is REPEATABLE_READ. The logic is as under: Transaction begins Read data from Table A If (Table A has Any Data) End Transaction and exit If Table A has No Data, Proceed further ...