Tagged Questions

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)

0
votes
0answers
36 views

How can I obtain concurrent deletes for this table?

We have a program that is currently doing something to the effect of: SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRANSACTION UPDATE [Source].[dbo].[SourceTable] SET [ProcessedOn] = ...
1
vote
1answer
44 views

Why would I not specify ROWLOCK to get rid of page locks?

I have a query that goes something like this: SELECT TOP (1) * FROM MyTable WITH (UPDLOCK, READPAST) ORDER BY SomeColumn ASC; This answer says that it's hard to say for sure but it will probably ...
0
votes
0answers
30 views

MySQL Upgrade from 5.5 to 5.6 causes system / table locks

We recently upgraded from MySQL 5.5 to 5.6 on AWS RDS. Before, we had a relatively performant database - definitely had its quirks, but it seemed to be tuned relatively well. After the upgrade, we're ...
0
votes
1answer
32 views

One of databases in MongoDB suddenly gets high lock %

I observe repeatable, sudden, short-time update queries slowdowns in MongoDB cluster for single database. That slowdowns lasts about 30 seconds and repeats few times a day. I found that lock % and ...
0
votes
0answers
18 views

Postgres advisory lock deadlocks itself?

I am executing pg_advisory_locks using the following set of statements: SELECT pg_advisory_lock(317,2); UPDATE posts SET dislikers = array_remove(dislikers, 7) WHERE id = 317; update posts set ...
0
votes
1answer
34 views

MySQL 5.6 replication causes 'waiting for table lock'

All of the sudden queries on slave server stopped with status "Waiting for table level lock" I restart mysql service and stop replications and locking does not show up anymore. Once I turn ...
7
votes
1answer
176 views

Detecting the locked table or row in SQL Server

I'm trying to understand/learn how to track down the details of a blocked session. So I created the following setup: create table foo (id integer not null primary key, some_data varchar(20)); ...
0
votes
1answer
65 views

READ_COMMITTED_SNAPSHOT locking on Non-Clustered Index

I'm in big trouble with one database instance of SQL Server 2008 R2. I have this structure that I've created it to simulate my real problem: USE [sor] GO ALTER DATABASE [sor] SET ...
11
votes
3answers
621 views

Will increased network latency cause table locks in MS SQL Server?

If I'm making a single call to a SQL Server database over a high-latency network, will table locks occur due to that latency? Say I query table A for some records, and SQL Server has to return that ...
1
vote
1answer
31 views

What happens when you have two events scheduled to run after identical n seconds?

I am running a survey and i am getting lots of data and i need to process that data quickly from within mysql.I am using a mysql events to help me process the hundreds of rows that i have. I have ...
1
vote
1answer
38 views

Is it possible that records are changed while a read query is still executing?

Suppose a read (select) query R is reading some 100 records from a table, 50 records are read so far, could in the meanwhile a write query W change some of the unread records? We didn't take any ...
0
votes
3answers
49 views

postgresql cannot drop a table when autovacuum holds lock on it

Autovacuuming process holds RowExclusiveLock and ShareUpdateExclusiveLock on the table, which I want to drop. I read that autovacuum should be canceled automatically, when submitting DROP TABLE ...
0
votes
0answers
49 views

Unresponsive MySQL database

I've setup a MySQL server on Ubuntu machine. When running a heavy query (the table has over 1M rows), the database becomes unresponsive. Upon connection it returns: Can't connect to MySQL server ...
10
votes
1answer
214 views

Sch-M WAIT blocks Sch-S in SQL Server 2014 but not SQL Server 2008 R2?

We recently migrated our production instances from SQL 2008 R2 to brand new SQL 2014 servers. Here is an interesting scenario that we uncovered with our usage of Service Broker. Consider a database ...
0
votes
1answer
104 views

How to generate replication snapshot without locking tables

When I reinitialize subscription and generate new snapshot it locks all tables and makes the database unusable for a very long time (never waited it out). Is there anyway to do this without locking?
1
vote
1answer
70 views

MySQL consistent nonlocking reads vs. INSERT … SELECT

