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)

7
votes
2answers
2k views

Shared Lock issued on IsolationLevel.ReadUncommitted

I read that if I use IsolationLevel.ReadUncommitted, the query should not issue any locks. However, when I tested this, I saw the following lock: Resource_Type: HOBT Request_Mode: S (Shared) ...
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
913 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 ...
1
vote
1answer
61 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
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 ...
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*/ ...
3
votes
1answer
111 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
42 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 ...
9
votes
2answers
173 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 ...
0
votes
1answer
110 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 ...
2
votes
2answers
128 views

Blocked Process Report -two updates causing a long block

I have a .NET application which is getting SQL update timeouts and I believe that some sort of blocking/locking is taking place which is causing this. I have ran a blocking process report on the ...
13
votes
1answer
194 views

Massive INSERTs blocking SELECTs

I have a problem with a massive amount of INSERTs that are blocking my SELECT operations. Schema I have a table like this: CREATE TABLE [InverterData]( [InverterID] [bigint] NOT NULL, ...
0
votes
1answer
338 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
1
vote
2answers
457 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
2answers
45 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 ...
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 ...
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 ...
0
votes
0answers
14 views

ReportServerTempDB - SessionLock table Locked [duplicate]

When a number of SSRS reports are run in a short period of time, I see a number of locks occur on the ReportServerTempDB. The table locked is dbo.SessionLock. The locks sit there until I restart the ...
4
votes
1answer
45 views

Deadlock best practices

We are running Postgres 9.3, and we have occasional deadlock issues. Currently someone notices and will manually kill off the processes. Is there a best practice in dealing with these when they ...
6
votes
1answer
348 views

What happens when we add an index to an existing table with a large amount of data?

I've a table which will contain around ~15 Million records. Now I need to add an index to the table. Adding an index will take some time to update every entry in the table. I'm quite confused ...
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 ...
7
votes
1answer
145 views

Does UPDLOCK guarantee concurrency

I have a table called tblOrderNumber which has 1 row and 1 column. This table stores what the next order number will be for my ecommerce website. It is ABSOLUTELY VITAL that the same order number is ...
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
2answers
77 views

DB randomly locks up when innodb_buffer_pool_size is set high

I have been struggling to work out what could be happening here but for many months, since upgrading to mysql 5.6 we seem to be experiencing random lag spikes in page loads which has been tracked back ...
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
26 views

Putting an order in the way how locks are aquired in MySQL

From MySQL documentation: A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the SQL statement. It does not matter whether ...
3
votes
2answers
7k views

How to find out what caused the database(DB2) in a locking state

I know how to check the DB is in a lock state or not, but my question is how to find out which query caused the lock on table /db. IS there any log file getting created if something unexpected ...
5
votes
1answer
1k 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 ...
1
vote
2answers
3k views

When is it safe to disable InnoDB doublewrite buffering?

MySQL InnoDB allows us to disable doublewrite buffering by setting innodb_doublewrite = 0. Other databases doesn't seem to allow this setting to be tweaked. How could InnoDB still be able to maintain ...
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 ...
2
votes
1answer
26 views

Which Table Will Get Locked With This Query

INSERT INTO stats.cps (time_stamp,entries,cust_id) select start_time, count(*) , c_id from gb.str where start_time between '$DATE10' and '$DATE' group by start_time, src_id order by start_time ASC ...
4
votes
1answer
159 views

“Lock request time out period exceeded” when publishing SqlServer databases in parallel

I am working on a continuous integration environment using SqlServer2014. There I need to publish many SqlServer databases simultaneously on the same instance. Our publish process uses a .sqlproj file ...
1
vote
1answer
98 views

Excessive page locking

There is a query: SELECT entry_id FROM table_name WHERE (path_id = 7 AND value_string = N'00020003-0004-0005-0006-000700080009' ) The table in this query contains some millions of records. Both ...
8
votes
2answers
12k views

Difference Between Row Level and Page Level Locking and Consequences

When attempting to run my Maintenance Plan, I receive the following error: Executing the query "" failed with the following error: "The index "" (partition 1) on table "" cannot be reorganized ...
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 ...
4
votes
2answers
76 views

Best practice regarding concurrency for INSERT into a table with composite primary key?

Say I've got the tables users, teams and a teams_users junction table (team_ID, user_ID / composite PK). If I wanted to add a user to a team, what's the best option when it comes to performance / ...
12
votes
1answer
30k 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)); ...
3
votes
2answers
65 views

How to handle lock information?

Due to analyzing several deadlocks I used sp_blitzIndex to get an overview about locks. There is a high accordance between the tables (and views) affected by the deadlocks and the tables being ...
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 ...
2
votes
2answers
105 views

Is there any reason not to use foreign keys in MySQL?

I've never really used foreign keys before but I am building a project where they could come in handy. I have looked at some other large software such as some forum systems and they don't use foreign ...
0
votes
2answers
134 views

FLUSH TABLES WITH READ LOCK not working?

I use this command to "lock" my database, flush logs , take a LVM snapshot and release it. Testing the recover + binlogs , appear it's not working. (I discovery this when during the recover, applying ...
9
votes
1answer
5k 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
vote
1answer
78 views

Locking and checkout mechanism

Tables: Clients (20+ fields) Id int, Name varchar, [State] varchar, Address varhcar ClientCheckout ClientId CheckedOutAt Datetime null I'm using a separate table for managing the ...
1
vote
1answer
81 views

How do I insert tens of thousands of rows without locking?

I have a server that receives data from thousands of locations all over the world. Periodically, that server connects to my DB server and inserts records, one row at a time in rapid succession. There ...
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 ...
1
vote
1answer
47 views

Using RowLock to enforce Once-And-Only-Once

I have a Web Service that can fire off a chain of events for an order. It does this when it gets a message telling it to (max of about 2 per second). The problem I have is that I have more than one ...
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 ...
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 ...
9
votes
1answer
118 views

Blocked sessions waiting with PAGELATCH_* wait types?

Edited : Why are sessions reporting being blocked but waiting with PAGELATCH_*, and not LCK_M_ related wait types? I previously assumed that SQL server would only report a blocking session in 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 ...