Re: Lock Tables Question
I think you're missing the concept of a transaction in the database sense. The idea behind a transaction is that you can perform multiple steps and if you don't complete all steps, any changes are reversed. The reversal process is handled by the database. A good example is moving money from bank account A to B. You specify how much to move and that amount is debited from account A and credited to account B, 2 steps. If the first step happens, but not the second, then the first step needs to be reversed. Until the transaction is complete, anything querying the data needs to see bank account in it's state before any transaction started, a type of versioning. You seem to be trying implement all this manually, which you would need to do if you are using MyISAM based tables. But you may not need to use transactions at all if your data does not have real time dependencies. On 6/4/07, David T. Ashley [EMAIL PROTECTED] wrote: I decided to go with a simple paradigm for my web-based database. Rather than transactions, each process locks the entire database while it is changing something, then unlocks it. This just serializes access (all other processes will block until the one modifying the database has finished). The method I was using is something like: LOCK TABLE thistable, thattable, theothertable, goshthislistcangetlongtable; Do whatever is needed; UNLOCK TABLES; I probably botched the syntax above. Rather than enumerate every table in the database (which could get to be a long list), I wondered if it is possible to just lock one table, with the gentleman's agreement that in order to modify the database, every process must first lock that particular table. For example: #1)LOCK TABLE x; #2)Make modifications to tables x, y, and z; #3)UNLOCK TABLES; Are there any race conditions in just using one table for this purpose? For example, SQL guarantees that a given SQL statement is atomic. But it is guaranteed that #2 will complete before #3 above? If every process uses the same rule, can anything unexpected happen? One more note: I'm sure that many of the skilled users on this list will be tempted to advocate more sophisticated methods. I appreciate all advice, but I'm just looking for an easy way to serialize access to my database and guarantee mutual exclusion. Each operation I want to do would take at most half a second, so another web process waiting that long won't make a difference. Simpler is easier for me. Thanks.
Re: Lock Tables Question
On 6/5/07, Brent Baisley [EMAIL PROTECTED] wrote: I think you're missing the concept of a transaction in the database sense. The idea behind a transaction is that you can perform multiple steps and if you don't complete all steps, any changes are reversed. The reversal process is handled by the database. A good example is moving money from bank account A to B. You specify how much to move and that amount is debited from account A and credited to account B, 2 steps. If the first step happens, but not the second, then the first step needs to be reversed. Until the transaction is complete, anything querying the data needs to see bank account in it's state before any transaction started, a type of versioning. You seem to be trying implement all this manually, which you would need to do if you are using MyISAM based tables. But you may not need to use transactions at all if your data does not have real time dependencies. I knew somebody was going to say this. Here is the relevant prose from my original post. BEGIN One more note: I'm sure that many of the skilled users on this list will be tempted to advocate more sophisticated methods. I appreciate all advice, but I'm just looking for an easy way to serialize access to my database and guarantee mutual exclusion. Each operation I want to do would take at most half a second, so another web process waiting that long won't make a difference. Simpler is easier for me. END There is no concept that I'm missing. I understand what a transaction is. But I just don't want to bothered. My application is simple enough that bogarting the database until all necessary modifications have been made and the tables are again consistent is good enough. Collisions are handled by serialization. Period. Somebody wins. Everyone else waits. Works for me.
Re: Lock Tables Question
David T. Ashley wrote:
There is no concept that I'm missing. I understand what a transaction is.
But I just don't want to bothered. My application is simple enough that
bogarting the database until all necessary modifications have been made and
the tables are again consistent is good enough.
Collisions are handled by serialization. Period. Somebody wins. Everyone
else waits. Works for me.
Then the simplest possible thing to do (besides using transactions, which IMO would
actually be a LOT less bother!) is use GET_LOCK('database_name'). That should handle
your requirement to make locks 'database-local.'
In my experience, using LOCK TABLES becomes a spaghetti problem that begins to involve
more and more things until you are going through *serious* contortions. I would avoid
it at all costs.
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock Tables Question
On 6/5/07, Baron Schwartz [EMAIL PROTECTED] wrote:
David T. Ashley wrote:
There is no concept that I'm missing. I understand what a transaction
is.
But I just don't want to bothered. My application is simple enough that
bogarting the database until all necessary modifications have been made
and
the tables are again consistent is good enough.
Collisions are handled by serialization. Period. Somebody
wins. Everyone
else waits. Works for me.
Then the simplest possible thing to do (besides using transactions, which
IMO would
actually be a LOT less bother!) is use GET_LOCK('database_name'). That
should handle
your requirement to make locks 'database-local.'
In my experience, using LOCK TABLES becomes a spaghetti problem that
begins to involve
more and more things until you are going through *serious* contortions. I
would avoid
it at all costs.
My only concern with GET_LOCK() is that lock is server-global rather than
database-global. This makes attacks possible in a shared setting (some bad
person could disable your database code by going after your lock).
My solution is just to lock all tables in one statement.
The only question I have (and nobody has answered this) is how many tables I
can include in a single LOCK TABLE statement. I thinking anything up to a
few thousand shouldn't be a problem. What is the limit?
Thanks, Dave.
Re: Lock Tables Question
Hi David, On Jun 5, 2007, at 3:55 PM, David T. Ashley wrote: My only concern with GET_LOCK() is that lock is server-global rather than database-global. This makes attacks possible in a shared setting (some bad person could disable your database code by going after your lock). My solution is just to lock all tables in one statement. The only question I have (and nobody has answered this) is how many tables I can include in a single LOCK TABLE statement. I thinking anything up to a few thousand shouldn't be a problem. What is the limit? The size of a query is limited by the value of the max_allowed_packet system parameter. So if you want to add more tables to your LOCK TABLE statement, you should just be able to increase max_allowed_packet until it works. Regards, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Lock Tables Question
I decided to go with a simple paradigm for my web-based database. Rather than transactions, each process locks the entire database while it is changing something, then unlocks it. This just serializes access (all other processes will block until the one modifying the database has finished). The method I was using is something like: LOCK TABLE thistable, thattable, theothertable, goshthislistcangetlongtable; Do whatever is needed; UNLOCK TABLES; I probably botched the syntax above. Rather than enumerate every table in the database (which could get to be a long list), I wondered if it is possible to just lock one table, with the gentleman's agreement that in order to modify the database, every process must first lock that particular table. For example: #1)LOCK TABLE x; #2)Make modifications to tables x, y, and z; #3)UNLOCK TABLES; Are there any race conditions in just using one table for this purpose? For example, SQL guarantees that a given SQL statement is atomic. But it is guaranteed that #2 will complete before #3 above? If every process uses the same rule, can anything unexpected happen? One more note: I'm sure that many of the skilled users on this list will be tempted to advocate more sophisticated methods. I appreciate all advice, but I'm just looking for an easy way to serialize access to my database and guarantee mutual exclusion. Each operation I want to do would take at most half a second, so another web process waiting that long won't make a difference. Simpler is easier for me. Thanks.
Re: Lock Tables Question
David T. Ashley wrote: I decided to go with a simple paradigm for my web-based database. Rather than transactions, each process locks the entire database while it is changing something, then unlocks it. This just serializes access (all other processes will block until the one modifying the database has finished). The method I was using is something like: LOCK TABLE thistable, thattable, theothertable, goshthislistcangetlongtable; Do whatever is needed; UNLOCK TABLES; I probably botched the syntax above. Rather than enumerate every table in the database (which could get to be a long list), I wondered if it is possible to just lock one table, with the gentleman's agreement that in order to modify the database, every process must first lock that particular table. You could use a string lock for this. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock Tables Question
On 6/4/07, Gerald L. Clark [EMAIL PROTECTED] wrote: David T. Ashley wrote: LOCK TABLE thistable, thattable, theothertable, goshthislistcangetlongtable; Do whatever is needed; UNLOCK TABLES; You could use a string lock for this. Thanks for the suggestion. It looks logically correct. I'd like to stay away from a string lock if possible because other database users could interfere with it (it is server global, and not tied to the database). My original question is still of interest to me ... Thanks.
RE: Lock Tables Question
Whatever you do, make sure that every bit of code that locks multiple resources locks them in the same order. That's the only way to avoid deadlocks. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: David T. Ashley [mailto:[EMAIL PROTECTED] Sent: Monday, June 04, 2007 3:54 PM To: mysql@lists.mysql.com Subject: Re: Lock Tables Question On 6/4/07, Gerald L. Clark [EMAIL PROTECTED] wrote: David T. Ashley wrote: LOCK TABLE thistable, thattable, theothertable, goshthislistcangetlongtable; Do whatever is needed; UNLOCK TABLES; You could use a string lock for this. Thanks for the suggestion. It looks logically correct. I'd like to stay away from a string lock if possible because other database users could interfere with it (it is server global, and not tied to the database). My original question is still of interest to me ... Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock Tables Question
On 6/4/07, Jerry Schwartz [EMAIL PROTECTED] wrote: Whatever you do, make sure that every bit of code that locks multiple resources locks them in the same order. That's the only way to avoid deadlocks. Hi Jerry, I really appreciate the good advice. However, my original question is still unanswered. My original question is whether I can lock one table (as a gentleman's rule followed by all processes) to serialize access to the database consisting of many tables. LOCK TABLE x; Manipulate many tables, perhaps not even including table x; UNLOCK TABLES; My question is really whether MySQL might do some strange optimizations ... or somehow buffer the middle query so that it completes after the UNLOCK. Thanks, Dave.
Re: Lock Tables Question
David T. Ashley wrote: On 6/4/07, Jerry Schwartz [EMAIL PROTECTED] wrote: Whatever you do, make sure that every bit of code that locks multiple resources locks them in the same order. That's the only way to avoid deadlocks. Hi Jerry, I really appreciate the good advice. However, my original question is still unanswered. My original question is whether I can lock one table (as a gentleman's rule followed by all processes) to serialize access to the database consisting of many tables. LOCK TABLE x; Manipulate many tables, perhaps not even including table x; UNLOCK TABLES; My question is really whether MySQL might do some strange optimizations ... or somehow buffer the middle query so that it completes after the UNLOCK. Thanks, Dave. Once you issue a LOCK TABLES command, you may not access any tables not in the LOCK statement. You must lock *ALL* tables you will use, perform your updates, and then UNLOCK TABLES. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lock Tables Question
Once you issue a LOCK TABLES command, you may not access any tables not in the LOCK statement. You must lock *ALL* tables you will use, perform your updates, and then UNLOCK TABLES. I didn't know that. I reviewed the documentation. Thanks. OK, then my only remaining question is how many tables I can lock in a single SQL statement. I'm guessing no practical limit (i.e. thousands). What is the maximum length of a MySQL statement, anyway? Thanks.