So, from my reading of the MySQL 5.5 documentation and some experiments I did with colleagues, I understand that the following is the case: With InnoDB tables, a simple SELECT statement (one that ...
1
vote
0answers
53 views

Update on a small table times out

We use Microsoft SQL Server Express Edition with Advanced Services 2008 R2 SP2 We have two locations. Each location has one sql server that serves about 20 client computers. Client computers are ...
1
vote
1answer
78 views

Improve performance on concurrent UPDATEs for a timestamp column in Postgres

I am using a timestamp column called updated_at for cache invalidation. More information on this particular technique here. The queries all have the same format UPDATE "managers" SET "updated_at" = ...
0
votes
1answer
57 views

Risk of disabling page locking

In another question a user asks how to force row level locking. The answer that he receives tell them how to force row level locking by altering the table and the indexes. The user that answered the ...
2
votes
1answer
438 views

What could cause waiting for table metadata lock?

I am using a MySQL 5.6 DB with MyISAM tables under FreeBSD 8.3. The main part of code is in PHP, but the problem arises, when I launch the self-written on C++ chat-server. I use the mysql.h for DB ...
2
votes
1answer
148 views

Implementing application locks within SQL Server (Distributed Locking Pattern)

In my application I have to perform a distributed locking pattern. Because we already have an instance of SQL Server to use, we decided that it would be easiest to implement the locking at SQL layer ...
0
votes
1answer
40 views

Will insert and update query wait while a table is being vacummed?

I want to vacuum a table in postgresql version 9.1 which will take between 10 to 12 minutes, and this needs to be done in real-time, in production use. I just want to know that if any INSERT or ...
5
votes
1answer
314 views

Why are U locks required when using Read Committed Snapshot Isolation

I consider myself a beginner at Sql Server locking. My understanding is that when using RCSI, Sql Server doesn't need to issue S locks because it uses row versioning (in most cases). From ...
1
vote
0answers
45 views

MySQL WRITE LOCK Not Preventing Inserts

This should be a simple task. Can someone tell me why the below code is still inserting rows? lock table wp_postmeta write; insert into wp_postmeta values (null, 1,'test','test'); I've checked my ...
0
votes
1answer
25 views

Upgrade mysql from 5.0 to 5.5 in master-master configuration leads to broken replication

We created two new mysql servers running 5.5. Our old server were 5.0. The two servers are set up with master-master replication - this was the case for the two 5.0 servers which had no issues. We ...
1
vote
1answer
64 views

Do not lock the whole table on select statement

I have a table that is constantly updated by some scripts. The scripts are adding new values and changing tuples but never deleting any tuple. I need to run some queries, that use all rows in the ...
0
votes
1answer
26 views

Is there a way to enumerate all locks that are currently being held via get_lock()?

I'm not seeing a way to list all locks currently being held by get_lock. I see you can check is_free if you know the name and release as well. I don't see an obvious function or infomration_schema ...
4
votes
1answer
185 views

Optimizing concurrent updates in Postgres

I'm running concurrent Postgres queries like this: UPDATE foo SET bar = bar + 1 WHERE baz = 1234 Each query affects the fixed K number of rows, and I can't find a way to enforce the order in which ...
0
votes
1answer
47 views

MySQL locking tables / rows on select statement

I have a web app that does an addition to a field based on a user input. So say for example User-A inputs 7, my web app would first query the contents of the field for the current number in there, ...
1
vote
0answers
61 views

MySQL replication of single DB to same server

I have a DB Server running CentOS 6.4 and MySQL 5.5 from RPMs (not source). I have dozens of databases but one of them is being hit quite hard with large selects which locks out other updates which ...
5
votes
1answer
94 views

SQL Server 2012 Unable to Utilize Defined Amount of Locks

So I've been beating my head against the wall for two days about this and I can't find a solution. I'm running a script to stage a large amount of data. This script requires roughly 35 million locks ...
0
votes
0answers
47 views

Rails with mysql 5.5 update queries take long

app was running Rails and MySQL 5.1 and worked quite well. When we have upgrade to the new server with MySQL 5.5 some update and delete queries started to work quite long (1 sec vs 50ms on old ...
0
votes
0answers
332 views

Why is InnoDB waiting for table metadata lock in simple INSERT/DELETE + SELECT?

I've started using a new MySQL server and moved an InnoDB table there. I'm seeing a weird behavior that when I have a long running INSERT / DELETE, any SELECT from the same table is locked which is ...
0
votes
1answer
90 views

Is UDPLOCK using Row-locks?

I have a UPDATE statement that is using a UPDLOCK inside a DTCTransaction to avoid deadlocks. Now I was wondering. is it locking on a Row-level or on a Table-level?
3
votes
1answer
276 views

high background flush avg mongodb

similar question: High Global Lock % on Mongodb Overview We have a production setup replica set in v2.4.8 mongodb running on five 4-core, 28gb RAM VMs with standard azure datadisk HDDs running on ...
4
votes
1answer
142 views

How to disable or delete an index

I created a non-clustered index on a live server (SQL Server 2008 R2) and it turned out not to give the performance benefits that I was hoping for. So I want to delete the index again. Unfortunately ...
2
votes
1answer
147 views

Does MySQL guarantee atomic upgrade from read lock to write lock?

If a client session is currently holding a read lock (to allow concurrent reads while already preventing writes), and then acquires a write lock on the same table(s), does MySQL guarantee that the ...
8
votes
1answer
633 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
93 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
47 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
105 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
300 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 ...
3
votes
1answer
283 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
527 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
374 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 ...
4
votes
0answers
149 views

User locking tables without privileges [closed]

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
211 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
187 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
2answers
380 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
702 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 ...