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
24 views

Refreshing a table using CREATE TABLE, DROP and Rename

A read-only database (database1) is updated via log shipping every minute, then a procedure queries multiple tables in it and refreshes a large table on database2. For this process I run an ...
2
votes
1answer
2k views

Insert into table from a query to a linked database hangs

I have a schema with some database links to other schemas that we use to pull data from. We pull the data into a staging table, massage it, and then do some merges into our schema/database. The data ...
1
vote
1answer
32 views

Help understanding InnoDB Deadlock Detection

I have a deadlock occurring when executing an Update and a Select in seperate threads but I'm not sure what can be done about it. Previously my understanding was that this could happen in parallel but ...
1
vote
1answer
248 views

Why are SELECT queries in sleeping status holding open transactions

Using sp_WhoIsActive, I see that I have a lot of processes in a "sleeping" status, several for hours. I am not sure of the cause and am still troubleshooting (timeout? bad code with uncommitted ...
1
vote
1answer
466 views

MySQL: SELECT GET_LOCK slow/timeout

I am using "Zebra Session" as a replacement for PHP's built-in session management. Zebra Session stores sessions in a MySQL database, and uses "session locking" in order to ensure that data is ...
0
votes
1answer
36 views

Generate incrementing IDs without sequences

Suppose there's a multi-tenant application where users can create some kind of documents with basic structure like CREATE TABLE users (id SERIAL PRIMARY KEY, email TEXT); CREATE TABLE documents ( ...
0
votes
1answer
39 views

Global Read Lock for Snapshot of MySQL Data

I have a database that is entirely comprised of MyISAM tables(yes, I did not create it). I need to create a slave and start replication so we could point read-only queries there. Here is my idea: ...
0
votes
1answer
63 views

MySql Lock Tables Blocking Logic

The MySQL documentation (5.5) for Lock tables states: Let's say that I'm trying to lock table1 and table2 in a single query, but table2 is already locked. Will MySQL hold onto a lock on table1 and ...
0
votes
1answer
916 views

How to update a (very) large table without locking in MySQL

I have a large table (58+ million records) which represents a relation between two records (player and target) in a second table. Unfortunately, whoever designed our schema didn't think things ...
0
votes
1answer
126 views

GET_LOCK([name], 0) behaviour and multiple GET_LOCK() order

I read from the documentation that the behavior of distributing a lock requested by many connections after RELEASE_LOCK is called from the connection holding the lock is undefined. I was curious: say ...
0
votes
1answer
339 views

Sybase ASE lock table to disallow users insert values

I need to disallow users to insert values in some table, for example for a 5 mins. Seems I should use at isolation clause but can't figure out how to use it. Thanks
0
votes
1answer
424 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 ...
0
votes
1answer
2k 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?
3
votes
0answers
316 views

Slow transactions, many locks

At high load conditions the server app stops responding to client because begin/commit statements are executing very slowly (some take 15 seconds). The queries are simple - insert, update two columns ...
2
votes
0answers
47 views

SQL Server Check Constraint Lock Order

I'm having problems with SQL Server 2012 filetable. Filetable defines an check constraint on a path_locator field with definition as: /*ParentsAreDirectories*/ ...
2
votes
0answers
88 views

SELECT blocking SELECT?

I have huge outputs from pg_stat_activity and the result from the second "combination of blocked and blocking activity" report from https://wiki.postgresql.org/wiki/Lock_Monitoring (2nd version), with ...
2
votes
0answers
106 views

innodb update with limit locks all matching (ignores limit)

For a query similar to this: UPDATE myinnotable1 SET value=5,updated=now(),status=1 WHERE status>10 AND message="match msg" LIMIT 1; I found that innodb engine locks ALL rows matching criteria ...
2
votes
0answers
53 views

Why does SQL Server 2005 only use an IS lock in this scenario?

I am attempting to examine the locking behind different queries but with the same output to avoid deadlocking situations. I have the following code: SELECT MIN(ID), TextData FROM PKKEYTESTER ...
2
votes
0answers
59 views

SQL Server 2008 R2 - Should I be worried at this statistics regarding indices causing locks?

We have some mild table locking issue during our peak hours so I figured the following query query taken from this site would be a good start. And boy, does it show me some seriously concerning ...
2
votes
0answers
800 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 ...
2
votes
0answers
4k views

Oracle Select Query Over Database Link Suddenly Hangs Endlessly

I have a query that's been in use for years and today it started hanging endlessly. I took the query apart and found the piece causing it to hang is a simple join on a primary key that should run like ...
1
vote
0answers
27 views

How to make a PostgreSQL trigger thread-safe

I want to make my PostgreSQL trigger function do something like incrementing counters etc (something complex with multiple queries etc) thread-safely (so the same function triggered concurrently won't ...
1
vote
0answers
26 views

2 phase locking with added increment mode

I have this assignment question where increment mode is added in addition to read and write lock modes in two phase locking.But I cant seem to figure out whether each lock mode has its own seperate ...
1
vote
0answers
56 views

How to avoid deadlocks when running queries on a remote server

I need to pull data from many different systems, all on remote, LIVE SQL Servers into one place for a one-off piece of work that will last a few weeks. This has to be run multiple times during the ...
1
vote
0answers
45 views

Long Transaction On File Upload Locks Strategies

I need to upload files to some SFTP or FTP servers (using libcurl) and register them in database. For this I have several tables -- locations to store files - some SFTP and FTP servers CREATE TABLE ...
1
vote
0answers
40 views

Row locks on selects

select t.*, row_number() over ( partition by t.Entity_Identifier order by t.Message_Sequence ) as seqnum from Process_Activity t (ROWLOCK) where t.Process_Id = Is it possible for ...
1
vote
0answers
68 views

Average Waiter Count increasing

I am in a nightmare. We have deployed an application to a UAT environment, which has only 7 users. Since then, we have noticed in the logs, a lot of deadlocks, and processes being victims and things. ...
1
vote
0answers
1k views

Many transactions are getting stuck in the “cleaning up” state - what could cause that?

I'm trying to track down a locking issue, and found via SHOW PROCESSLIST that when the queries in question are waiting for locks and timing out, there are no other queries active in the process list. ...
1
vote
0answers
71 views

Handling this multi-threading scenario in the database?

I am working on sample bus ticket booking application,in I am facing these two multi-threading scenarios. Scenario 1 1: Let's say there are two users, they both pick a particular seat and submit ...
1
vote
0answers
31 views

MySQL - Transactions and causality

I'm new to MySQL's LOCK and TRANSACTION and have a question about different isolation levels of a transaction: Given two transactions executing the same code (auto-commit is false): (1) get data of ...
1
vote
0answers
214 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 ...
1
vote
0answers
140 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
0answers
92 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 ...
1
vote
0answers
166 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 ...
1
vote
0answers
780 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
0answers
234 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 ...
1
vote
0answers
397 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; ...
0
votes
0answers
75 views

Long running query with index seek

I was tuning this query, the execution plan looks fine for me but based on the statistics the Lock waits time is very high .What should i do to fix this problem? (@P0 bigint) SELECT mgmtemails0_.Id ...
0
votes
0answers
178 views

MySQL queries waiting for table lock

Today I had some weird behaviour on my MySQL server that I couldn't explain. Hopefully someone can give me some debugging hints. The initial problem was that some parts of our webshop didn't seem to ...
0
votes
0answers
43 views

Trigger copies history table: Will removing index lock options speed up the process?

I have a trigger applied to a table to write inserts and updates to a history table. That history table can grow pretty fast, and we want to see, if we can speed up insert operation a bit. The values ...
0
votes
0answers
188 views

Postgresql: Update or insert stuck

I'm running update-or-insert query on a table of approximatelly 3mln rows, 4 columns. Query is part of every day process, and for same days, the query stucks forever. It's just blocked. It's ...
0
votes
0answers
82 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] = ...
0
votes
0answers
192 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 ...
0
votes
0answers
1k 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
0answers
84 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 ...
0
votes
0answers
84 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
0answers
159 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, ...
0
votes
0answers
150 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 ...
0
votes
0answers
478 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 ...
0
votes
0answers
124 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 ...