Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [locking]

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

0
votes
1answer
21 views

Update Statement on one table takes locks on other tables

Given the following statement on the AdventureWorks database: UPDATE Sales.SalesOrderDetail SET OrderQty = 100 WHERE ModifiedDate >= '2014-01-01' If I open another query window and look at the ...
0
votes
1answer
42 views

Mysql Alter table add column with ALGORITHM=INPLACE, LOCK=NONE

Am running below query Query Alter table dbname.tablename add column column1 varchar(50), add column column2 text , ALGORITHM=INPLACE, LOCK=NONE; It takes more time to execute. Ideally running ...
3
votes
1answer
267 views

Checking @@ROWCOUNT failing

I have a code similar to the one below in one of my SQL procedures, declare @rowcount int update table1 set value = @value where id = @id select @rowcount = @@ROWCOUNT if ( @rowcount = 0 ) begin ...
0
votes
0answers
15 views

Can't cancel query in Redshift

I have run a SQL in redshift and it hangs over 1 hour(Even doing explain on the SQL). I tried to use cancel/pg_cancel_backend/pg_terminate_backend to cancel/terminate the session. It seemed the ...
0
votes
1answer
29 views

Differences of MySQL locking for update with WHERE and IN

So I have a table with id as INT NOT NULL as the PK. When I do query 1 (Trans1) in a transaction I get 3 rows locked, and when I do query 2 (Trans2) also in a transaction, I do also get 3 rows locked. ...
0
votes
1answer
24 views

Mysql replication rebuild dump database lock

I need to rebuild master-slave replication of whole instance of mariadb on innodb. What I often see is that people do read lock then dump master then unlock, like: FLUSH TABLES WITH READ LOCK; SHOW ...
0
votes
0answers
35 views

Why two select statements are causing deadlock? S and IX locks

<deadlock> <victim-list> <victimProcess id="process516293848" /> </victim-list> <process-list> <process id="process516293848" taskpriority="0" logused="17436" ...
0
votes
0answers
33 views

Steps to unlock a locked select in postgres

I have tried to update a table in Postgresql 9.6 (AWS) based in two inner joins. Since it was taking too long, I cancelled it. Now I'm trying to run a query to count records from the inner join but ...
1
vote
2answers
59 views

Overly eager explicit row locking causes deadlock

I'm running into deadlock problems on MariaDB using the INNODB engine. In my use case, I've got a number of threads working on a table. Each thread first reads a number of rows and then updates those....
1
vote
1answer
36 views

Recalculate and replace large table without interrupting table reads

There's a large table in the database containing statistical information. This table must be read frequently (by lookups which return a single row). This table must be completely recalculated every ...
1
vote
0answers
21 views

Postgres 10.6: UPDATE on partially indexed column sometimes hangs

I have a table (6 small columns 2M+ rows) with a column dead. There are two indexes on this table, both are partial with dead = false. From time to time I want to scan the table and mark rows with ...
3
votes
1answer
36 views

Causes for pg_class locks except DROP TABLE

I do experience the following situation on a PostgreSQL 11: A transaction with unknown statements creates an ACCESS SHARE lock on pg_class. Another connection wants to execute DROP TABLE which causes ...
-2
votes
2answers
62 views

Are lost updates completely handled in SQL Server or is manual intervention still required? [closed]

As lost updates occur when 2 different transaction try to update the same thing at the same time - different sessions update the same row; the last one overwrites the changes made by the first. We ...
0
votes
1answer
168 views

Oracle Database troubleshooting enq: TX - row lock contention

AWR Reports of a 12.1.0.2 EE Oracle Database shows enq: TX - row lock contention with up to 80% of DB Time. The application is huge with more than 500 tables and 100.000 registered users, on average ...
0
votes
2answers
36 views

SELECT … FOR UPDATE returns old value after waiting for lock

It seems that if SELECT ... FOR UPDATE has to wait for a lock, and meanwhile another thread changes what would be the result, and commits, then the initial query returns the old result, i.e. from ...
0
votes
1answer
51 views

Ensuring unique value for group of rows in table with trigger and concurrent transactions

I've seen similar variations of this question asked, but nothing that tackles this particular implementation and I was wondering if it was possible to do with sql/plpgsql. Take this as more of a ...
1
vote
1answer
54 views

Is it possible to show DML that is locking some table?

