Stack Overflow
Stack Overflow

Questions tagged [table-locking]

104 questions
147
votes
4 answers

Create an index on a huge MySQL production table without table locking

I need to create an index on a ~5M rows MySQL table. It is a production table, and I fear a complete block of everything if I run a CREATE INDEX statement... Is there a way to create that index without blocking inserts and selects? Just wondering I…
n0cturnal
  • 1,615
  • 2
  • 11
  • 8
38
votes
5 answers

Why is table-level locking better than row-level locking for large tables?

According to the MySQL manual: For large tables, table locking is often better than row locking, Why is this? I would presume that row-level locking is better because when you lock on a larger table, you're locking more data.
Jason Baker
  • 192,085
  • 135
  • 376
  • 510
25
votes
3 answers

Will laravel database transaction lock table?

I use laravel5.5's database transaction for online payment application. I have a company_account table to record each payment(type, amount, create_at, gross_income). I need to access the last record's gross_income, when a new record created. So I…
LF00
  • 27,015
  • 29
  • 156
  • 295
18
votes
2 answers

MySQL 5.6 - table locks even when ALGORITHM=inplace is used

I'm running the following ALTER command on a MySQL 5.6 database on a large table with 60 million rows: ALTER TABLE `large_table` ADD COLUMN `note` longtext NULL, ALGORITHM=INPLACE, LOCK=NONE; Despite specifying both ALGORITHM=INPLACE and…
jeffreyveon
  • 13,400
  • 18
  • 79
  • 129
10
votes
4 answers

MySQL table locking: holder reads and writes, other sessions only read?

Is it possible to lock a table such that the holder can read and write, and other sessions can only read? The documentation seems to suggestion that a read lock allows everyone to only read, and a write lock allows only the holder to read and write…
John Bachir
  • 22,495
  • 29
  • 154
  • 227
10
votes
3 answers

SQL Server - Merging large tables without locking the data

I have a very large set of data (~3 million records) which needs to be merged with updates and new records on a daily schedule. I have a stored procedure that actually breaks up the record set into 1000 record chunks and uses the MERGE command with…
Josh
  • 16,286
  • 25
  • 113
  • 158
10
votes
1 answer

Rails running multiple delayed_job - lock tables

Hey. I use delayed_job for background processing. I have 8 CPU server, MySQL and I start 7 delayed_job processes RAILS_ENV=production script/delayed_job -n 7 start Q1: I'm wondering is it possible that 2 or more delayed_job processes start…
xpepermint
  • 35,055
  • 30
  • 109
  • 163
9
votes
3 answers

How to explicitly lock a table in Microsoft SQL Server (looking for a hack - uncooperative client)

This was my original question: I am trying to figure out how to enforce EXCLUSIVE table locks in SQL Server. I need to work around uncooperative readers (beyond my control, closed source stuff) which explicitly set their ISOLATION LEVEL to READ…
Peter Rabbitson
  • 93
  • 1
  • 1
  • 4
9
votes
1 answer

Locking a table with a select in Entity Framework

I need to do something like this select * from myTable with (xlock,holdlock) using Entity Framework. Is this possible? I've opened a TransactionScope with the Serializable isolation level but my selects are not locking the tables. I'd like them…
Manuel Felício
  • 91
  • 1
  • 3
7
votes
1 answer

Mysql - Serialization failure: 1213 Deadlock found when try to get lock; try restarting transaction

I have menus, categories and products tables. I am using mysql 5.5, All tables are innoDB and in all cases id is the primary key (int) with autoincrement. menus table id, name, status categories table id, menu_id, name products table id, menu_id,…
dav
  • 8,931
  • 15
  • 76
  • 140
7
votes
2 answers

SELECT ... FOR UPDATE from one table in multiple threads

I need a little help with SELECT FOR UPDATE (resp. LOCK IN SHARE MODE). I have a table with around 400 000 records and I need to run two different processing functions on each row. The table structure is appropriately this: data ( `id`, …
Vyktor
  • 20,559
  • 6
  • 64
  • 96
6
votes
1 answer

Mysql transaction with table locking

I need to use table locking (write) and along with that update a few tables, so I need transactions at the same time, as the locking is not transaction-safe. From mysql documentation I read the…
dav
  • 8,931
  • 15
  • 76
  • 140
6
votes
3 answers

In MS SQL Server 2005, what happens when a temp table is being accessed by different executions of the same SP?

I have a stored procedure that first checks for a temp table (#temp_table), deletes it if it exists, uses the table, then finally drops it when it's done. This SP is called randomly when a user does something to trigger it and it is likely that…
jreed121
  • 2,067
  • 4
  • 34
  • 57
5
votes
2 answers

UPDATE query in MySQL on large table

I have a MySQL database with 21M records and I'm trying to do an update on about 1M records but the query fails with ERROR 1206 (HY000): The total number of locks exceeds the lock table size. Is it possible to update the table without acquiring…
rampr
  • 1,877
  • 4
  • 21
  • 36
5
votes
2 answers

How innodb tables are locked when ON INSERT trigger is processed?

I have two innodb tables: articles id | title | sum_votes ------------------------------ 1 | art 1 | 5 2 | art 2 | 8 3 | art 3 | 35 votes id | article_id | vote ------------------------------ 1 | 1 …
Silver Light
  • 44,202
  • 36
  • 123
  • 164
1
2 3 4 5 6 7