MYSQL :: Table Locking

About Table Locking
One thread insert data to a specific table continuously using 'LOAD DATA INFILE..' or 'INSERT INTO ...' query. Actually, this table maintains only for one hour's data, so the delete query is performed before every insert query.
At the same time, another thread issues select query on that table periodically.

What I want to know is, whether it is free from the table corruption or not. Table level locking is performed in MySQL server? Or, I have to do explicitly?

Table Locking
I just have a question about the table locking in 3.23.56. If I am inserting a
row into a table that is MyISAM type, is the entire table locked? And, if so,
what does this mean for concurrent selects?

What Are The BIG Reasons For Locking The TABLE?
What are the BIG reasons for locking the table? :)
Why do we need to lock the table?

Table Locking When Using Left Join
I need someone to clarify something for me.
If I have a SELECT query on tableA that uses a left join to TableB, does TableB get locked for the duration of the entire select query? Suppose that the SELECT on tableA is slow, but the join to tableB is really fast, does tableB experience a lock for the duration of the query on tableA?

Is it better to just run a query on tableA, and after run fast indexed queries on tableB?

Table Locking & Multiple Jobs
I'm using mySQL 4.1 (will be upgrading to 5 soon), and I have a problem with table locking.

My table is MyISAM.

I have a job running every x seconds that loops round the following process 10 times.
Each time it loops round it locks the table, reads into memory the oldest record in the table, deletes this oldest record and then unlocks the table and does some further processing of the data in memory.

Problem is that I need to have two or more of these jobs running at once, and when this happens, occasionally each of the two jobs will process the same record in the table ... how can this be????? I'm doing a table lock which I thought would prevent two seperate jobs accessing the same record?!?!

Table Locking When Updating From DataSet?
I have a bunch of code that updates tables from a VB.Net DataSets.

The original code updated Access2000 tables, I migrated the MDBs to MySql and have converted the data adaptors and command builders to MySqlClient statements.

Does anyone have any code examples to to the table locking using DataSets???

Locking A Table/Record/Field
Is there a way to actually insert data into the table and then lock it, disallowing all write attempts to the table/record/field?

Locking Of Table Through VB For Write/Update
Can anybody help me out by giving some hint on how to lock MySQL tables from VB for doing write or update?

Denormalized Tally, Table Locking Issues?
Say I have a `news` table (MyISAM). People can vote an article up/down. User votes are recorded in `news_votes`. To alleviate the need for performing expensive AGGREGATE functions with a join between the two, I was thinking of keeping a running tally in the news table (`tally`, mediumint).

Assume a few hundred news stories per day each with hundreds of votes per day. I'm afraid that the table-level locking of MyISAM on the news table will result in performance degradation due to the fact that SELECTS can't run concurrently with UPDATES.

What are you thoughts about this method of slight denormalization? I've seen it on lower concurrency system archetecture like Wordpress blogs.

Locking
I'm wondering if there is a way to lock the database (or do something
else) to execute more than one query with assurance that they are
executed exactly one after another (figure if there are several threads
and I give it a couple of queries they may mix). Thereason is that
suppose we have two tables, one with an auto_increment key, and the
other that references that key:

table order (id ... primary key auto_increment ...
table transaction(order_id, ... foreign key refences order(id) ...

Now, I do something like:

insert into order ...
id = last_insert_id()
insert into transaction(id,0,0,...

Help In Locking Tables
I am trying to lock a table which was created in the MyISAM engine.
I use the following query:

LOCK TABLES A WRITE;
That works fine
At the end of my executing I invoke:

UNLOCK TABLES;
That also works fine

The problem is that in between those 2 queries, I am invoking another query on table B (different table) and I get the following error:

Table 'B' was not locked with LOCK TABLES. Error number 1100
Can anyone explain why I need to lock table B as well?
There are a few tables that I am accessing between the lock and unlock queries. Do I need to lock all of them?

Do I Need To Implement Locking
I have an app I have written in Delphi which will eventually have up to 30 concurrent users. Do I need to implement my own read and write locks, or does MySQL take care of this? What about commiting queries?

Locking Tables
I have a rather simple Select query that has a LEFT JOIN between two tables. I want to take the results of this query and store it in a third table.

The two joined tables are called `new data` and `fast`. The third table where the results are to be stored is called `fall outs`.

When I try this as an INSERT INTO query, I get the error message that I've exceeded the number of locks (both the `fast` table and `new data` table have 7 million rows each).

So I've used this code thanks to the help file:

SET @@AUTOCOMMIT=0;
LOCK TABLES `fall outs` WRITE, `new data` READ;
INSERT INTO `fall outs`
SELECT (insert my select statement here)
WHERE (insert my where statement here);
UNLOCK TABLES;

When I do this, I get an error message that the table `fast` was not locked, and thus I get 0 results.

How do I make `fall outs` WRITE and have both `fast` and `new data` as READ? I can't seem to find any tutorials that do this.

Locking Records?
I am using MSAccess 2000 to run against MySQL 4.0.12. What I need to have happen is when a user pulls up a record and begins updating it, another user on another workstation cannot access that same record.

Where do I begin with this? Is it a simple setting in the .ini file or using MySQLAdmin?

Locking Issue
I am running MySQL 4.0.20 on redhat 9.

But i frequently face a problem of that the table is being lock. i see from the process list that i am the only one trying to update the record and the rest of the connection do nothing. Below is the log file i get when i try to show the innodb status when the table is lock. Code:

Optimistic Locking?
Can anyone tell me if MySQL uses optimistic or pessimistic locking with
InnoDB tables? And can this be modified in any way?

When I use the search function on the mysql site, it only gives me
search results for MaxDB...

Locking Tables
I am trying to install a web based game and it says my tables need to be locked. So I created a new database and ran the installer file and everything installed fine except the test for lock tables it failed so the install stopped. This is run in php. I can't find anything in my database on how to lock tables.

I am running MySQL 4.0.16 on a phpMyAdmin 2.7.0-pl2

Row Locking Issue
I am running a MySQL server for a department-critical function at my company. The basic setup is a MyISAM-based MySQL 5.1 installation, with one specific table to hold the work that is to be distributed among the employees. Other tables are for user settings (based out of the client, written in C++ with a wxWindows base, using libmysql as the medium for query submission and retrieval).

Overall, the most troublesome problem yet encountered is that each query -- one to locate a workable row, the next to pull the information from that row, and the last to mark that row as touched and no longer available to others (three in total, all occurring within one connected session) -- has a very undesireable tendency to collide during the initial "locate query." When this occurs, one employee ends up with their ID in the "worked by" field, but both clients have the same information given to them.

I've read up on InnoDB, and explored some of the locking mechanisms, but even when the DB type is migrated to InnoDB, the server runs inexcusably slow (sometimes taking up to nine seconds to return a query). It's row locking features seemed to accomplish very little in the way of preventing a read from occurring on the row, though UPDATE and DELETE statements where blocked.

In the end, I am simply wondering if there is a way to force the DB to block read access to a row that has already been read by another client, and move on to the next record in the DB, while being invisible to the user (and preferrably the client application). The only solution I can derive from all of this is to actually mark the first returned work record as completed, and then (somehow) locate it after it's been marked, preventing further reads from the row based solely on the client's rules of retrieval.

MyISAM Locking
I'm trying to learn how to facilitate multi-user access to a MyISAM database table. My understanding is that if I only allow SELECT and INSERT operations to occur, I don't have to worry about locking. But if I allow DELETE and UPDATE operations to occur, I do have to worry about locking. Is this correct?

File Locking
This is a perl cgi app uses one table which has one record.
For every time the app runs it needs to retrieve data from the record then update.
Every client should have an updated set of values from the one record or
no two or more clients should have the same values.

Record Locking
when a user selects a record from a table, the record field, "locked" is updated to "locked" so that no one else can edit that record until it is unlocked again. Somehow I need to unlock the record not only when the user finishes editing the record, but also in the event of the user closing the browser, or just navigating away from the page
is there any possible way to do that?

Innodb Locking
I have started using innodb for an application I want to build.
The application inserts customers, along with other data in the database.
My question:
Is there a way to lock a particicular record so that only one person can access it.
I mean, when an employee of my company opens a record either to see it or update it, how can I be sure that at the same time another employee does not edit or even delete the same record.
I tried select .. lock in share mode and select .. for update but they did not do what I expected, I could still access the same entry from multiple clients.

Row Locking In A Transaction
I've read that any selct statement issued in a Transaction will lock the resulting rows in the record set until the transaction is either COMMIT or ROLLBACK is issued. well what happens in the following situation.... A user workin in a content management system begins a transaction and within that transaction a SELECT statement is used on a table to populate a drop down menu with options. The drop down options are also used as menu options elsewhere on the site. If that statement selects all the rows in that table, does that mean that every row in that table will be unavailable to any other request until this user is done the transaction?

If this is the case, is there a way to NOT lock rows with certain statements in the middle of a transaction without ending the transaction?

Locking Tables
I'm using mysqldump (the version is whatever one is distributed with mysql 4.1.7) on windows 2003.
here's my syntax:

mysqldump --flush-logs --delete-master-logs --all-databases > path to backup

It does create the backup as expected, however I'm unable to do inserts while the backup is running.

I tried adding --opt (I thing its on by default anyways) and also tried adding --skip-lock-tables, but it still appears to lock the table anyways.
The inserts are on MyISAM tables, the documentation leads me to believe this is possible - am I wrong about that, or is there a syntax problem I'm missing?

Locking And Deleting
I have myisam tables. I read lock and write lock tables for various selects and inserts. Should I also lock tables when deleting? If so, is it a write lock or a read lock

Locking Tables
I am new to MySQL and databases in general. I am in the process of building a shopping cart application and came across one example that explicitly locked any table being used for reading or writing. I was just wondering if manually locking the tables is necessary or if MySql handles that behind the scenes

Triggers/Locking/Constraints
I have two issues. Firstly, I have a table of users and a table of permissions. Is there a way to use a trigger or some kind of locking in InnoDB tables such that the superuser and its permissions cannot be deleted?

I also would like to know if it's possible to constrain insertions into a certain table such that one and only one of two fields must be null. In this case, my table of permissions has two foreign keys for each record: a user ID and a group ID. If the permission applies to a user, the group ID should be null, and vice versa.

For security reasons, I would greatly prefer if this logic could be put in the database so that I can simply revoke ALTER privileges from the DB user so nothing can be done from PHP.

Locking A Record From Being Updated?
I am building a system where more than one person has access to a table, and everyone has the ability to update a record.

How do I prevent a second person being able to update that record if someone else has that record open for editing already?

How Can I Export Via Mysqldump Without Locking Db?
I really need to find a solution to this - I've tried the command line way, even with the --skip-add-locks option it gives:
mysqldump: Got error: 1044: Access denied for user 'xxx'@'%' to database 'xxx' when using LOCK TABLES
The isp say they won't grant the locks priviledge to me, so how can I backup my db? (not phpmyadmin, needs to be automated at some point).

How Does Record Locking Work?
I am having record locking issues - I need to be able to do it! I have spent a fair amount of time reading the available documentation and posts (at various sites), but not gotten any clear answers. So I have several questions about this whole subject:

1. Is there anywhere in the MySQL documentation that covers all aspects of record locking, including by engine?

2. Does "SELECT...LOCK IN SHARE MODE" or "SELECT...FOR UPDATE" lock records with any engines OTHER than InnoDB?

3. The way Sarah from AB MYSQL describes "SELECT...LOCK IN SHARE MODE" and "SELECT...FOR UPDATE" in another "record locking" post, it is NOT clear if the "lock" stops a "SELECT" from finding the record. So, does the lock simply stop the UPDATE or LOCK of the record by another process, or does it also stop the READ (via SELECT or anything else) from other processes? And, is that different for different engines?

4. From the documentation I've read, the assumption is that the UPDATE is "atomic", i.e., that no concurrent access by another process can occur between the testing of and the setting of the value field(s). Is that how it works? And, does it work that way with all engines?

Innodb Record Locking
I am writing an order entry app and would like to implement multi user access as follows using Innodb:

1) The first person into an order (a record in tblOrder) gets read-write access.
2) Successive people get read-only access until first person gets out of the order and are warned about this before they try to open the order.

I've tried the following:

SELECT *
FROM tblOrder
WHERE ID = 1
LOCK IN SHARE MODE;

Locking: Multitple Users
I have written a delphi application that is accessing a mySQL database on a linux server. The application is running really well, however a couple more users will soon be using my application from their computers also.

My concern is that data is going to be corrupted if people are updating the records are the same time.

I know mySQL supports table locking, but what about issuing read and write locks? What would be the best approach to this potential problem?

Mysqldump Locking Issue
ok I want to do a dump daily of my database, this works fine, however
the problem I have is that while the dump is executing itself, I cant
access my website cause the thing locks tables I think.

Is there a way to dump and allow queries to happen at the same time
without them being put on hold?

Locking Tables Or Rows
I have a database (MyISAM) that houses our customers' usernames, passwords, and other various information. Since we will have multiple people using this database there is a chance that two users may try to modify a record at the same time, which will overwrite either persons changes to the record.

If two users are accessing the same record at the same time and one user makes a change, is there a way to let the second user know that the record has been modified, or give the option to overwrite the first users' changes?

Prevent MySQL Locking Up
I was doing some major work using MySQL this weekend, and I realized that MySQL is quite fragile. I mean, it can lock up so easily, and it's not funny. How can you prevent MySQL from locking up? What things can we really do so that MySQL doesn't lock up the entire server so easily?

Locking/unlocking Tables
Do I have to take care of locking / unlocking tables in my code if I have more than one connection to the database established?
Or does the DBMS handle it?

DELETE Statement Locking
Every night we delete data more than 7 days old to keep the database from getting too big. During this time, no other queries can run, they all end up being locked for like an hour.Is there a way I can configure the DELETE FROM statement where it doesn't affect SELECT queries and possibly the INSERTs as well?

Locking Required During Update
if a update query is executed with concurrent access to the database is table locking always recommended? i am thinking specifically about updates that do not take into account the value that is already stored in the DB.
let me give an example to explain. a person wants to update the salary of someone we could write:

Update table1 Set salary = "30" Where personid = 5;

in that case the user is not reading the salary value eg they are not doing salary = salary + 100 so is there a need to do any locking if multiple users do the same thing say using a thin client? i cant see the possibility of a lost update error occurring.

MySQL 5.1.22: Partitioning & Row-level Locking
Our website tracks web stats for about 31,000 users. We have a system that could really benefit from the partitioning and row-level locking and replication available in MySQL 5.1.22. For example, we have two tables that contain 5+ million records. Without partitioning, we have to purge records frequently to keep the database at a manageable size. We expect that row-level locking would also help our performance because the system does a lot of inserts to track visitor information.

To make the change, we'd be migrating to MySQL 5.1.22 from a system running MySQL Enterprise 5.0.46, PHP 5.1.6 and Apache/2.0.52 on a 64-Bit CentOS 5 system. We are using a single Master (server 1 with 8 GB RAM) to dual slave (server 2 with 6 GB RAM) database configuration. Both servers are Intel Xeon Dual Processor, Quad Core 1.86 GHz machines.

If anyone has done a comparable migration and could share their lessons learned, that would be fantastic. We're in the planning stages, and it would be great if we could anticipate any challenges.

Huge Problem With Mysql Locking Up
I was hoping for some urgent help. One of my websites is having big problems with MySQL. Its a popular website and MySQL hasn't been able to cope. The site recently froze, here is a processlist from the time it started to freeze, its very messy:

http://www.trailblasters.co.uk/sitepoint/main.htm

The query that shows up the most has been removed and the site is running ok again but I sense it will freeze again at some point. UPDATES are run very regularly, could this be the problem? How can I overcome this issue?

Single Record Locking - Permanent?
Is it possible to lock single records for all but a certain set of users,
permanently?
The intention is to make old items permanently unchangeable by anyone but
managers.

Multiple Theads Locking Mysql
We have been using MySQL for about 2 years with no problem, but recently we have had to restart it almost daily. It seems like at certain times many proceses are started that are not needed and mysql locks up. Has anyone else ever had this type of problem? I am going to isamchk the tables to see if there are problems.

MySQL Locking - Server Hang
I am using:

1. PHP 4.3.1
2. MySQL 4.0.12
3. Apache 1.3.27
4. vBulletin Version 2.3.0

The Problem Is:

My servers load seems to be jumped abnormally until sometimes it hang. When I check on the processlist of mysql :

mysql>show processlist;

It shows there is more than 10 locking process on the same database.

How to set time out for database locking? or do you have any other suggestions?

Row Level Locking With InnoDB Tables
I am relatively new to MySql (4.0.14) but I have read through the relevent
documentation and am still confused about how row level locking behaves
with InnoDB tables.

I created a database with a single innodb table which has 2 columns, one
of which is indexed. The locking behavior I see when I test against this
database is that it uses row level locks if the "SELECT ... FOR UPDATE"
involves the indexed column, but uses table level locks if instead it
involves the non-indexed column.

For example, if I have 2 mysql clients that perform the following operations:

client1> set autocommit=0;
client1> begin;
client1> SELECT my_column FROM my_table WHERE my_column = 1 FOR UPDATE;
client2> set autocommit=0;
client2> begin:
client2> SELECT my_column FROM my_table WHERE my_column = 2 FOR UPDATE;

The above query by client2 will block if the column in question is not
indexed, implying that client1 has somehow locked the entire table, even
though client1 and client2 are selecting different rows.

Am I misconfiguring something, or does InnoDB simply only support row
level locking when you are selecting indexed rows?

Question About Row Level Locking With InnoDB Tables
I am relatively new to MySql (4.0.14) but I have read through the relevent
documentation and am still confused about how row level locking behaves
with InnoDB tables.

I created a database with a single innodb table which has 2 columns, one
of which is indexed. The locking behavior I see when I test against this
database is that it uses row level locks if the "SELECT ... FOR UPDATE"
involves the indexed column, but uses table level locks if instead it
involves the non-indexed column.

For example, if I have 2 mysql clients that perform the following operations:

client1> set autocommit=0;
client1> begin;
client1> SELECT my_column FROM my_table WHERE my_column = 1 FOR UPDATE;
client2> set autocommit=0;
client2> begin:
client2> SELECT my_column FROM my_table WHERE my_column = 2 FOR UPDATE;

The above query by client2 will block if the column in question is not
indexed, implying that client1 has somehow locked the entire table, even
though client1 and client2 are selecting different rows.

Am I misconfiguring something, or does InnoDB simply only support row
level locking when you are selecting indexed rows?

Row-Level Locking With MySqlCommand - 50K MySQL Requests - Hangs
When I designed my .NET API architecture, I used transactions for EVERY data-related query. Even for SELECT queries, yes yes I know..very bad.

Just for fun, I called a method that runs a loop infinetly. The problem is, when this method runs, none of the other pages work...they just hang until this page has finished execution. Sometimes, the pages do work...but after a long "hang time".

I'm guessing that the transaction is locking the table...the other pages access the table as well.

Then, I replaced out all the transactions with regular commands....the problem still happens. When that method runs infinetly, other pages hang execution.

here is the relevant
while(true)
{
for(int i=0; i < 50000; i++)
CallSomeMethod();
Thread.Sleep(1000); //Sleep this thread for 1 second
}

In the API, CallSomemethod:
public ReturnCollection CallSomeMethod()
{
MySqlCommand comm = DataAccess.OpenCommand(sql);
(ReturnCollection )comm.ExecuteScalar(); //yes i know this isn't valid code, just making an example
}

When the code above on a page runs, no other page can run..they just hang. I thought MySql can handle 80000 requests a second??????

Table Design Question? House Table, Owner Table, Code Violations Table - Best Way?
Given the tables:

HOUSE
house_ID
address

OWNER
owner_ID
name
telephone...

HOUSE_OWNER_JOIN
?

CODE_VIOLATION_HISTORY
house_ID
violation_ID
violationStatement
...

My goal is to be able to track code violations of the house PER owner.

For example, I need to display a page that shows the current house with it's coe violations and a link to show the HOUSE's history of violation regardless of owner, Like:

House 1009283
Address
Past history (link to the following)

House History
2001-01-04 Owner: John Smith Code Violation: Gutter issue
1999-06-01 Owner: John Smith Code Violation: Faulty Steps
1998-03-02 Owner: Sam Spade Code Violation: Driveway carcks
1990-01-12 Owner: Keith Sledge Code Violation: Grass untidy

For the design of the HOUSE_OWNER_JOIN table, I thought of two ways I could go on this and this is where I need your help.

Option 1:
Have the HOUSE_OWNER_JOIN table keep dates so I can track the ownership changes that way:

HOUSE_OWNER_JOIN
houseID
ownerID
dateOwnershipBegan
dateOwnershipEnded

then I could look up all code violations by date and associate them with their rightful owner.

==================================================
Option 2:
Have the HOUSE_OWNER_JOIN table be the primary keeper of identity data by adding a new primary key and changing the CODE_VIOLATION_HISTORY table to reference that table by chaning the referencing key from house_ID to house_owner_ID:

HOUSE_OWNER_JOIN
house_owner_ID
houseID
ownerID
dateOwnershipBegan
dateOwnershipEnded

CODE_VIOLATION_HISTORY
house_owner_ID
violationStatement
...

Table Locking

I just have a question about the table locking in 3.23.56. If I am inserting a row into a table that is MyISAM type, is the entire table locked? And, if so, what does this mean for concurrent selects?

I just have a question about the table locking in 3.23.56. If I am inserting a
row into a table that is MyISAM type, is the entire table locked? And, if so,
what does this mean for concurrent selects?