I already saw here how I can list my locked objects. Suppose I, using PL/SQL Developer(pink cylinder logo one), run select * from someTable for update and didn't commit nor rollback. Now using the ...
0
votes
1answer
20 views

locking question around INSERT during UPDATE query

Say I have a large table products id, color, cost and I want to run a query to update the cost of all 'red' items UPDATE products SET cost = cost + 1 WHERE color = 'red' Let's pretend that there ...
0
votes
2answers
53 views

Delete queries blocks other concurrent transactions

DELETE queries in my production SQL server are causing deadlocks. I know that DELETE is causing this because i checked the Extended Events and checked the deadlock XML and found out that this DELETE ...
2
votes
1answer
31 views

Prevent a user from making changes to some columns in a MS SQL Server database

I'd like to prevent a user from making changes to some columns in a MS SQL Server database. I cannot lock the column as READ ONLY as there will be changes made to that column. Let me explain as best ...
0
votes
1answer
22 views

perform non locking select when updating another table

We need to perform the following (periodic) query in mariadb: update A set status='INACTIVE' where status='ACTIVE' and a.b_id in (select id from B where ... LOCK IN SHARE MODE); Unfortunately it ...
0
votes
1answer
259 views

Why select statement against a table in oracle returns ORA-04021

I have a big partitioned, local indexed table in Oracle database, what I am doing is import a big dump file into it with "impdp" Why when I issue a select statement against that table Oracle returns ...
2
votes
1answer
133 views

Deadlock with insert and delete

Deadlock graph: <deadlock> <victim-list> <victimProcess id="process21881f1bc28" /> </victim-list> <process-list> <process id="process21881f1bc28" ...
0
votes
1answer
124 views

Concurrent update queries locking rows when `where` clause doesn't match

My application sends messages to Slack. On very rare occasions we may need to retract the message going out. These retractions are handled by queueing a job for each messageId that has been created ...
2
votes
1answer
104 views

Backend Table/Row Locking Structure using CTE Queries

This question is an extension of a previous question that I asked. Table Locking When using Unions With the same query we are using that is causing blocking the various queries used in these UNIONS ...
2
votes
1answer
107 views

Table Locking When using Unions

When multiple selects are used in a UNION/UNION ALL are the table locks for each individual SELECT statement handled individually or are all table locks held until all SELECT statements are completed? ...
0
votes
2answers
28 views

Software Uses SQLite and Has Frequent DB Lock Errors. Zero Access to Code. Anything we can do?

I've written an API for a client that accepts orders from clients and converts them into print jobs that go to an HTTP bridge into the company's RIP software. On the backend, the bridge is reading and ...
2
votes
1answer
25 views

ExclusiveLock on related table due to update?

Recently ran into the following: Consider the tables created by this: create table table_a ( id bigserial not null, last_update timestamp, primary key (id) ); create table ...
1
vote
0answers
41 views

DeadLock - Two Process Locked Same Index

We have met a scenario that two process acquired exclusive lock on same index ans the same time two are requesting for shared lock to other process which eventually leads to dead lock. I am still not ...
0
votes
0answers
45 views

Concurent DELETE with condition

For example, I have articles, and authors. An article needs at least one author. But you can delete multiple authors at the same time and concurrently. So I need to prevent having a state where the ...
1
vote
1answer
194 views

Mongo queries are slow and yielding/waiting for locks thousand times

We are using mongo setup with replica sets on AWS. Setup Details: 1 Primary node: r3.8xlarge 4 secondary nodes: r5.xlarge Mongo version: 3.0.8 (WiredTiger) Database size: 358GB We have configured ...
1
vote
1answer
195 views

Will (ROWLOCK,UPDLOCK,READPAST) with ORDER BY non-clustered columns work?

I have a SQL code as below #table creation CREATE TABLE TAB ([i_task_id] [int] IDENTITY(1,1) NOT NULL, [i_priority] [tinyint] NULL, [i_status] [smallint] NULL CONSTRAINT [PK_TAB] PRIMARY KEY ...
0
votes
1answer
176 views

DROP TABLE IF EXISTS hangs on Waiting for table metadata lock

I'm using a dockerized MySQL 5.7.13 as part of an integration test of an application. The way the system works is as follows: I start up the main application. It opens up a connection to MySQL, but ...
1
vote
0answers
45 views

PostgreSQL : FOR SHARE & FOR UPDATE priority

