SELECT LOCK IN SHARE MODE and FOR UPDATE
Baron wrote nice article comparing locking hints in MySQL and SQL Server.
In MySQL/Innodb LOCK IN SHARE MODE and SELECT FOR UPDATE are more than hints. Behavior will be different from normal SELECT statements. Here is simple example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
SESSION1: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into tst values(1); Query OK, 1 row affected (0.00 sec) SESSION2: mysql> begin ; Query OK, 0 rows affected (0.00 sec) mysql> select * from tst; Empty set (0.01 sec) #Session2 does not see any rows as transaction was not commited yet. SESSION1: mysql> commit; Query OK, 0 rows affected (0.01 sec) SESSION2: mysql> select * from tst; Empty set (0.00 sec) mysql> select * from tst lock in share mode; +---+ | i | +---+ | 1 | +---+ 1 row in set (0.00 sec) mysql> select * from tst for update; +---+ | i | +---+ | 1 | +---+ 1 row in set (0.00 sec) #Standard SELECT does not see rows while SELECT for UPDATE and LOCK IN SHARE MODE sees it. |
What is happening ? SELECT for UPDATE and LOCK IN SHARE MODE modifiers effectively run in READ-COMMITTED isolation mode even if current isolation mode is REPEATABLE-READ. This is done beause Innodb can only lock current version of row. Think about similar case and row being deleted. Even if Innodb would be able to set locks on rows which no more exist – would it do any good for you ? Not really – for example you could try to update the row which you just locked with SELECT FOR UPDATE but this row is already gone so you would get quite unexpected error updating the row which you thought you locked successfully. Anyway it is done this way for good all other decisions would be even more troublesome. This complexity is what you have to pay for multiversioning.
Lets also think how these modifiers can be user and what do we expect from them in practice. LOCK IN SHARE MODE is actually often used to bypass multiversioning and make sure we’re reading most current data, plus to ensure it can’t be changed. This for example can be used to read set of the rows, compute new values for some of them and write them back. If we would not use LOCK IN SHARE MODE we could be in trouble as rows could be update before we write new values to them and such update could be lost. Note I said some of them. If you want to read set of rows and modify all of them you may chose to use SELECT FOR UPDATE. This will ensure you get write locks for all rows at once which reduces chance of deadlocks – lock will not need to be upgraded when update happens. SELECT FOR UPDATE also blocks access to the data using LOCK IN SHARE MODE. So by using these two modifiers you may effectively implement instant data invalidation – using SELECT FOR UPDATE to quickly lock data which is no more correct so it is not used while you recompute it. Note it also works if LOCK IN SHARE MODE is used with selects – standard selects are run in non-locking mode which means they never lock any rows and just use old row versions if they were updated.
All said above applies to default REPEATABLE-READ mode. With different isolation modes there could be some differences but logic stills the same.
So these hints are very powerful and helpful for application development but should be used wisely. Do not assume you can simply add SELECT FOR UPDATE to your select and reduce deadlocks if you’re updating selected rows. As query results may chance you need to access how it affects your application and perform changes required.
What is missing in Innodb locking.? In my oppinion few rather important peices missing in Locking implementation of Innodb are:
Lock table Innodb can lock tables but it will still need to set row level locks which is memory and CPU overhead. For some bulk operations it would be more efficient to use table locks. As I tested it really takes some resources.
Unlocking non matched rows Imagine you’re running DELETE FROM USERS WHERE NAME LIKE “%Heikki%”; How any rows do you think will be locked ? Actually all of them, not only ones which are matched by like because locks are taken on Innodb level before MySQL performs like matching, and row is not unlocked if it does not match.
Smarter deadlock victum scheduling At this point transaction which made least updates is killed to resolve deadlock. Which means if transaction takes a lot of locks but does not do much updates it may never have chance to complete. The best example would be
INSERT INTO MyISAMTable SELECT * FROM INNODBTable; – A lot of shared locks on Innodb table but no updates. Supporting MySQL hints
“HIGH_PRIORITY” and “LOW_PRIORITY” would probably be good start.
Comments (56)
Your demonstration of multiversioning is very helpful.
You said “Do not assume you can simply add SELECT FOR UPDATE to your select and reduce deadlocks if you’re updating selected rows. As query results may chance you need to access how it affects your application and perform changes required.”
If I do a SELECT FOR UPDATE and then update some of the rows, how can I get a deadlock? If the SELECT completes, it means I was able to get write locks on each row returned, so I should not have any lock contention with other transactions, right?
If you do SELECT FOR UPDATE you can get deadlock while SELECT FOR UPDATE is running, if other transaction for example locking same rows in different order. However you will not get deadlock on row updates themselves as they do not add any locks.
Also this action might not be only one performed by transaction, this is why I’m saying “reduce” rather than “eliminate” 🙂
OK, that’s what I thought you meant (I thought about it more after commenting). The approach I’m taking at my current employer is to try to get everything reading and updating the rows in the same order. Usually it’s not a problem, because the ones that happen out of order are programs that get something to do, do a bunch, then update the rows back one at a time — it’s just a matter of sorting them before the update loop (they are typically processed out of PK order because they are processed in some other order, such as priority).
I wanted to make sure, given that the first query is a SELECT FOR UPDATE and it succeeds, there’s not some other sneaky way to get into a deadlock 🙂 These programs are running only two queries: SELECT FOR UPDATE and then the update. So as far as I can tell, they won’t deadlock with other instances of themselves, though they might deadlock with something else that locks rows out of order.
I’ve written more about this in my article about the “little known” way to create a deadlock: http://www.xaprb.com/blog/2006/08/03/a-little-known-way-to-cause-a-database-deadlock/
Updating data in the same order is good strategy anyway.
Yes you’re right SELECT FOR UPDATE should not deadlock with other instances if MySQL executes them same way (which it might not).
“if MySQL executes them same way (which it might not)”
Does that mean “if they do not use the same index” ? If they use the same index, they should lock rows in the same order, right? From what I understand, each row has a pointer to the next row, so a query that is scanning an index will always scan it in the same order, no matter what physical order the data is in.
Sorry for the 20 questions, but I want to understand this thoroughly 🙂 The InnoDB internals manual sometimes helps, but other times not so much.
Yes. What I meant it could be full table scan if range is large or index range if range is small. Or join order of tables can be different if you execute the join. Well… for joins it is actually even more complicated – even join of tables in same order can result in rows accessed in different order and so in deadlock.
But these are all exceptions rather than common case 🙂
Hi Peter, How can a join of tables in same order can result in access of rows in different order. Would you please explain?
I’ve been searching for a solution to this issue for a while and stumbled across this post during my efforts. I thought perhaps you could provide an answer. I have an application that works with a flat table that will be used by multiple users. If one users is working with a record, I’d like to be able to display a message to the effect of “This record is currently in use” when another users attempts to access that same record. Is this possible?
Thanks,
Meghan
Meghan,
MySQL/Innodb does not have an option to check if given row is locked other than trying to lock it and catching lock wait timeout.
In your case you could probably have a flag to make the row as “being worked on”.
If it is especially good idea if locks are long term (and this is probably the only case in which you would like to display the message) – having transaction open while user is entering something in dialog box is not a good idea.
Yes, the locks are somewhat long-term and it would be useful to tie a locked row to a particular user. Thanks for the insight!
Hello. Yes I have the same problem as Meghan. I would like to see if a row is locked or not BEFORE I try to lock it. Im using DotNetConnector from mysql website and the ONLY way to see if an exception occred is to se if there is an exception. Is what realy the only way? Seems to me that using an exception to indicate that you where not able to write data is not a good way to tell the developer that it went wrong. What about a simple boolean? true if yes, it went ok -and false if not. If an internal error happens, of course -throw the exception. I know that the ExecuteOneQuery() is returning a number indicating if the write (update) went good or bad -of it also throws an exception.
Seems wrong to me
Is there an other way to check if the row that you want to update it locked? Without exceptions?
Thanks,
Bo
There is no way to check if row is locked in MySQL so far,
even though I agree it would be handy sometimes.
Hi
I have four tables
t1
t2
t3
t4
I want to retrive information from t2 and insert/update on t3 and t4
but this process depends on t1.
What i have done
1.retrive infromation from table t2
2.Inside while loop i write condition for LOCKING
3.
if(mysql_query(“LOCK TABLE t1 WRITE”)){
Insert / update in t3 and t4
update/insert t1
mysql_query(“UNLOCK TABLES”);
}
4.End while loop
But Its not going inside if(mysql_query(“LOCK TABLE t1 WRITE”)){
So my code not working properly
databse are innodb
I want to execute row level lock
Please Help on this issue ASAP
I am looking for your reply
Thanks
Hi Baron,
You say there is no “Unlocking non matched rows” feature. The example you give is:
DELETE FROM USERS WHERE NAME LIKE “%Heikki%”;
First of all, mysql will not be able to use indices so indeed all rows are locked.
But….. This is exactly what you expect. Why? Well suppose that someone else wants
to insert a new row with NAME set to: “fooHeikkibar”. To allow for serializability,
the insert statement should block. To make this clear, suppose that the USERS table
is considered consistent when there is an even number of records (or zero records)
that have a NAME with Heikki in it. No consider the following schedule:
BEGIN BEGIN
[nothing happens here] INSERT “fooHeikkibar”
DELETE WHERE NAME LIKE “%Heikki%”;
[nothing happens here] INSERT “barHeikkifoo”
END END
In this schedule, the database is not consistent anymore because there will be one
record remaining in the USERS table that contains the substring Heikki.
Why is my text formatting being messed up?
I will put the two transactions below each other.
sigh…
TIMESTAMP – STATEMENT
———————
T1 – BEGIN
T2 – [nothing happens here]
T3 – DELETE WHERE NAME LIKE “%Heikki%”;
T4 – [nothing happens here]
T5 – END
T1 – BEGIN
T2 – INSERT “fooHeikkibar”
T3 – [nothing happens here]
T4 – INSERT “barHeikkifoo”
T5 – END
And I misspelled your name too is see now.
Peter, Baron…..easy to become confused 🙂
Darseq,
There is question of locking and isolation mode. First if DELETE runs after first insert it would have to wait on the second transaction to commit, even if it only locks matched rows. So in fact DELETE can’t complete in the example above before insert transaction commits.
Hi Peter,
You are right, I was mistaking. Of course the DELETE blocks on the INSERT.
But I think my point still stands. I will assume that the isolation mode
is to be fully SERIALIZABLE. Then consider this scenario with transactions
TR1 and TR2:
TRANSACTION – STATEMENT
———————————————————————
TR1 – BEGIN
TR1 – DELETE FROM USERS WHERE NAME LIKE “%Heikki%”;
TR2 – BEGIN
TR2 – INSERT INTO USERS VALUES(“HeikkiTuuri”);
TR2 – END
TR1 – SELECT NAME FROM USERS WHERE NAME LIKE “%Heikki%” LOCK IN SHARE MODE
TR1 – END
If the DELETE statement of TR1 would not lock all records, then the INSERT
statement of TR2 may actually succeed without blocking (depending on the
initial contents of the USERS table). In my example the SELECT of TR2
uses a SHARE LOCK (I agree that this is a question of locking). Now the
DELETE of TR1 does not ‘see’ record “HeikkiTuuri”, whereas the SELECT of
TR1 does. This is in contraction with the REPEATABLE READ property that
any serializable execution should have.
darseq,
Indeed if you have SERIALIZABLE isolation or REPEATABLE-READS you will need to have those locks set, this is similar to why Innodb sets next-key locks in the indexes now.
For most applications however READ-COMMITED is enough and when locks can be avoided.
Hi,
I am working on solving an issue where I am running into some deadlocking. Essentially things look like this (MySQL 5.0.45/Windows/Connector-J-5.1.5):
Transaction-1
————-
SELECT p FROM assets WHERE asset.id=12
various other select statements on this and other tables
….
UPDATE assets SET x=1, y=2, z=3 WHERE asset.id=12
Transaction-2
————–
UPDATE assets SET mod_date=NOW() WHERE asset.id=12
It appears that the deadlock is occurring because Transaction-1 is upgrading from a S to an X lock but Transaction 2 is already waiting for an X lock on assets since it came in between the SELECT and UPDATE statements in Transaction-1 and hence the deadlock condition.
The MySQL 5.0 Reference Guide has the following advice:
Another way to serialize transactions is to create an auxiliary “semaphore†table that contains just a single row. Have each transaction update that row before accessing other tables. In that way, all transactions happen in a serial fashion. Note that the InnoDB instant deadlock detection algorithm also works in this case, because the serializing lock is a row-level lock.
This doesn’t make a lot of sense to me and I gave it a try and it didn’t fix the deadlocking so I am assuming I am misunderstanding things.
Essentially I added a new table with 1 row (containing a single TinyInt), then as the first statement for every transaction there is an UPDATE semaphore SET lock=1;
Since this is a row level lock, I fail to see how it will serialize the _entire_ transaction beyond this update and indeed it didn’t work in that fashion.
Can anyone offer any insight/advice on this topic?
TIA.
Hi NBW
The way I see it is as follows: You enter a transaction and issue the
UPDATE semaphore SET lock=1 statement. Now when another transaction tries
to do the same, it waits for the earlier transaction to release the lock.
That is when the transaction executes the commit statement which is the
final statement of each transaction. For this to work of course AUTOCOMMIT
should be disabled. In this way deadlocks can indeed never occur. This is
because the UPDATE statement sets an EXCLUSIVE lock on the semaphore row.
DO NOT EVER DO THIS:
update SOMETABLE as fn set fn.counter = (select count(*) from SOMEOTHERTABLE as p where p.X = fn.X LOCK IN share mode);
Good luck with your locked mysql
In an ODBC based application, I need to lock a row while selecting it. For this
I did use SELECT FOR UPDATE in SQLPREPARE() odbc primitive. But it is not working.
WHat may be the reason, Ia m working on Linux.
Pratap, you would do better of asking question on forum and providing complete SQL example.
Most typical the problem is you do not have transaction open.
Hi Pratab,
Check the following:
– Is the transaction isolation level properly set?
For ODBC use the ODBC API and something like:
SQLSetConnectAttr(dbc_h,
SQL_ATTR_TXN_ISOLATION,
(SQLPOINTER) isolation,
SQL_IS_INTEGER);
– Is AUTOCOMMIT set to false?
For ODBC use the ODBC API and something like:
SQLSetConnectAttr(dbc_h,
SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)autocommit,
SQL_IS_UINTEGER);
– When using ODBC you do not need to specify BEGIN statements. A new transaction start automatically
after the previous transaction commits.
To commit a transaction in ODBC, use the API:
SQLEndTran(SQL_HANDLE_DBC,dbc_h,SQL_COMMIT);
If you choose to use a ‘sql passthrough’ COMMIT
statement however, the ODBC driver itself may not
know about your transaction ending and may not
automatically start a new one so beware of this.
Oh yeah, and before I forget: A lock only becomes active when
the statement is actually EXECUTED, so a PREPARE does not
necessarily lock until the point of actually EXECUTING the
statement.
Following code cause an undetectable deadlock when run simultaneously. (Lock wait timeout exceeded;)
START TRANSACTION;
SELECT id into tmp from mf_banners where id=192 FOR UPDATE;
UPDATE mf_banners set is_active=is_active where id=192;
COMMIT;
And I can’t understand why.
Transaction 1 takes X lock for record 192, then Transaction 2 try to take the same lock and can’t, so T2 waits for X lock. Then T1 tries to update record (It holds X lock for that record) and it can’t! Why???
I have serialized access with GET_LOCK and RELEASE_LOCK and that helped to avoid lock wait timeouts, but i’am not sure that they are replication safe. Are they replication safe, especially when used inside stored procedures?
Thank you for help.
Interesting, but I think the update should actually succeed.
Did you test with mysql.exe in two console (dos?) boxes?
Using mysql ensures that you operate directly on the server
with no other software layers in between. Also I find the
syntax is_active=is_active rather odd. Always test using the
most simple case. So do something like is_active=’0′ or so.
I don’t know about GET_LOCK/RELEASE_LOCK.
p.s: did you know that a
‘SELECT id into tmp from mf_banners where id=192 LOCK IN SHARE MODE’
together with ‘REPEATABLE_READ’ suffices to get serializability?
Darseq> I have tested from two console boxes with mysql 5.0.21 on freebsd6 and linux. I also thought that update should succeed, but it does not! It waits for some table level locks which were made by second SELECT FOR UPDATE which in turn waits for X lock. That is why undetectable deadlock happens. But my opinion that that is a bug. Just try yourself (may be i missed something) – start 2 transactions from two consoles and make any SELECT FOR UPDATE for the SAME ROW in both transactions. Second transaction will wait for the lock, then from the first transaction try to UPDATE that row. In my case it blocks both transaction until timeout and it is very very strange! Can anyone explain me why? Or tell me that it is not working just for me because of my stupidness.
Victor,
I would suggest to try it with recent MySQL 5.0 version and if it still happens report it at bugs.mysql.com
Problem solved with mysql 5.0.56.
I don’t think I understand. I thought a deadlock was when two tasks wanted access to a resource that the other had already locked. When something asks for a lock and can not get it because something else has the lock was called lock contention.
Also, when you say: “Lock table Innodb can lock tables but it will still need to set row level locks which is memory and CPU overhead. For some bulk operations it would be more efficient to use table locks.”, I am curious as to why, if the whole table is locked, row level locks are still needed.
Ben,
If one thread just tries to lock the resouce which is already locked lock wait happens. If however the locking thread holds resources other threads is waiting for (most simple case) wait would happen forever and it is called deadlock.
Why Innodb locks row level locks always is because it is designed this way 🙂
Ben,
A deadlock is like Peter explains, but basically you are saying the same.
So to combine both explanations just consider two transactions A en B and
one table with rows r1 and r2 in it. Transactions A locks r1, and
transaction B locks r2, then transaction A wants to lock r2 but has to
wait to get it, and finally transaction B wants to lock r1 but also has
to wait. In this scenario transaction A and B are waiting for eachother
and neither one can ever proceed which is called a deadlock.
As to why table locks are more efficient is because of this:
When a transaction wants to access a table it first checks if there is
a table lock sitting on it. Only after doing this check, row locks come
into play. So there are actually two levels of locking, first the table
lock, only then the row locks. Checking only a single table lock is
less overhead then having to check multiple row locks.
Ben, just to clarify some things, copied from the mysql manual:
The correct way to use LOCK TABLES and UNLOCK TABLES with transactional tables, such as InnoDB tables, is to begin a transaction with SET AUTOCOMMIT = 0 (not START TRANSACTION) followed by LOCK TABLES, and to not call UNLOCK TABLES until you commit the transaction explicitly. When you call LOCK TABLES, InnoDB internally takes its own table lock, and MySQL takes its own table lock. InnoDB releases its internal table lock at the next commit, but for MySQL to release its table lock, you have to call UNLOCK TABLES. You should not have AUTOCOMMIT = 1, because then InnoDB releases its internal table lock immediately after the call of LOCK TABLES, and deadlocks can very easily happen. InnoDB does not acquire the internal table lock at all if AUTOCOMMIT=1, to help old applications avoid unnecessary deadlocks.
Hi everybody,
I have an a problem and I cannot resolve.
I don’t know how I can resolve this:
Transaction 1
START TRANSACTION;
UPDATE
sginfra_actasSETact_estado_id= ‘9’ WHEREact_id= ‘3713’(Affected Row = 1)
Other statments….
UPDATE
sginfra_actasSETact_estado_id= ‘9’ WHEREact_id= ‘3713’(Affected Row = 0)
I’m in the same transactions.
Why there is a lock?
Thansk
Gaditano
The first update statement results in a lock for other transactions.
Since these update statements are in the same transaction, the second
update statement does not block. However because the row was already
updated with the first update statement, an identical update will
affect zero rows because the update has already been made.
is this code what im doing is correct..?
im using codeigniter framework of this one…engine use is iNNodb
[code]
db->simple_query(‘SET AUTOCOMMIT=0’);
$lockquery = “LOCK TABLE costumer_prof WRITE”;
$db->insert(‘costumer_prof’,$valueAdded);
if($db->trans_status() === FALSE) //is this ok to used trans_status without trans_start or trans_begin..?
{
$db->trans_rollback()
return 1;
}
else
{
$db->trans_commit();
return 0;
}
$lockquery = “UNLOCK TABLE”;
}
?>
[/code]
am i implementing the right way tosh lock table or better to use [code]$lockquery = “LOCK TABLE costumer_prof IN SHARE MODE”;[/code]
hope this thread is still active..i really appreciate for those who reply …i’ll acknowledge it:)
Hi Jan.
I do not know which underlying driver the codeigniter framework
uses so I cannot answer the question of whether you should
explicitely execute a ‘begin transaction’ or not. You can always
find out of course by just running two transactions simulatiously
and see for how long the locks are held.
As for your second question, whether you should use a WRITE or
READ lock the rule is as follows: If you update/insert (write) a
table, then also use a WRITE lock to prevent deadlocks. If you
would use a READ lock instead, serializability is still
guaranteed but a deadlock can easily occur:
transaction A:
Read record 2 (READ LOCK)
transaction B:
Read record 2 (READ LOCK)
transaction A:
Write record 2 (blocks)
transaction B:
Write record 2 (blocks too, deadlock).
If you use WRITE locks, this situation can
never occur.
thanks darseq for the reply..i thought this thread is not already active…mistaken..:).
okay…as i read your #34 comment..you say that this will not use transaction…as what mysql manual said also..i’ll just try it up..what you said.:)..i’ll just test it..
im using WRITE LOCK for the insert..another question
table1 is in a WRITE LOCK,.(insert a data only) so there will be no other user can access that table1 until it is not releases a lock…what if there is another user access that table1 also doing an insert data.so he can’t access the table1..?..so i think that would be the answer..i just want to clear up everything..im in a doubt situation..:)..
is there a way that i can temporarily stored the data insert by another user..because the table1 will be access by not too many users..(possible there will be a simultaneous execution will happen.)..im just being pessimistic to this one..
?
what is the difference if i use share mode/exclusive mode and using WRITE or read instead..?..
sorry to ask you many question..:(..but please help me to this one.
Most DBMS systems use:
– shared locks (SLOCK) for a read lock.
– exclusive locks (XLOCK) for a write lock.
A lock compatibility matrix tells you which
locks are granted and which are not when
two (or more) transactions execute. Just do
a search for ‘lock compatibility, matrix’
with google.
So suppose you have two transactions again:
transaction A: XLOCK table1, update record 6
transaction B: update record 6
I would just experiment again to find out whether
B blocks on A even when B does not set a lock.
By the way, there is also something called an update
lock (ULOCK) which is useful when inserting/updating
a table record while others can still read the record
even if they use SLOCKS on the same record. But these
locks are used for records. I do not think that innodb
implements update locks for tables but I am not sure
on this.
there is no ULOCK in innodb…:(…
okay..i’ll just read more about lock compatibility…so it means to say ill be using shared/exclusive rather than write or read…
is it ok ill put the lock on the query for example this one
SELECT pep_id, pep_name FROM person WHERE id= $id LOCK IN SHARE MODE;
OR ill just do it like this
function InsertData {
$query = “LOCK TABLE person IN SHARE MODE”;
$query = INSERT INTO person(pep_id, pep_name) VALUES (‘1′,’John’);
}
Thanks Again for the reply Darseq:)
Locks can be set on multiple levels of granularity.
If one chooses to lock tables, all other users are
blocked for as long as the transaction takes to
finish its job. If one chooses to lock records
instead, other records in the same table can still
be accessed, but access time may be slower (the
dbms requires more resources to lock records than
to lock a single table).
To summarize: If you transactions are fast and no
user interaction happens in between, it is probably
best to quickly lock the entire table and do the
work. If transactions may be slow or user dialogs
are placed in between, you may be better of using
records locks. If using records locks, than innodb
knows about two kind of locks: Exclusive and shared.
Last time I checked, innodb does not support record
‘update’ locks (which I think is kind of flawed).
So the practical explaination is:
SELECT … FOR UPDATE sets a XLOCK on the accessed record.
SELECT … LOCK IN SHARE MODE sets a SLOCK on the accessed record.
In both cases you will get serializability, but to improve
concurrency, one should only set a XLOCK if the transaction
actually updates/writes to the record.
i appreaciate so much your help darseq.:)
i’ll now continue my coding ..i’ve understand it clearly now…:)…
if i will encounter a problem..i will post again here..:)…
hope it would be a success^_^..
No problem, Keep in mind also that the
transaction isolation level must be set
to at least repeatable read to ensure
serializability. Lower levels allow for
more concurrency but then you will have
to check whether your transactions keep
the database in a consistent state. What
is considered ‘consistent’ is defined by
you as a programmer.
darseq,,
i’ve tried EXCLUSIVE and SHARED LOCK , and i found out..it wont work..i kinda get this error [#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘IN SHARE MODE’ at line 1]
my syntax is correct i use this one…LOCK TABLE hotels IN SHARE MODE and LOCK TABLE hotels IN EXCLUSIVE MODE…
..i test my code in xampp…try to query it.
and when i replace it with this one ..LOCK TABLE hotels WRITE..it works …also with READ..so strange….
i’ve read one of this article it says
so the transactional locks are converted into
standard READ|WRITE locks with a warning and then work as expected from the conventional
locks (or, if sql_mode is set to STRICT*, an attempt to acquire a transactional lock
returns an error).
heres the link if you want to see it..
http://bugs.mysql.com/bug.php?id=46663
i got it…it says their
LOCK TABLES … IN [EXCLUSIVE|SHARE] MODE syntax does not exist in 5.1
the version im using now is 5.1 :)…what a mess im doing with my code…its not the code is the problem..my version…lol..:)
another problem i encounter
$this->db->query(‘SET AUTOCOMMIT=0’);
$this->db->query(“LOCK TABLES hotels READ, location WRITE”);
$this->db->query(“SELECT hotel_id FROM hotels WHERE hotel_id = 1 “);
$query = $this->db->get();
$this->db->query(“UPDATE location SET location_id = 5 WHERE location_id = 1”);
$this->db->query(“UNLOCK TABLES”);
i can’t access to my hotels table..in my SELECT statement..please help to resolve this one..
Hi.
I need to select count of specific rows from table, then insert a new record with one field calculated using that value (something like auto_increment but for groups of records). Following queries doesn’t always work:
T1: BEGIN;
T2: BEGIN;
T1: SELECT COUNT(*) INTO @for_later_insert_t1 FROM table1 WHERE field1 = ‘not_existing_const’ AND field2 like ‘const_prefix_%’ FOR UPDATE; — (table1 has a key – field1, field2) selects 0.
T2: SELECT COUNT(*) INTO @for_later_insert_t2 FROM table1 WHERE field1 = ‘not_existing_const’ AND field2 like ‘const_prefix_%’ FOR UPDATE; — waits.
T1: INSERT INTO table1 SET field1 = ‘not_existing_const’, field2 = ‘const_prefix_1’, field3 = @for_later_insert_t1;
T1: COMMIT;
T2: — unblocks, @for_later_insert_t2 == 1
T2: INSERT INTO table1 SET field1 = ‘not_existing_const’, field2 = ‘const_prefix_1’, field3 = @for_later_insert_t2;
T2: COMMIT;
They doesn’t work:
* if field2 like ‘const_prefix_%’ is replaced with field2 = ‘const_prefix_1’ (SELECT … FOR UPDATE in that case isn’t locked, and insert causes deadlock).
* If the SELECT … FOR UPDATE in one transaction is using different key.
However those queries work if records with field1 = ‘not_existing_const’, field2 = ‘const_prefix_1’ already exist.
Why deadlock occurs if there are no such records?
Is it safe to retry the failed transaction? Or transaction is not meant to solve such problems and better use GET_LOCK()/RELEASE_LOCK()?
Mikhail, please use our forums for asking questions such as this.
Peter could you explain what does “modifiers can be user” mean?
And also s/peices/pieces/
SESSION1:
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
SESSION2:
mysql> select * from tst;
Empty set (0.00 sec)
It seems like if we issue a commit or rollback before ‘mysql> select * from tst;’ on SESSION2 it reads the committed record SESSION1 !?
That is, if we end the current transaction on SESSION2 it reads the committed record!?
I am not sure if it makes full sense to you experts but I wanted to write my observation.
I am a dba and new to innoDB ( mySql) and was testing it before moving forward with a design that I am working on. My research following this issue brought me to this site.
I have question, If I mark autocommit = false and lock the row, do some stuff, update row and mark commit
before commit, do my locked method is get by another thread ? as it’s in autocommit =false.
To avoid this do i need to lock and mark autocmmit=false and then update the row ?