source: trunk/sources/HeuristicLab.Grid/Database.cs @ 507

Last change on this file since 507 was 507, checked in by gkronber, 13 years ago

fixed a thread-safety bug in the database. write-lock was released before the transaction was committed (#197 Use SQLite backend to store waiting engines and results instead of in-memory dictionaries)

File size: 11.7 KB
Line 
1#region License Information
2/* HeuristicLab
3 * Copyright (C) 2002-2008 Heuristic and Evolutionary Algorithms Laboratory (HEAL)
4 *
5 * This file is part of HeuristicLab.
6 *
7 * HeuristicLab is free software: you can redistribute it and/or modify
8 * it under the terms of the GNU General Public License as published by
9 * the Free Software Foundation, either version 3 of the License, or
10 * (at your option) any later version.
11 *
12 * HeuristicLab is distributed in the hope that it will be useful,
13 * but WITHOUT ANY WARRANTY; without even the implied warranty of
14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
15 * GNU General Public License for more details.
16 *
17 * You should have received a copy of the GNU General Public License
18 * along with HeuristicLab. If not, see <http://www.gnu.org/licenses/>.
19 */
20#endregion
21
22using System;
23using System.Collections.Generic;
24using System.Linq;
25using System.Text;
26using System.Data;
27using System.Data.SQLite;
28using System.Data.Common;
29using System.Threading;
30
31namespace HeuristicLab.Grid {
32  class Database {
33    private string connectionString;
34    private ReaderWriterLockSlim rwLock;
35    public Database(string connectionString) {
36      this.connectionString = connectionString;
37      rwLock = new ReaderWriterLockSlim();
38    }
39
40    #region create empty database
41    public void CreateNew() {
42      rwLock.EnterWriteLock();
43      try {
44        using(DbConnection cnn = new SQLiteConnection(connectionString)) {
45          cnn.Open();
46          using(DbTransaction t = cnn.BeginTransaction()) {
47            using(DbCommand cmd = cnn.CreateCommand()) {
48              cmd.CommandText = "CREATE TABLE Job (ID integer primary key autoincrement, Guid text, Status text, CreationTime DateTime, StartTime DateTime, RawData blob)";
49              cmd.Transaction = t;
50              cmd.ExecuteNonQuery();
51            }
52            t.Commit();
53          }
54        }
55      } finally {
56        rwLock.ExitWriteLock();
57      }
58    }
59    #endregion
60
61    internal void InsertJob(Guid guid, JobState jobState, byte[] rawData) {
62      rwLock.EnterWriteLock();
63      try {
64        using(SQLiteConnection cnn = new SQLiteConnection(connectionString)) {
65          cnn.Open();
66          using(DbTransaction t = cnn.BeginTransaction()) {
67            using(DbCommand c = cnn.CreateCommand()) {
68              c.Transaction = t;
69              c.CommandText = "Insert into Job (Guid, Status, CreationTime, StartTime, RawData) values (@Guid, @Status, @CreationTime, @StartTime, @RawData)";
70              DbParameter guidParam = c.CreateParameter();
71              guidParam.ParameterName = "@Guid";
72              guidParam.Value = guid.ToString();
73              c.Parameters.Add(guidParam);
74              DbParameter statusParam = c.CreateParameter();
75              statusParam.ParameterName = "@Status";
76              statusParam.Value = jobState.ToString();
77              c.Parameters.Add(statusParam);
78              DbParameter creationParam = c.CreateParameter();
79              creationParam.ParameterName = "@CreationTime";
80              DateTime now = DateTime.Now;
81              creationParam.Value = now;
82              c.Parameters.Add(creationParam);
83              DbParameter startParam = c.CreateParameter();
84              startParam.ParameterName = "@StartTime";
85              if(jobState == JobState.Busy) startParam.Value = now;
86              else startParam.Value = null;
87              c.Parameters.Add(startParam);
88              DbParameter rawDataParam = c.CreateParameter();
89              rawDataParam.ParameterName = "@RawData";
90              rawDataParam.Value = rawData;
91              c.Parameters.Add(rawDataParam);
92              c.ExecuteNonQuery();
93            }
94            t.Commit();
95          }
96        }
97      } finally {
98        rwLock.ExitWriteLock();
99      }
100
101    }
102
103    internal JobEntry GetNextWaitingJob() {
104      rwLock.EnterUpgradeableReadLock();
105      try {
106        using(SQLiteConnection cnn = new SQLiteConnection(connectionString)) {
107          cnn.Open();
108          JobEntry job = new JobEntry();
109          using(DbTransaction t = cnn.BeginTransaction()) {
110            using(DbCommand c = cnn.CreateCommand()) {
111              c.Transaction = t;
112              c.CommandText = "select guid,creationtime,starttime,rawdata from job, (select id from job where status=@Status order by creationtime limit 1) as next where job.id=next.id";
113              DbParameter statusParameter = c.CreateParameter();
114              statusParameter.ParameterName = "@Status";
115              statusParameter.Value = JobState.Waiting.ToString();
116              c.Parameters.Add(statusParameter);
117              DbDataReader r = c.ExecuteReader();
118              if(!r.HasRows) {
119                r.Close();
120                t.Commit();
121                return null;
122              }
123              r.Read();             
124              job.Status = JobState.Busy;
125              job.Guid = r.GetGuid(0);
126              job.CreationTime = r.GetDateTime(1);
127              job.StartTime = r.IsDBNull(2) ? null : new Nullable<DateTime>(r.GetDateTime(2));
128              job.RawData = (byte[])r.GetValue(3);
129              r.Close();
130            }
131            rwLock.EnterWriteLock();
132            try {
133              using(DbCommand updateCmd = cnn.CreateCommand()) {
134                updateCmd.Transaction = t;
135                updateCmd.CommandText = "Update job set Status=@Status where Guid=@Guid";
136                DbParameter statusParam = updateCmd.CreateParameter();
137                statusParam.ParameterName = "@Status";
138                statusParam.Value = JobState.Busy.ToString();
139                DbParameter guidParam = updateCmd.CreateParameter();
140                guidParam.ParameterName = "@Guid";
141                guidParam.Value = job.Guid.ToString();
142                updateCmd.Parameters.Add(statusParam);
143                updateCmd.Parameters.Add(guidParam);
144                updateCmd.ExecuteNonQuery();
145              }
146              t.Commit();
147            } finally {
148              rwLock.ExitWriteLock();
149            }
150            return job;
151          }
152        }
153      } finally {
154        rwLock.ExitUpgradeableReadLock();
155      }
156    }
157
158    internal void SetJobResult(Guid guid, byte[] result) {
159      rwLock.EnterWriteLock();
160      try {
161        using(SQLiteConnection cnn = new SQLiteConnection(connectionString)) {
162          cnn.Open();
163          using(DbTransaction t = cnn.BeginTransaction()) {
164            using(DbCommand c = cnn.CreateCommand()) {
165              c.Transaction = t;
166              c.CommandText = "Update Job set Status=@Status, RawData=@RawData where Guid=@Guid";
167              DbParameter rawDataParam = c.CreateParameter();
168              DbParameter statusParam = c.CreateParameter();
169              DbParameter guidParam = c.CreateParameter();
170              rawDataParam.ParameterName = "@RawData";
171              rawDataParam.Value = result;
172              guidParam.ParameterName = "@Guid";
173              guidParam.Value = guid.ToString();
174              statusParam.ParameterName = "@Status";
175              statusParam.Value = JobState.Finished.ToString();
176              c.Parameters.Add(rawDataParam);
177              c.Parameters.Add(statusParam);
178              c.Parameters.Add(guidParam);
179              c.ExecuteNonQuery();
180            }
181            t.Commit();
182          }
183        }
184      } finally {
185        rwLock.ExitWriteLock();
186      }
187    }
188
189    internal void UpdateJobState(Guid guid, JobState jobState) {
190      rwLock.EnterWriteLock();
191      try {
192        using(SQLiteConnection cnn = new SQLiteConnection(connectionString)) {
193          cnn.Open();
194          using(DbTransaction t = cnn.BeginTransaction()) {
195            using(DbCommand c = cnn.CreateCommand()) {
196              c.Transaction = t;
197              c.CommandText = "Update Job set Status=@Status, StartTime=@StartTime where Guid=@Guid";
198              DbParameter statusParam = c.CreateParameter();
199              DbParameter startTimeParam = c.CreateParameter();
200              DbParameter guidParam = c.CreateParameter();
201              startTimeParam.ParameterName = "@StartTime";
202              if(jobState == JobState.Busy)
203                startTimeParam.Value = DateTime.Now;
204              else
205                startTimeParam.Value = null;
206              guidParam.ParameterName = "@Guid";
207              guidParam.Value = guid.ToString();
208              statusParam.ParameterName = "@Status";
209              statusParam.Value = jobState.ToString();
210              c.Parameters.Add(startTimeParam);
211              c.Parameters.Add(statusParam);
212              c.Parameters.Add(guidParam);
213              c.ExecuteNonQuery();
214            }
215            t.Commit();
216          }
217        }
218      } finally {
219        rwLock.ExitWriteLock();
220      }
221    }
222
223
224    internal JobEntry GetJob(Guid guid) {
225      rwLock.EnterReadLock();
226      try {
227        using(SQLiteConnection cnn = new SQLiteConnection(connectionString)) {
228          cnn.Open();
229          DbCommand c = cnn.CreateCommand();
230          c.CommandText = "Select Status, CreationTime, StartTime, Rawdata from Job where Guid=@Guid";
231          DbParameter guidParameter = c.CreateParameter();
232          guidParameter.ParameterName = "@Guid";
233          guidParameter.Value = guid.ToString();
234          c.Parameters.Add(guidParameter);
235          DbDataReader r = c.ExecuteReader();
236          if(r.HasRows) {
237            r.Read();
238            JobEntry job = new JobEntry();
239            job.Guid = guid;
240            job.Status = (JobState)Enum.Parse(typeof(JobState), r.GetString(0));
241            job.CreationTime = r.GetDateTime(1);
242            job.StartTime = r.IsDBNull(2) ? null : new Nullable<DateTime>(r.GetDateTime(2));
243            job.RawData = (byte[])r.GetValue(3);
244            return job;
245          }
246          r.Close();
247        }
248      } finally {
249        rwLock.ExitReadLock();
250      }
251      return null;
252    }
253
254    internal JobState GetJobState(Guid guid) {
255      rwLock.EnterReadLock();
256      try {
257        using(SQLiteConnection cnn = new SQLiteConnection(connectionString)) {
258          cnn.Open();
259          DbCommand c = cnn.CreateCommand();
260          c.CommandText = "Select Status from Job where Guid=@Guid";
261          DbParameter guidParameter = c.CreateParameter();
262          guidParameter.ParameterName = "@Guid";
263          guidParameter.Value = guid.ToString();
264          c.Parameters.Add(guidParameter);
265          DbDataReader r = c.ExecuteReader();
266          if(r.HasRows) {
267            r.Read();
268            return (JobState)Enum.Parse(typeof(JobState), r.GetString(0));
269          }
270          r.Close();
271        }
272      } finally {
273        rwLock.ExitReadLock();
274      }
275      return JobState.Unknown;
276    }
277
278    internal long GetJobCount(JobState status) {
279      rwLock.EnterReadLock();
280      try {
281        using(SQLiteConnection cnn = new SQLiteConnection(connectionString)) {
282          cnn.Open();
283          DbCommand c = cnn.CreateCommand();
284          c.CommandText = "Select Count(id) from Job where Status=@Status";
285          DbParameter stateParameter = c.CreateParameter();
286          stateParameter.ParameterName = "@Status";
287          stateParameter.Value = status.ToString();
288          c.Parameters.Add(stateParameter);
289          return (long)c.ExecuteScalar();
290        }
291      } finally {
292        rwLock.ExitReadLock();
293      }
294    }
295
296    /// <summary>
297    /// Does nothing right now (= running jobs that disappear are never restarted).
298    /// </summary>
299    internal void RestartExpiredActiveJobs() {
300    }
301
302    /// <summary>
303    /// Does nothing right now (= results are never deleted).
304    /// </summary>
305    internal void DeleteExpiredResults() {
306    }
307  }
308}
Note: See TracBrowser for help on using the repository browser.