Here is my situation: I have 2 types of actions A & B, each read or update a single ressource. Actions A should be able to be executed concurrently. Actions A should wait for Actions B to be ...
0
votes
0answers
114 views

What happens if two processes attempt to update the same row in the same Postgres table at the same time

To clarify the question, what is supposed to happen if two different processes attempt to UPDATE the same row (uniquely identified by primary key) at the same time? - And does it make any difference ...
0
votes
1answer
130 views

What does innodb_table_locks setting do exactly?

Can someone please explain how this setting affects the locking behaviour in innodb (with an example). I ran numerous experiments with different configurations of (autocommit, innodb_table_locks, LOCK ...
0
votes
1answer
24 views

Synchronising batch delete from multiple applications with advisory lock in Postgres

We are moving our application from one-instance to multiple instances (all connected to the same Postgres database) in order to increase resiliency of one of our services, and I am now facing a synch ...
0
votes
1answer
167 views

sp_getapplock not working in multiple Connection [closed]

I wrote below script and run it in two tab of SQL Server management studio: DECLARE @RC INT Begin TRAN Exec @RC =sp_getapplock @Resource='MyLock', @LockMode='Exclusive', @LockOwner='Transaction', ...
2
votes
2answers
185 views

Strange deadlock with multi-column index with innodb on MySQL

I'm seeing some odd (to me) behavior in MySQL. Let's start with the table that I'll be talking about. CREATE TABLE `active_foo` ( `active_foo_id` bigint(20) NOT NULL AUTO_INCREMENT, `c_id` ...
1
vote
1answer
80 views

How can I drop a column from a large (700M rows) hot (multiple transactions per second) table with minimal downtime?

My research suggests I can drop a column on a table in a matter of moments (provided I obtain an exclusive lock) because it is only a metadata change. The table has two different services ...
0
votes
1answer
42 views

Reporting on a SQL DB Server with many write operations and few read operations [closed]

I know this is a generic question, but I have to clarify some basic questions. I know that when a write is happening to a SQL Server database table, a read to that table will have to wait. My question ...
1
vote
1answer
22 views

How to explain/introspect what locks a DDL operation will need (Postgres)

I would like to have some way of inspecting what locks a DDL operation will need to acquire. Preferably something generic enough that I don't need to personally know/encode knowledge of all of the ...
1
vote
2answers
656 views

Could not obtain exclusive lock on database 'model' - who is using it?

when trying to run the following query in order to create a new database: CREATE DATABASE [Lunch] CONTAINMENT = NONE ON PRIMARY ( NAME = N'Lunch', FILENAME = N'E:\Data Files\...
2
votes
2answers
74 views

Allow only one reader to a table

I have a simple Postgres table which port numbers to listen on. If one port is used it can't be used again. However due to multiple worker/threads they many times read simultaneously. I want that ...
0
votes
1answer
40 views

How many row level locks can I have to same row?

I need some insights on row-level locking in Postgres. How many shared locks (FOR SHARE) can be concurrently on a particular tuple in a relation . Will have many shared locks on same tuple cause any ...
4
votes
1answer
70 views

How locks in Postgres behave when quitting the JVM abnormally

I have a table 'car', as follows Table "public.car" Column | Type | Collation | Nullable | Default ----------+--------+-----------+----------+--------- car_no | bigint | ...
3
votes
1answer
73 views

Row Locking in Postgres

I have table named 'car' Table "db145004db.car" Column | Type | Collation | Nullable | Default ----------+--------+-----------+----------+--------- car_no | bigint | |...
1
vote
1answer
38 views

Missing rows (gaps) on range read from the end of the table

On a data processing pipeline, getting gaps in returned data. Process A - inserts sequentially into a table. Table has an auto-increment primary key id. Process B - selects max(id) [into X] and then ...
0
votes
2answers
195 views

MySQL 5.1 - long running select locking the table and not allowing inserts

Platform - MySQL 5.1, MyISAM engine We reached max on int (id, auto increment) in our Prod system. Changed the type to BigInt. Even since this change was made, long running selects are locking the ...
3
votes
1answer
265 views

Why does MySQL forbid LOCK TABLES in stored procedures?

I have read through several sections of the MySQL reference manual, and while it states repeatedly that LOCK TABLES cannot be used in a stored procedure, I have never found an explanation for the ...