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 (3)

1
vote
1answer
10 views

Process possessing lock crashes

I have two machines, M1 and M2, through which I am connecting to MySQL Database on machine M3 using Perl script (DBI.pm). So, here is the scenario that I am confused about: M1 places a table level ...
3
votes
0answers
44 views

ODBC Connections locking tables in SQL Server

We have an ERP system that is a little less than modern, the backend is currently sitting on a SQL Server 2008 R2 installation. We have through mandate of management (isn't that always the case) the ...
0
votes
0answers
32 views

How to minimize locking / maximize update throughput on small table

I have problem on SQL Server. I have a very, very small table which has a very high update frequency. CREATE TABLE [dbo].[counters]( [id] [int] NOT NULL, [val] [bigint] NOT NULL) Table has ...
1
vote
0answers
13 views

renaming a table with locks ,tried sp_rename but no luck [closed]

I am trying to rename a table but it has many locks in it.Is there a way other than sp_rename ?
4
votes
1answer
112 views

MySQL locking in Duplicate Key Error

From the docs: If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to ...
2
votes
1answer
67 views

How do I place an UpdateLock on a row with an index

Let's assume I have a table like this on a SQL Server 2014: CREATE TABLE Accounts ( Id int IDENTITY(1,1) NOT NULL, OwnerId int NOT NULL, Balance bigint NOT NULL CONSTRAINT PK_Account ...
2
votes
0answers
52 views

Select Query with S lock wins on Insert with IX lock in deadlock

Select Query with S lock wins on Insert with IX lock in deadlock in Sql Server 2008R2. Attaching the deadlock xml file.Please help! http://pastebin.com/nY9EXbS8 This is from the error log by ...
0
votes
0answers
25 views

Schema Locks blocking during Alter Table Add Constraint?

I am dealing with a customer using MS SQL Server with Entity Framework to generate their database upgrades. EF generates a "Migration Script", which in this case is a fairly straightforward collection ...
0
votes
0answers
29 views

How to troubleshoot the “lock request timeout exceeded” error?

Scenario: Usually I received "lock request time-out exceeded" message on client's server. This is not fixed by time and other things. I am getting details of executed SQLs by running query with sys....
0
votes
0answers
77 views

Why does CREATE TRIGGER lock a table in MySql?

What I've done? I tried to execute a CREATE TRIGGER statement on a table with about 4.x million rows. The trigger I tried to create was a INSERT trigger. And it looked like that the whole table was ...
1
vote
2answers
55 views

How to find out why this query is in waiting state?

I have a query that has been several minutes in waiting and is not running at all. In the SQL Developer monitor session view it shows that query has been waiting for 667 seconds? How can I find ...
0
votes
2answers
91 views

MySql timeouts waiting for table lock, no apparent deadlocks

I started having the following error on two different applications that write to the same database and table: Lock wait timeout exceeded; try restarting transaction At the beginning I thought it ...
2
votes
1answer
47 views

Is there a way to determine locks that a given query might apply on SQL objects?

Consider a simple query, as simple as a select statement: select * from Teachers where Name like N'%John%' As soon as I start this query, it finishes. Thus, I get no chance to see sp_who2 to find ...
0
votes
1answer
68 views

improving performance of concurrent inserts on innodb with randomized primary

I was looking at my design for primary keys which is based on my own incrementing BIGINT key generator. It is designed to generate batches of 50 numbers for use on that process, but mostly it ends up ...
0
votes
1answer
91 views

ORA-02049 Timeout: Distributed Transaction Waiting for Lock Solution needed

We are cleaning up the last year's transactions from our primary database. We use public database link to delete the records a day by day from another database. Each days records are around 30-40k, ...
5
votes
1answer
31 views

During rollback, is lock escalation reversed

Generally, SQL Server will take locks during updates. It also supports Lock Escalation: Lock escalation is the process of converting many fine-grain locks into fewer coarse-grain locks, reducing ...
0
votes
1answer
82 views

Locking in stored procedures [closed]

If a stored procedure has multiple statements, will the lock acquired for the first statement be released once its executed or will it hold on till entire stored procedure is complete? I am running a ...
6
votes
1answer
58 views

Does issuing DDL against a view in PostgreSQL take out the same locks as issuing DDL against a table?

I understand that views are implemented using the rule system but I'm unclear if this has any advantages/disadvantages when running transactional DDL against them. Does issuing CREATE OR REPLACE VIEW ....
2
votes
0answers
19 views

In PostgreSQL, can you read from an index during a ALTER INDEX … SET TABLESPACE operation?

I have a PostgreSQL 9.3 server, and there's a large index that I want to move to another tablespace on another disk, because I want to free up space on the original disk. However this is a production ...
0
votes
1answer
36 views

pg_stat_activity shows lots of easy queries running for minutes

After application server start its connection pool fills quickly and application can't work properly. I went through pg_stat_activity and noticed lots of queries running for minutes: But postgres's ...
2
votes
0answers
38 views

How to avoid row locks on both tables joined in an update?

I have little knowledge about MySQL internals, but I'm experiencing that in a query like: UPDATE t1 JOIN t2 ON t1.id = t2.id SET t1.a = x, t1.b = y, [, ...] WHERE [ ... ] rows from both t1 and ...
3
votes
0answers
74 views

MySQL queries waiting for other queries to finish

We have 30-40 different projects in Python and PHP that update, insert and select more than 1 million rows of data in MySQL DB every day. Currently we use InnoDB Engine for our tables. The problem: ...
1
vote
1answer
61 views

MySQL INSERT … SELECT Performance

I know that this query will lock table2: UPDATE table1... SELECT .. FROM table2 How about this query ? INSERT INTO table1... SELECT .. FROM table2 Does this query also create a lock on table2?
0
votes
1answer
67 views

`ON CONFLICT DO UPDATE` causing deadlocks?

I have a project where I'm trying to use the PostgreSQL ON CONFLICT DO UPDATE clause, and I'm having huge numbers of deadlocking issues. My schema is as follows: webarchive=# \d web_pages ...
1
vote
1answer
65 views

Sybase 12 to behave like Oracle and SQL Server 2014 - non blocking transaction read

We utilize an ODBC connection so our code can access various databases - namely Oracle, Sybase 12 and SQL Server 2014. NOTE : We have a table called which we use for locks. This table only ever has ...
3
votes
1answer
50 views

What is the impact of temporarily setting ALLOW_PAGE_LOCKS ON

The MSDN ALTER INDEX page says "An index cannot be reorganized when ALLOW_PAGE_LOCKS is set to OFF." I'm modifying our index reorg/rebuild process to be sensitive to indexes that have ...
2
votes
1answer
76 views

Update with CTE & OUTPUT blocks on concurrent calls

I was having deadlock issues, so I started using this CTE trick that I read somewhere. No more deadlocks. But now each client that I add slows down (blocks?) the sproc. E.g. a 1-2 second update with ...
2
votes
2answers
33 views

MySQL lock on MEMORY storage engine, when does it apply?

I want to use MySQL MEMORY storage engine to handle my PHP session variables (actually I already implemented it but since all my tests are being done by a single user, me, I am unable to notice this). ...
2
votes
2answers
100 views

Percona 5.7 tokudb poor query performance - wrong (non-clustered) index chosen

I have a table with about 8.5m rows in it. The table is tokudb and it has the indexes described below. I'm experiencing dismal performance when trying to run update statements like the following: ...
0
votes
1answer
16 views

Does MariaDB require aliases for subquery table locks?

MySQL suffers from a bug (which they claim is not a bug) that prevents a table lock from working on a sub-query unless you create a table alias for each mention of the table name. See: https://bugs....
1
vote
1answer
120 views

InnoDB MVCC vs Locking

From what I understand you InnoDB supports Row Level Locking MVCC (Multiversion Concurrency Control) Row Level Locking Locking is for when multiple writers are trying to update the same rows....
3
votes
2answers
50 views

SQL Server - What access do I have during a transaction rollback?

As the title says, do I have read/write access to my entire database while it is rolling back a transaction? Or read/write access to only the tables that the transaction did not write to? What is the ...
0
votes
1answer
22 views

Mysql locking - How to acquire a lock in the DB for each userId

Hi I have a J2EE Server(clustered) that executes requests from multiple users. I would like that each request of a given user will be handled like so: { acquireLockForUser(userId); // i.e. insert ...
18
votes
6answers
478 views

Can I rely on reading SQL Server Identity values in order?

TL;DR: The question below boils down to: When inserting a row, is there a window of opportunity between the generation of a new Identity value and the locking of the corresponding row key in the ...
2
votes
1answer
73 views

Hold exclusive lock on a whole DB

As I explained in Set a filegroup to readonly, I wanna set some tables readonly, to have better performance. These tables are only written by an ETL that runs daily, then they are only read. ...
1
vote
2answers
77 views

Select query on an index column with gap locking

I am trying to understand in which cases will a select query make a gap locking on an indexed column select. What I read so far: Select queries use the repeatable read mode in InnoDB and will not ...
1
vote
1answer
40 views

Innodb Isolation levels and locking performance

I am reading a lot about locking and isolation levels in MySQL and InnoDB. I understood the technical differences and basic approach, but I still fail to understand a situation where for example READ ...
2
votes
1answer
26 views

How do I find what keys mysql will use for a query?

I just had a query encounter a deadlock, where the lock it was trying to acquire was for an index. But when I used explain to visualize the query, I saw that a different index was listed under key and ...
0
votes
0answers
33 views

PostgreSQL not running every update on my script

I'm running a dozen update statements on a loop and some updates just won't happen (they return 0 affected rows), If i recall the script it will slowly update the rows, I don't know how to debug this....
1
vote
1answer
11 views

What happens during RCSI while tempdb is being populated?

Our database/application utilizes Read Committed Snapshot Isolation so that readers are not blocked by writers, and writers are not blocked by readers. Of course that means when a row/page/table lock ...
0
votes
1answer
65 views

Log Shipping- Can I alter the schema of a table that is being used in the secondary instance?

We have two SQL Server instances with log shipping set on a database. Can I alter the schema (add columns, remove columns etc.) of the table in the Primary instance/db while the table is being used in ...
0
votes
1answer
46 views

avoid lock and allow two scripts to insert data in one table

I have a database called db1 and I'm filling it with other two different databases, let's call db2 and db3. I connect db2 and db3 to db1 using postgres_fdw and now I'm running scripst like this: ...
0
votes
0answers
27 views

MySQL lock on READ and concurrent execution

I'm building a small application (in Java) and I have arrived to a point where, depending on a field in one my database table the action to be executed has to be completely unique. I have a ...
2
votes
0answers
26 views

Does Disabling Page Level Locking Have an Adverse Effect on Other DBs

I got the following from a vendor about what their software (“IM”) needs: “IM needs its own SQL Server Instance if the DB is installed on a shared SQL server. This is a mandatory prerequisite because ...
1
vote
1answer
106 views

PostgreSQL optimistic reads (equivalent to SQL Server's Read Committed Snapshot)

Does PostgreSQL provide a built-in optimistic concurrency control mechanism such as SQL Server's Read Committed Snapshot Isolation? Readers can read all the rows without blocking, but writes will ...
2
votes
0answers
74 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*/ (filetable_parents_are_directories((...
3
votes
1answer
640 views

How to get SQL insert and/or update to not lock entire table on MS SQL Server

Very much a newbie on DB work, so appreciate your patience with a basic question. I'm running SQL Server 2014 on my local machine, and I have a small table and a basic client application to test ...
1
vote
2answers
119 views

SELECT…FOR UPDATE - Will only an UPDATE release the lock?

I'm using a SELECT ...FOR UPDATE in a Java PreparedStatement. I may or may not need to execute the update depending on the results of the SELECT. If I execute a rs.close(); or pStmt.close(); without ...
10
votes
2answers
604 views

Good reasons to use SELECT… WITH XLOCK?

I am facing some re-occurring deadlocks, one of which is a Keylock and contains a SELECT query with XLOCK hint that becomes the deadlock victim. The other statement is an INSERT into one of the tables ...
1
vote
2answers
88 views

How can I ensure that only one user at a time can call a stored procedure? Can I lock an SP?

I have a procedure that generates reports basis user requests. The request is such that at a time only one user can call the procedure. Is there any way I can handle this using DB2? Like place a lock ...