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)

6
votes
1answer
373 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 ...
6
votes
1answer
184 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
50 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 ...
0
votes
1answer
40 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
27 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 ...
1
vote
1answer
46 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
36 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 ...
1
vote
2answers
308 views

How to force SQL server to use row locking for specific update/delete statements

I read this great response to a similar question How to force the use of row locks but I was looking for a per update/delete statement instead of permanently putting the rowlock restriction on an ...
4
votes
1answer
268 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
110 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 ...
1
vote
2answers
131 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 ...
4
votes
2answers
136 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 / ...
2
votes
1answer
25 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 "...
1
vote
0answers
36 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
1answer
89 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 ...
3
votes
2answers
119 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 ...
0
votes
0answers
87 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
214 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 ...
1
vote
1answer
56 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 ...
0
votes
0answers
88 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
144 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 ...
0
votes
2answers
223 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
144 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 blocking sessions in the ...
1
vote
0answers
50 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 ...
0
votes
1answer
59 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: ...
2
votes
2answers
143 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 ...
2
votes
1answer
255 views

what happens when I try to insert data into mysql while table is locked?

Simple question but somehow impossible to find a clear answer: What happens when I try to insert data into mysql while the table is locked? 1) INSERT query fails 2) INSERT query waits until lock is ...
0
votes
2answers
141 views

How to manage concurrencies without locking tables in MySQL?

In our php app, we have a table that records orders recieved at the reception. Since we can have up to 7 agents/active accounts, how is it possible to manage the concurrencies in the orders table ...
1
vote
2answers
146 views

Oracle UPDATE statements lock race condition

Say I have a table MY_TABLE without optimistic lock column. I have the following update statement that will be executed with org.hibernate.SQLQuery: UPDATE MY_TABLE SET NAME = ? WHERE SOME_NUMBER = ? ...
5
votes
1answer
1k views

Why does UPDLOCK cause SELECTs to hang (lock)?

I've got a select in SQL SERVER that locks the whole table. Here's the setup script (make sure you don't overwrite anything) USE [master] GO IF EXISTS(SELECT 1 FROM sys.databases d WHERE d.name = '...
3
votes
2answers
596 views

Using sp_getapplock to implement a queue. Is it correct? Is there a better way?

I've been reading a series of posts by Paul White about SQL Server Isolation Levels and came across a phrase: To emphasise the point, pseudo-constraints written in T-SQL have to perform ...
1
vote
2answers
86 views

Can I make postgres more verbosely tell me what it's doing?

I'm trying to use ALTER TABLE on a live db (not in production, just poking around at a dev box and trying to learn some things) Here is the command I'm trying: psql -h $IP -p 5432 -U $DBUSER -d $...
2
votes
1answer
70 views

Deleting large amounts of cascading records with minimal locking

I'm looking for a strategy to purge large amounts of logically deleted data from a MySQL database. The deleted records are in a relatively small table, but are referred to with many ON DELETE CASACDE ...
3
votes
1answer
180 views

Can a Table Lock eliminate a deadlock or make it worse Oracle

We have a situation in which an Oracle Database is having deadlocks issues. I said that perhaps we should lock into some table locks, and a guys response to me was "Unless I am misunderstanding ...
1
vote
2answers
329 views

FLUSH TABLE : can't execute the given command because you have active locked tables mysql

If I execute these queries one by one, all fail except the first: Query1 : flush table tablename1 with read lock; Works Query2 : flush table tablename2 with read lock; Error : can't ...
5
votes
1answer
335 views

Exactly what error is returned by PostgreSQL `FOR UPDATE NOWAIT` when a selected row cannot be locked immediately?

The PostgreSQL 9.4 documentation states that adding the NOWAIT option to a SELECT FOR UPDATE means an error is generated when a row cannot be locked: To prevent the operation from waiting for ...
1
vote
2answers
87 views

Spotlight for SQL Server LockType explanation required

I have just started using Spotlight as I'm a software developer, but I am forced to identify database issues causing blocking (Spotlight shows me AWAITING_COMMAND at the points and all other processes ...
0
votes
0answers
65 views

MYSQL- concurrency issue

I use JDBC to connect to MySQL. order_items table order_item_id order_id quantity unit_price shipping_price business_id workflow_id delivery_id item_id Orders table billing_address_id ...
0
votes
0answers
61 views

PostgreSQL 9.3: Minimize locking when updating a large table with primary key

I am building a DB solution to handle a fast stream of online events. Each event UPSERTs a row in a location_update table, using its car_id primary key. Since there are very few new car_ids, the ...
1
vote
0answers
41 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 ...
3
votes
0answers
595 views

Which is the right way to fix “Error: 1205 Lock wait timeout exceeded; try restarting transaction”

I have a PHP script that sync a local DB against external datasource. Each time the script runs I perform some triggers on the DB where INSERT or UPDATE happen. These are the the triggers for that ...
1
vote
0answers
111 views

Is it a good idea to have too many named locks?

I am using MySQL as a data store and a Java App on the top. There are many state changes that happen, and I am seeing some concurrency bugs in there. I wanted to know if I can use named locks (...
6
votes
1answer
273 views

Locking in Postgres for UPDATE / INSERT combination

I have two tables. One is a log table; another contains, essentially, coupon codes that can only be used once. The user needs to be able to redeem a coupon, which will insert a row into the log table ...
0
votes
1answer
85 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 ...
2
votes
2answers
268 views

Locking issues under transaction on Postgres

I recently got a "Out of shared memory - You might need to increase max_locks_per_transaction" error. This is a long running process where I import a bunch of CSV files after proper processing. I ...
1
vote
1answer
416 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
246 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 ...
1
vote
1answer
122 views

INSERT [TableName] SELECT * FROM [TableName]

I just ran across a query: INSERT [TableName] SELECT * FROM [TableName] Note that it is the same table. This query causes serious blocking on one of my test databases (I wanted to drop it but ...
2
votes
0answers
153 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 ...