Stack Overflow
Stack Overflow

Questions tagged [table-locking]

104 questions
1
vote
1 answer

Table locking in a plpgsql function

Let's say I've written plpgsql function that does the following: CREATE OR REPLACE FUNCTION foobar (_foo_data_id bigint) RETURNS bigint AS $$ BEGIN DROP TABLE IF EXISTS tmp_foobar; CREATE TEMP TABLE tmp_foobar AS SELECT * FROM…
Elliot B.
  • 17,060
  • 10
  • 80
  • 101
0
votes
2 answers

Mysql Insert consecutive rows per user

I have a table called 'picks' and When a user logs in and starts making picks, it initially inserts 64 rows into the "picks" table for that user (has the userid as a column). The rows are inserted using a loop in php so there are 64 inserts using…
EricP
  • 1,459
  • 6
  • 33
  • 55
0
votes
1 answer

Doing FOR UPDATE and JOIN when using ROW SHARE lock?

According to PostgreSQL documentation, the ROW SHARE lock works like this: The SELECT command acquires a lock of this mode on all tables on which one of the FOR UPDATE FOR NO KEY UPDATE, FOR SHARE, or FOR KEY SHARE options is specified... So if I…
Smooth Researcher
  • 17
  • 3
0
votes
0 answers

Lock escalation on update statement that uses a primary key

I inherited a database that seems to have a LOT of lock escalation going on. I ran a trace and it's full of escalation events. Some of it is caused by things like BULK INSERTS. I can understand how that causes an issue with locking. There are some…
Mulciber Coder
  • 127
  • 9
0
votes
0 answers

Does table locking work in MariaDB master-master Replication?

I'm considering MariaDB master-master configuration for a webapp database. My application has some index locking, something like: SELECT COUNT(*) FROM person WHERE event=? FOR UPDATE; The transaction checks the subscribers amount to an event and…
Tobia
  • 9,165
  • 28
  • 114
  • 219
0
votes
0 answers

due to foreign key relations, oracle locks a table which will not be affected by the delete statement (on delete set null / on delete cascade)

We observed that while issuing a delete statement, oracle is locking tables which are not impacted due to the specific delete statement. Consider 4 tables as below. create table TABLE_A (id number(14,0) not null primary key, code…
GGC
  • 1
  • 2
0
votes
0 answers

H2: counting (with table lock)

I need to implement a counter by prefix and get the current value. Therefore I created a table UPLOAD_ID: CREATE TABLE UPLOAD_ID ( COUNTER INT NOT NULL, UPLOAD_PREFIX VARCHAR(60) PRIMARY KEY ); Using H2 and a Spring nativeQuery: …
Stefan K.
  • 7,701
  • 6
  • 52
  • 64
0
votes
0 answers

SQL Server table data deletion while insertion is in progress in another session

I have a table (TableA). There is a probability that more than one process may try to delete data from TableA and at the same time more than one process may try to insert data into same table i.e. TableA. Will this result in table lock? If yes, how…
Mahendra Dhaker
  • 43
  • 8
0
votes
0 answers

Partition tables getting locked due to a process which supports XA protocol

Upon starting our system server, the tables which are having partitions upon them(Range paritioning in postgres) are getting locked(in a way that SELECT query is still fetching output from them) but there are some ALTER statements which are getting…
Rohit K
  • 1
  • 1
0
votes
0 answers

Does stored procedure with select statement called from another stored procedure with transaction lock the table?

I have a stored procedure calling another stored procedure. The outer stored procedure got tran while the inner stored procedure has no tran just select statement. Does the table in the inner tran gets locked? The stored procedure s looks like…
ESSSTTT
  • 1
0
votes
1 answer

How to remove a lock from a table in a Synapse Dedicated Pool?

There has been a lock on 2 different tables since last night. What is the query to remove a lock from a table in a Synapse Dedicated Pool? Thanks in advance
v-jh
  • 66
  • 3
0
votes
1 answer

Preventing/Reducing deadlocks within a django application

I've been having a few issues recently working with a large MySQL database within django, MySQL seems to be having issues with deadlocks. I keep getting the error: (1213, 'Deadlock found when trying to get lock; try restarting transaction') Usually…
Daniel Askwith
  • 1
0
votes
0 answers

unlocking a table that someone else locked

Let's say a coworker does LOCK TABLES table_name and then leaves for the day without unlocking the table. Maybe their computer is locked so you can't just kill the process on their computer. Maybe you could unplug their computer but that might not…
neubert
  • 15,947
  • 24
  • 120
  • 212
0
votes
1 answer

Temporal Table and Table locks

I am thinking of using a temporal table solution for one of our application, and want to read historic data while new data is being written. Can this cause table locks? I have millions of rows to query/maybe even insert/update/delete.
Emil
  • 281
  • 1
  • 2
  • 11
0
votes
1 answer

Does InnoDB block on a SELECT that joins with a locked table?

Suppose, I run this query, which locks the entire table in InnoDB until it finishes: Update UserDetails set balance = 0 Then I run this query: Select * from User inner join UserDetails on (User.id = UserDetails.userid) We know that selecting from…
lbj99
  • 3
  • 1
  • 2
1 2 3 4
5
6 7