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.

1
vote
0answers
23 views

Why does an UPDATE to a full-text-indexed column lock the full text index?

I'm troubleshooting a deadlock on a SQL Server full-text index. From what I can see in SentryOne's monitoring, two queries deadlocked when trying to acquire locks on the full-text index; one query was ...
1
vote
0answers
38 views

The GET_LOCK() and RELEASE_LOCK() functions are taking too long

We are trying to use the MySQL's locking functions GET_LOCK() and RELEASE_LOCK() in our Spring Java application. The code we are using is defined in a procedure for both seperately which simply invoke ...
-2
votes
1answer
54 views

SSMS Application Security idle session locking window [closed]

We are running multiple versions of SQL Server Management Studio in our network, I believe from 2000 through 2017. For our security review we need to find out how to set an inactivity timeout in the ...
2
votes
1answer
24 views

SQL Azure “hot” table query slowdown over time

I have an Azure SQL Database table that is acting as a "prioritized queue". As such, it gets heavily written to and deleted from over time. What's happening is that after a week or two, the DB size ...
3
votes
2answers
84 views

Gap Locking in Read Committed isolation level in Mysql

MYSQL VERSION : 5.7.X STORAGE ENGINE : Innodb I have a general idea that Read Committed Isolation will mostly use Shared and Exclusive Record Locks. But, as per mysql docs, there are some cases where ...
0
votes
1answer
13 views

mongodb read write locks

I'm currently investigating the possibility of using mongodb. This would contain the a collection of 100million+ documents. I need a process that will be constantly running deleting documents older ...
9
votes
1answer
421 views

Lock in SQL Server and Oracle [duplicate]

I have been doing some tests of locking/blocking in MSSQL Server and Oracle, I noticed one difference: In Oracle - I performed an update for one row without issuing commit or rollback and in another ...
0
votes
0answers
29 views

Counter Field that Increments on Update?

I need a "counter" field that gets the latest value of a counter and then increments the global counter value so that the next update on a reocrd gets the new latest value. For example, lets say this ...
0
votes
0answers
18 views

InnoDB locking: what does index record mean?

From MySQL documentation Record Locks A record lock is a lock on an index record. what does index record mean for cluster index? what does index record mean for secondary index? Is it a key-...
0
votes
0answers
30 views

MySQL InnoDB: how to interpret this InnoDB monitor output of Next-Key locks?

Next-Key Locks from MySQL documentation A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record. InnoDB performs row-level ...
0
votes
1answer
11 views

Postgres 9.6.6 on Ubuntu 16.04, lots of errors

We're running perfsonar on a group of our servers, and while doing some maintenance work found that we get giant logfiles under /var/log/postgresql table bloat on the database of the perfsonar ...
0
votes
0answers
42 views

Perform SELECT without Locking Table but don't allow Dirty Reads

Is there a way I can perform a SELECT query which wont lock the table it is reading but also won't perform dirty reads? for example, assume Table1 has 100m records: SELECT Col1, Col2 FROM ...
5
votes
1answer
108 views

SQL Server Exclusive(X) lock not consistently blocking Shared(S) lock on a resource

My understanding of locking in SQL Server has been that if one process holds an exclusive (X) lock on a resource e.g.a key row in an index, another process cannot acquire a Shared (S) lock on that ...
0
votes
1answer
26 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
185 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
281 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
33 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
31 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
29 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
40 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
79 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
69 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
33 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
60 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
66 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
343 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
51 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
58 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
57 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
24 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
58 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
39 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
547 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
255 views

Deadlock with insert and delete

Deadlock graph: <deadlock> <victim-list> <victimProcess id="process21881f1bc28" /> </victim-list> <process-list> <process id="process21881f1bc28" ...
0
votes
1answer
133 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
179 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
149 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? ...
1
vote
2answers
46 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
27 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
47 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
368 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
254 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
257 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
47 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
219 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
182 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
25 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 ...