ilto:shawn.l.gr...@oracle.com]
Sent: Friday, March 21, 2014 3:34 PM
To: mysql@lists.mysql.com
Subject: Re: Locking a Database (not tables) x
Hi David.
On 3/21/2014 1:42 PM, David Lerer wrote:
Frequently, we import a production dump that contains only 1 or 2 databases into one of our QA instances
that con
1, 2014 3:34 PM
To: mysql@lists.mysql.com
Subject: Re: Locking a Database (not tables) x
Hi David.
On 3/21/2014 1:42 PM, David Lerer wrote:
> Frequently, we import a production dump that contains only 1 or 2 databases
> into one of our QA instances that contains many more databases. (i.e.
Perhaps enabling read only, followed by import with super user will do what you
want.
On Mar 22, 2014, at 12:26 AM, Manuel Arostegui wrote:
> 2014-03-21 18:42 GMT+01:00 David Lerer :
>
>> Frequently, we import a production dump that contains only 1 or 2
>> databases into one of our QA inst
2014-03-21 18:42 GMT+01:00 David Lerer :
> Frequently, we import a production dump that contains only 1 or 2
> databases into one of our QA instances that contains many more databases.
> (i.e. "database" being a "schema" or a "catalogue).
> At the beginning of the import script, we first drop all
Hi David.
On 3/21/2014 1:42 PM, David Lerer wrote:
Frequently, we import a production dump that contains only 1 or 2 databases into one of our QA instances
that contains many more databases. (i.e. "database" being a "schema" or a
"catalogue).
At the beginning of the import script, we first dro
From: Wayne Leutwyler [mailto:wleut...@columbus.rr.com]
Sent: Friday, March 21, 2014 2:12 PM
To: David Lerer
Subject: Re: Locking a Database (not tables) x
You could set max_connections = 0; then kill off any remaining connections. Do
your data load and then set you max_connections back to what it was prior.
Hi David,
I think try using show full processlist that shall tell the query that is
running. I think the problem is with your query only. we need to fine tune
the query. Please send the query and the explain plan for the same. share
more stats on things that you notice during that time.
Regards
At 01:07 PM 1/7/2009, David Scott wrote:
1) InnoDb
2) 5.0.51 on Linux
3) No, a Select with a bunch of Joins, a Where, group and order
4) 37 seconds
5) Yes
6) Show Processlist does not show anything, just the user, what are you
looking for?
David,
I was looking to see if the other queries
Oh and we increased the key_buffer_size=1200M (30% of ram) no change.
2009/1/7 David Scott
> 1) InnoDb2) 5.0.51 on Linux
> 3) No, a Select with a bunch of Joins, a Where, group and order
> 4) 37 seconds
> 5) Yes
> 6) Show Processlist does not show anything, just the user, what are you
> looking
1) InnoDb2) 5.0.51 on Linux
3) No, a Select with a bunch of Joins, a Where, group and order
4) 37 seconds
5) Yes
6) Show Processlist does not show anything, just the user, what are you
looking for?
2009/1/7 mos
> At 11:20 AM 1/7/2009, you wrote:
>
>> When we run a large query other queries start
At 11:20 AM 1/7/2009, you wrote:
When we run a large query other queries start to back up when the large one
gets to the 'creating sort index' phase, this lock seems to affect the whole
server, all databases... does anyone know what may be causing this?
Thanks in advance
--
David Scott
David,
On Wed, Jan 7, 2009 at 6:20 PM, David Scott
wrote:
> When we run a large query other queries start to back up when the large one
> gets to the 'creating sort index' phase, this lock seems to affect the
> whole
> server, all databases... does anyone know what may be causing this?
>
More specifics
Perrin Harkins wrote:
> Assuming you're using InnoDB tables, "SELECT...FOR UPDATE" will lock
> the rows as you describe. It can prevent other inserts and updates to
> neighboring rows as well, depending on what isolation level you're
> running (default is REPEATABLE READ).
Thanks, in fact it eve
On Sat, Aug 9, 2008 at 8:10 AM, John Smith <[EMAIL PROTECTED]> wrote:
> Now here's the question: I need to lock certain rows, so that no other
> client can read or write that rows (I want those clients to wait until the
> transaction is commited or rolled back). I don't want to lock the entire
> ta
On Feb 12, 2008 12:28 PM, Frederic Belleudy <[EMAIL PROTECTED]> wrote:
> select video_id from videos_innodb where state='QUEUE' limit 10 FOR UPDATE;
> => it's waiting for the first to session to commit, so I cannot get
> other videos with the same state!!
>
> commit;
> => I get 10 video_id.
no ideas?
--
Sebastian Mork
[EMAIL PROTECTED]
--
On Mon, 06 Mar 2006 19:46:53 +0100
Sebastian Mork <[EMAIL PROTECTED]> wrote:
> Hi,
> is it not possible to lock federated tables (creating a lock on the remote
> machine to lock the table on the machine containing the data?)
>
> I've a situation
Hello.
Have a look here:
http://dev.mysql.com/doc/refman/5.0/en/converting-tables-to-innodb.html
http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html
PaginaDeSpud wrote:
> i'm using myisam. Is there any tip i should know before to migrate this
> table to innodb ?
>
> Th
i'm using myisam. Is there any tip i should know before to migrate this
table to innodb ?
Thanks !
- Original Message -
From: "Gleb Paharenko" <[EMAIL PROTECTED]>
To:
Sent: Tuesday, November 29, 2005 12:35 PM
Subject: Re: locking issues
Hello.
What table
Hello.
What table engine do you use for your tables? InnoDB usually
is the best choice if you have lots of concurrent updates and inserts.
"PaginaDeSpud" <[EMAIL PROTECTED]> wrote:
> I'm getting locking issues due to tables very often updated/insert.
>
> It's splitted into two tables,
Hi,
MySQL locks work only until a session expries, so they will not be of much use
in your case. The best solution will be to implement this logic in your
application - the simplest method is to add a field to the table which will
keep the information about whether the specific record is locked
In the last episode (Sep 07), Rich said:
> I started a different thread on this, but then I realized I might not
> have to use InnoDB for this.
>
> Let me explain what I wish to achieve.
>
> I want to create records in an established table. I then want them
> locked (either by locking the whole
The first thing I would do is to upgrade the Kernel, as per you r mail
u said u were running 2.4.20-8, get the latest one for RH9 that is
2.4.20-31.9 SMP, and you might see a huge difference, if it doesn't
work, then make sure you have properly indexed the colums, mytop is a
great tool for diagnos
Hello.
Use SHOW PROCESSLIST and slow-query log to catch the query which locks
tables for a long time. Upgrade to 4.1.13. If you use MyISAM, think
about moving towards InnoDB.
Aaron <[EMAIL PROTECTED]> wrote:
> Hi all ,
>
> I have been experiencing intermittent locking issues with MY
Heikki,
- Original Message -
> Terry,
>
> - Original Message -
> From: "Terry Riley" <[EMAIL PROTECTED]>
> Newsgroups: mailing.database.myodbc
> Sent: Monday, December 06, 2004 8:15 PM
> Subject: Locking Issue?
>
>
> > Can someone help, please?
> >
> > We set up a server to h
Terry,
- Original Message -
From: "Terry Riley" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Monday, December 06, 2004 8:15 PM
Subject: Locking Issue?
Can someone help, please?
We set up a server to handle a coldfusion web application (CFMX 6.1)
running against MySQL 4.1
- Original Message -
Thanks for those hints, Dathan (see below):
>
> -Original Message-
> From: Terry Riley [mailto:[EMAIL PROTECTED]
> Sent: Monday, December 06, 2004 10:12 AM
> To: [EMAIL PROTECTED]
> Subject: Locking Issue?
>
> Can someone help, please?
>
> We set up a serv
-Original Message-
From: Terry Riley [mailto:[EMAIL PROTECTED]
Sent: Monday, December 06, 2004 10:12 AM
To: [EMAIL PROTECTED]
Subject: Locking Issue?
Can someone help, please?
We set up a server to handle a coldfusion web application (CFMX 6.1)
running against MySQL 4.1.3b-beta on Win
On Fri, Jul 23, 2004 at 01:44:16PM -0400, Michael Sleman wrote:
> Does MyISAM lock the whole table when doing SELECTs?
Yes, each client obtains a read lock on the table for a SELECT query.
> How about if there are several processors? Is there locking?
It's no different with multiple CPUs.
Jerem
At 11:42 -0400 4/17/04, Mark Susol|Ultimate Creative Media wrote:
> The general answer to your question, if you're willing to cooperate
with the server, is to lock the tables from within the server so that
no other clients can modify them, and use FLUSH TABLES to flush any
changes to disk. Whi
> The general answer to your question, if you're willing to cooperate
> with the server, is to lock the tables from within the server so that
> no other clients can modify them, and use FLUSH TABLES to flush any
> changes to disk. While the lock remains in place, copy the table
> files. Then unlo
At 0:03 -0400 4/16/04, Mark Susol|Ultimate Creative Media wrote:
Did I really ask that tough a question? Anyone?
I'm not sure you're asking an answerable question. Consider this requirement
that you give below:
> So what do I need to do before running any backup scripts to ensure the
> tables wi
Did I really ask that tough a question? Anyone?
On 4/15/04 7:38 PM, "Mark Susol | Ultimate Creative Media"
<[EMAIL PROTECTED]> wrote:
> I've found a nice shell script to use to backup my server's MySQL databases.
> https://sourceforge.net/projects/automysqlbackup/
>
> However, when I tried this
Excuse me for late response.
When I do it from the mysql prompt it works fine but trough the API (I use
DAC for MySQL from microolap, maybe the bug is in there software!!) it
doesn't work fine.
I have try it on several versions: 4.1.1, 5.0.0 and 4.0.0.
Thank you for your help. If you think it
Hans van Dalen <[EMAIL PROTECTED]> wrote:
>
> A question about locking. In my code (delphi but that doesn't much matter)
> I fire ad-hoc queries (select). Before the query i fire a locking query
> (read) for that particular tables. Something like this:
>
> lock tables tab1 a read, tab2 b read
>
You can use MySQL's built in locking, or you can do this:
Find a job that is waiting
Run an update like this:
UPDATE table SET status = 'processing'
WHERE jobid = found_job AND status = 'waiting'
If you get back a 1 from that query, it made one change, and you're good. If
you ge
James
-Original Message-
From: Chris Nolan [mailto:[EMAIL PROTECTED]
Sent: Monday, February 16, 2004 4:52 AM
To: James Kelty
Cc: [EMAIL PROTECTED]
Subject: Re: Locking
Hi!
Next-key locking essentially doesn't work on rows - it works on indexes.
It ensures that "phantom reads" can&#
Does it have exclusive and shared?
-James
-Original Message-
From: Chris Nolan [mailto:[EMAIL PROTECTED]
Sent: Monday, February 16, 2004 4:52 AM
To: James Kelty
Cc: [EMAIL PROTECTED]
Subject: Re: Locking
Hi!
Next-key locking essentially doesn't work on rows - it works on indexe
Hi!
Next-key locking essentially doesn't work on rows - it works on indexes.
It ensures that "phantom reads" can't happen.
InnoDB does indeed do row-locking. In fact, it has one of the most
efficient representations of locks of any relational database.
Regards,
Chris
On Mon, 2004-02-16 at 23:4
Hi Andrey,
many thanks for quick response. plz find my further query below.
- Original Message -
From: "Andrey Subbotin" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, December 19, 2003 12:27 PM
Subject: Re: Locking the tables
>
> Hello Binay.
&g
Hello Binay.
Friday, December 19, 2003, 1:53:01 PM, you wrote:
B> I want to put write lock on a(set of) table(s), so that only i
B> can insert into it and no body else unless i remove the write lock.
B> How can i achieve it??
AFAIR, you usu. do that with:
LOCK TABLE mytbl WRITE;
// do smth. her
Kim G. Pedersen wrote:
BUT
just a little hint , u sure it not ur _ (underscore) in table name which
is a kind of wildcard that give u trouble ??
This raises an interesting question. I wrote earlier about troubles with
mismatch field count and missing field errors. I have both tables and
fie
Hi
U lucky man ,,, I can't make my locks work at all :(
BUT
just a little hint , u sure it not ur _ (underscore) in table name which
is a kind of wildcard that give u trouble ??
regards
Kim Pedersen
> Fella's,
>
> First of all I'd like to welcome myself to this list. Have been here
before,
hello.
You can read lock whole database's tables like this :
FLUSH TABLES WITH READ LOCK ;
and unlock with
UNLOCK TABLES ;
- Original Message -
From: "Franz, Fa. PostDirekt MA" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, February 05, 2003 10:35 PM
Subject: locking tab
On Thursday 12 December 2002 14:06, Greg_Cope at sandwich dot pfizer dot com
wrote:
> I may be confused here but it would appear that when you issue a LOCK
> TABLES tbl_name { READ|WRITE }; You cannot read from another unlocked table
> in the same connection eg:
>
> mysql> LOCK TABLES users read
At 19:52 + 12/9/02, [EMAIL PROTECTED] wrote:
Hi All,
I may be confused here but it would appear that when you issue a LOCK TABLES
tbl_name { READ|WRITE }; You cannot read from another unlocked table in the
same connection eg:
You're supposed to lock all the tables that you'll need until you
Michael,
- Original Message -
From: "Michael Bacarella" <[EMAIL PROTECTED]>
To: "Heikki Tuuri" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
> > SELECT * FROM mytable WHERE mycol = xxx LOCK IN SHARE MODE;
> >
> > or
> >
> > SELECT * FROM mytable WHERE mycol = xxx FOR UPDATE;
> >
> >
> > The
On Fri, Jun 21, 2002 at 12:38:06AM +0300, Heikki Tuuri wrote:
> InnoDB type tables in MySQL have row level locking. Call SET AUTOCOMMIT=0 or
> wrap your transaction in BEGIN ... COMMIT.
>
> Make sure you have an index on column 'mycol' so that InnoDB finds the row
> without a table scan. Then you
On Thursday 20 June 2002 17:50, Cal Evans wrote:
What you want to do is wrap the update in a transaction with transaction
isolation level set to SERALIZABLE. This should insure that whichever client
is first to initiate the transaction will have effectively exclusive access
to the record in qu
Short answer:
You don't. That's not the way SQL databases work. When you hear of 'row
level locking' it means something different than you are used to if you are
coming from Access/Foxpro.
If you are using InnoDB tables then you can:
BEGIN TRANS
statement
statement
statement
COMMIT or ROLL
Hi!
- Original Message -
From: "Galen Wright-Watson" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Thursday, June 20, 2002 11:32 PM
Subject: Re: locking on row level ...
>
> On Wed, 19 Jun 2002, Silmara Cristina Basso wrote:
>
> > I'm
On Wed, 19 Jun 2002, Silmara Cristina Basso wrote:
> I'm newbie MySQL and I'm using MySQL-max 4.0.1(Innodb), with one application
> developed in Delphi and connect through MyODBC. The question is ...
> How can i do to lock one row so that it is editing in the application?
>
>
The impression I g
.
- Original Message -
From: "Alexander Keremidarski" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Thursday, May 23, 2002 1:54 AM
Subject: Re: Locking TABLES for myisamchk, please help!!
> Mark wrote:
> > Wednesday, May 22, 2002, 9:23:02 AM, louie w
Mark wrote:
> Wednesday, May 22, 2002, 9:23:02 AM, louie wrote:
> Personally, I find the behavior of myisasmchk to be a bit "beta" when it
> comes live tables. It would be real easy for myisasmchk to refuse to run
> when mysqld is running. That should be a built-in precaution. I can
> understand
Wednesday, May 22, 2002, 9:23:02 AM, louie wrote:
lm> Hi, i was thingking about locking the tables first so no connection
lm> could write so i can do myisamchk. Is this process safe?
lm> Procedures:
lm> 1. lockdb
lm> 2. run myisamchk -r or -o
lm> 3. unlock db
lm> Btw, mysqld is running. I want
louie,
Wednesday, May 22, 2002, 9:23:02 AM, you wrote:
lm> Hi, i was thingking about locking the tables first so no connection
lm> could write so i can do myisamchk. Is this process safe?
lm> Procedures:
lm> 1. lockdb
lm> 2. run myisamchk -r or -o
lm> 3. unlock db
lm> Btw, mysqld is running. I
> What error occurs if i try to access (read or write) a table that was
locked
> (LOCK TABLES mytable WRITE) by another thread?
My belief is that you do not get an error. The thread attempting to access
the
locked table is stalled until the lock is released. If the lock is never
released, you
Hi
What about a table with logged in user ids and a timestamp (I use a sessions
table).
Update the timestamp when they access the system. Check when a user tries to
login. If that user id, but a different person, has accessed the system
within the last xx minutes or seconds then consider them lo
On 19 Aug 2001 19:29:17 +0100, Wesley Darlington wrote:
> Hi,
Howdy.
> On Thu, Aug 16, 2001 at 02:04:25PM +0100, Ken Guest wrote:
> > At the moment, what I am doing is:
> >
> > execute "LOCK TABLES foo READ"
> > select info from foo
> > //next lock implictly unlocks previous one
> >
Hi,
On Thu, Aug 16, 2001 at 02:04:25PM +0100, Ken Guest wrote:
> At the moment, what I am doing is:
>
> execute "LOCK TABLES foo READ"
> select info from foo
> //next lock implictly unlocks previous one
> //as it's done by the same thread/process
> //chances of conf
On Thu, Aug 16, 2001 at 02:04:25PM +0100, Ken Guest wrote:
>
> hi,
> I'm new to this list, but not so new to using MySQL and have a question
> about locking. (This is my first foray into datalocking with MySQL.)
>
> I'd like to lock a table (a row in that table would be better) for read
> and
To avoid the "Fatal Embrace" where two users each lock a table, and
then attempt to read the table the other locked, you must lock
all table you intend to use for the duration of the lock.
The manual clearly states that a subsequent lock or unlock command
will first unlock all the tables you curre
MezzIndex.ID, MezzIndex.Title
- Original Message -
From: "Andrius Armonas" <[EMAIL PROTECTED]>
To: "Johan Wahlström" <[EMAIL PROTECTED]>
Sent: Tuesday, July 17, 2001 6:19 AM
Subject: Re: locking problem!
> if you're using write lock and do not unlock
On Thu, Mar 08, 2001 at 10:30:18PM -0500, Justin wrote:
>
> Typically, in a database backed website with a very high percentage
> of selects to inserts, people do not "expect" update operations
> (posts and so on) to complete very fast. But they do "expect" page
> requests to be very fast.. the d
thanks,
Then I imagine, as a rule, I should be thinking about changing
that server variable to set selects to always have priority?
Without transactions, one should never have a situation
where any ill effects can result from delaying "users" that
issue live insert or update requests until the p
Hi,
Imagine the scenario of the following happening in quick succession:
user1: select * from table --- ticks away, doing the select
user2: select * from table --- also ticks away
user3: insert into table ... --- locked!, waiting for selects
to finis
MySQL doesn't have built in row-level locking, but they are chaning that.
The best thing to do, for now, is to have a record_locked column, and set it
to 1 if the record is in use. To lock the record, you would run a query like
update table_name set record_locked = 1 where id = ??? and record
66 matches
Mail list logo
