Stack Overflow
Stack Overflow

Questions tagged [table-locking]

104 questions
2
votes
1 answer

postgresql db table locking or row locking multi query execution

I would like to understand how postgreSQL is doing multi query execution for example i have a DB that there are lots of insert queries running for example 20-40 per minute and lots of select queries like 200-300 per minute (simple query by primary…
Emrah Mehmedov
  • 1,492
  • 13
  • 28
2
votes
3 answers

mysql table locked after php crashes

I have a MySQL DB and an innoDB table in it. I have a php page that connects, locks the table, does some updates, then unlocks the table. The PHP page is being served up with apache via wamp. The php page uploads a file to the database. I decided…
jworrin
  • 825
  • 1
  • 8
  • 20
2
votes
1 answer

Possible to select...for update, do e-commerce transaction, and then call a MySQL stored procedure in PHP?

I am currently building a PHP e-commerce website for my client. Its been going smoothly but I've hit a roadblock and was wondering if any MySQL/PHP experts can help me. Basically, the e-commerce site sells a product only once (meaning they only have…
user366343
  • 21
  • 1
2
votes
2 answers

Why would innoDB lock tables

I'm using innoDB on AWS. I keep getting a ton of the following messages in my logs: Waiting for table level lock I thought innoDB only did row-level locks. I don't have any lock tables in my code. Does anyone have any idea why this would happen?
Andrew Sparrow
  • 167
  • 2
  • 2
  • 12
2
votes
1 answer

Python & Postgres: would psycopg2.connect lock the table?

I am running a python script to do ETL(Extract, transform, load) and I put all the psql queries in one transaction. Here's the transaction: conn = psycopg2.connect(...) try: cur = conn.cursor() #q1 cur.execute("create temp table tt (like t…
odieatla
  • 1,049
  • 3
  • 15
  • 35
2
votes
1 answer

InnoDB only insert record if referenced id exists (without FOREIGN KEYS)

Foreign keys may be the best approach for this problem. However, I'm trying to learn about table locking/transactions, and so I'm hoping that we can ignore them for the moment. Let's pretend that I have two tables in an InnoDB database: categories…
EleventyOne
  • 7,300
  • 10
  • 35
  • 40
2
votes
2 answers

Communications link failure to MySQL during long LOCK TABLES

I am encountering some strange behaviour. I have a Java programm that permanently writes data to a MySQL table using prepared statements and batch inserts. If some other process issues a LOCK TABLES t WRITE on the same table, and releases it shortly…
twonkeys
  • 552
  • 6
  • 11
2
votes
1 answer

SQL Server 2008R2: 'DELETE FROM' hangs forever, but 'SELECT' works fine

I'm not a DBA by any means, so I'm lost on this one. I have a table with 152,000 rows in it. I'm trying to execute this statement: DELETE FROM FILES WHERE Asset_Id = 340 And it hangs forever in the query window -- just says "Executing query...".…
Matt
  • 23,363
  • 39
  • 111
  • 152
2
votes
1 answer

How SQL Server handles UPDATE transactions?

We have a table which is used to Generate Unique Numeric keys. These keys are then used as a PrimaryKey in other tables. Table structure is like this: TableName VARCHAR CurrentKey INT So we have data in this table like TableName …
Yaqub Ahmad
  • 27,569
  • 23
  • 102
  • 149
1
vote
1 answer

How to check if table is available to drop & create a trigger?

I've a long script in sql server 2000 (17k lines and growing). Problem is that about the half, several triggers must be dropped & recreated as part of remote installation procedure. Sometimes, one or two tables are in use and installation stopped…
OldyGuy
  • 21
  • 3
1
vote
1 answer

When is a table locked for insert when a stored procedure is called

I have a table that is used for reporting purposes and data is inserted each time a user runs a report from the web. The insert can vary from a single row to several thousand depending on the parameters of the report. The select statement used for…
kuda
  • 68
  • 4
1
vote
1 answer

Difference between a row lock and a table lock in Oracle database

What is the difference between row locking and table locking in Oracle Database? Would a for loop with the UPDATE statement trigger a table lock?
RU Ahmed
  • 558
  • 4
  • 23
1
vote
1 answer

Create index in huge MariaDB production database without table locking

I have a table with 202M records where I need to add a few indexes and I can't find it anywhere (or maybe I don't understand the lingo) if that is possible to do, without locking, in MariaDB 10.3. I found this post where I can see that that is…
Matej Žerovnik
  • 61
  • 2
  • 5
1
vote
1 answer

How to prevent table locking with multiple users writing and reading from table at the same time?

I have a program I wrote for a group that is constantly writing, reading and deleting per session per user to our SQL server. I do not need to worry about what is being written or deleted as all the data being written/deleted by an individual will…
Mike - SMT
  • 14,784
  • 4
  • 35
  • 79
1
vote
1 answer

PDO General error: 2014 Cannot execute queries while other unbuffered queries are active when trying to LOCK TABLES

I am updating someone's old old code from using mysql() to using PDO. In one place they have some LOCK TABLES commands to prevent two users from accessing the same data at the same time. When running LOCK TABLES, PDO throws "General Error: 2014…
Ben Holness
  • 2,457
  • 3
  • 28
  • 49
1 2
3
4 5 6 7